Tuning SQL from over 30mins to less than 1s by using Combined Index

于 2012-10-17 12:45:40 发布
阅读量99 收藏
点赞数
Tuning SQL by using combined index  --Running from over 30mins downgrade to 0.1s

******************ENV********************
RAC: 2 nodes
OS:  LINUX 5
DB: 10.2.0.5

******************ENV********************

1) Set OS env for Chinese query

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

2) Check sql plan roughly, generate general plan shoule be fine, no need to use advance plan, just for detail purpose!

set linesize 200
set pagesize 200
set long 99999999
SQL> explain plan for
  2  SELECT *
  3    FROM (SELECT ROWNUM AS row_i,
  4                 "gj" AS "gj",
  5                 "sfzhm" AS "sfzhm",
  6                 "姓名" AS "姓名",
  7                 "性别" AS "性别",
  8                 "jszzt" AS "jszzt",
  9                 "zjcx" AS "zjcx",
 10                 TO_CHAR ("cclzrq", 'yyyy-mm-dd hh24:mi:ss')
 11                    AS "cclzrq",
 12                 "fzjg" AS " fzjg",
 13                 TO_CHAR ("yxqs", 'yyyy-mm-dd hh24:mi:ss')
 14                    AS "ysq s",
 15                 TO_CHAR ("yxqz", 'yyyy-mm-dd hh24:mi:ss')
 16                    AS "yxqz",
 17                 "dabh"
 18            FROM GX_JG.V_GAJT_DRIVINGLICENSE_JX
 19           WHERE     1 = 1
 20                 AND "jszzt_DCODE" LIKE 'A%'
 21                 AND "zjcx" = 'C1'
 22                 AND yxqs >= to_date('20101018','yyyymmdd') ) where ROWNUM<=500
 23  /


Explained.

Elapsed: 00:00:00.03
SQL> select * from table(dbms_xplan.display(null,null,'ADVANCED',null));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2726607306

