How constraints may affect cost based optimizer’s choises

Especially on data warehouse environments constraints are perceived as evil because of their ETL load costs. Where as their reporting performance gains may motivate you for using constraints even with these known costs.

One of the most common example is the NOT NULL constraint with ANTI JOINs, for joining massive amount of data HASH JOIN is something you are always after but if you do not let CBO to know that there are no NULL values on the columns you are joining you may end up with a NESTED LOOP join method which usually lasts for the eternity. Please check this paper for this case’s details; Speeding Up Queries with Semi-Joins and Anti-Joins: How Oracle Evaluates EXISTS, NOT EXISTS, IN and NOT IN

So as always, we need to test, calculate and decide on the alternative cost of using or not using any option available with Oracle for your own scenarios, after investigating below examples I shared you may also choose to go with VALIDATED constraints or not.

Not Null constraints and the optimizer after 10g

Check constraints and the optimizer after 10g

Telling the database that one-to-one mandatory relationship exists on the join columns with a foreign key, you can eliminate the unnecessary table’s join cost over a view after 10g Release 2

Having a Novalidate constraint hides valuable information from the CBO

SQL*Loader direct path load disables the foreign key constraint&ignores the foreign key constraint&ignores the trigger where as direct path insert just ignores the foreign key constraint

Leave a Comment

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

You are commenting using your 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