RE: Join cardinality and query tuning.

From: Charu Joshi <joshic_at_mahindrabt.com>
Date: Wed, 3 Nov 2004 12:00:56 +0530
Message-ID: <MHEAIPLKCACENJKNJIALAEMPCIAA.joshic@mahindrabt.com>

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

Received on Wed Nov 03 2004 - 00:27:14 CST