Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Cardinality from an Explain plan

Re: Cardinality from an Explain plan

From: Spencer <spencerp_at_swbell.net>
Date: Sat, 19 May 2001 16:13:02 -0500
Message-ID: <_jBN6.465$yH1.7395@nnrp1.sbc.net>

you have not provided enough details about your tables, indexes, query, explain output, or trace file for anyone to provide a careful analysis of the problem you report.

have you made sure to collect statistics for the indexes ? is the data skewed, and if it is, have are you collected histograms on the indexed columns ? (the histograms will be of use only if you are not using bind variables)

what makes you think that the 300,000 is "so far off" ?

remember that for a "nested loops" join path, oracle chooses a 'master' table, and for each row returned from the 'master', the other table (or index) is going to be searched. so if the query returns 300 rows from the master, and the second table in the query contains 1,000 rows... well, do the math.

on another note: please avoid crossposting to multiple oracle newsgroups... you are not necessarily reaching a bigger audience... you are reaching the same audience multiple times.

"Steve Perry" <sperry_at_sprynet.com> wrote in message news:pmnN6.5355$y_3.2669085_at_typhoon.austin.rr.com...
> I have a query that returns 300 rows using nested loops, index range scans
> and unique scans.
> standard tables, b-tree indexes, the stats are up to date and the data is
> freshly loaded.
>
> When I do an explain on the sql, Oracle estimates the cardinality for the
> statement is 300,000 rows.
>
> I've noticed this same problem for certain bitmapped access paths.
>
> Can somebody explain why Oracle is so far off (1000%) when the metrics
> should not be skewed.
>
> Solaris 2.6
> Oracle 8.1.5 EE
> optimizer_mode: first_rows
>
> Thanks,
> Steve
>
>
>
Received on Sat May 19 2001 - 16:13:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US