Composite index optimization query containing function expressions

A friend in the circle has an SQL statement to be optimized. The SQL statement and execution plan are as follows: SELECT2MAX (tt. workitem_id) workitem_id, 3tt. Digest (tt. pos_rcv_datim, yyyymmdd) 201408159GROUPBY10tt. TAS

A friend in the circle has an SQL statement to be optimized. The SQL statement and execution plan are as follows: SELECT 2 MAX (tt. workitem_id) workitem_id, 3 tt. task_id 4 FROM 5 doudou tt 6 WHERE 7 tt. position_id = 5 8 AND TO_CHAR (tt. pos_rcv_datim, 'yyyymmdd') = 20140815 9 group by 10 tt. task_id; 67

A friend in the circle has an SQL statement to be optimized. The SQL statement and execution plan are as follows:
SELECT
2 MAX (tt. workitem_id) workitem_id,
3 tt. task_id
4 FROM
5 doudou tt
6 WHERE
7 tt. position_id = 5
8 AND TO_CHAR (tt. pos_rcv_datim, 'yyyymmdd') = 20140815
9 GROUP
10 tt. task_id;

670 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3539805324

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1797 | 88053 | 4570 (1)| 00:00:55 |
| 1 | HASH GROUP BY | | 1797 | 88053 | 4570 (1)| 00:00:55 |
|* 2 | TABLE ACCESS FULL| DOUDOU | 1800 | 88200 | 4569 (1)| 00:00:55 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("TT"."POSITION_ID"=5 AND
TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("TT"."POS_RCV_DATIM"),'yyyymmdd'))=2
0140815)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
16633 consistent gets
16630 physical reads
0 redo size
35014 bytes sent via SQL*Net to client
1007 bytes received via SQL*Net from client
46 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
670 rows processed

It can be seen that the full table scan here may make it possible for a friend to remove TO_CHAR and create a composite index (pos_rcv_datim, position_id), but this SQL statement cannot be modified, so there is no way to composite indexes. Otherwise, we can create composite indexes containing function expressions.

Create index ind_doudou04 on doudou (TO_CHAR (tt. pos_rcv_datim, 'yyyymmdd'), position_id) and then read the latest execution plan:

Execution Plan
----------------------------------------------------------
Plan hash value: 1864030226

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1862 | 87514 | 50 (2)| 00:00:01|

| 1 | HASH GROUP BY | | 1862 | 87514 | 50 (2)| 00:00:01|

|* 2 | INDEX RANGE SCAN| IND_DOUDOU4 | 1864 | 87608 | 49 (0)| 00:00:01|

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(TO_CHAR(INTERNAL_FUNCTION("POS_RCV_DATIM"),'yyyymmdd')='201
40815' AND "TT"."POSITION_ID"=5)
filter("TT"."POSITION_ID"=5)

Statistics
----------------------------------------------------------
17 recursive calls
0 db block gets
62 consistent gets
52 physical reads
0 redo size
35014 bytes sent via SQL*Net to client
1007 bytes received via SQL*Net from client
46 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
670 rows processed

The above SQL optimization is not difficult, but here I want to show that most of the composite indexes we usually see are (col1, col2 ,... Coln) This type rarely creates composite indexes including (col1, func (col, func_name). Do not take it for granted that it is possible or impossible, oracle is more practical.

Original article address: I would like to thank the original author for sharing the COMPOSITE INDEX OPTIMIZATION query containing function expressions.