Re: Speed up query

From: astalavista <nobody_at_nowhere.com>
Date: Sat, 11 Oct 2008 17:14:35 +0200
Message-ID: <48f0c2da$0$6845$426a34cc@news.free.fr>

Thanks,
Below the output form autotrace:

971 rows selected.

Elapsed: 00:03:05.04

Execution Plan

---

          0
SELECT STATEMENT Optimizer=CHOOSE (Cost=910 Card=1 Bytes=283)

          1                  0

  SORT (ORDER BY) (Cost=910 Card=1 Bytes=283)

          2                  1

    COUNT

          3                  2
      FILTER


          4                  3
        NESTED LOOPS (Cost=907 Card=1 Bytes=283)


          5                  4
          NESTED LOOPS (Cost=905 Card=1 Bytes=241)


          6                  5
            NESTED LOOPS (Cost=904 Card=1 Bytes=199)


          7                  6
              TABLE ACCESS (FULL) OF 'PFMQ_MESSAGESTATUS' (Cost=902 Card=1 

Bytes=53)

          8                  6
              TABLE ACCESS (BY INDEX ROWID) OF 'PFMQ_MESSAGEINFO' (Cost=2 

Card=1 Bytes=146)

          9                  8
                INDEX (UNIQUE SCAN) OF 'XPKPF_MESSAGEINFO' (UNIQUE) (Cost=1 

Card=36)

         10                  5
            INDEX (UNIQUE SCAN) OF 'XPKPF_MESSAGEDATA' (UNIQUE) (Cost=1 

Card=1 Bytes=42)

         11                  4
          TABLE ACCESS (BY INDEX ROWID) OF 'PFMQ_MESSAGEDATASTORAGE' (Cost=2 

Card=1 Bytes=42)

         12                 11
            INDEX (UNIQUE SCAN) OF 'XPKPF_MESSAGEDATASTORAGE' (UNIQUE) 

(Cost=1 Card=1)

         13                  3
        SORT (AGGREGATE)


         14                 13
          FIRST ROW (Cost=3 Card=2 Bytes=100)


         15                 14
            INDEX (RANGE SCAN (MIN/MAX)) OF 'XPKPF_MESSAGESTATUS' (UNIQUE) 

(Cost=3 Card=570557)

Statistics

---

          0  recursive calls
          0  db block gets
    1132925  consistent gets
      55878  physical reads
          0  redo size
     387012  bytes sent via SQL*Net to client
       1207  bytes received via SQL*Net from client
         66  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        971  rows processed

Received on Sat Oct 11 2008 - 10:14:35 CDT