A Preliminary Study on the Performance of composite indexes

Source: Internet
Author: User
Tags sorts
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 ).

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.