DB version:11.2.0.4
The below query is going for a full table scan , most like due to the TRUNC function in the WHERE clause.
Is there any way I can re-write the WHERE clause of this query ?
SELECT count(*),
proc_stat_code,
bridge_id
from RT_PKMST_MASTER
where trunc(create_date_time)=trunc(sysdate)
group by proc_stat_code,bridge_id
order by 3;
-- Execution plan
Plan hash value: 258310236
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 210 | 90031 (1)| 00:18:01 | | |
| 1 | SORT GROUP BY | | 15 | 210 | 90031 (1)| 00:18:01 | | |
| 2 | PARTITION RANGE ALL| | 33441 | 457K| 90028 (1)| 00:18:01 | 1 | 40 |
| 3 | PARTITION HASH ALL| | 33441 | 457K| 90028 (1)| 00:18:01 | 1 | 64 |
|* 4 | TABLE ACCESS FULL| RT_PKMST_MASTER | 33441 | 457K| 90028 (1)| 00:18:01 | 1 | 2560 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(TRUNC(INTERNAL_FUNCTION("CREATE_DATE_TIME"))=TRUNC(SYSDATE@!))