DB version: 11.2.0.4
PKMSDGV_ITEM_XA table is 500 GB in size . It is not partitioned. I had to cancel the below query after 40 minutes.
The below query goes for an INDEX FULL SCAN on PKMSDGV_ITEM_XA_UPD_IND index. This Index is 59 GB in size.
Any way to speed up this INDEX FULL SCAN. Optimizer has ignored my parallel hint. The below execution plan was generated from Shared pool.
Stats are not stale for this table.
select /*+ parallel(20) */ count(*) from PKMSDGV_ITEM_XA
WHERE TRUNC (DATE_UPD + 1 / 3) = TRUNC (SYSDATE - 1)
Execution plan:
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 80566 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX FULL SCAN| PKMSDGV_ITEM_XA_UPD_IND | 27M| 212M| 80566 (4)| 00:16:07 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRUNC(INTERNAL_FUNCTION("DATE_UPD")+.3333333333333333333333333333333333333333)=TRUNC(SYSDATE@!-1))
Note
-----
- Degree of Parallelism is 1 because of hint
Note:
I know the query runs fine if I change the WHERE clause like below. But, I want to prove to the application team that both the above and below versions of the query return the same row count.
WHERE date_upd >= Trunc(SYSDATE - 1) - 1/3
AND date_upd < Trunc(SYSDATE) - 1/3;