Optimizer behaviour

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

Code:

---

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 :

Code:

---


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 :

Code:

---

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 :

Code:

---

         

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.

Your problem seems to be the date conversion in:

Code:

---

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

---

Try reversing this to:

Code:

---

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

---

Good luck!

:D

Quote:

---

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.

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.

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

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.

Did you use ordered hint?