源于ocp的index rebuild题目分析_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2782 | 回复: 0   主题: 源于ocp的index rebuild题目分析        下一篇 
    本主题由 koei123 于 2015-6-1 14:57:09 移动
kim
注册用户
等级:中校
经验:1729
发帖:222
精华:0
注册:2011-7-21
状态:离线
发送短消息息给kim 加好友    发送短消息息给kim 发消息
发表于: IP:您无权察看 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。

--转自 北京联动北方科技有限公司

该贴由koei123转至本版2015-6-1 14:57:08



赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论