Re: Cardinality Hint?

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 <ORACLE-L_at_fatcity.com> 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: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).