Statement with Order by

From: Meinrad Teufel <Meinrad.Teufel_at_frqnet.de>
Date: Tue, 25 Aug 1998 13:17:14 +0200
Message-ID: <35E29D39.C31BD969@frqnet.de>

Hi,

What can I do to short the response time of an select statement with order by.
I made some tests with and without order by . Of course there are some differences.
For selecting of 26000 rows, it needs about 35s with order by and without < 1s (only selecting not fetching). I think the difference is too big.

Here is my Select Statement with the Execution Plan.

SELECT 0 AS DL_STATE, MSG_ID, TIMESTAMP, MSG_TYP, TEXT FROM MESSAGE WHERE

        NOT EXISTS (
                                SELECT MSG_ID FROM MESSAGEDISPLAY WHERE
                                    MESSAGE.MSG_ID=MESSAGEDISPLAY.MSG_ID

AND
                                    EXISTS (
                                                SELECT DP_ID FROM

DISPLAY WHERE MESSAGEDISPLAY.DP_ID=DISPLAY.DP_ID AND

                                                    DP_NAME =
'testdisplay')) AND
        TIMESTAMP >= 900000000 AND
        MSG_TYP IN ( 'Typ1',Typ2' )

UNION ALL
SELECT 1 AS DL_STATE, m.MSG_ID, m.TIMESTAMP, m.MSG_TYP,m.TEXT FROM MESSAGE m,MESSAGEDISPLAY md, DISPLAY d WHERE

        m.MSG_ID  = md.MSG_ID AND
        md.DP_ID  = d.DP_ID AND
        d.DP_NAME = 'testdisplay' AND
        m.TIMESTAMP >= 9000000000 AND
        m.MSG_TYP IN ( 'Typ1'.'Typ2' )
        ORDER BY TIMESTAMP DESC;

Execution Plan

---

   0 SELECT STATEMENT Cost=200 Optimizer=CHOOSE    1 0 SORT (ORDER BY)

   2    1     UNION-ALL
   3    2       FILTER
   4    3         TABLE ACCESS (BY ROWID) OF 'MESSAGE'
   5    4           INDEX (RANGE SCAN) OF 'IDX_TIMESTAMP'
   6    3         NESTED LOOPS
   7    6           TABLE ACCESS (FULL) OF 'DISPLAY'
   8    6           INDEX (UNIQUE SCAN) OF 'MESSAGEDISPLAY_PK'
   9    2       NESTED LOOPS
  10    9         MERGE JOIN (CARTESIAN)
  11   10           TABLE ACCESS (BY ROWID) OF 'MESSAGE'
  12   11             INDEX (RANGE SCAN) OF 'IDX_TIMESTAMP'
  13   10           SORT (JOIN)
  14   13             TABLE ACCESS (FULL) OF 'DISPLAY'
  15    9         INDEX (UNIQUE SCAN) OF 'MESSAGEDISPLAY_PK'

Statistics

---

       1016  recursive calls
      62037  db block gets
     132756  consistent gets
       7245  physical reads
      19098  redo size
    4146453  bytes sent via SQL*Net to client
      20393  bytes received via SQL*Net from client
       1764  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      26423  rows processed



------------------------------------------------------------

Thanks much
Meini Received on Tue Aug 25 1998 - 06:17:14 CDT