Re: how to change this query, so that it will not use the correlation?

From: Prince Kumar <gspk_at_yahoo.com>
Date: 24 Sep 2003 15:55:14 -0700
Message-ID: <629275ba.0309241455.43052d42_at_posting.google.com>

Daniel!

Thanks for the response.

I know the number of rows returned is irrelevent. I shouldn't have have that information there.

EXISTS, wouldn't work either in this case. I believe, I somehow have to get rid of the correlation, to have the best response time. [All the stats and indexes are in place]. The table MRECORD has many rows (a few million) and when the "WHERE" condition (line# 30 to 32) is applied, it gets only a few hundred rows.

Anyway, I have the query attached with explain plan.

  1  select     record#,entity, (select nvl(sum(amount),0)
  2                     from    entryitems
  3                     where   comp# = 1234 and
  4                             paymentdate <= sysdate and
  5                             state = 'X' and
  6                             (entryitems.payKey = mr.record#
  7                                     or
  8                             entryitems.payKey in
  9                                     (select record# from mrecord
childrec
 10                                     where   childrec.comp# = 1234
and
 11                                             childrec.parentent =
mr.record#
 12                                     )
 13                             )
 14                     ) neg,
 15                     (select nvl(sum(amount),0)
 16                     from    entryitems
 17                     where   comp# = 1234  and
 18                             paymentdate <= sysdate and
 19                             state = 'X' and
 20                             (entryitems.recKey = mr.record#
 21                                     or
 22                             entryitems.recKey in
 23                                     (select record# from mrecord
childrec
 24                                     where   childrec.comp# = 1234
and
 25                                             childrec.parentent =
mr.record#
 26                                     )
 27                             )
 28                     )    pos
 29  from mrecord mr
 30  where	mr.comp# = 1234 and
 31		mr.whencreated <= sysdate and
 32*		mr.type in ('A','B')

/

OPS$GS> l  

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=89 Bytes=2314)

   1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'ENTRYITEMS' (Cost=63
Card=72 Bytes=1440)
   4    3         INDEX (RANGE SCAN) OF 'IX_ENTRYITEMS_PARENTENT'
(NON-UNIQUE) (Cost=12 Card=1454)
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'MRECORD' (Cost=3
Card=1 Bytes=10)
   6    5         INDEX (UNIQUE SCAN) OF 'PK_MRECORD' (UNIQUE) (Cost=2

Card=2884447)

   7 0 SORT (AGGREGATE)

   8    7     FILTER
   9    8       TABLE ACCESS (BY INDEX ROWID) OF 'ENTRYITEMS' (Cost=63
Card=72 Bytes=1440)
  10    9         INDEX (RANGE SCAN) OF 'IX_ENTRYITEMS_PARENTENT'
(NON-UNIQUE) (Cost=12 Card=1454)
  11    8       TABLE ACCESS (BY INDEX ROWID) OF 'MRECORD' (Cost=3
Card=1 Bytes=10)
  12   11         INDEX (UNIQUE SCAN) OF 'PK_MRECORD' (UNIQUE) (Cost=2

Card=2884447)
  13 0 INLIST ITERATOR
  14 13 TABLE ACCESS (BY INDEX ROWID) OF 'MRECORD' (Cost=38 Card=89 Bytes=2314)
  15 14 INDEX (RANGE SCAN) OF 'IX_MRECORD_CLRDATE' (NON-UNIQUE) (Cost=5 Card=106)

Statistics

---

          0  recursive calls
          4  db block gets
   28024824  consistent gets
         48  physical reads
          0  redo size
      19852  bytes sent via SQL*Net to client
        961  bytes received via SQL*Net from client
         44  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        630  rows processed

danielroy10junk_at_hotmail.com (Daniel Roy) wrote in message news:<3722db.0309240603.66ebfa5_at_posting.google.com>...
> Try replacing the "in" by an "exists", as it can perform much better,
> especially if the list of possible values is large. I can't think of
> any way to remove the "or", but I've never seen an "or" slow down a
> select statement considerably. Note also that if the query returns 500
> rows, it doesn't mean necessarily that it should run fast. What
> determines the speed is the number of rows it has to go through, and
> not the number of rows returned. It would be much easier for us to
> help if we had the execution plan. Also make sure that your stats are
> up-to-date.
>
> Daniel
Received on Thu Sep 25 2003 - 00:55:14 CEST