Performance tune case - Suboptimal index and missing index

最新推荐文章于 2022-02-15 13:32:28 发布

CPP_CHEN

最新推荐文章于 2022-02-15 13:32:28 发布

阅读量679

点赞数

分类专栏： Oracle 文章标签： performance access table statistics filter null

本文链接：https://blog.csdn.net/CPP_CHEN/article/details/7599910

版权

Oracle 专栏收录该内容

23 篇文章

订阅专栏

本文通过两个具体案例展示了SQL查询中索引的重要性：一是如何通过创建复合索引来减少子最优索引扫描返回的行数；二是如何通过为特定列创建索引来避免全表扫描，从而提高查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1) Sub-optimal index.

SQL> -- Example 1: sub-optimal index
SQL>
SQL> select /* KM1 */ job_id, department_id, last_name
2 from employees
3 where job_id = 'SA_REP'
4 and department_id is null ;
JOB_ID DEPARTMENT_ID LAST_NAME
---------- --------------- -------------------------
SA_REP . Grant
SQL>
SQL> @pln KM1
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID cdqaq2k8dvvma, child number 0
-------------------------------------
select /* KM1 */ job_id, department_id,
last_name from employees where job_id = 'SA_REP' and department_id is
null
Plan hash value: 1019430118save

---------------------------------------------------------------------------------------
| Id    | Operation | Name      | Starts | E-Rows | A-Rows | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           | 1    | | 1 | 4 |
|* 1 |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEES | 1         | 1 | 1    | 4           |
|* 2 |      INDEX RANGE SCAN                        | EMP_JOB_IX   | 1         | 30            | 30 | 2    |

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

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPARTMENT_ID" IS NULL)
2 - access("JOB_ID"='SA_REP')

===============

Step *2) Using INDEX RANG SCAN to search the table to get the ROWID, predicate is 'JOB_ID = SA_REP'. 30 rows returned.

*1) Continue process the 30 rows, accessing these 30 rows by ROWID and filter out the 'DEPARTMENT_ID IS NULL' rows. 1 row returned.

This example shows that the index range scan returns too many rows which will be dropped later which is a tremendous waste if the configuration is large.

We can create index for deparment_id and job_id to solve this problem.

SQL> create index emp_job_dept_ix on employees (department_id, job_id) compute statistics ;

SQL> select /* KM2 */ job_id, department_id, last_name
2 from employees
3 where job_id = 'SA_REP'
4 and department_id is null ;
JOB_ID DEPARTMENT_ID LAST_NAME
---------- --------------- -------------------------
SA_REP . Grant
SQL>
SQL> @pln KM2
PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------
SQL_ID b4wnf48g9pgzy, child number 0
-------------------------------------
select /* KM2 */ job_id, department_id, last_name from employees where
job_id = 'SA_REP' and department_id is null
Plan hash value: 798439539
--------------------------------------------------------------------------------------------
| Id | Operation    | Name                           | Starts | E-Rows | A-Rows | Buffers |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |                                     | 1         |                 | 1    | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES    | 1 | 1    | 1              | 2 |
|* 2 | INDEX RANGE SCAN    | EMP_JOB_DEPT_IX | 1    | 1    | 1              | 1 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("DEPARTMENT_ID" IS NULL AND "JOB_ID"='SA_REP')
filter("JOB_ID"='SA_REP')
SQL>

===============

2) Missing Index

SQL> select /* KM3 */ last_name, phone_number
2 from employees
3 where phone_number = '650.507.9822';
LAST_NAME PHONE_NUMBER
------------------------- --------------------
Feeney 650.507.9822
SQL>
SQL> @pln KM3
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID 8vzwg0vkrjp8r, child number 0
-------------------------------------
select /* KM3 */ last_name, phone_number from employees where
phone_number = '650.507.9822'
Plan hash value: 1445457117

----------------------------------------------------------------------------
| Id | Operation    | Name | Starts | E-Rows | A-Rows | Buffers |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT |                           | 1         |                 | 1              | 7 |
|* 1 |   TABLE ACCESS FULL | EMPLOYEES | 1         | 1    | 1    | 7           |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PHONE_NUMBER"='650.507.9822')

=====================================

Step *1) FULL TABLE ACCESS just return 1 row, which means the read/access to the other rows is totally wasted/dropped which means too much meaningful access/read to the rows.

Create an index for this column to solve this problem.

SQL> -- Create new index on phone_number
SQL>
SQL> create index emp_phone_ix on employees (phone_number) compute statistics ;
SQL>
SQL> select /* KM4 */ last_name, phone_number
2 from employees
3 where phone_number = '650.507.9822';
LAST_NAME PHONE_NUMBER
------------------------- --------------------
Feeney 650.507.9822
SQL>
SQL> @pln KM4

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 3tcqa5jqsyzm0, child number 0
-------------------------------------
select /* KM4 */ last_name, phone_number from employees where
phone_number = '650.507.9822'

Plan hash value: 1086981517
-----------------------------------------------------------------------------------------
| Id | Operation                        | Name    | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |                                  | 1 |                 | 1             | 3            |
| 1 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1         | 1    | 1             | 3            |
|* 2 |       INDEX RANGE SCAN    | EMP_PHONE_IX | 1 | 1              | 1 | 2    |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PHONE_NUMBER"='650.507.9822')