Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: INDEX Question
Hi,
Thank you very much for your interest.
The result can be of 250000 sets and it needs about 3 minutes (only selecting, not fetching). If I take the same statement without order by it takes about 3s.
One index is the attribute time_stamp which I use it in order by.
Execution Plan
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1824 Card=6546 B ytes=477858)
1 0 SORT (ORDER BY)
2 1 UNION-ALL 3 2 FILTER 4 3 TABLE ACCESS (FULL) OF 'MESSAGE' (Cost=1117 Card=5455 Bytes=283660) 5 3 NESTED LOOPS (Cost=1 Card=1 Bytes=126) 6 5 TABLE ACCESS (FULL) OF 'DISPLAY' (Cost=1 Card=1 Bytes=100) 7 5 INDEX (UNIQUE SCAN) OF 'MESSAGEDISPLAY_PK' (UNIQUE) 8 2 NESTED LOOPS (Cost=4 Card=1091 Bytes=194198) 9 8 NESTED LOOPS (Cost=2 Card=1 Bytes=126) 10 9 TABLE ACCESS (FULL) OF 'DISPLAY' (Cost=1 Card=1 Bytes=100) 11 9 TABLE ACCESS (FULL) OF 'MESSAGEDISPLAY' 12 8 TABLE ACCESS (BY ROWID) OF 'MESSAGE' 13 12 INDEX (UNIQUE SCAN) OF 'MESSAGE_PK' (UNIQUE)
Statistics
1079 recursive calls 528520 db block gets 548322 consistent gets 25731 physical reads 22484 redo size 6523499 bytes sent via SQL*Net to client 193438 bytes received via SQL*Net from client 17505 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 262543 rows processed
I tried to rewrite the statement, but I didn't find a solution without UNION. So I enlarged the SORT_AREA_SIZE to a size with the best performance, but it is not good enough. Are there more Parameters which supports the sorting?
Thanks,
Meinrad
In article <7a9se1$vqd$1_at_nnrp1.dejanews.com>,
andrewf_at_jaredgroup.com wrote:
> Can you provide more detail? How big is the result set? How slow is the
> execution? What's the index and what's the plan?
>
> From the info available, if a SORT is the reason of slowness, you may want to
> tune the system to better support SORT. But a better solution may be to
> rewrite your SQL to get rid of Union.
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Feb 19 1999 - 03:49:10 CST