Re: reference cursors
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Jan 2004 22:01:26 -0000
Sorry,
Ignore the last post - I've just checked the 10 Beta
manuals. The way you've used the hint, it is supposed
to specify the single table cardinality for each of the
four tables after application of any single-table (i.e.
non-join) predicates on those tables.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
Jan 29th 2004 UKOUG Unix SIG - v$ and x$
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, January 30, 2004 4:44 PM
Subject: Re: reference cursors
>
> According to the 9.2 manual, the cardinality() hint
> should take a list of table aliases, and a cardinality.
> Then when that list of tables starts the join order,
> the cardinality of the join up to that point is what
> you have specified.
>
> Consequently the four separate cardinality hints
> in your query are saying:
> if you start with t1, the cardinality from t1 is 10
> if you start with t2, the cardinality from t2 is 10
> if you start with t3, the cardinality from t3 is 10
> if you start with t4, the cardinality from t4 is 10
> This may not be what you intend. As a consequence, it
> is possible that some other small effect relating to ref
> cursors or dynamic sql is causing a different table ordering,
> and a dramatic change in the statistics that Oracle is
> required to use.
>
> L:ine 11 may be a big part of your problem. Oracle assumes
> the cardinality of a table cast() is 8,168 - but your run time
> stats show 2 rows returned.
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
