query optimization question

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

        anyone out there have better advice than the oracle racketeers?

thanks
-mark Received on Thu Jan 11 2001 - 12:00:07 CET