Oracle SQL Tuning with function-based index TRUNC
SQL> explain plan
for
SELECT MAX(ordernumber)
FROM GROCERY.orderproposal
WHERE TRUNC(createddate) = TRUNC(sysdate);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3380303538
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 6562 (7)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | 18 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 18 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 18 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 154K| 2712K| 6562 (7)| 00:00:01 | Q1,00 | PCWC | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 6 | INDEX FAST FULL SCAN| IDX_ORDERPROPOSAL_1 | 154K| 2712K| 6562 (7)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(TRUNC(INTERNAL_FUNCTION("CREATEDDATE"))=TRUNC(SYSDATE@!))
Note
-----
- automatic DOP: Computed Degree of Parallelism is 3
22 rows selected.
SQL> SELECT TO_CHAR(SYSDATE, 'dd-mm-yy hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'
-----------------
18-04-18 14:59:08
SQL> CREATE INDEX "GROCERY"."IDX_ORDERPROPOSAL_TMP" ON "GROCERY"."ORDERPROPOSAL"(trunc(CREATEDDATE))
TABLESPACE "GROCERY_DATA" ;
Index created.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> explain plan
for
SELECT MAX(ordernumber)
FROM GROCERY.orderproposal
WHERE TRUNC(createddate) = TRUNC(sysdate);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3684081882
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 3066 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| ORDERPROPOSAL | 154K| 2862K| 3066 (1)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_ORDERPROPOSAL_TMP | 62361 | | 389 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(TRUNC(INTERNAL_FUNCTION("CREATEDDATE"))=TRUNC(SYSDATE@!))
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
19 rows selected.
Now I am roll back the changes.
SQL> SELECT TO_CHAR(SYSDATE, 'dd-mm-yy hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'
-----------------
18-04-18 15:12:04
SQL> drop index "GROCERY"."IDX_ORDERPROPOSAL_TMP" ;
Index dropped.
You can see the performance improvement from the picture