Explain Plan and Cardinality

From: <schonlinner_at_yahoo.com>
Date: 15 Sep 2005 00:12:17 -0700
Message-ID: <1126768337.887172.267330@g43g2000cwa.googlegroups.com>

Hi,

Database: Oracle 10g (10.1.0.4) on HP/UX PA Risc 64 bit.

When doing an explain plan on a sql select statement I can see the cardinality in different steps of the plan.

>From the Oracle documentation I've read that the cardinality is how
many rows Oracle expects from this step. So if there is something like "TABLE ACCESS BY LOCAL INDEX ROWID" with cardinality 25 displayed by explain plan, then I thought that Oracle thinks that it retrieves round about 25 rows from this step.

Now sometimes the cardinality is totally wrong: I know that a specific step returns 200000 rows, but the cardinality in Oracle displays only 2-5 rows.

I thought it may be the statistics which is wrong. But the tables have been analyzed (by the default analyze job which is automatically configured in 10g on our system).

Is there anything which I can do to give Oracle more information so it guesses the cardinality better? Why is it "wrong"?

Regards,
Alex Received on Thu Sep 15 2005 - 02:12:17 CDT