Re: query optimization question
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