Cardinality Oracle Hint


I just discovered very interesting Oracle undocumented (yet according to Tom “safe” ) hint CARDINALITY. Cardinality hint compensates for the optimizers inability to estimate the inter-table join result set. This is NOT a problem with the optimizer, as no amount of metadata will help when joining tables with complex, multi-column where clauses.

The cardinality hint is used in two general cases, complex joins and dynamically created tables like global temporary tables (and possibly using materializations using the WITH clause):
This hint compensates for the CBO inability to estimate the tables join result set. It is really not an issue of CBO, it simply cannot do good estimate with complex multi-complex joins (this hint is not about two tables join, but complex multi-tables joins).

It can be used in complex joins as well as dynamically created tables like global temporary tables.
One interesting side effect Alain Lavallee posted on my blog today is that CARDINALITY hint can enforce joins of remote tables on remote server when doing inserts into local server when using db-link, DRIVING_SITE hint unfortunatelly does not enforce that, see more details here https://jiri.wordpress.com/2009/05/20/queries-across-db-link-and-how-driving_site-hint-does-not-work-for-inserts/

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: