组合索引使用错误案例

今天收到一个上线sql:

相关脚本如下:

索引结构:

create unique index PK_OSS_OPERATORSITE_ACT_DAY on OSS_OPERATORSITE_ACT_DAY (STATEDATE, PROVCODE, AREACODE, SITECODE, USERNUMBER, PORTALID);

merge into 条件是:

 Forall i In 1 .. l_Type_Usernumber.Count

                    Merge Into Oss_Operatorsite_Act_Day x Using Dual y

                                 On(x.Usernumber = l_Type_Usernumber(i) And

                                    x.Sitecode = l_Type_Sitecode(i) And

                                    x.Portalid = l_Type_Portalid(i) And

                  x.statedate = Ln_Statday)

 

从这个代码一看就知道问题所在了：

这里就可以看出来入口只有4个字段，主键是6个字段，一般只有merge的字段会大于或等于索引的字段：另外再试想下usernumber（手机号）其实就可以保证provcode(省份),areacode（地区）的唯一性了，这是在业务上保证的，一个手机号定了，那么他的省份和地区就固定下来了，我们优化的时候很多时候是可以从业务上去优化的。

另外其实这里还有一个问题，很明显索引建立的有问题，导致性能差别极大：

 

拿这个例子做个测试:

SQL> 

select * from OSS_OPERATORSITE_ACT_DAY_hch where STATEDATE=20121205 and usernumber ='13826093949' and SITECODE='0' and PORTALID=12; （和merge条件一致）

Execution Plan

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

Plan hash value: 2326136338

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

| Id  | Operation                   | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                               |     1 |   198 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| OSS_OPERATORSITE_ACT_DAY_HCH  |     1 |   198 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | OSS_OPERATORSITE_ACT_DAY_IDX1 |     1 |       |     1   (0)| 00:00:01 |             --走索引1的开销是5个逻辑读:

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

Predicate Information (identified by operation id):

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

   2 - access("STATEDATE"=20121205 AND "USERNUMBER"=13826093949 AND "SITECODE"='0' AND "PORTALID"=12)

Note

-----

   - dynamic sampling used for this statement

Statistics

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

          0  recursive calls

          0  db block gets

          5  consistent gets

          0  physical reads

          0  redo size

       1091  bytes sent via SQL*Net to client

        492  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

我们如果走索引2:

select /*+index(a,OSS_OPERATORSITE_ACT_DAY_IDX2)*/* from OSS_OPERATORSITE_ACT_DAY_hch a where STATEDATE=20121205 

and usernumber ='13826093949' and SITECODE='0' and PORTALID=12;                     （和merge条件一致）

SQL> /

Execution Plan

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

Plan hash value: 3005087471

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

| Id  | Operation                   | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                               |     1 |   198 |  1102   (1)| 00:00:14 |

|   1 |  TABLE ACCESS BY INDEX ROWID| OSS_OPERATORSITE_ACT_DAY_HCH  |     1 |   198 |  1102   (1)| 00:00:14 |

|*  2 |   INDEX RANGE SCAN          | OSS_OPERATORSITE_ACT_DAY_IDX2 |     1 |       |  1102   (1)| 00:00:14 |   --走索引2的一行的开销是3576个逻辑读

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

Predicate Information (identified by operation id):

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

   2 - access("STATEDATE"=20121205 AND "SITECODE"='0' AND "USERNUMBER"=13826093949 AND "PORTALID"=12)

       filter("USERNUMBER"=13826093949 AND "SITECODE"='0' AND "PORTALID"=12)

Note

-----

   - dynamic sampling used for this statement

Statistics

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

          0  recursive calls

          0  db block gets

       3576  consistent gets

          0  physical reads

          0  redo size

       1091  bytes sent via SQL*Net to client

        492  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

 

性能差别非常大，请大家注意，一个是5个逻辑读，一个是3576，这仅仅是查询一行的开销:  主要的区别就是由于组合索引对查询索引字段的排列顺序导致的，

请大家以后注意这种情况:

1. 无需要的字段，不纳入组合索引列

2. 组合索引在创建的时候需要注意排列顺序(注：并非sql查询顺序)