------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                        |   500 |   560K|   288   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| GAJT_PARA_CODE         |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_GAJT_PARA_CODE_FLH |     1 |       |     1   (0)| 00:00:01 |
|*  3 |  TABLE ACCESS BY INDEX ROWID| GAJT_PARA_CODE         |     1 |    25 |     2   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN          | IND_GAJT_PARA_CODE_FLH |     1 |       |     1   (0)| 00:00:01 |
|*  5 |  TABLE ACCESS BY INDEX ROWID| GAJT_PARA_CODE         |     1 |    25 |     2   (0)| 00:00:01 |
|*  6 |   INDEX RANGE SCAN          | IND_GAJT_PARA_CODE_FLH |     1 |       |     1   (0)| 00:00:01 |
|*  7 |  TABLE ACCESS BY INDEX ROWID| GAJT_PARA_CODE         |     1 |    25 |     2   (0)| 00:00:01 |
|*  8 |   INDEX RANGE SCAN          | IND_GAJT_PARA_CODE_FLH |     1 |       |     1   (0)| 00:00:01 |
|*  9 |  COUNT STOPKEY              |                        |       |       |            |          |
|  10 |   VIEW                      |                        |   500 |   560K|   288   (1)| 00:00:04 |
|  11 |    COUNT                    |                        |       |       |            |          |
|* 12 |     TABLE ACCESS FULL       | GAJT_DRIVINGLICENSE    |   500 | 39500 |   288   (1)| 00:00:04 |    ---CBO estimate incorrectly ?
------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$4        / GAJT_PARA_CODE@SEL$4
   2 - SEL$4        / GAJT_PARA_CODE@SEL$4
   3 - SEL$5        / GAJT_PARA_CODE@SEL$5
   4 - SEL$5        / GAJT_PARA_CODE@SEL$5
   5 - SEL$6        / GAJT_PARA_CODE@SEL$6
   6 - SEL$6        / GAJT_PARA_CODE@SEL$6
   7 - SEL$7        / GAJT_PARA_CODE@SEL$7
   8 - SEL$7        / GAJT_PARA_CODE@SEL$7
   9 - SEL$1
  10 - SEL$335DD26A / from$_subquery$_001@SEL$1
  11 - SEL$335DD26A
  12 - SEL$335DD26A / T@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$4" "GAJT_PARA_CODE"@"SEL$4" ("GAJT_PARA_CODE"."FLH"))
      INDEX_RS_ASC(@"SEL$5" "GAJT_PARA_CODE"@"SEL$5" ("GAJT_PARA_CODE"."FLH"))
      INDEX_RS_ASC(@"SEL$6" "GAJT_PARA_CODE"@"SEL$6" ("GAJT_PARA_CODE"."FLH"))
      INDEX_RS_ASC(@"SEL$7" "GAJT_PARA_CODE"@"SEL$7" ("GAJT_PARA_CODE"."FLH"))
      FULL(@"SEL$335DD26A" "T"@"SEL$3")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      MERGE(@"SEL$3")
      OUTLINE_LEAF(@"SEL$335DD26A")
      OUTLINE_LEAF(@"SEL$7")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$4")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   1 - filter(TO_NUMBER("LH")=31)
   2 - access("FLH"=:B1)
   3 - filter(TO_NUMBER("LH")=41)
   4 - access("FLH"=:B1)
   5 - filter(TO_NUMBER("LH")=55)
   6 - access("FLH"=:B1)
   7 - filter(TO_NUMBER("LH")=34)
   8 - access("FLH"=:B1)
   9 - filter(ROWNUM<=500)
  12 - filter("T"."ZJCX"='C1' AND "T"."YXQS">=TO_DATE(' 2010-10-18 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "T"."ZT" LIKE 'A%')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "GAJT_PARA_CODE".ROWID[ROWID,10], "LH"[VARCHAR2,4], "NR"[VARCHAR2,512]
   2 - "GAJT_PARA_CODE".ROWID[ROWID,10]
   3 - "GAJT_PARA_CODE".ROWID[ROWID,10], "LH"[VARCHAR2,4], "NR"[VARCHAR2,512]
   4 - "GAJT_PARA_CODE".ROWID[ROWID,10]
   5 - "GAJT_PARA_CODE".ROWID[ROWID,10], "LH"[VARCHAR2,4], "NR"[VARCHAR2,512]
   6 - "GAJT_PARA_CODE".ROWID[ROWID,10]
   7 - "GAJT_PARA_CODE".ROWID[ROWID,10], "LH"[VARCHAR2,4], "NR"[VARCHAR2,512]
   8 - "GAJT_PARA_CODE".ROWID[ROWID,10]
   9 - "from$_subquery$_001"."ROW_I"[NUMBER,22], "from$_subquery$_001"."gj"[VARCHAR2,512],
       "from$_subquery$_001"."sfzhm"[VARCHAR2,30], "from$_subquery$_001"."姓名"[VARCHAR2,30],
       "from$_subquery$_001"."性别"[VARCHAR2,512], "from$_subquery$_001"."jszzt"[VARCHAR2,512],
       "from$_subquery$_001"."zjcx"[VARCHAR2,50], "from$_subquery$_001"."cclzrq"[VARCHAR2,19],
       "from$_subquery$_001"." fzjg"[VARCHAR2,512], "from$_subquery$_001"."ysq s"[VARCHAR2,19],
       "from$_subquery$_001"."yxqz"[VARCHAR2,19], "from$_subquery$_001"."dabh"[VARCHAR2,12]
  10 - "from$_subquery$_001"."ROW_I"[NUMBER,22], "from$_subquery$_001"."gj"[VARCHAR2,512],
       "from$_subquery$_001"."sfzhm"[VARCHAR2,30], "from$_subquery$_001"."姓名"[VARCHAR2,30],
       "from$_subquery$_001"."性别"[VARCHAR2,512], "from$_subquery$_001"."jszzt"[VARCHAR2,512],
       "from$_subquery$_001"."zjcx"[VARCHAR2,50], "from$_subquery$_001"."cclzrq"[VARCHAR2,19],
       "from$_subquery$_001"." fzjg"[VARCHAR2,512], "from$_subquery$_001"."ysq s"[VARCHAR2,19],
       "from$_subquery$_001"."yxqz"[VARCHAR2,19], "from$_subquery$_001"."dabh"[VARCHAR2,12]
  11 - "T"."DABH"[VARCHAR2,12], "T"."SFZMHM"[VARCHAR2,30], "T"."XM"[VARCHAR2,30],
       "T"."XB"[VARCHAR2,10], "T"."GJ"[VARCHAR2,50], "T"."ZJCX"[VARCHAR2,50], "T"."CCLZRQ"[DATE,7],
       "T"."FZJG"[VARCHAR2,50], "T"."YXQS"[DATE,7], "T"."YXQZ"[DATE,7], "T"."ZT"[VARCHAR2,30],
       ROWNUM[4]
  12 - "T"."DABH"[VARCHAR2,12], "T"."SFZMHM"[VARCHAR2,30], "T"."XM"[VARCHAR2,30],
       "T"."XB"[VARCHAR2,10], "T"."GJ"[VARCHAR2,50], "T"."ZJCX"[VARCHAR2,50], "T"."CCLZRQ"[DATE,7],
       "T"."FZJG"[VARCHAR2,50], "T"."YXQS"[DATE,7], "T"."YXQZ"[DATE,7], "T"."ZT"[VARCHAR2,30]




3) Try to run SQL to check original time           --Not finish after 30mins (run at noon just for the evidence of
                           --confirming the real elaspe time!)

SQL> SELECT *
  2    FROM (SELECT ROWNUM AS row_i,
  3                 "gj" AS "gj",
  4                 "sfzhm" AS "sfzhm",
  5                 "姓名" AS "姓名",
  6                 "性别" AS "性别",
  7                 "jszzt" AS "jszzt",
  8                 "zjcx" AS "zjcx",
  9                 TO_CHAR ("cclzrq", 'yyyy-mm-dd hh24:mi:ss')
 10                    AS "cclzrq",
 11                 "fzjg" AS " fzjg",
 12                 TO_CHAR ("yxqs", 'yyyy-mm-dd hh24:mi:ss')
 13                    AS "ysq s",
 14                 TO_CHAR ("yxqz", 'yyyy-mm-dd hh24:mi:ss')
 15                    AS "yxqz",
 16                 "dabh"
 17            FROM GX_JG.V_GAJT_DRIVINGLICENSE_JX
 18           WHERE     1 = 1
 19                 AND "jszzt_DCODE" LIKE 'A%'
 20                 AND "zjcx" = 'C1'
 21                 AND yxqs >= to_date('20101018','yyyymmdd') ) where ROWNUM<=500
 22  /
          FROM GX_JG.V_GAJT_DRIVINGLICENSE_JX
                     *
ERROR at line 17:
ORA-01013: user requested cancel of current operation


Elapsed: 00:33:32.22

SQL> 


4) Get table & View statistcs

