Re: ORDER BY performance

First, nested loops can be really ugly depending on the size of your tables. You may want to force a hash join with the "USE_HASH" hint.

The problem could also be that you do not have enough memory allocated for the sort. You should check your "sort_area_size" in the v$parameter view and make sure it is large enough. If you use the hash join, you should also check the "hash_area_size" in the v$parameter table. These values can be adjusted via your init*.ora file or via the "alter system" command depending on which version of Oracle you're running.

"Mark W Modrall" <modrall_at_world.std.com> wrote in message news:Fz502t.Mou_at_world.std.com...
> 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