Forum Stats

  • 3,783,352 Users
  • 2,254,762 Discussions
  • 7,880,372 Comments

Discussions

Oracle 11gR2 - Partitioned Local Index

Sachin B
Sachin B Member Posts: 80
edited Feb 19, 2010 5:24AM in General Database Discussions
Hi,

created local index on partition table as below.

CREATE INDEX SE_TSTAMP_IDX ON SE (TSTAMP) PARALLEL 6 NOLOGGING LOCAL;

index for partition oct05 set ot unusable where as all othere partitions are set to usable , when i run select query with or conditions it oracle not use index, but if i change query to between it uses
SELECT
count(tstamp)
FROM SE
WHERE 
(
(TSTAMP>= TIMESTAMP '2009-10-03 00:00:00.000'  AND TSTAMP<= TIMESTAMP  '2009-10-03 10:59:59.000')
or 
(TSTAMP>= TIMESTAMP '2009-10-04 00:00:00.000'  AND TSTAMP<= TIMESTAMP  '2009-10-04 10:59:59.000')
)
Explain plan result
----------------------------------------------------------------------------------------------------
----

| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pst
op |

----------------------------------------------------------------------------------------------------
----

|   0 | SELECT STATEMENT    |                  |     1 |    11 |  1296   (1)| 00:00:16 |       |
   |

|   1 |  SORT AGGREGATE     |                  |     1 |    11 |            |          |       |
   |

|   2 |   PARTITION RANGE OR|                  |   182K|  1955K|  1296   (1)| 00:00:16 |KEY(OR)|KEY(
OR)|

|*  3 |    TABLE ACCESS FULL| SUBSCRIBER_EVENT |   182K|  1955K|  1296   (1)| 00:00:16 |KEY(OR)|KEY(
OR)|

----------------------------------------------------------------------------------------------------
Query with between
SELECT
count(tstamp)
FROM SE
WHERE 
TSTAMP between TIMESTAMP '2009-10-03 00:00:00.000'  AND TIMESTAMP  '2009-10-04 10:59:59.000';
explain plant result
----------------------------------------------------------------------------------------------------
----------

| Id  | Operation                 | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstar
t| Pstop |

----------------------------------------------------------------------------------------------------
----------

|   0 | SELECT STATEMENT          |                  |     1 |    11 |   359   (2)| 00:00:05 |
 |       |

|   1 |  SORT AGGREGATE           |                  |     1 |    11 |            |          |
 |       |

|   2 |   PARTITION RANGE ITERATOR|                  |   203K|  2187K|   359   (2)| 00:00:05 |     3
 |     4 |

|*  3 |    INDEX FAST FULL SCAN   | SUBEV_TSTAMP_IDX |   203K|  2187K|   359   (2)| 00:00:05 |     3
 |     4 |

----------------------------------------------------------------------------------------------------
I like to know why oracle optimizer unable to use index in case of "OR" condition?

Edited by: Sachin B on Feb 15, 2010 10:32 PM
Tagged:

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    but whe i use between it works as expected.
    So why post here?

    What is your question?
  • Sachin B
    Sachin B Member Posts: 80
    edited Feb 16, 2010 1:43AM
    hi,

    sorry for the mistakes.

    CREATE INDEX SUBEV_TSMS_COM_IDX ON SE (TSTAMP,MSISDN) PARALLEL 6 NOLOGGING LOCAL;

    alter index SUBEV_TSTAMP_IDX modify partition P2009100500 unusable;

    below query fetch data from two partition oct 03 and oct 04 and both partition index state usable (oct 05 set to unusable)
    SELECT
    count(tstamp)
    FROM SUBSCRIBER_EVENT
    WHERE 
    (
    (TSTAMP>= TIMESTAMP '2009-10-03 00:00:00.000'  AND TSTAMP<= TIMESTAMP  '2009-10-03 10:59:59.000')
    or 
    (TSTAMP>= TIMESTAMP '2009-10-04 00:00:00.000'  AND TSTAMP<= TIMESTAMP  '2009-10-04 10:59:59.000')
    )
    if i run above query optimiser won't use index. where as if i change query to "between" it uses.
    SELECT
    count(tstamp)
    FROM SUBSCRIBER_EVENT
    WHERE 
    TSTAMP between TIMESTAMP '2009-10-03 00:00:00.000'  AND TIMESTAMP  '2009-10-04 10:59:59.000';
    my question is why optimiser unable to use index incase of or?

    Edited by: Sachin B on Feb 15, 2010 10:42 PM
  • Taral
    Taral Member Posts: 505
    Please put your code between "{code}" keyword without quotes. So, it's readable. Also, post predicate section of plan for both queries
  • Taral
    Taral Member Posts: 505
    edited Feb 17, 2010 10:40AM
    I am using 10.2.0.3 and mine is using index. Post other details like table structure and how you generate stats
    drop table t purge ;
    
     CREATE TABLE t (id NUMBER , tstamp timestamp )
      PARTITION BY RANGE (tstamp) (
        PARTITION t_jan_2009 VALUES LESS THAN (to_date('2009-02-01','yyyy-mm-dd')),
        PARTITION t_feb_2009 VALUES LESS THAN (to_date('2009-03-01','yyyy-mm-dd')),
        PARTITION t_mar_2009 VALUES LESS THAN (to_date('2009-04-01','yyyy-mm-dd')),
        PARTITION t_apr_2009 VALUES LESS THAN (to_date('2009-05-01','yyyy-mm-dd')),
        PARTITION t_may_2009 VALUES LESS THAN (to_date('2009-06-01','yyyy-mm-dd')),
        PARTITION t_jun_2009 VALUES LESS THAN (to_date('2009-07-01','yyyy-mm-dd')),
        PARTITION t_jul_2009 VALUES LESS THAN (to_date('2009-08-01','yyyy-mm-dd')),
        PARTITION t_aug_2009 VALUES LESS THAN (to_date('2009-09-01','yyyy-mm-dd')),
        PARTITION t_sep_2009 VALUES LESS THAN (to_date('2009-10-01','yyyy-mm-dd')),
        PARTITION t_oct_2009 VALUES LESS THAN (to_date('2009-11-01','yyyy-mm-dd')),
        PARTITION t_nov_2009 VALUES LESS THAN (to_date('2009-12-01','yyyy-mm-dd')),
        PARTITION t_dec_2009 VALUES LESS THAN (to_date('2010-01-01','yyyy-mm-dd'))
     );
    
    INSERT INTO t SELECT rownum, to_date('2009-01-01', 'yyyy-mm-dd') + rownum / 274 FROM dual CONNECT BY level <= 100000;
    commit;
    create index t_idx on t (tstamp,id) LOCAL;
    
    exec dbms_stats.gather_table_stats(user,'T');
    
    SELECT count(tstamp)
      FROM t
     WHERE ((TSTAMP >= TIMESTAMP
            '2009-10-03 00:00:00.000' AND TSTAMP <= TIMESTAMP
            '2009-10-03 10:59:59.000') or
           (TSTAMP >= TIMESTAMP
            '2009-10-04 00:00:00.000' AND TSTAMP <= TIMESTAMP
            '2009-10-04 10:59:59.000')) ;
    
    --------------------------------------------------------------------------------------------------
    | Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT         |       |     1 |    11 |     4   (0)| 00:00:01 |       |       |
    |   1 |  SORT AGGREGATE          |       |     1 |    11 |            |          |       |       |
    |   2 |   CONCATENATION          |       |       |       |            |          |       |       |
    |   3 |    PARTITION RANGE SINGLE|       |   128 |  1408 |     2   (0)| 00:00:01 |    10 |    10 |
    |*  4 |     INDEX RANGE SCAN     | T_IDX |   128 |  1408 |     2   (0)| 00:00:01 |    10 |    10 |
    |   5 |    PARTITION RANGE SINGLE|       |   118 |  1298 |     2   (0)| 00:00:01 |    10 |    10 |
    |*  6 |     INDEX RANGE SCAN     | T_IDX |   118 |  1298 |     2   (0)| 00:00:01 |    10 |    10 |
    --------------------------------------------------------------------------------------------------
    
    SELECT count(tstamp)
      FROM t
     WHERE tstamp between TIMESTAMP '2009-10-03 00:00:00.000' AND TIMESTAMP
     '2009-10-04 10:59:59.000';
     
    -------------------------------------------------------------------------------------------------
    | Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |       |     1 |    11 |     2   (0)| 00:00:01|       |       |
    |   1 |  SORT AGGREGATE         |       |     1 |    11 |            |         |       |       |
    |   2 |   PARTITION RANGE SINGLE|       |   402 |  4422 |     2   (0)| 00:00:01|    10 |    10 |
    |*  3 |    INDEX RANGE SCAN     | T_IDX |   402 |  4422 |     2   (0)| 00:00:01|    10 |    10 |
    ------------------------------------------------------------------------------------------------
  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    The two queries are different.

    The first query excludes rows where TSTAMP is between 11:00am of 03-Oct-2010 and the midnight.
    The second query includes those rows as well.


    Hemant K Chitale
  • Sachin B
    Sachin B Member Posts: 80
    Hi Taral,

    set one partition index to unusable, then you will get diffrent results with "OR" and "BETWEEN"
    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)|
    ----------------------------------------------------
    in above code we are not fetching data from partition which to set to unusable.

    Regards,
    Sachin
This discussion has been closed.