Wow. Can't believe I didn't see that. She's grabbing every transient customer (cgrno is the customer type), and is not matching any other criteria.
This list never ceases to amaze me . . . Thanks. Barb > ---------- > From: Freeman, Robert [SMTP:[EMAIL PROTECTED]] > Reply To: [EMAIL PROTECTED] > Sent: Tuesday, April 09, 2002 4:13 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Explain: In List Iterator > > Uh.... I'm just wondering about the customer table there... anything look > odd about the use of that table in this query or am I missing something > there? > > RF > > -----Original Message----- > Sent: Tuesday, April 09, 2002 5:44 PM > To: Multiple recipients of list ORACLE-L > > > OMG I thought we were looking at a canned-app query!!!! > > If it was my database, I would be turning the query upside down and > educating the developer in a not-so-gentle manner. But that's my style - > hormonal and direct. :) You are the better person if you can get through > to the developer without resorting to my curt type of "people skills" > > Post your tkprof, I am curious to see it. > > > -----Original Message----- > > From: Baker, Barbara [SMTP:[EMAIL PROTECTED]] > > Sent: Tuesday, April 09, 2002 4:45 PM > > To: '[EMAIL PROTECTED]'; Koivu, Lisa > > Subject: RE: Explain: In List Iterator > > > > Thanks, Lisa! > > I have not tkprof'd it. I'm beginning to wonder if the thing will ever > > complete. > > I think I might instead suggest ever-so-gently to the developer that > this > > doesn't belong in the production database. > > > > (See, Dennis!! I'm becoming the kinder, gentler dba...) > > Barb > > > > > ---------- > > > From: Koivu, Lisa[SMTP:[EMAIL PROTECTED]] > > > Sent: Tuesday, April 09, 2002 2:42 PM > > > To: '[EMAIL PROTECTED]' > > > Cc: '[EMAIL PROTECTED]' > > > Subject: RE: Explain: In List Iterator > > > > > > Hi Barbara, > > > > > > I believe 'in list iterator' is the way the optimizer is handling one > of > > > your in () statements in your query. Beware in lists with a large > > number > > > of > > > values in the set... as expansion of these in lists can create an > > > incredibly > > > ugly OR'd query. Saw this once and gave it the no_expand hint - > > problem > > > solved. However I don't think that's the problem here - have you > > tkprof'd > > > it? > > > > > > This is one nasty query. Distinct, outer join, trunc on a date, > > cartesian > > > joins... yuck! > > > > > > Lisa Koivu > > > Oracle Database Tank > > > Fairfield Resorts, Inc. > > > 954-935-4117 > > > > > > > > > > -----Original Message----- > > > > From: Baker, Barbara [SMTP:[EMAIL PROTECTED]] > > > > Sent: Tuesday, April 09, 2002 5:11 PM > > > > To: Multiple recipients of list ORACLE-L > > > > Subject: Explain: In List Iterator > > > > > > > > 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 > > > > -- > > > > Author: Baker, Barbara > > > > 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). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Koivu, Lisa > 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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Freeman, Robert > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara 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).