Re: query optimization question

In article <G6zvw8.5Kr_at_world.std.com>,
  modrall_at_world.std.com (Mark W Modrall) wrote:
> 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
>
>

x.b is not null is, regrettably, incorrect It should be x.b > 0 or x.b > chr(0)
However this is a typical Rule Based Optimizer tric (tm). Are you still using it?
Welcome in the Wonderful World of Oracle :)

BTW *my* charge is only $100 per hour.

Hth,

--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply
------------------------------------------------------------------------


Sent via Deja.com
http://www.deja.com/