Re: Explain: In List Iterator

"Baker, Barbara" wrote:
>
> Hi, list.
> I'm trying to find out what's eating my system. I found the query, and
> explain'ed it. I've never seen an "in list iterator" before. Can anyone
> tell me what that is???
>
> Thanks!!
>
> Barb
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2398 Card=25814
> Bytes=9964204)
> 1 0 SORT (UNIQUE) (Cost=2398 Card=25814 Bytes=9964204)
> 2 1 MERGE JOIN (CARTESIAN) (Cost=969 Card=25814 Bytes=9964204)
> 3 2 NESTED LOOPS (OUTER) (Cost=1 Card=1 Bytes=380)
> 4 3 NESTED LOOPS (Cost=8 Card=1 Bytes=324)
> 5 4 INLIST ITERATOR (CONCATENATED)
> 6 5 TABLE ACCESS (BY INDEX ROWID) OF 'AD' (Cost=1590
> Card=145 Bytes=20300)
> 7 6 BITMAP CONVERSION (TO ROWIDS)
> 8 7 BITMAP CONVERSION (FROM ROWIDS)
> 9 8 INDEX (RANGE SCAN) OF 'I_AD2' (NON-UNIQUE)
> 10 4 TABLE ACCESS (BY INDEX ROWID) OF 'PUB' (Cost=1 Card=3600
> Bytes=662400)
> 11 10 INDEX (UNIQUE SCAN) OF 'I_PUB1' (UNIQUE)
> 12 3 TABLE ACCESS (BY INDEX ROWID) OF 'INVROWS' (Cost=1
> Card=627725 Bytes=35152600)
> 13 12 INDEX (RANGE SCAN) OF 'I_INVR1' (NON-UNIQUE) (Cost =1
> Card=627725)
> 14 2 SORT (JOIN) (Cost=968 Card=25814 Bytes=154884)
> 15 14 TABLE ACCESS (FULL) OF 'CUSTOMER' (Cost=968 Card=25814
> Bytes=154884)
>
> SELECT DISTINCT a.serieno, p.paper, p.adno, p.pubno, p.class, p.xsize,
> p.ysize,
> p.state, trunc(p.mdate) mdate, p.vno, a.unet, trunc(a.rdate) rdate,
> trunc(a.startdate) startdate, trunc(a.enddate) enddate, a.ratecode,
> a.cus4name,
> a.cus2no, i.dcode, i.ino FROM arcdb.ad a, arcdb.pub p, arcdb.invrows i,
> advdb.customer c WHERE p.adno = a.adno AND p.vno = a.vno AND p.adno =
> i.adno(+)
> AND p.vno = i.vno(+) AND p.pubno = i.pubno(+) AND a.serieno IN
> (7,8,24,31,33,39,52,58,60,63,137,144,419,434,440,444,445,451,463) AND
> p.paper =
> 'DNA' AND p.state IN ('NOT', 'VAR') AND p.adno > 0 AND p.pubno = 1 AND
> p.vnoflag
> = 'Y'AND c.cgrno = 'TT' AND trunc(a.rdate) BETWEEN to_date('01-MAR-02',
> 'DD-MON-YY') AND to_date('07-MAR-02', 'DD-MON-YY')
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --

Iteration on your IN (blah, blah, blah ...). Let me guess, you have lots of customers?

Step one : get rid of DISTINCT, either by adding the missing joind condition, or by having the relevant condition relegated to a AND EXISTS (blah blah). Then you'l be able to start the serious work.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).