Oracle_create an index using unusable

最新推荐文章于 2024-04-19 16:58:44 发布

奋斗的小鸟_oracle

最新推荐文章于 2024-04-19 16:58:44 发布

阅读量1.2k

点赞数

分类专栏： Oracle 技术分享

本文链接：https://blog.csdn.net/xuliq/article/details/50857224

版权

Oracle 技术分享专栏收录该内容

51 篇文章 0 订阅

订阅专栏

1、using unusable create index

SQL> create index tbcs.INX_SUBS_PAYPLAN_APPLYOID on tbcs.SUBS_PAYPLAN (APPLYOID, REGION) local tablespace D_LOG_03 parallel 10 unusable;

Index created.

2、the indexes statistics

Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- -------------- ----------- ----------- ------------ ------ ------ -------------- ----------
INX_CM_SUBS_PAY NONUNIQUE 3 #### 52,783,878 52,783,878 1 1 30,781,601 YES NO 90,966 08-01-2013
PLAN_ACCTID

INX_SUBS_PAYPLA NONUNIQUE 2 #### 32,288,845 32,288,845 1 1 29,187,992 YES NO 150,560 08-01-2013
N_CANCELOID

INX_CM_SUBS_PAY UNIQUE 3 #### 58,687,034 58,687,034 1 1 35,930,710 YES NO 56,654 08-01-2013
PLAN_SUBSID

INX_SUBS_PAYPLA NONUNIQUE 0 0 0 0 0 0 0 NO NO 0 03-11-2016
N_APPLYOID

INX_SUBS_PAYPLA NONUNIQUE 2 #### 144,656 925,631 1 2 413,250 YES NO 157,412 08-01-2013
N_GRPSUBSID

PK_CM_SUBS_PAYP UNIQUE 2 #### 52,843,561 52,843,561 1 1 30,398,239 YES NO 116,273 08-01-2013

---the index INX_SUBS_PAYPLAN_APPLYOID is null

3、rebuild unusable index

SELECT /*+ rule */ 'alter index '||t.index_owner||'.'||t.index_name||' rebuild partition '||partition_name||' PARALLEL(DEGREE 12) '||' tablespace '||t.tablespace_name||' nologging;'
FROM dba_ind_partitions t,dba_indexes b
WHERE t.index_name=b.index_name AND t.status='UNUSABLE' and b.index_name='INX_SUBS_PAYPLAN_APPLYOID'
and t.index_owner=b.owner ORDER BY b.table_name,partition_name;

4、the index statistics

---the index statistics is still null

5、manual gathering statistics

exec system.pkg_optstats.gather_table_stats( 'TBCS', 'SUBS_PAYPLAN', p_degree => 16, p_force => 1, p_reason => 'INITIAL' );

4、the index statistics

Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- -------------- ----------- ----------- ------------ ------ ------ -------------- ----------
INX_CM_SUBS_PAY NONUNIQUE 3 #### 49,070,080 58,452,031 1 1 39,695,422 YES NO 80,143 03-11-2016
PLAN_ACCTID

INX_SUBS_PAYPLA NONUNIQUE 2 #### 29,829,302 29,829,302 1 1 28,222,375 YES NO 115,313 03-11-2016
N_CANCELOID

INX_CM_SUBS_PAY UNIQUE 3 #### 53,040,742 53,040,742 1 1 39,252,269 YES NO 41,541 03-11-2016
PLAN_SUBSID

INX_SUBS_PAYPLA NONUNIQUE 2 #### 51,724,446 55,170,663 1 1 39,788,398 YES NO 251,836 03-11-2016
N_APPLYOID

INX_SUBS_PAYPLA NONUNIQUE 2 #### 153,440 1,346,292 1 3 463,257 YES NO 135,342 03-11-2016
N_GRPSUBSID

PK_CM_SUBS_PAYP UNIQUE 2 #### 50,031,269 50,031,269 1 1 34,452,748 YES NO 88,610 03-11-2016
LAN

---the index statistics is no null

remark:

1. 这个问题的原因是新建索引上没有收集统计信息；
2. 如果是正常create index 的话，在10g以后，索引上的统计信息是会在创建过程中自己收集；
3. 之所以这里没有被收集，刚试验是因为使用create index unusble & rebuild partiton方式创建，此时是没有被收集的；
4. 保证生产的稳定，故在使用unusable & rebuild方式创建后，建议使用dbms_stats.gather_index_stats单独对索引收集