RE: Join cardinality and query tuning.
- From: "Charu Joshi" <joshic@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 3 Nov 2004 14:11:27 +0530
Thanks Lex,
Then why is the row-source cardinality shown in the Explain Plan radically
different from the one calculated using this formula (Please refer to my
original post.)? Is there any silly mistake in my calculations (I quite am
prone to that.)?
Thanks & regards,
Charu.
PS: I really hope to be able to attend your seminar someday.
-----Original Message-----
From: Natural Join B.V. [mailto:lex.de.haan@xxxxxxxxxxxxxx]
Sent: Wednesday, November 03, 2004 2:57 PM
To: joshic@xxxxxxxxxxxxxx
Subject: RE: Join cardinality and query tuning.
looks very much like two formulas from one of my seminars :-)
yes, this is still the basic formula for calculating join selectivity and
cardinality. There isn't much else Oracle can do ...
Cheers,
Lex.
>
> Hi Jaromir,
>
> Thanks for your suggestions - some or all of these could be the reasons
for
> Oracle preferring FTS over RANGE SCAN. But before I delve into actually
> finding that out, let me repeat my basic question:
>
> The formula for calculating Join Selectivity and Join Cardinality is:
>
> JS = (1/MAX(NDV(T1.c1), NDV(T2.c1)))
> * (Card(T1) - Num_Nulls(T1.c1)/Card(T1))
> * (Card(T2) - Num_Nulls(T2.c1)/Card(T2))
>
> Join Cardinality = SelectedRows(T1)* SelectedRows(T2)* JS
> where SelectedRows(T) = Card(T) * FilterFactor
>
> Is this correct for version 9.2.0.3? What other factors can affect this
> formula (eg. histograms, system statistics)?
>
> My hunch is that the MAIN reason for Oracle prefering FTS over RANGE SCAN
in
> this case is the incorrect estimation of join cardinality. I will try
> tweaking statistics to reduce estimated join cardinality and will see if
> that makes Oracle choose RANGE SCAN. Will get back to you on this.
>
> Many thanks & regards,
> Charu.
>
>
>
> *********************************************************
> Disclaimer:
>
> This message (including any attachments) contains
> confidential information intended for a specific
> individual and purpose, and is protected by law.
> If you are not the intended recipient, you should
> delete this message and are hereby notified that
> any disclosure, copying, or distribution of this
> message, or the taking of any action based on it,
> is strictly prohibited.
>
> *********************************************************
> Visit us at http://www.mahindrabt.com
>
> --
> //www.freelists.org/webpage/oracle-l
>
*********************************************************
Disclaimer:
This message (including any attachments) contains
confidential information intended for a specific
individual and purpose, and is protected by law.
If you are not the intended recipient, you should
delete this message and are hereby notified that
any disclosure, copying, or distribution of this
message, or the taking of any action based on it,
is strictly prohibited.
*********************************************************
Visit us at http://www.mahindrabt.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