-Get rough stats from toad first

GAJT_DRIVINGLICENSE: 2.38G Non-partition  9961047 rows
    Condition column index status:
        ZJCX: No index
        YXQS: Index
        ZT:   No index

GAJT_PARA_CODE: 256K, 4832 rows

-Check real stats and confirm if stats correct!

alter session set db_file_multiblock_read_count=64;   --default is 16
select count(*) from GX_JG.GAJT_DRIVINGLICENSE;       --same with via toad

9961047 rows returned 

select count(*) from GX_JG.GAJT_PARA_CODE;            --same with via toad

5) Check conditioned column selectivity   --via toad

              ZT:   19/9961047
        ZJCX:   27/9961047
        YXQS:   3106/9961047
   


6) Try to get condition column return respectivly;

SQL> select count(*) from GX_JG.GAJT_DRIVINGLICENSE where ZT LIKE 'A%';    --No return over 5 mins
select count(*) from GX_JG.GAJT_DRIVINGLICENSE where ZT LIKE 'A%'
                           *
ERROR at line 1:
ORA-01013: user requested cancel of current operation


Elapsed: 00:05:01.59

SQL> select count(*) from GX_JG.GAJT_DRIVINGLICENSE where ZJCX='C1';       --No return over 5 mins
select count(*) from GX_JG.GAJT_DRIVINGLICENSE where ZJCX='C1'
                           *
