Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: INDEX Question

Re: INDEX Question

From: <meinrad.teufel_at_frqnet.de>
Date: Fri, 19 Feb 1999 09:49:10 GMT
Message-ID: <7ajc2k$75d$1@nnrp1.dejanews.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US