The last time I checked, there were about 60 hints documented, and about another 60 things in the executable that looked like hints.
The cardinality hint SEEMS to dictate a value for the computed cardinality of a single table access (see the 10053 trace) at any point where Oracle says - when I visit this table once, how many rows do I expect to meet the current set of predicates. If you want to see its effects, just run a few (full) explain plans of a multi-table query with this hint applied to one or more tables, and see the effect it has on the CARDINALITY column of the plan_table. The simplest use (which I think the MView one is) is to set the cardinality of one table in a two-table join to one so that Oracle knows that it ought to be the first table in the join path. I don't think it is supposed to be an 'end-user' hint. On the other hand, prior to Oracle 9 it could be a cute trick to throw at code that uses global temporary tables to avoid the problem of Oracle uses default values that the developer knows to be inappropriate. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) ____UK_______March 19th ____USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -----Original Message----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 19 February 2003 15:35 > >Jonathan, > >Could you provide more information on this hint ? I couldn't find it in >the 9i Performance Tuning >Guide and Reference, although there is the DYNAMIC_SAMPLING hint. >Hemant > >At 12:28 AM 19-02-03 -0800, you wrote: > >>/*+ cardinality(t,N) */ >>where t is a table alias and N is a number >>instructs the optimizer to use N as the >>computed cardinality of table t without >>going to the trouble of checking. >> >>Regards >> >>Jonathan Lewis >>http://www.jlcomp.demon.co.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
