SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

getting wrong cardinality causing a Nest Loop instead of Hash Join

529051Dec 16 2006 — edited Dec 17 2006

Oracle version 9.2.0.6

Without the use_hash hint the optimizer chooses

Nested Loop
view card = 600000 <- really close
index range card = 1 <-- this is very wrong it is more like 1000000

The index being used is an compsite index of 3 columns. I am suppling the SQL the value of the lead column:
col = 12345a

The access predicate line for the index range scan line is:
tab1.col1='12345a' AND tab1.col2 = tab2.col2 AND tab1.col3 = tab2.col3

I have analyzed all table in question at 100% with cascade.

If I do
select count(1) from tab1 where col1='12345a'
the cardinality is right on as we have a histogram on that column as the data is skewed.

how can I help Oracle understand more rows will be returned from the index range scan?

Thanks

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 14 2007

Added on Dec 16 2006

9 comments

854 views