In pinwu performance optimization, Chapter 4.4.3 describes the two features of composite indexes: prefix and optionality. What is prefix? This book describes how to exclude skipscanindex (there are not many pre-index columns, such as gender). If the constraints do not contain the first column of the composite index, this composite index will not be used. What is optionality? This book describes
In pinwu performance optimization, Chapter 4.4.3 describes the two features of composite indexes: prefix and optionality. What is prefix? This book describes how to exclude the skip scan index (there are not many pre-index columns, such as gender). If the constraints do not contain the first column of the composite index, this composite index will not be used. What is optionality? This book describes
In pinwu performance optimization, Chapter 4.4.3 describes the two features of composite indexes: prefix and optionality. What is prefix? The book explains how to exclude skip scan index (there are not many pre-index columns, such as gender). If the constraints do not contain the first column of the composite index, this composite index will not be used. What is optionality? The more field values are described in this book, the higher the optionality, the fewer positioning records, and the higher the query efficiency. That is to say, the columns with few returned records should be placed before the composite index.
In the book "get more than oracle", Chapter 5.2.1.9 also introduced the two features of composite indexes: ① In the case of equivalent queries, the composite index column no matter which column is in the front, the performance is the same. ② When one column is a range query and the other is an equivalent query, the equivalence query column is in front and the range query column is in front, such indexes are efficient.
Based on the different opinions of the two authors of composite indexes, we can identify the truth from the facts through tests. The test environment is 11.2.0.3 single instance, and oracle linux 5.4
SQL> create table t as select * from dba_objects;
Table created.
SQL> select count (*) from t;
COUNT (*)
----------
109971
SQL> select count (distinct object_type) from t;
COUNT (DISTINCTOBJECT_TYPE)
--------------------------
45
SQL> select count (distinct object_id) from t;
COUNT (DISTINCTOBJECT_ID)
------------------------
109971
SQL> create index ind_t_obj_id on t (object_id, object_type );
Index created.
SQL> create index ind_t_obj_ty on t (object_type, object_id );
Index created.
SQL> select/* + index (t, ind_t_obj_ty) */* from T where object_id = 5585 and object_type = 'table ';
Execution Plan
----------------------------------------------------------
Plan hash value: 2583045626
Bytes --------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------------
| 0 | select statement | 11 | 2277 | 4 (0) | 00:00:01 |
| 1 | table access by index rowid | T | 11 | 2277 | 4 (0) | 00:00:01 |
| * 2 | index range scan | IND_T_OBJ_TY | 2 | 3 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("OBJECT_TYPE" = 'table' AND "OBJECT_ID" = 5585)
Note
-----
-Dynamic sampling used for this statement (level = 2)
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1622 bytes sent via SQL * Net to client
520 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select/* + index (t, ind_t_obj_id) */* from T where object_id = 5585 and object_type = 'table ';
Execution Plan
----------------------------------------------------------
Plan hash value: 607336433
Bytes --------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------------
| 0 | select statement | 11 | 2277 | 2 (0) | 00:00:01 |
| 1 | table access by index rowid | T | 11 | 2277 | 2 (0) | 00:00:01 |
| * 2 | index range scan | IND_T_OBJ_ID | 2 | 1 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("OBJECT_ID" = 5585 AND "OBJECT_TYPE" = 'table ')
Note
-----
-Dynamic sampling used for this statement (level = 2)
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1622 bytes sent via SQL * Net to client
520 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Here we can see that the results of the equivalent query are different, which proves that the optionality of pinwu is correct.
SQL> select/* + index (t, ind_t_obj_ty) */* from T where object_id> 20 and object_id <2000 and object_type = 'table ';
488 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2583045626
Bytes --------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------------
| 0 | select statement | 912 | 184K | 49 (0) | 00:00:01 |
| 1 | table access by index rowid | T | 912 | 184K | 49 (0) | 00:00:01 |
| * 2 | index range scan | IND_T_OBJ_TY | 912 | 6 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("OBJECT_TYPE" = 'table' AND "OBJECT_ID"> 20 AND "OBJECT_ID" <2000)
Note
-----
-Dynamic sampling used for this statement (level = 2)
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
85 consistent gets
0 physical reads
0 redo size
51220 bytes sent via SQL * Net to client
872 bytes encoded ed via SQL * Net from client
34 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
488 rows processed
SQL> select/* + index (t, ind_t_obj_id) */* from T where object_id> 20 and object_id <2000 and object_type = 'table ';
488 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 607336433
Bytes --------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------------
| 0 | select statement | 912 | 184K | 11 (0) | 00:00:01 |
| 1 | table access by index rowid | T | 912 | 184K | 11 (0) | 00:00:01 |
| * 2 | index range scan | IND_T_OBJ_ID | 9 | 10 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("OBJECT_ID"> 20 AND "OBJECT_TYPE" = 'table' AND "OBJECT_ID" <2000)
Filter ("OBJECT_TYPE" = 'table ')
Note
-----
-Dynamic sampling used for this statement (level = 2)
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
87 consistent gets
0 physical reads
0 redo size
51220 bytes sent via SQL * Net to client
872 bytes encoded ed via SQL * Net from client
34 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
488 rows processed
The result is self-evident. When the preceding two sqls do not use hint, CBO automatically selects IND_T_OBJ_ID. Therefore, the conclusion of the book "more than oracle" is open to discussion (if it is not my test ).