ERROR at line 1:
ORA-01013: user requested cancel of current operation


Elapsed: 00:05:09.06

SQL> select count(*) from GX_JG.GAJT_DRIVINGLICENSE where YXQS>=TO_DATE('2010-10-18 00:00:00', 'syyyy-mm-ddhh24:mi:ss');

  COUNT(*)
----------
   1306935

Elapsed: 00:00:00.18

Elapsed: 00:00:00.18
SQL> explain plan for
  2  select count(*) from GX_JG.GAJT_DRIVINGLICENSE where YXQS>=TO_DATE('2010-10-18 00:00:00', 'syyyy-mm-ddhh24:mi:ss');

Explained.

Elapsed: 00:00:00.09
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3362028376

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |     8 |  3400   (1)| 00:00:41 |
|   1 |  SORT AGGREGATE   |                |     1 |     8 |            |          |
|*  2 |   INDEX RANGE SCAN| IND_DRIVI_YXQS |  1303K|     9M|  3400   (1)| 00:00:41 |
------------------------------------------------------------------------------------

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

   2 - access("YXQS">=TO_DATE(' 2010-10-18 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

15 rows selected.


7) Create combined index on YXQS ZJCX ZT in order

SQL> create index ind_c_yxqs_zjcx_zt on GX_JG.GAJT_DRIVINGLICENSE(YXQS,ZJCX,ZT) nologging parallel 6;

Index created.

Elapsed: 00:00:28.37

8) Run SQL again   --Elapsed: 00:00:00.01    352  consistent gets

SQL> set autot trace
SQL> SELECT *
  2    FROM (SELECT ROWNUM AS row_i,
  3                 "gj" AS "gj",
  4                 "sfzhm" AS "sfzhm",
  5                 "姓名" AS "姓名",
  6                 "性别" AS "性别",
  7                 "jszzt" AS "jszzt",
  8                 "zjcx" AS "zjcx",
  9                 TO_CHAR ("cclzrq", 'yyyy-mm-dd hh24:mi:ss')
 10                    AS "cclzrq",
 11                 "fzjg" AS " fzjg",
 12                 TO_CHAR ("yxqs", 'yyyy-mm-dd hh24:mi:ss')
 13                    AS "ysq s",
 14                 TO_CHAR ("yxqz", 'yyyy-mm-dd hh24:mi:ss')
 15                    AS "yxqz",
 16                 "dabh"
 17            FROM GX_JG.V_GAJT_DRIVINGLICENSE_JX
 18           WHERE     1 = 1
 19                 AND "jszzt_DCODE" LIKE 'A%'
 20                 AND "zjcx" = 'C1'
 21                 AND yxqs >= to_date('20101018','yyyymmdd') ) where ROWNUM<=500
 22  /

500 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 525775064

