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

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: