"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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).