Hi,
I need tuning recommendations for the below query which is to run on a partitioned table, please share your ideas.
SELECT primary_id
FROM schema.table
WHERE create_date>=to_timestamp('2018-10-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND create_date<to_timestamp('2018-10-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1075K| 50M| 104K (1) | 00:20:52 | | |
| 1 | PARTITION RANGE SINGLE | | 1075K| 50M| 104K (1) | 00:20:52 | 90 | 90 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID | table | 1075K| 50M| 104K (1) | 00:20:52 | 90 | 90 |
|* 3 | INDEX RANGE SCAN | IDX03 | 487K| | 2980 (1) | 00:00:36 | 90 | 90 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CREATE_DATE">=TIMESTAMP' 2018-10-25 00:00:00.000000000' AND "CREATE_DATE"<TIMESTAMP' 2018-10-26
00:00:00.000000000')
16 rows selected.
DB Version - 11.2.0.4.0
Statistics -
TABLE_NAME LAST_ANALYZED
------------------------------ ---------
Table 25-OCT-18