Re: Fun with the CARDINALITY and DYNAMIC_SAMPLING hint

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 14 Sep 2012 15:33:17 +0100
Message-ID: <EA9E5D54AB464237AE41429F6E7972DF_at_Primary>


Actually the cardinality hint allows for more than single table cardinality - but it's quite hard to use.

cardinalty(t1, 4) -- single table cardinality of t1 is 4 cardinality(t1, t2, 4) -- if t1 and t2 are the first two tables in the join order then the join cardinality is 4
cardinality(t1, t2, t3, 4) -- if t1 and t2 are the first three tables in the join order (in that order) then the join cardinality is 4   etc.

I can't remember which commas are mandatory, which are optional, and which are wrong. (The ones between the tables might be mandatory) Effects may be version dependent

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

  • Original Message ----- From: "Dominic Brooks" <dombrooks_at_hotmail.com> To: <christopher.taylor2_at_parallon.net>; <oracle-l_at_freelists.org> Sent: Friday, September 14, 2012 2:52 PM Subject: RE: Fun with the CARDINALITY and DYNAMIC_SAMPLING hint

| Cardinality hints and dynamic sampling only affect single table
| cardinality estimates so using both for a single table doesn't make
| sense.
|
| If you want to influence join estimates something like OPT_ESTIMATE is
much more flexible, but undocumented.
|

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Sep 14 2012 - 09:33:17 CDT

Original text of this message