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/