Which version of Oracle ?
Running Rule based of Cost based ?
Is x.b declared as a NOT NULL column ?
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
Practical Oracle 8i: Building Efficient Databases
Publishers: Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
Mark W Modrall wrote in message ...
>Hi...
>
> we've got a couple of tables and some indexes and we're
>having trouble getting the following query to make use of the
>indexes:
>
>select * from x,y where x.a = y.a order by x.b desc;
>
>x.b has the index on it already...
>
> we had high-priced oracle consultants tell us there was
>no way to get the index in on the act unless you add a bogus
>where clause on x.b, e.g.
>
>select * from x,y where x.b is not null and x.a=y.a order by x.b;
>
> we'd had no luck trying with a couple of hints to get
>the index used in the first case, but it seems ridiculous to have
>to create a bogus where clause to get the optimizer to recognize
>the index...
>
Received on Thu Jan 11 2001 - 05:34:39 CST
