Re: cardinality in query plans?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 15 Mar 2004 14:24:09 -0000
Message-ID: <021d01c40a99$33e4c040$7002a8c0@Primary>

How about giving us a clue -

Which version of Oracle ?

Two tables - we could probably cope with

    select {small (subset) list of columns}     from {two table names}
    where {list of join predicates}
    and {list of filter predicates}

And an execution plan on a two-table joins could only be a maximum of 8 lines.

I'll take a guess at the bind variable question, though: your query is using the bind variables to specify a range.

And if you used the same values for the binds as you did for the original query, you must be on Oracle 8, otherwise bind variable peeking would have produced the same plan.

The cardinality is the number of rows returned from that step of the plan - not the number input - so with a table of 8M rows, the cardinality could literally be any integer between zero and 8M on the scan line.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar

Original Message ----- From: <ryan.gaffuri_at_cox.net> To: <oracle-l_at_freelists.org> Sent: Monday, March 15, 2004 1:50 PM Subject: cardinality in query plans?

: I'm doing a two table join and both tables are analyzed. Oracle is
incorrectly choosing a full tables scan over an index search(I tested it, index search has 1/4 the logical I/Os).
:
: When I see the cardinality for the full tablescan I see 262,000. However,
when I do a count(*) of the table or check num_rows in dba_tables I see 870,000 records. I'm assuming this is why Oracle is choosing the full table scan.
:
: table is analyzed as follows:
:
: exec dbms_stats.gather_table_stats(cascade=>true).
:
: this interesting part is that when I use bind variables Oracle chooses the
proper plan. Any ideas?
:
:

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Mar 15 2004 - 08:20:43 CST