DB version: 11.2.0.4
OS : OL 6.6
I was trying to delete rows older than 6 years from the below table ( 6 x 365 = 2190 ).
There are 3.8 Million rows which are older than 6 years and the Table size is 9GB.
But, the DELETE is taking very long due to Full table scan. And the parallel hint is ignored too.
state_mod_time column is part of an index with 2 columns . But, state_mod_time column's order is second. It could be the reason why this index is ignored when I tried to force the use of index using an index hint .
SQL> select index_name, column_name, COLUMN_POSITION from USER_IND_COLUMNS where table_name = 'MNP_RCP_STATUS_STATE_HISTORY' ORDER By index_name,COLUMN_POSITION ASC;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ ---------------
RCP_EBI_STATUS_MOD_TIME_IDX1 NP_TXN_ID 1
RCP_EBI_STATUS_MOD_TIME_IDX1 STATUS_MOD_TIME 2
Statistics in this table is 4 months old, but it is not stale.
Is there any to force the optimizer to use the above index ? Or any other way to speed up this DELETE ?
Explain plan output of each statement given below
Query1.
delete /*+ parallel(4) */
from mnp_rcp_status_state_history
where state_mod_time < sysdate - 2190;
Plan hash value: 2681822326
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 51M| 1951M| 242K (4)| 00:48:29 |
| 1 | DELETE | MNP_RCP_STATUS_STATE_HISTORY | | | | |
|* 2 | TABLE ACCESS FULL| MNP_RCP_STATUS_STATE_HISTORY | 51M| 1951M| 242K (4)| 00:48:29 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATE_MOD_TIME"<SYSDATE@!-2190)
14 rows selected.
Query2.
select /*+ parallel(4) */ count(*)
from mnp_rcp_status_state_history
where state_mod_time < sysdate - 2190;
Plan hash value: 1562979060
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 242K (4)| 00:48:27 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| MNP_RCP_STATUS_STATE_HISTORY | 51M| 536M| 242K (4)| 00:48:27 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATE_MOD_TIME"<SYSDATE@!-2190)
14 rows selected.
Query3. I tried using the index hint as shown below , but still the query is going for FTS
select /*+ index(mh rcp_ebi_status_mod_time_idx1) */ count(*)
from mnp_rcp_status_state_history mh
where state_mod_time < sysdate - 2190 ;
Plan hash value: 1562979060
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 242K (4)| 00:48:27 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| MNP_RCP_STATUS_STATE_HISTORY | 51M| 536M| 242K (4)| 00:48:27 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATE_MOD_TIME"<SYSDATE@!-2190)
14 rows selected.