Re: query optimization question

From: Stephan Born <stephan.born_at_beusen.de>
Date: Thu, 11 Jan 2001 18:04:05 +0100
Message-ID: <3A5DE785.EE2B6FE5_at_beusen.de>


Mark W Modrall wrote:
> 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?

No, the 'order by'-clause seems not to be a hint for choosing an index for oracle...at least for Ora 7.3.4....but I dont believe that this will be changed in the upper versions...

The 'order by' is executet AFTER the joins...you can see this when you include the pseudo-column 'rowid' into your statement....the order of rownum is not '1, 2, 3, 4, ....' as you would expect it when you order the resultset with 'order by'.

So, the joining is done without any index....and it seems ok for me since
there is NO limitation on the data for any table...

I did the statement with several options:

index on x(b) as you did it
index on x(b, a)

with and without analyzing the data (to see whether the different optimizers
show differenet execution-plans)

Nothing brings the result you want.

BUT: the execution-plans didn't tell anything about the way the ordering is done.
Maybe Oracle does use the index for that task...but I dont think so.

Regards, Stephan Received on Thu Jan 11 2001 - 18:04:05 CET

Original text of this message