---------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                        |   500 |   560K|   346   (0)| 00:00:05 |
|*  1 |  TABLE ACCESS BY INDEX ROWID   | GAJT_PARA_CODE         |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN             | IND_GAJT_PARA_CODE_FLH |     1 |       |     1   (0)| 00:00:01 |
|*  3 |  TABLE ACCESS BY INDEX ROWID   | GAJT_PARA_CODE         |     1 |    25 |     2   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN             | IND_GAJT_PARA_CODE_FLH |     1 |       |     1   (0)| 00:00:01 |
|*  5 |  TABLE ACCESS BY INDEX ROWID   | GAJT_PARA_CODE         |     1 |    25 |     2   (0)| 00:00:01 |
|*  6 |   INDEX RANGE SCAN             | IND_GAJT_PARA_CODE_FLH |     1 |       |     1   (0)| 00:00:01 |
|*  7 |  TABLE ACCESS BY INDEX ROWID   | GAJT_PARA_CODE         |     1 |    25 |     2   (0)| 00:00:01 |
|*  8 |   INDEX RANGE SCAN             | IND_GAJT_PARA_CODE_FLH |     1 |       |     1   (0)| 00:00:01 |
|*  9 |  COUNT STOPKEY                 |                        |       |       |            |          |
|  10 |   VIEW                         |                        |   500 |   560K|   346   (0)| 00:00:05 |
|  11 |    COUNT                       |                        |       |       |            |          |
|  12 |     TABLE ACCESS BY INDEX ROWID| GAJT_DRIVINGLICENSE    |   500 | 39500 |   346   (0)| 00:00:05 |
|* 13 |      INDEX RANGE SCAN          | IND_C_YXQS_ZJCX_ZT     |       |       |    21   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER("LH")=31)
   2 - access("FLH"=:B1)
   3 - filter(TO_NUMBER("LH")=41)
   4 - access("FLH"=:B1)
   5 - filter(TO_NUMBER("LH")=55)
   6 - access("FLH"=:B1)
   7 - filter(TO_NUMBER("LH")=34)
   8 - access("FLH"=:B1)
   9 - filter(ROWNUM<=500)
  13 - access("T"."YXQS">=TO_DATE(' 2010-10-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "T"."ZJCX"='C1' AND "T"."ZT" LIKE 'A%')
       filter("T"."ZJCX"='C1' AND "T"."ZT" LIKE 'A%')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        352  consistent gets
          0  physical reads
          0  redo size
      37103  bytes sent via SQL*Net to client
        855  bytes received via SQL*Net from client
         35  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        500  rows processed

9) Create combined index on YXQS ZJCX only in order

SQL> create index ind_c_yxqs_zjcx on GX_JG.GAJT_DRIVINGLICENSE(YXQS,ZJCX) nologging parallel 6;

Index created.

Elapsed: 00:00:21.25

10) Run SQL again      --Elapsed: 00:00:00.03    354  consistent gets

SQL> set autot trace
SQL> SELECT *
  2    FROM (SELECT ROWNUM AS row_i,
  3                 "gj" AS "gj",
  4                 "sfzhm" AS "sfzhm",
  5                 "姓名" AS "姓名",
  6                 "性别" AS "性别",
  7                 "jszzt" AS "jszzt",
  8                 "zjcx" AS "zjcx",
  9                 TO_CHAR ("cclzrq", 'yyyy-mm-dd hh24:mi:ss')
 10                    AS "cclzrq",
 11                 "fzjg" AS " fzjg",
 12                 TO_CHAR ("yxqs", 'yyyy-mm-dd hh24:mi:ss')
 13                    AS "ysq s",
 14                 TO_CHAR ("yxqz", 'yyyy-mm-dd hh24:mi:ss')
 15                    AS "yxqz",
 16                 "dabh"
 17            FROM GX_JG.V_GAJT_DRIVINGLICENSE_JX
 18           WHERE     1 = 1
 19                 AND "jszzt_DCODE" LIKE 'A%'
 20                 AND "zjcx" = 'C1'
 21                 AND yxqs >= to_date('20101018','yyyymmdd') ) where ROWNUM<=500
 22  /

500 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 1817236639

