tbwshc

单个分区索引失效导致绑定变量查询无法使用索引

一个客户碰到的问题,由于分区维护操作,导致个别分区对应的索引处于UNUSABLE状态,最终导致基于绑定变量的查询无法利用索引。

 

 

通过一个具体的例子来说明这个问题:

SQL> create table t_part
2 (id number,
3 name varchar2(30))
4 partition by range (id)
5 (partition p1 values less than (10),
6 partition p2 values less than (20),
7 partition pmax values less than (maxvalue));

Table created.

SQL> create index ind_t_part_id on t_part(id) local;

Index created.

SQL> insert into t_part
2 select rownum, object_name
3 from user_objects;

94 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user, 'T_PART', cascade => true)

PL/SQL procedure successfully completed.

SQL> select index_name, partition_name, status
2 from user_ind_partitions
3 where index_name = 'IND_T_PART_ID';

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IND_T_PART_ID P1 USABLE
IND_T_PART_ID P2 USABLE
IND_T_PART_ID PMAX USABLE

创建分区表后,分别采用硬编码和绑定变量的方式进行查询:

SQL> var v_id number
SQL> exec :v_id := 5

PL/SQL procedure successfully tb completed.

SQL> set autot on exp
SQL> select * from t_part where id = 5;

       ID NAME
---------- ------------------------------
        5 WRH$_ACTIVE_SESSION_HISTORY


Execution Plan
----------------------------------------------------------
Plan hash value: 4087175928

--------------------------------------------------------------------------------------------
|Id|Operation                         |Name        |Rows|Bytes|Cost|Time   |Pstart|Pstop|
--------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT                  |            |  1|  31|  2|00:00:01|     |    |
| 1| PARTITION RANGE SINGLE           |            |  1|  31|  2|00:00:01|   1 |   1|
| 2| TABLE ACCESS BY LOCAL INDEX ROWID|T_PART      |  1|  31|  2|00:00:01|   1 |   1|
|*3|  INDEX RANGE SCAN               |IND_T_PART_ID|  1|    |  1|00:00:01|   1 |   1|
--------------------------------------------------------------------------------------------

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

  3 - access("ID"=5)

SQL> select * from t_part where id = :v_id;

       ID NAME
---------- ------------------------------
        5 WRH$_ACTIVE_SESSION_HISTORY


Execution Plan
----------------------------------------------------------
Plan hash value: 2089936139

--------------------------------------------------------------------------------------------
|Id|Operation                         |Name        |Rows|Bytes|Cost|Time   |Pstart|Pstop|
--------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT                  |            |  1|  17|  2|00:00:01|     |    |
| 1| PARTITION RANGE SINGLE           |            |  1|  17|  2|00:00:01| KEY | KEY|
| 2| TABLE ACCESS BY LOCAL INDEX ROWID|T_PART      |  1|  17|  2|00:00:01| KEY | KEY|
|*3|  INDEX RANGE SCAN               |IND_T_PART_ID|  1|    |  1|00:00:01| KEY | KEY|
--------------------------------------------------------------------------------------------

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

  3 - access("ID"=TO_NUMBER(:V_ID))

无论采用那种方式,Oracle都会选择分区索引扫描的执行计划。

下面MOVE一个查询并不会访问的分区,使其索引状态变为UNUSABLE:

SQL> alter table t_part move partition p2;

Table altered.

SQL> set autot off
SQL> select index_name, partition_name, status
 2 from user_ind_partitions
 3 where index_name = 'IND_T_PART_ID';

INDEX_NAME                    PARTITION_NAME                STATUS
------------------------------ ------------------------------ --------
IND_T_PART_ID                 P1                            USABLE
IND_T_PART_ID                 P2                            UNUSABLE
IND_T_PART_ID                 PMAX                          USABLE

SQL> set autot on exp
SQL> select * from t_part where id = 5;

       ID NAME
---------- ------------------------------
        5 WRH$_ACTIVE_SESSION_HISTORY


Execution Plan
----------------------------------------------------------
Plan hash value: 4087175928

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

