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