---------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                        |   500 |   560K|   413   (0)| 00:00:05 |
|*  1 |  TABLE ACCESS BY INDEX ROWID   | GAJT_PARA_CODE         |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN             | IND_GAJT_PARA_CODE_FLH |     1 |       |     1   (0)| 00:00:01 |
|*  3 |  TABLE ACCESS BY INDEX ROWID   | GAJT_PARA_CODE         |     1 |    25 |     2   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN             | IND_GAJT_PARA_CODE_FLH |     1 |       |     1   (0)| 00:00:01 |
|*  5 |  TABLE ACCESS BY INDEX ROWID   | GAJT_PARA_CODE         |     1 |    25 |     2   (0)| 00:00:01 |
|*  6 |   INDEX RANGE SCAN             | IND_GAJT_PARA_CODE_FLH |     1 |       |     1   (0)| 00:00:01 |
|*  7 |  TABLE ACCESS BY INDEX ROWID   | GAJT_PARA_CODE         |     1 |    25 |     2   (0)| 00:00:01 |
|*  8 |   INDEX RANGE SCAN             | IND_GAJT_PARA_CODE_FLH |     1 |       |     1   (0)| 00:00:01 |
|*  9 |  COUNT STOPKEY                 |                        |       |       |            |          |
|  10 |   VIEW                         |                        |   500 |   560K|   413   (0)| 00:00:05 |
|  11 |    COUNT                       |                        |       |       |            |          |
|* 12 |     TABLE ACCESS BY INDEX ROWID| GAJT_DRIVINGLICENSE    |   500 | 39500 |   413   (0)| 00:00:05 |
|* 13 |      INDEX RANGE SCAN          | IND_C_YXQS_ZJCX        |       |       |    19   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER("LH")=31)
   2 - access("FLH"=:B1)
   3 - filter(TO_NUMBER("LH")=41)
   4 - access("FLH"=:B1)
   5 - filter(TO_NUMBER("LH")=55)
   6 - access("FLH"=:B1)
   7 - filter(TO_NUMBER("LH")=34)
   8 - access("FLH"=:B1)
   9 - filter(ROWNUM<=500)
  12 - filter("T"."ZT" LIKE 'A%')
  13 - access("T"."YXQS">=TO_DATE(' 2010-10-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "T"."ZJCX"='C1')
       filter("T"."ZJCX"='C1')


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        354  consistent gets
          3  physical reads
          0  redo size
      37103  bytes sent via SQL*Net to client
        855  bytes received via SQL*Net from client
         35  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)

Conclude: 
Principal for using combined index: prefix and selectivity
复合索引设计建议
1. 分析SQL语句中的约束条件字段
2. 如果约束条件字段比较固定，则优先考虑创建针对多字段的普通B*树复合索引。例如同时涉及到 YXQS ZJCX ZT 三个字段的条件，则可以考虑建立一个复合索引
3. 如果单个字段是主键或唯一字段，或者可选性非常高的字段，尽管约束条件字段比较固定，也不一定要建成复合索引，可建成单字段索引，降低复合索引开销。
4. 在复合索引设计中，需首先考虑复合索引第一个设计原则：复合索引的前缀性（prefixing）。即SQL语句中，只有复合索引的第一个字段作为约束条件，该复合索引才会启用。
5. 在复合索引设计中，其次应考虑复合索引的可选性（Selectivity或Cardinality）。即按可选性高低，进行复合索引字段的排序。例如上述索引的的字段排列顺序为： YXQS ZJCX ZT 。
6. 如果条件涉及的字段不固定，组合比较灵活，则分别 YXQS ZJCX ZT 三个字段建立索引  
7. 如果是多表连接SQL语句，注意是否可以在被驱动表（drived table）的连接字段与该表的其它约束条件字段上，创建复合索引。
8. 通过多种SQL分析工具，分析执行计划并以量化形式评估效果。


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

转载于:http://blog.itpub.net/27064837/viewspace-746608/

cuixu4254
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值