|Id|Operation                         |Name        |Rows|Bytes|Cost|Time   |Pstart|Pstop|
--------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT                  |            |  1|  31|  2|00:00:01|     |    |
| 1| PARTITION RANGE SINGLE           |            |  1|  31|  2|00:00:01|   1 |   1|
| 2| TABLE ACCESS BY LOCAL INDEX ROWID|T_PART      |  1|  31|  2|00:00:01|   1 |   1|
|*3|  INDEX RANGE SCAN               |IND_T_PART_ID|  1|    |  1|00:00:01|   1 |   1|
--------------------------------------------------------------------------------------------

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

  3 - access("ID"=5)

SQL> select * from t_part where id = :v_id;

       ID NAME
---------- ------------------------------
        5 WRH$_ACTIVE_SESSION_HISTORY


Execution Plan
----------------------------------------------------------
Plan hash value: 1818654859

--------------------------------------------------------------------------------------------
| Id| Operation             | Name  | Rows | Bytes |Cost(%CPU)| Time    | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT      |       |   1 |   17 |   2 (0)| 00:00:01 |      |      |
| 1| PARTITION RANGE SINGLE|       |   1 |   17 |   2 (0)| 00:00:01 |  KEY |  KEY |
|* 2|  TABLE ACCESS FULL   | T_PART |   1 |   17 |   2 (0)| 00:00:01 |  KEY |  KEY |
--------------------------------------------------------------------------------------------

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

  2 - filter("ID"=TO_NUMBER(:V_ID))

可以看到,对应非绑定变量方式,Oracle是可以明确定位到要访问的分区,因此SQL执行计划不受影响,仍然是索引扫描。而对于绑定变量的方式则不同,由于这个执行计划对于任何一个输入值都要采用相同的计划,因此Oracle无法判断一个查询是否会访问分区索引UNUSABLE的分区,所以Oracle对于绑定变量的查询采用了单分区的全表扫描执行计划。

为了解决这个问题,除了REBUILD失效的分区外,还可以采用HINT的方式,强制Oracle选择索引扫描的执行计划:

SQL> select /*+ index(t_part ind_t_part_id) */ * from t_part where id = :v_id;

       ID NAME
---------- ------------------------------
        5 WRH$_ACTIVE_SESSION_HISTORY


Execution Plan
----------------------------------------------------------
Plan hash value: 2089936139

--------------------------------------------------------------------------------------------
|Id|Operation                         |Name        |Rows|Bytes|Cost|Time   |Pstart|Pstop|
--------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT                  |            |  1|  17|  2|00:00:01|     |    |
| 1| PARTITION RANGE SINGLE           |            |  1|  17|  2|00:00:01| KEY | KEY|
| 2| TABLE ACCESS BY LOCAL INDEX ROWID|T_PART      |  1|  17|  2|00:00:01| KEY | KEY|
|*3|  INDEX RANGE SCAN               |IND_T_PART_ID|  1|    |  1|00:00:01| KEY | KEY|
--------------------------------------------------------------------------------------------

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

  3 - access("ID"=TO_NUMBER(:V_ID))

SQL> exec :v_id := 15

PL/SQL procedure successfully completed.

SQL> select /*+ index(t_part ind_t_part_id) */ * from t_part where id = :v_id;
select /*+ index(t_part ind_t_part_id) */ * from t_part where id = :v_id
*
ERROR at line 1:
ORA-01502: index 'TEST.IND_T_PART_ID' or partition of such index is in unusable state


SQL> select * from t_part where id = :v_id;

       ID NAME
---------- ------------------------------
       15 WRH$_ACTIVE_SESSION_HISTORY_PK


Execution Plan
----------------------------------------------------------
Plan hash value: 1818654859

--------------------------------------------------------------------------------------------
| Id | Operation             | Name  | Rows | Bytes |Cost(%CPU)| Time    |Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT      |       |   1 |   17 |   2 (0)| 00:00:01 |     |      |
| 1 | PARTITION RANGE SINGLE|       |   1 |   17 |   2 (0)| 00:00:01 | KEY |  KEY |
|* 2 |  TABLE ACCESS FULL   | T_PART |   1 |   17 |   2 (0)| 00:00:01 | KEY |  KEY |
--------------------------------------------------------------------------------------------

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

  2 - filter("ID"=TO_NUMBER(:V_ID))

虽然使用HINT可以让Oracle强制索引扫描,但是如果绑定变量的值指向失效的索引分区,则会导致执行报错。而默认的不使用HINT的语句则不会报错。

posted on 2012-09-05 11:45 chen11-1 阅读(1158) 评论(0)  编辑  收藏