To: <joshic@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 2 Nov 2004 21:14:42 +0100
----- Original Message -----
From: "Charu Joshi" <joshic@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, November 02, 2004 9:09 AM
Subject: RE: Join cardinality and query tuning.
>select /*+ ORDERED
> USE_NL(T29238)
> INDEX(T29238 W_ACTIVITY_F_N5)
> INDEX(T230600 W_LOV_D_M3) */
> count(*)
Hi Charu,
I would start with the basic selects
Select
T29238.X_BT_OUTCOME_AREA_WID
from
W_ACTIVITY_F T29238
where
T29238.X_BT_OUTCOME_AREA_WID = :x - alternatively use a literal instead of
a bind variable
and
Select
T230600.ROW_WID,
T230600.VAL
from
W_LOV_D T230600
where
T230600.VAL in ('Save-No', 'Save-Yes')
If you see a wrong (i.e. strong differing from the computed row count)
cardinality there could be something "wrong" with statistics.
If the execution plan opened doesn't corresponds with desired plan,
particularly if a full scan is preferred, check your
db_file_multiblock_read_count if it is not unrealistically high.
See excellent discussion of this problem in Jonathan Lewis paper
http://www.dbazine.com/jlewis12.shtml
You may also consider experimenting with system statistics.
I'm strongly convinced that if this two selects match both in cardinality
and in plan (and the cardinality of the second select is *very*low) the join
will correspond with yours expectations (i.e. both access paths will be used
within NL).
regards,
Jaromir D.B. Nemec
http://www.db-nemec.com
--
http://www.freelists.org/webpage/oracle-l
