SqlTuning&Composite Index

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/