Oracle Logbook

June 14, 2012

Improving performance with foreign key

Filed under: Uncategorized — heliodias @ 4:21 pm

Oracle statistics are single table  only (may be version 12 will come with relationship statistic)

When you have a subquery with FKs between then, you may know that for each query at the child table, it will have just one row on the parent table,but Oracle is blind in this matter.

Then Oracle make some math to guess how it could be the selectivity on the main table. And usually it goes wrong and sometimes very wrong.

So the only way to give to Oracle a perfect picture, is creating the foreign key (yes that foreign key that you avoided creating due to “never will be violated”) Than Oracle will give you a much better plan.

I´d decide write about it after answer this post at OTN Different Execution paths for similar query.

Advertisements

Blog at WordPress.com.