关于重构索引--index rebuild的OCP 题

In which scenarios would you rebuild an index? (Choose all thatapply.)
A.when you need to disable the index usage
B.when you need to change storage options
C.when you need to enable index monitoring
D.when you need to move the index to another tablespace

 

　这里先剖开indx失效来测试一下关于索引对应的字段没有not null的限制会使index ffs失效。
　　SQL> create table xiaoyu01 as select rownum cn fromdual;
　　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 | SELECTSTATEMENT  |         |    1|    3   (0)| 00:00:01 |
　　|   1 |  SORTAGGREGATE   |         |    1|           |         |
　　|   2|   TABLE ACCESS FULL| XIAOYU01|    1|    3   (0)| 00:00:01 |
　　———————————————————————–
　　因为没有not null的限制cbo会对表段执行全表扫描。

　　即使手动hint，由于index的该列没有notnull限制，cbo会以其hint写法引起sql执行的结果不准确而舍弃hint的plan
　　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 | SELECTSTATEMENT  |         |    1|    3   (0)| 00:00:01 |
　　|   1 |  SORTAGGREGATE   |         |    1|           |         |
　　|   2|   TABLE ACCESS FULL| XIAOYU01|    1|    3   (0)| 00:00:01 |
　　———————————————————————–

　　而如果我手动添加这个约束，btree的index不会存储null值，此时hint的plan不会影响其执行结果，而其实默认cbo也会选择这个执行plan，因为indexsegment比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 | SELECTSTATEMENT     |               |    1|    2   (0)| 00:00:01 |
　　|   1 |  SORTAGGREGATE      |               |    1|           |         |
　　|   2|   INDEX FAST FULL SCAN|INDEX_XIAOYU01|    1|    2   (0)| 00:00:01 |
　　——————————————————————————–

　　下面根据ocp的那道考题来看看index需要重建的情况分析。
　　A 手动使index unusable，都将其unusable了，相当于index不予以维护了，然后再次启用的时候，就需要

index rebuild。然而题目给的是：在你禁用的过程中，会使用到重建索引。然而在禁用索引的时候，只需要

执行alter index index_xiaoyu01 unusable;即可。A 不对。

 

　　SQL> alter index index_xiaoyu01 unusable;
　　Index altered.
　　SQL> select index_name,status from user_indexes whereindex_name=’INDEX_XIAOYU01′;
　　INDEX_NAME                    STATUS
　　—————————— ——–
　　INDEX_XIAOYU01                UNUSABLE
　　SQL> explain plan for select count(*) from xiaoyu01;
　　explain plan for select count(*) from xiaoyu01
　　*
　　ERROR at line 1:
　　ORA-01502: index ‘ZEBRA.INDEX_XIAOYU01′ or partition of suchindex is in
　　unusable state
　　此时index都处于一个不可用的状态了，hint的plan失效了。

　　B 修改storage，关于storage也就是存储的选项，其实是segment级别的，initialextent、next extent、pctincrease、minextentmaxextent，这些参数一般建表时没有特别需要时都是用的默认的选项，不需要手动制定。
　　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(pctincrease100);
　　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(initial64K);
　　Index altered.

　　C 使index enablemonitoring，这个由于小鱼也做过关于index监控的job，主要是减小系统维护index的压力，删除没有使用的index，肯定是不需要rebuild的。
　　SQL> alter index index_xiaoyu01  monitoringusage;
　　Index altered.
　　SQL> select index_name,status from user_indexes whereindex_name=’INDEX_XIAOYU01′;
　　INDEX_NAME                    STATUS
　　—————————— ——–
　　INDEX_XIAOYU01                VALID

　　D 移动index到别的表空间，这个说实话我觉得跟alter table movetablespace差不多，而index并不支持这等语法，要采用rebuild的方式移动。
　　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 VIEWoption
　　SQL> alter index index_xiaoyu01 rebuild tablespaceusers;
　　Index altered.

所以是BD。