Optimizer behaviour

[rshivagami]

I need the help of the gurus out here in understanding how this execution plan is changing :
For the following query :

SELECT ais.standard_type_id AS STD_TYPE, 
       ais.ss_id AS STD_ID, 
       std.title AS TITLE, 
       std.description AS DESCRIPTION
  FROM mv_assessment_hierarchy mv, 
       clg_assessment_result ar, 
       clg_item_result ir,
       as_item_standard ais, 
       CLG_STANDARD std
  WHERE mv.ASSESSMENT_LOCATOR = 'M125Xassess-1::AS' and
	to_char(ar.completed_date,'YYYY-MM-DD hh24:mi:ss') =  '2004-04-30 13:09:59'  and
	mv.ASSESSMENT_LOCATOR = ar.ASSESSMENT_LOCATOR and
	ar.status = 20 and
	ar.taker_id = 59 and
	ir.ASSESSMENT_RESULT_ID = ar.ASSESSMENT_RESULT_ID and
	mv.item_locator = ir.ITEM_LOCATOR and	
	ir.item_locator = ais.item_locator AND
	ais.ss_id = std.ss_id

this is the explain plan :

 
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	9  	 	      	             	 
  TABLE ACCESS BY INDEX ROWID	MV_ASSESSMENT_HIERARCHY	28  	868  	3  	 	      	             	 
    NESTED LOOPS		1  	224  	9  	 	      	             	 
      NESTED LOOPS		1  	193  	6  	 	      	             	 
        NESTED LOOPS		1  	77  	5  	 	      	             	 
          NESTED LOOPS		1  	56  	4  	 	      	             	 
            TABLE ACCESS FULL	CLG_ASSESSMENT_RESULT	1  	35  	2  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	CLG_ITEM_RESULT	70  	1 K	2  	 	      	             	 
              INDEX RANGE SCAN	PK_ITEM_RESULT	49  	 	1  	 	      	             	 
          INDEX RANGE SCAN	AS_ITEM_STANDARD_AK1	20  	420  	1  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	CLG_STANDARD	1  	116  	1  	 	      	             	 
          INDEX UNIQUE SCAN	PK_STANDARD	1  	 	 	 	      	             	 
      INDEX RANGE SCAN	MV_ASS_LOCATOR	28  	 	1

When I change the query to take away my check on ar.completed_date as below :

SELECT  ais.standard_type_id AS STD_TYPE, 
        ais.ss_id AS STD_ID, 
        std.title AS TITLE, 
	std.description AS DESCRIPTION
  FROM mv_assessment_hierarchy mv, 
       clg_assessment_result ar, 
       clg_item_result ir,
       as_item_standard ais, 
       CLG_STANDARD std
  WHERE mv.ASSESSMENT_LOCATOR = 'M125Xassess-1::AS' and
	mv.ASSESSMENT_LOCATOR = ar.ASSESSMENT_LOCATOR and
	ar.status = 20 and
	ar.taker_id = 59 and
	ir.ASSESSMENT_RESULT_ID = ar.ASSESSMENT_RESULT_ID and
	mv.item_locator = ir.ITEM_LOCATOR and	
	ir.item_locator = ais.item_locator AND
	ais.ss_id = std.ss_id

here is the explain plan :

 	 

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		9  	 	19  	 	      	             	 
  HASH JOIN		9  	1 K	19  	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	MV_ASSESSMENT_HIERARCHY	28  	868  	3  	 	      	             	 
      INDEX RANGE SCAN	MV_ASS_LOCATOR	28  	 	1  	 	      	             	 
    HASH JOIN		76  	14 K	15  	 	      	             	 
      HASH JOIN		76  	5 K	7  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	CLG_ITEM_RESULT	70  	1 K	2  	 	      	             	 
          NESTED LOOPS		25  	1 K	4  	 	      	             	 
            TABLE ACCESS FULL	CLG_ASSESSMENT_RESULT	1  	31  	2  	 	      	             	 
            INDEX RANGE SCAN	PK_ITEM_RESULT	49  	 	1  	 	      	             	 
        INDEX FAST FULL SCAN	AS_ITEM_STANDARD_AK1	979  	20 K	2  	 	      	             	 
      TABLE ACCESS FULL	CLG_STANDARD	48  	5 K	7

How does the usage of the column COMPLETED_DATE of table CLG_ASSESSMENT_RESULT affect the usage of the PrimaryKey on CLG_STANDARD ? There is no relation or no constraint between them whatsoever...
Thanks.

[LKBrwn_DBA]

Your problem seems to be the date conversion in:

to_char(ar.completed_date,'YYYY-MM-DD hh24:mi:ss') =  '2004-04-30 13:09:59'

Try reversing this to:

ar.completed_date = TO_DATE('2004-04-30 13:09:59','YYYY-MM-DD hh24:mi:ss')

Good luck!

[abhaysk]

Originally posted by rshivagami
How does the usage of the column COMPLETED_DATE of table CLG_ASSESSMENT_RESULT affect the usage of the PrimaryKey on CLG_STANDARD ? There is no relation or no constraint between them whatsoever

There is ofcourse a relation, not direct though, that what number of rows (to be specific blocks) will be filtered with and without COMPLETED_DATE & that inturn affecting the amount to joining required..

Abhay.

[rshivagami]

This is kind of hard to understand. My explain plan is not using the index though I am explicitly doing the join using my PK only... why - because some other indirectly related filter is not present - I still do not get it...
Thanks.

[tamilselvan]

Choose the driving table correctly.

Which table among mv_assessment_hierarchy mv,
clg_assessment_result ar will produce less number of rows after filter?

And verify indexes are created on the joined columns.

Tamil

[rshivagami]

I changed the FROM clause and now have the tables in the decreasing order of the number of rows they return, still no difference.
I have indexes on all joined columns on mv_assessment_hierarchy, but not on clg_assessment_result as it is an OLTP table.

Shiva.

[tamilselvan]

Did you use ordered hint?