Re: query optimization question

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...

>