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



Comments