ORDER BY performance

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 CDT