| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Join cardinality and query tuning.
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_at_naturaljoin.nl]
Sent: Wednesday, November 03, 2004 2:57 PM
To: joshic_at_mahindrabt.com
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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 03 2004 - 02:41:16 CST
 |
 |