RE: Join cardinality and query tuning.
- From: "Charu Joshi" <joshic@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Nov 2004 13:38:37 +0530
-----Original Message-----
From: Charu Joshi [mailto:joshic@xxxxxxxxxxxxxx]
Sent: Tuesday, November 02, 2004 12:18 PM
To: ryan_gaffuri@xxxxxxxxxxx
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.
--
//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:
