Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle 11gR2 - Partitioned Local Index

Sachin BFeb 16 2010 — edited Feb 19 2010
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

Comments

3474044
Answer

SOLVED:

Solution:

Add the following to SQL Workshop ---> SQL SCRIPTS ---> CREATE --> RUN

DELETE YOURS_TABLE_NAME WHERE EMTY_COLUMN_NAME IS NULL;
Marked as Answer by 3474044 · Sep 27 2020
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 19 2010
Added on Feb 16 2010
#11gr2, #general-database-discussions, #index, #oracle
6 comments
1,512 views