Yup.....
-----Original Message-----
Sent: Wednesday, April 10, 2002 11:48 AM
To: Multiple recipients of list ORACLE-L
It appears that there are no join conditions on it.
Can you say 'cartesian'? :)
Jared
On Tuesday 09 April 2002 15:13, Freeman, Robert wrote:
> 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: Jared Still
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).
