Re: Join cardinality and query tuning.

• Original Message ----- From: "Charu Joshi" <joshic_at_mahindrabt.com> To: <oracle-l_at_freelists.org> 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