Re: explain plan conundrum

It's not 20 billion rows but 20 billion bytes. It's "only" 1 billion rows. The cartesion product of 5K rows and 366K rows is 1830M rows or 1.8G. If the join predicate is not very selective, .5 or .33 for example, that would yield an estimated join cardinality of 1G (after rounding).

At 11:34 AM 11/4/2003, you wrote:
>I cant sql trace it now. I hae run statspack. this query is running now
>and I dont want to run another copy with a trace on until this finishes,
>since I dont want to suck up resources. Im at a loss as to where the 20
>billion rows comes from in this explain plan? Everything including the
>indexes are analyzed.
>
>when the two tables involved have 36k and 5k rows involved.
>looks like some form of cartesian join, but its not showing up in the
>plan. The two tables are joined by a column.
>
>any place to look on this? I know I need the 10046 trace, but I cant get
>that yet and it make take 12 hours to get it after this runs.
>
>select col1,
> col2,
> col3
>from tab1
> tab2
>where tab1.col1 = tab2.col2;
>
>
>Operation Object Name Rows Bytes Cost Object
>Node In/Out PStart PStop
>
>SELECT STATEMENT Optimizer Mode=CHOOSE 1
>G 237
> HASH JOIN 1
> G 20G 237
> INDEX FAST FULL SCAN PK1 5 K 11
> K 3
> TABLE ACCESS FULL TABLE2 366 K 4
> M 231

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).