Forum Stats

  • 3,781,163 Users
  • 2,254,485 Discussions
  • 7,879,599 Comments

Discussions

Categories

Index issue with or and between when we set one partition index to unusable

Sachin B
Sachin B Member Posts: 80
edited Feb 22, 2010 9:11AM in General Database Discussions
Need to understand why optimizer unable to use index in case of "OR" whenn we set one partition index to unusable, the same query with between uses index.

“OR” condition fetch less data comparing to “BETWEEN” still oracle optimizer unable to use indexes in case of “OR”

1. Created local index on partitioned table
2. ndex partition t_dec_2009 set to unusable
-- Partitioned local Index behavior with “OR” and with “BETWEEN”

SQL> CREATE TABLE t (
  2    id NUMBER NOT NULL,
  3    d DATE NOT NULL,
  4    n NUMBER NOT NULL,
  5    pad VARCHAR2(4000) NOT NULL
  6  )
  7  PARTITION BY RANGE (d) (
  8    PARTITION t_jan_2009 VALUES LESS THAN (to_date('2009-02-01','yyyy-mm-dd')),
  9    PARTITION t_feb_2009 VALUES LESS THAN (to_date('2009-03-01','yyyy-mm-dd')),
 10    PARTITION t_mar_2009 VALUES LESS THAN (to_date('2009-04-01','yyyy-mm-dd')),
 11    PARTITION t_apr_2009 VALUES LESS THAN (to_date('2009-05-01','yyyy-mm-dd')),
 12    PARTITION t_may_2009 VALUES LESS THAN (to_date('2009-06-01','yyyy-mm-dd')),
 13    PARTITION t_jun_2009 VALUES LESS THAN (to_date('2009-07-01','yyyy-mm-dd')),
 14    PARTITION t_jul_2009 VALUES LESS THAN (to_date('2009-08-01','yyyy-mm-dd')),
 15    PARTITION t_aug_2009 VALUES LESS THAN (to_date('2009-09-01','yyyy-mm-dd')),
 16    PARTITION t_sep_2009 VALUES LESS THAN (to_date('2009-10-01','yyyy-mm-dd')),
 17    PARTITION t_oct_2009 VALUES LESS THAN (to_date('2009-11-01','yyyy-mm-dd')),
 18    PARTITION t_nov_2009 VALUES LESS THAN (to_date('2009-12-01','yyyy-mm-dd')),
 19    PARTITION t_dec_2009 VALUES LESS THAN (to_date('2010-01-01','yyyy-mm-dd'))
 20  );

SQL> INSERT INTO t
  2  SELECT rownum, to_date('2009-01-01','yyyy-mm-dd')+rownum/274, mod(rownum,11), rpad('*',100,'*')
  3  FROM dual
  4  CONNECT BY level <= 100000;

SQL> CREATE INDEX i ON t (d) LOCAL;

SQL> execute dbms_stats.gather_table_stats(user,'T')

-- Mark partition t_dec_2009 to unusable:

SQL> ALTER INDEX i MODIFY PARTITION t_dec_2009 UNUSABLE;

--- Let’s check whether the usable index partition can be used to apply a restriction: BETWEEN

SQL> SELECT count(d)
    FROM t
    WHERE d BETWEEN to_date('2009-01-01 23:00:00','yyyy-mm-dd hh24:mi:ss')
                AND to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss');

SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic +partition'));

--------------------------------------------------------
| Id  | Operation               | Name | Pstart| Pstop |
--------------------------------------------------------
|   0 | SELECT STATEMENT        |      |       |       |
|   1 |  SORT AGGREGATE         |      |       |       |
|   2 |   PARTITION RANGE SINGLE|      |    12 |    12 |
|   3 |    INDEX RANGE SCAN     | I    |    12 |    12 |
--------------------------------------------------------

--- Let’s check whether the usable index partition can be used to apply a restriction: OR

