Index not getting used

From: Parvez <parvez_mk_at_yahoo.com>
Date: 28 Nov 2005 00:34:22 -0800
Message-ID: <1133166862.671744.66390@g43g2000cwa.googlegroups.com>

Hi All,

I am having situation where my below query is not using index on updated date column, I have index on two date columns, created date and updated date, the query under focus has a OR condition with both the columns, the query execution is very high (full table scan) as below. If I just use created date it picks corresponding index and query execution time is very less. The tables are analyzed.

SELECT a.deal_no


      ,a.deal_ver_no

      ,a.origin_type

      ,a.created_date

      ,a.updated_date

      ,b.type

      ,b.employee

      ,b.created_date

  FROM deal a


      ,medium b

 WHERE a.deal_no = b.deal_no
   AND b.medium_code = 888
   AND ( a.created_date >= TO_DATE ('20040101','yyyymmdd') AND
a.created_date < TO_DATE ('20040102','yyyymmdd')
          OR updated_date >= TO_DATE ('20040101','yyyymmdd') AND
updated_date < TO_DATE ('20040102','yyyymmdd')
       )

   AND action_flag IN ('I', 'U')
   AND b.a.created_date = (SELECT MAX(c.created_date)

                              FROM medium c
                             WHERE c.deal_no = b.deal_no
                               AND c.type = b.type
                               AND c.created_date < TO_DATE

('20040102','yyyymmdd'))

real: 10219

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=159 Card=1 Bytes=204)    1 0 FILTER

   2    1     NESTED LOOPS (Cost=159 Card=1 Bytes=204)
   3    2       TABLE ACCESS (FULL) OF 'medium' (Cost=156 Card=1
Bytes=54)
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'deal' (Cost=3
Card=5756 Bytes=863400)
   5    4         INDEX (RANGE SCAN) OF 'DEAL_FK_I' (NON-UNIQUE)
(Cost=2 Card=5756)
   6    1     SORT (AGGREGATE)
   7    6       TABLE ACCESS (BY INDEX ROWID) OF 'medium' (Cost=4
Card=1 Bytes=34)
   8    7         INDEX (RANGE SCAN) OF 'MEDUIM_IDX1' (NON-UNIQUE)

(Cost=3 Card=1)

Statistics

---

          0  recursive calls
        127  db block gets
     917130  consistent gets
     114016  physical reads
          0  redo size
        818  bytes sent via SQL*Net to client
        306  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
2	rows processed

It does not use updated date index even after giving hint, I suspect this is happening because of many NULL value in updated date.

If I put hint to use rule based optimizer, as below query execution time is the least.

SELECT /*+ RULE */

       a.deal_no


      ,a.deal_ver_no

      ,a.origin_type

      ,a.created_date

      ,a.updated_date

      ,b.type

      ,b.employee

      ,b.created_date

  FROM deal a


      ,medium b

 WHERE a.deal_no = b.deal_no
   AND b.medium_code = 888
   AND ( a.created_date >= TO_DATE ('20040101','yyyymmdd') AND
a.created_date < TO_DATE ('20040102','yyyymmdd')
          OR updated_date >= TO_DATE ('20040101','yyyymmdd') AND
updated_date < TO_DATE ('20040102','yyyymmdd')
       )

   AND action_flag IN ('I', 'U')
   AND b.a.created_date = (SELECT MAX(c.created_date)

                              FROM medium c
                             WHERE c.deal_no = b.deal_no
                               AND c.type = b.type
                               AND c.created_date < TO_DATE

('20040102','yyyymmdd'))

 real: 1203

Execution Plan

---

   0 SELECT STATEMENT Optimizer=HINT: RULE    1 0 CONCATENATION

   2    1     FILTER
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'deal'
   5    4           INDEX (RANGE SCAN) OF 'deal_IDX7' (NON-UNIQUE)
   6    3         TABLE ACCESS (BY INDEX ROWID) OF 'medium'
   7    6           INDEX (RANGE SCAN) OF 'medium_IDX1' (NON-UNIQUE)
   8    2       SORT (AGGREGATE)
   9    8         TABLE ACCESS (BY INDEX ROWID) OF 'medium'
  10    9           INDEX (RANGE SCAN) OF 'medium_IDX1' (NON-UNIQUE)

  11    1     FILTER
  12   11       NESTED LOOPS
  13   12         TABLE ACCESS (BY INDEX ROWID) OF 'deal'
  14   13           INDEX (RANGE SCAN) OF 'deal_IDX8'(NON-UNIQUE)
  15   12         TABLE ACCESS (BY INDEX ROWID) OF 'medium'
  16   15           INDEX (RANGE SCAN) OF 'medium_IDX1' (NON-UNIQUE)

Statistics

---

          0  recursive calls
          0  db block gets
         58  consistent gets
          0  physical reads
          0  redo size
        818  bytes sent via SQL*Net to client
        307  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

Putting a Ordered by hint is better then no hint but still is not as good as Rule hint, why is it so, I want to avoid putting a Rule hint Received on Mon Nov 28 2005 - 02:34:22 CST