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  |