cardinality in query plans?

From: <ryan.gaffuri_at_cox.net>
Date: Mon, 15 Mar 2004 8:50:35 -0500
Message-Id: <20040315135036.ZIGE5813.lakemtao04.cox.net@smtp.east.cox.net>

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 - 07:47:02 CST