源于ocp的index rebuild题目分析_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2786 | 回复: 0

主题： 源于ocp的index rebuild题目分析

本主题由 koei123 于 2015-6-1 14:57:09 移动

kim

注册用户

等级：中校
经验：1729
发帖：222
精华：0
注册：2011-7-21
状态：离线
发送短消息息给kim

加好友发送短消息息给kim

发消息

发表于：

2015-4-2 16:04:01 | [全部帖] [楼主帖]

楼主

源于一个网友提供的关于ocp的考题，关于index失效何时需要重建，既然遇见了index的讨论就说一些下面需要注意的细节吧。

这里先剖开indx失效来测试一下关于索引对应的字段没有not null的限制会使index ffs失效。

SQL> create table xiaoyu01 as select rownum cn from dual;
Table created.
SQL> create index index_xiaoyu01 on xiaoyu01(cn);
Index created.
SQL> explain plan for select count(*) from xiaoyu01;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1497785914
———————————————————————–
| Id   | Operation         | Name   | Rows   |  Cost (%CPU)| Time   |
———————————————————————–
|   0 | SELECT STATEMENT    |       |    1 |   3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |    1 |         |       |
|   2 |   TABLE ACCESS FULL | XIAOYU01 |    1 |   3   (0)| 00:00:01 |
———————————————————————–

因为没有not null的限制cbo会对表段执行全表扫描。

即使手动hint，由于index的该列没有not null限制，cbo会以其hint写法引起sql执行的结果不准确而舍弃hint的plan

SQL> explain plan for select /*+index_ffs(xiaoyu01)*/count(*) from xiaoyu01;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1497785914
———————————————————————–
| Id  | Operation          | Name   | Rows  |  Cost (%CPU)| Time   |
———————————————————————–
|   0 | SELECT STATEMENT    |       |    1 |   3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |    1 |         |       |
|   2 |   TABLE ACCESS FULL | XIAOYU01 |    1 |   3   (0)| 00:00:01 |
———————————————————————–

而如果我手动添加这个约束，b tree的index不会存储null值，此时hint的plan不会影响其执行结果，而其实默认cbo也会选择这个执行plan，因为index segment比table segment一般来说size会小。

SQL> alter table xiaoyu01 modify(cn not null);
Table altered.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 4255440449
——————————————————————————–
| Id  | Operation            | Name       | Rows  |  Cost (%CPU)| Time   |
——————————————————————————–
|   0 | SELECT STATEMENT      |           |    1 |   2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE      |           |    1 |         |       |
|   2 |   INDEX FAST FULL SCAN | INDEX_XIAOYU01 |    1 |   2   (0)| 00:00:01 |
——————————————————————————–

下面根据ocp的那道考题来看看index需要重建的情况分析。
A 手动使index unusable，都将其unusable了，相当于index不予以维护了，小鱼觉得是需要重建的，而ocp的考题居然说不需要rebuild，保留意见吧。

SQL> alter index index_xiaoyu01 unusable;
Index altered.
SQL> select index_name,status from user_indexes where index_name=’INDEX_XIAOYU01′;
INDEX_NAME STATUS
—————————— ——–
INDEX_XIAOYU01 UNUSABLE
SQL> explain plan for select /*+index_ffs(xiaoyu01)*/count(*) from xiaoyu01;
explain plan for select /*+index_ffs(xiaoyu01)*/count(*) from xiaoyu01
*
ERROR at line 1:
ORA-01502: index ‘ZEBRA.INDEX_XIAOYU01′ or partition of such index is in
unusable state

此时index都处于一个不可用的状态了，hint的plan失效了。

B 修改storage，关于storage也就是存储的选项，其实是segment级别的，initial extent、next extent、pctincrease、minextent maxextent，这些参数一般建表时没有特别需要时都是用的默认的选项，不需要手动制定。

SQL> alter index index_xiaoyu01 storage(initial 64k);
alter index index_xiaoyu01 storage(initial 64k)
*
ERROR at line 1:
ORA-02203: INITIAL storage options not allowed
SQL> alter index index_xiaoyu01 storage(pctincrease 100);
alter index index_xiaoyu01 storage(pctincrease 100)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

看出根本无法去修改storage参数，必须要以重建的方式去修改。

SQL> alter index index_xiaoyu01 rebuild storage(initial 64K);
Index altered.

C 使index enable monitoring，这个由于小鱼也做过关于index监控的job，主要是减小系统维护index的压力，删除没有使用的index，肯定是不需要rebuild的。

SQL> alter index index_xiaoyu01  monitoring usage;
Index altered.
SQL> select index_name,status from user_indexes where index_name=’INDEX_XIAOYU01′;
INDEX_NAME                         STATUS
—————————— ——–
INDEX_XIAOYU01                 VALID

D 移动index到别的表空间，这个说实话我觉得跟alter table move tablespace差不多，而index并不支持这等语法，要采用rebuild的方式移动，从index的存储方式来说，移动index到别的tablespace和rebuild该index看不出什么必然的联系啊，如有理解其中的缘由的欢迎告知。

SQL> alter index index_xiaoyu01 move users;
alter index index_xiaoyu01 move users
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
SQL> alter index index_xiaoyu01 rebuild tablespace users;
Index altered.

所以从我的理解来说这题abd是正确的，也否认了我之前的ab正确的肯定，关于d选项我从index的体系来分析确实看不出为什么需要重建index。

--转自