Home -> Usenet -> comp.databases.oracle -> ORDER BY performance

ORDER BY performance

From: Mark W Modrall <modrall_at_world.std.com>
Date: 2000/08/11
Message-ID: <Fz502t.Mou_at_world.std.com>#1/1 






Hi...



        we're trying to eke out as much performance as possible
from what appears to be a simple query.  We've got 2 tables, one
with most of the salient row properites and another with some
normalized, many-to-one values.  We want to select on the
normalized table based on some attribute and then get most of
the columns from the main table and order the results by a date
column...  ie



SELECT * from a,b WHERE b.select=x AND b.fk=a.pk ORDER BY a.date;



        we've been tweaking with hints and clause ordering, etc
and don't see anything else we can do for it...  we're down to
an explain plan that looks like



SELECT STATEMENT   Cost =


  SORT ORDER BY INDX_ADATE Cost = 1


    NESTED LOOPS   Cost = 1

      INDEX RANGE SCAN PK_B Cost = 1
      TABLE ACCESS BY INDEX ROWID A Cost = 2
        INDEX RANGE SCAN PK_A Cost = 1





6 rows selected.



which is pretty succinct...  What we're down to, though, is some
experimental data suggesting that the sort gets considerable more
expensive the more columns you have in the result record.  I would
have thought that sorting a temp result set wouldn't be implemented
to shuffle all the bytes of the result records when ordering, but
it looks like that's what's happening...



        Are there any hints to get it to do more efficient sorts?
I.e. just sort a list of temp ROWNUMs and return them in that order
rather than shuffling the whole pile?



        We tried implementing it as



SELECT * from a WHERE EXISTS (select * from b WHERE b.select=x AND


        b.fk=a.pk) ORDER BY a.date;



and got a nice, short plan doing 2 index walks, but the problem
was that it was walking all of a.date first, and then looking for
EXISTS in b where the b test is much more restrictive.  I added
a hint to the second query to PUSH_SUBQ and we ended up back
with the first explain plan again.



        So, just to reiterate, is the Oracle SORT operation really
inefficent in that it's shuffling all the bytes to do a sort?  Is
there any way to get it to do more efficient sorting?



thanks


-mark
Received on Fri Aug 11 2000 - 00:00:00 CEST












Original text of this message