SQL> SELECT count(d)
    FROM t
    WHERE 
    (
    (d >= to_date('2009-01-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-01-01 23:59:59','yyyy-mm-dd hh24:mi:ss'))
    or 
    (d >= to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-02-02 02:00:00','yyyy-mm-dd hh24:mi:ss'))
    );

SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic +partition'));

----------------------------------------------------
| Id  | Operation           | Name | Pstart| Pstop |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |       |       |
|   2 |   PARTITION RANGE OR|      |KEY(OR)|KEY(OR)|
|   3 |    TABLE ACCESS FULL| T    |KEY(OR)|KEY(OR)|
----------------------------------------------------
“OR” condition fetch less data comparing to “BETWEEN” still oracle optimizer unable to use indexes in case of “OR”

Regards,
Sachin B.
Tagged:
· Share on TwitterShare on Facebook

Answers

  • 561093
    561093 Member Posts: 2,146
    Hi,

    What is your database version????

    I ran the same test and optimizer was able to pick the index for both the queries.
    SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> set autotrace traceonly exp
SQL>
SQL>
SQL>  SELECT count(d)
  2  FROM t
  3  WHERE d BETWEEN to_date('2009-01-01 23:00:00','yyyy-mm-dd hh24:mi:ss')
  4              AND to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss');

Execution Plan
----------------------------------------------------------
Plan hash value: 2381380216

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     1 |     8 |    25   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE           |      |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |  8520 | 68160 |    25   (0)| 00:00:01 |     1 |     2 |
|*  3 |    INDEX RANGE SCAN       | I    |  8520 | 68160 |    25   (0)| 00:00:01 |     1 |     2 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("D">=TO_DATE(' 2009-01-01 23:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "D"<=TO_DATE(' 2009-02-02 01:00:00', 'syyyy-mm-dd hh24:mi:ss'))

SQL>  SELECT count(d)
  2  FROM t
  3  WHERE
  4  (
  5  (d >= to_date('2009-01-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-01-01 23:59:59','yyyy-mm-dd hh24:mi:ss'
  6  or
  7  (d >= to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-02-02 02:00:00','yyyy-mm-dd hh24:mi:ss'
  8  );

Execution Plan
----------------------------------------------------------
Plan hash value: 3795917108

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |     1 |     8 |     4   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE          |      |     1 |     8 |            |          |       |       |
|   2 |   CONCATENATION          |      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE SINGLE|      |    13 |   104 |     2   (0)| 00:00:01 |     2 |     2 |
|*  4 |     INDEX RANGE SCAN     | I    |    13 |   104 |     2   (0)| 00:00:01 |     2 |     2 |
|   5 |    PARTITION RANGE SINGLE|      |    13 |   104 |     2   (0)| 00:00:01 |     1 |     1 |
|*  6 |     INDEX RANGE SCAN     | I    |    13 |   104 |     2   (0)| 00:00:01 |     1 |     1 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D">=TO_DATE(' 2009-02-02 01:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "D"<=TO_DATE(' 2009-02-02 02:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - access("D">=TO_DATE(' 2009-01-01 23:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "D"<=TO_DATE(' 2009-01-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
       filter(LNNVL("D"<=TO_DATE(' 2009-02-02 02:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR
              LNNVL("D">=TO_DATE(' 2009-02-02 01:00:00', 'syyyy-mm-dd hh24:mi:ss')))

SQL> set autotrace off
SQL>
    Asif Momen
    http://momendba.blogspot.com
    · Share on TwitterShare on Facebook
  • Sachin B
    Sachin B Member Posts: 80
    Hi,

    I am running on 11gR2, things works fine till we are not setting any partition index to unusable.
    SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

set one partition index to unusable, then you will get diffrent results with "OR" and "BETWEEN"

SQL> ALTER INDEX i MODIFY PARTITION t_dec_2009 UNUSABLE;
 
SQL> SELECT count(d)
    FROM t
    WHERE 
    (
    (tstamp >= to_date('2009-01-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and tstamp <= to_date('2009-01-01 23:59:59','yyyy-mm-dd hh24:mi:ss'))
    or 
    (tstamp >= to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss') and tstamp <= to_date('2009-02-02 02:00:00','yyyy-mm-dd hh24:mi:ss'))
    );
 
SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic +partition'));
 
----------------------------------------------------
| Id  | Operation           | Name | Pstart| Pstop |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |       |       |
|   2 |   PARTITION RANGE OR|      |KEY(OR)|KEY(OR)|
|   3 |    TABLE ACCESS FULL| T    |KEY(OR)|KEY(OR)|
----------------------------------------------------
    · Share on TwitterShare on Facebook
  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    Can you post the full Explain Plan output ? We'd like to see the Cardinality -- expected Row Counts -- for the two plans.


    Hemant K Chitale
    · Share on TwitterShare on Facebook
  • 561093
    561093 Member Posts: 2,146
    Sachin,

    Check this link:


    http://momendba.blogspot.com/2010/02/understanding-skipunusableindexes.html



    Asif Momen
    http://momendba.blogspot.com
    · Share on TwitterShare on Facebook
  • Mohamed Houri
    Mohamed Houri Member Posts: 1,223 Bronze Trophy
    edited Feb 22, 2010 9:11AM
    Look to your selects

    1) the first one will hit two paritions t_jan_2009 and t_feb_2009 (by the way why have you partition single)
    WHERE d BETWEEN to_date('2009-01-01 23:00:00','yyyy-mm-dd hh24:mi:ss') AND to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss');

    This query will not hit the partition PARTITION t_dec_2009 that have its local index set to unusable

    2) the second one wil hit many partitions including the one you set unusable t_dec_2009
    WHERE
    (d >= to_date('2009-01-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-01-01 23:59:59','yyyy-mm-dd hh24:mi:ss'))
    or
    (d >= to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-02-02 02:00:00','yyyy-mm-dd hh24:mi:ss'))
    );

    I didn't reproduce this case but I think the problem is that the first one will never hit your disabled index while the second will; you may be have set session to skip unusable indexe and this is why it couldnt' access the table via your disabled indexe

    I will try it and let you know what I have found

    Regards

    Mohamed Houri

    Edited by: Mohamed Houri on 22-Feb-2010 06:09
    · Share on TwitterShare on Facebook
This discussion has been closed.