SqlTuning&Composite Index

最新推荐文章于 2025-07-05 12:04:55 发布

转载最新推荐文章于 2025-07-05 12:04:55 发布 · 121 阅读

本文详细探讨了SQL查询性能优化中复合索引的应用与效果，通过对比不同索引策略下的执行计划和性能指标，揭示了合理使用复合索引能显著提升查询效率的关键原理。具体分析了如何选择具有更好选择性的列作为复合索引的前导列，以实现更高效的查询处理。实例展示了在创建和调整索引策略后，查询性能从分钟级缩短至秒级的显著改善。

SQL> select a.processinstid from sas.wf_h_processinst a where a.createtime >= sysdate - 90;
select a.processinstid from sas.wf_h_processinst a where a.createtime >= sysdate - 90
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

Elapsed: 00:00:22.44

*******************半天不出来，Ctr+C中断了。
SQL> select count(a.processinstid) from sas.wf_h_processinst a where a.createtime >= sysdate - 90;

COUNT(A.PROCESSINSTID)
----------------------
1085095

Execution Plan
----------------------------------------------------------
Plan hash value: 3833004957
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 56234 (2)| 00:11:15 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| WF_H_PROCESSINST | 750K| 5862K| 56234 (2)| 00:11:15 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):

SQL> select count(*) from sas.WF_H_PROCESSINST;
COUNT(*)
----------
9489902

SQL> select index_name,index_type from dba_indexes where table_name='WF_H_PROCESSINST';

INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
PK_WF_H_PROCESSINSTN NORMAL
WF_IDX_H_PROCINST_PDEFIDN NORMAL
SYS_IL0000055274C00008$$ LOB
PK_WF_H_PROCESSINSTN NORMAL
WF_IDX_H_PROCINST_PDEFIDN NORMAL
SYS_IL0000056639C00008$$ LOB
PK_WF_H_PROCESSINST NORMAL
WF_IDX_H_PROCINST_PDEFID NORMAL
SYS_IL0000080038C00008$$ LOB
SYS_IL0000068136C00008$$ LOB
IDX_WF_H_PROCESSINST_N NORMAL

11 rows selected.
SQL> col COLUMN_NAME for a30
SQL> /
INDEX_OWNER INDEX_NAME COLUMN_NAME TABLE_OWNER
------------------------------ ------------------------------ ------------------------------ ------------------------------
GSIOMS PK_WF_H_PROCESSINSTN PROCESSINSTID GSIOMS
GSIOMS WF_IDX_H_PROCINST_PDEFIDN PROCESSDEFID GSIOMS
SAS PK_WF_H_PROCESSINSTN PROCESSINSTID SAS
SAS WF_IDX_H_PROCINST_PDEFIDN PROCESSDEFID SAS
CSSS PK_WF_H_PROCESSINST PROCESSINSTID CSSS
CSSS WF_IDX_H_PROCINST_PDEFID PROCESSDEFID CSSS
CSSS_HIS IDX_WF_H_PROCESSINST_N PROCESSINSTID CSSS_HIS
7 rows selected.

SQL> create index ind_createtime on sas.wf_h_pro
2
SQL>
SQL>
SQL>
SQL> ALTER SESSION ENABLE PARALLEL DML;
ERROR:
ORA-12841: Cannot alter the session parallel DML state within a transaction
Elapsed: 00:00:00.00
SQL> commit;
Commit complete.

Elapsed: 00:00:00.00
SQL> ALTER SESSION ENABLE PARALLEL DML;

Session altered.

Elapsed: 00:00:00.01
SQL> create index ind_createtime on sas.wf_h_processinst(createtime) PARALLEL 6;

Index created.

Elapsed: 00:00:28.58
SQL> Alter session disable parallel DML;
Session altered.

SQL> select a.processinstid from sas.wf_h_processinst a where a.createtime >= sysdate - 90;

1084816 rows selected.

Elapsed: 00:00:14.65

Execution Plan
----------------------------------------------------------
Plan hash value: 342427583
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 750K| 10M| 28553 (1)| 00:05:43 |
|* 1 | VIEW | index$_join$_001 | 750K| 10M| 28553 (1)| 00:05:43 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | IND_CREATETIME | 750K| 10M| 13020 (1)| 00:02:37 |
| 4 | INDEX FAST FULL SCAN| PK_WF_H_PROCESSINSTN | 750K| 10M| 33048 (1)| 00:06:37 |
-----------------------------------------------------------------------------------------------

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

1 - filter("A"."CREATETIME">=SYSDATE@!-90)
2 - access(ROWID=ROWID)
3 - access("A"."CREATETIME">=SYSDATE@!-90)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
102752 consistent gets
0 physical reads
0 redo size
21035042 bytes sent via SQL*Net to client
796023 bytes received via SQL*Net from client
72323 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1084816 rows processed

******************drop 普通索引：
SQL> drop index ind_createtime;
Index dropped.

*******************************新建复合索引：
SQL> ALTER SESSION ENABLE PARALLEL DML;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL>
SQL> create index sas.ind_createtime_processinstid on sas.wf_h_processinst(createtime,processinstid) PARALLEL 6;
commit;

Alter session disable parallel DML;

Index created.

Elapsed: 00:00:32.72
SQL>
Commit complete.

Elapsed: 00:00:00.00
SQL> SQL> SQL>
Session altered.

Elapsed: 00:00:00.00
SQL> SQL> SQL> SQL>
SQL>
SQL>
SQL> select a.processinstid from sas.wf_h_processinst a where a.createtime >= sysdate - 90;

1084539 rows selected.

Elapsed: 00:00:08.08

Execution Plan
----------------------------------------------------------
Plan hash value: 1411650524

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 750K| 10M| 2658 (1)| 00:00:32 |
|* 1 | INDEX RANGE SCAN| IND_CREATETIME_PROCESSINSTID | 750K| 10M| 2658 (1)| 00:00:32 |
-------------------------------------------------------------------------------------------------

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

1 - access("A"."CREATETIME">=SYSDATE@!-90 AND "A"."CREATETIME" IS NOT NULL)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
75846 consistent gets
3794 physical reads
0 redo size
21029621 bytes sent via SQL*Net to client
795814 bytes received via SQL*Net from client
72304 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1084539 rows processed

结论：合理使用复合索引，能使性能有较大的提升；把索引选择性更好的列做前导，会大大提升性能的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13750068/viewspace-1613126/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13750068/viewspace-1613126/