Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Join cardinality and query tuning.

RE: Join cardinality and query tuning.

From: Charu Joshi <joshic_at_mahindrabt.com>
Date: Tue, 2 Nov 2004 13:38:37 +0530
Message-ID: <MHEAIPLKCACENJKNJIALGEMACIAA.joshic@mahindrabt.com>

-----Original Message-----
From: Charu Joshi [mailto:joshic_at_mahindrabt.com] Sent: Tuesday, November 02, 2004 12:18 PM To: ryan_gaffuri_at_comcast.net
Subject: RE: Join cardinality and query tuning.

Hi Ryan,

[RG]

Does his formula take into account the distinctiveness of the join column?
[RG]

I guess it does - it contains the expression 1/MAX(NDV(T1.c1), NDV(T2.c2)). Where NDV is the Number of Distinct Values of the join column.

[RG]

I believe that the cardinality value at the 'join' level is the estimated number of records viewed during the operation, not returned.
[RG]

I don't think so, but I will need to experiment a bit to confirm.

[RG]

Do you have histograms? without them, this is often not accurate.
[RG]

Good point. There is histogram on X_BT_OUTCOME_AREA_WID column of W_ACTIVITY_F since it has a skew, but not on ROW_WID column of W_LOV_D (which is a unique column - though the constraint hasn't been declared). Will need to do some experimentation on the effect of histograms on join cardinality. Don't know why but my gut feeling is that histograms *ON THE JOIN COLUMNS* won't affect the join cardinality. A histogram on a filter predicate column would affect the estimated cardinality of that table and consequently the join cardinality by the histograms *on the join columns* won't affect JC. This on version 9.2.0.3.

[RG]

you are probably getting the hash join becuase of bad column order in your index. your column order should be
(val,row_id)
[RG]

There are two single-column indexes - one on VAL and other on ROW_WID columns. Ideally it should have picked up the index on VAL column, but it doesn't matter because the table W_LOV_D is quite small. I think the hash join is again because of the huge estimated join cardinality, because of which it decides to do FFS on W_ACTIVITY_F_N5 instead of RANGE_SCAN. Will try this out though.

Thanks & Regards,
Charu.

--
http://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

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 02 2004 - 02:04:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US