CODEWITHSUNDEEP
sqloracle-databaseoracle11gquery-performance
Alberto
Alberto

Reputation: 795

Oracle Index Timestamp

Scenario:

I have a table with 568801 rows and an index on the column TIM_RECEPT (TIMESTAMP). The index is generated as TRUNC("TIM_RECEPT").

This table was filled on 19 of April with this query:

INSERT INTO MY_TABLE SELECT <fields> FROM <tables>

The next days the table has been loaded with: 

INSERT INTO MY_TABLE SELECT <fields> 
FROM <tables> WHERE alias.tim_recept > TRUNC(SYSDATE -1)

Problem:

When I use the index in explain plan, return this:

EXPLAIN PLAN FOR
select *
from  MY_TABLE
where trunc(TIM_RECEPT) >= TO_DATE('22/11/2017', 'DD-MM-YYYY')
    and trunc(TIM_RECEPT) <= TO_DATE('26/04/2018', 'DD-MM-YYYY')
;

select * from table(dbms_xplan.display);

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 42112 |    13M|  8690   (1)| 00:01:45 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_TABLE  | 42112 |    13M|  8690   (1)| 00:01:45 |
|*  2 |   INDEX RANGE SCAN          | IMYTABLE1 | 42112 |       |   114   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------

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

   2 - access(TRUNC(INTERNAL_FUNCTION("TIM_RECEPT"))>=TO_DATE(' 2017-11-22 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              TRUNC(INTERNAL_FUNCTION("TIM_RECEPT"))<=TO_DATE(' 2018-04-26 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))

BUT if I change the initial date and rest one day, the index is not used:

EXPLAIN PLAN FOR
select *
from  MY_TABLE
where trunc(TIM_RECEPT) >= TO_DATE('21/11/2017', 'DD-MM-YYYY')
    and trunc(TIM_RECEPT) <= TO_DATE('26/04/2018', 'DD-MM-YYYY')
;

select * from table(dbms_xplan.display);

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 42395 |    14M|  8739   (1)| 00:01:45 |
|*  1 |  TABLE ACCESS FULL| MY_TABLE | 42395 |    14M|  8739   (1)| 00:01:45 |
------------------------------------------------------------------------------

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

   1 - filter(TRUNC(INTERNAL_FUNCTION("TIM_RECEPT"))>=TO_DATE(' 
              2017-11-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              TRUNC(INTERNAL_FUNCTION("TIM_RECEPT"))<=TO_DATE(' 2018-04-26 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))

Any idea about this issue?

Upvotes: 1

Views: 1736

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21063

The fact that Oracle optimizer doesn't use index i snot necessary a problem. It is a problem when the resources consumed (in most cases elapsed time) with the FULL TABLE SCAN access is higher that an alternative execution plan using INDEX ACCESS (which you not explicitly states).

Form the point of view of the optimizer, both execution plans are fine and lead to approximately same time.

So if the experience with those queries is different, and the actual elapsed time is highly different from the estimation (01:45) you (or your DBA)) should take following steps:

1) Verify Optimizer Statistics of the table

Stale statistics may fool the optimizer. Especially in case you gather the statistics on a small table and that inserts a large number of records.

2) Verify optimizer parameters and System Statistics

Some parameters notably DB_FILE_MULTIBLOCK_READ_COUNT are highly relevant for the selection between FTS and INDEX ACCES.

Similar is valid for System Statistics setting.

Final Remark

To access a non-trivial parts of a large table (say 50 from 100 days) is not necessary a use case of INDEX ACCESS. Check the partitioning feature, which (range partitioning) is designed for this kind of access.

Upvotes: 2

Related Questions