ITLife365 生活互动学习空间 - 互动学习空间-oracle index 索引使用总结收藏

总结：

（1）alter index ii storage(next 40)10g的不可以改变索引的存储参数，但是11g开始可以改变索引的部分存储参数,其中INITIAL 和 MINEXTENTS 不可以被修改

（2） alter index ii rebuild online tablespace test 可以改变存储参数，改变索引的表空间，减少表空间的碎片，在索引比较大的时候可以用paralle+nologging 在加快索引的建立

(3) alter index index_name unusable;索引会被优化器忽略，并且dml操作也不会维护这个索引，索引必须重建或者删除.10g环境下索引状态变成unusable时，段的空间没有释放，11g环境索引状态变成unusable时，段空间释放. 唯一索引变成unusable状态后不能插入属于

分区索引修改成unusable

      ALTER INDEX i_emp_ename MODIFY PARTITION p2_i_emp_ename UNUSABLE;

(4)索引的disable状态只是针对函数索引来设置的，其他索引都不能设置成disable，如果函数索引变成disable状态后，不能插入数据。

（5）invisible index 会被优化器所忽略，但是可以设置参数OPTIMIZER_USE_INVISIBLE_INDEXES,优化器会索引，索引变成invisible，不影响数据的插入

（6）monriting index usage只能监控此索引是否被使用过，每次 MONITORING USAGE开启后，V$OBJECT_USAGE 视图就会初始化一次重设，之前的清理就被重设.在 MONITORING 时，如果索引被使用，V$OBJECT_USAGE 里就会显示此索引被使用

遗留问题：

（1）在11g中为什么要修改存储的参数 alter index ii storage(next 40),什么情况下要修改这个参数呢？

（2）为什么要把函数索引变成disable状态，什么情况下disable 函数索引

（3）11g后索引变成unusable 后，段空间被释放，那么rebuild index 怎么利用现存的索引重建的呢

两者的区别是：enable和disable仅仅只针对函数索引。
ORACLE官方文档提供的说法是：

ENABLE Clause

Enable applies only to a function-based index that has been disabled because a user-defined function used by the index was dropped or replaced. This clause enables such an index if these conditions are true:

The function is currently valid

The signature of the current function matches the signature of the function when the index was created

The function is currently marked asDETERMINISTIC

Restriction on Enabling Function-based Indexes You cannot specify any other clauses of ALTER INDEX in the same statement with ENABLE.

DISABLE Clause

DISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.

UNUSABLE Clause

Specify UNUSABLE to mark the index or index partition(s) or index subpartition(s) UNUSABLE. An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked UNUSABLE, the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it.

Restriction on Marking Indexes Unusable You cannot specify this clause for an index on a temporary table.

如果发现一个索引失效以后，对其使用enable命令，可能会引发ORA-02243的错误，这是由于ENABLE只针对函数索引有效，可以试试rebuild，如果对一个索引执行失效命令，也可能会遇到这个错误，原因是一样的。

1、如果你想alter index ，那么这个索引要么建立在你的用户下，要么你必须有 ALTER ANY INDEX 的系统权限，当你有alter any index权限后，你可以对索引进行如下更改，但是不能改变索引列的结构：
Rebuild or coalesce an existing index

Deallocate unused space or allocate a new extent
Specify parallel execution (or not) and alter the degree of parallelism
Alter storage parameters or physical attributes
Specify LOGGING or NOLOGGING
Enable or disable key compression
Mark the index unusable
Make the index invisible
Rename the index
Start or stop the monitoring of index usage

1.2、改变索引的存储特征

通过alter index 去改变包含主键、唯一约束，普通索引，但是不能通过alter index 修改索引的存储参数 INITIAL 和 MINEXTENTS 。
版本10.2.0.1.0：
SQL>create table t1 as select * from emp;
表已创建。
SQL> create index ii on t1(empno);
SQL>select dbms_metadata.get_ddl('INDEX','II','SCOTT') from dual;
DBMS_METADATA.GET_DDL('INDEX','II','SCOTT')
--------------------------------------------------------------------------------
  CREATE INDEX "SCOTT"."II" ON "SCOTT"."T1" ("EMPNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0

但是
SQL> ALTER INDEX  scott.ii  STORAGE (NEXT 40);
ALTER INDEX  scott.ii  STORAGE (NEXT 40)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted
SQL> alter index ii storage(next 8192);
alter index ii storage(next 8192)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted
SQL>  alter index ii storage(next 16384);
alter index ii storage(next 16384)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted
当版本是11.2.0.1.0 ：
SQL> create table t1 as select * from emp;
表已创建。

SQL> create index ii on t1(empno);
索引已创建。
SQL> select dbms_metadata.get_ddl('INDEX','II','SCOTT') from dual;
DBMS_METADATA.GET_DDL('INDEX','II','SCOTT')
----------------------------------------------------------------------------
  CREATE INDEX "SCOTT"."II" ON "SCOTT"."T1" ("EMPNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
SQL> alter index ii storage(next 40);
索引已更改。
SQL> select dbms_metadata.get_ddl('INDEX','II','SCOTT') from dual;
DBMS_METADATA.GET_DDL('INDEX','II','SCOTT')
------------------------------------------------------------------------------
  CREATE INDEX "SCOTT"."II" ON "SCOTT"."T1" ("EMPNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FR
SQL> alter index ii storage(next 16384);
索引已更改。
SQL> select dbms_metadata.get_ddl('INDEX','II','SCOTT') from dual;
DBMS_METADATA.GET_DDL('INDEX','II','SCOTT')
------------------------------------------------------------------------------
  CREATE INDEX "SCOTT"."II" ON "SCOTT"."T1" ("EMPNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 F

结论：通过实验我们看到，在10.2.0.1.0 版本的时候是不允许修改索引的存储参数的，但是到了11.2.0.1.0,此参数是可以修改的

1.3、Rebuilding an Existing Index

一个现有的索引可以重建,也可以coalesce，也可以删除新建，考虑其中的代价，来决定是那种方式。
当你拿现有索引当成源，重新建索引的时候，可以改变存储的参数，改变表空间，减少现有索引中的碎片，对比删除索引重建，rebuild会性能好一些。
rebuild其他用户的索引需要的权限：CREATE ANY TABLE、 CREATE ANY INDEX
SQL> alter index ii rebuild online tablespace test;
Index altered.
Index altered.
最好是加上online 参数，否则其他session就不能对表进行dml操作。
对于比较大的索引可以加上 paralle +nologging 加快索引的重建。

alter index ii rebuild online nologging tablespace test parallel 2;
alter index ii logging;
alter index ii noparallel;
online rebuild index 有个限制就是不能超过最大键值的长度ORA-1450 (maximum key length exceeded) ，如果报错可以采用offline, coalescing, or dropping and re-creating。

1.4、Making an Index Unusable

       当你把一个索引的状态由valid，变成unusable时，这个索引会被优化器忽略，并且dml操作也不会维护这个索引。当你把一个分区的分区索引变成unusable时，其他分区仍然是valid状态。在使用这个索引前你必须删除重建或者rebuild

当版本是10G：
查看索引的状态：
SQL> SELECT INDEX_NAME AS "INDEX OR PART NAME", STATUS FROM   USER_INDEXES
   UNION ALL
SELECT PARTITION_NAME AS "INDEX OR PART NAME", STATUS FROM   USER_IND_PARTITIONS;
INDEX OR PART NAME      STATUS
------------------------------ --------
II      VALID
SQL> alter index ii unusable;
Index altered.
SQL> SELECT INDEX_NAME AS "INDEX OR PART NAME", STATUS FROM   USER_INDEXES
   UNION ALL
SELECT PARTITION_NAME AS "INDEX OR PART NAME", STATUS FROM   USER_IND_PARTITIONS;
   UNION ALL
SELECT PARTITION_NAME AS "INDEX OR PART NAME", STATUS FROM   USER_IND_PARTITIONS;
INDEX OR PART NAME       STATUS
------------------------------ --------
II       UNUSABLE
SQL> select segment_name,bytes from user_segments where segment_name='II';
SEGMENT_NAME       BYTES
--------------------------------------------------------------------------------- ----------
II     4194304
当11g环境：
SQL> select status from dba_indexes where table_name='T1';
STATUS
--------
VALID
SQL> alter index ii unusable;
索引已更改。
SQL> select status from dba_indexes where table_name='T1';
STATUS
--------
UNUSABLE
SQL> select segment_name,segment_type,extent_id,blocks from dba_extents where segment_name='II';
未选定行

如果是分区索引这样修改：
 ALTER INDEX i_emp_ename MODIFY PARTITION p2_i_emp_ename UNUSABLE;

SQL> create unique index ii on t1(empno);
索引已创建。

SQL> select a.index_name,a.index_type,a.visibility,status,a.uniqueness from dba_indexes a where table_name='T1';

INDEX_NAME                     INDEX_TYPE                  VISIBILIT STATUS   UNIQUENES
------------------------------ --------------------------- --------- -------- ---------
II                             NORMAL                      VISIBLE   VALID    UNIQUE
IND                            FUNCTION-BASED NORMAL       VISIBLE   VALID    NONUNIQUE
SQL> alter index ii unusable;
索引已更改。
SQL> select  a.index_name,a.index_type,a.visibility,status,a.uniqueness from dba_indexes a where table_name='T1';
INDEX_NAME                     INDEX_TYPE                  VISIBILIT STATUS   UNIQUENES
------------------------------ --------------------------- --------- -------- ---------
II                             NORMAL                      VISIBLE   UNUSABLE UNIQUE
IND                            FUNCTION-BASED NORMAL       VISIBLE   VALID    NONUNIQUE
SQL> insert  into t1 select *From emp;
insert  into t1 select *From emp
*
第 1 行出现错误:
ORA-01502: 索引 'SCOTT.II' 或这类索引的分区处于不可用状态

结论：通过对10g和11g环境的测试，10g环境下索引状态变成unusable时，段的空间没有释放，11g环境索引状态变成unusable时，段空间释放

唯一索引变成unusable状态后，不能插入数据

1.5、Making an Index disable

DISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword
SQL> create  unique index uq on t1(upper(ename));
索引已创建。
SQL> select INDEX_NAME,status,funcidx_status from dba_indexes where table_name='T1';
INDEX_NAME                     STATUS   FUNCIDX_
------------------------------ -------- --------
II                             VALID
UQ                             VALID    ENABLED
SQL> select INDEX_NAME,status,funcidx_status from dba_indexes where table_name='T1';
INDEX_NAME                     STATUS   FUNCIDX_
------------------------------ -------- --------
II                             VALID
UQ                             VALID    DISABLED
SQL> insert into t1 select *From t1;
insert into t1 select *From t1
*
第 1 行出现错误:
ORA-30554: 基于函数的索引SCOTT.UQ被禁用
SQL> drop index uq;
索引已删除。
SQL> create index ind on t1(lower(ename));
索引已创建。

SQL> select INDEX_NAME,status,funcidx_status from dba_indexes where table_name='T1';
INDEX_NAME                     STATUS   FUNCIDX_
------------------------------ -------- --------
II                             VALID
IND                            VALID    ENABLED
SQL> alter index ind disable;
索引已更改。
SQL> select INDEX_NAME,status,funcidx_status from dba_indexes where table_name='T1';
INDEX_NAME                     STATUS   FUNCIDX_
------------------------------ -------- --------
II                             VALID
IND                            VALID    DISABLED
SQL> insert into t1 select *From emp;
insert into t1 select *From emp
*
第 1 行出现错误:
ORA-30554: 基于函数的索引SCOTT.IND被禁用
SQL> alter index ind enable;
索引已更改。
SQL> alter index ind unusable;
索引已更改。
SQL> insert into t1 select *From emp;
已创建12行。

结论：alter index disable只能适用于函数索引，如果把函数索引disable，是不能插入数据的。如果是把函数索引unusable ，那么此索引不可用，但是仍然可以插入数据。

1.6 、 Making an Index Invisible

   不可见的索引是会被优化器所忽略，除非你在session级别或者system级别设置了参数 OPTIMIZER_USE_INVISIBLE_INDEXES =true，让索引不可见是为了让索引unusable或者要删除索引而考虑。分区索引是不能不可见的，否则会报错。
SQL> select  a.index_name,a.index_type,a.visibility,status from dba_indexes a where table_name='T1';
INDEX_NAME                     INDEX_TYPE                  VISIBILIT STATUS
------------------------------ --------------------------- --------- --------
II                             NORMAL                                                VISIBLE   VALID
IND                      FUNCTION-BASED NORMAL             VISIBLE   VALID
SQL> select *from t1 where empno=7788;
执行计划
----------------------------------------------------------
Plan hash value: 2627886109
------------------------------------------------------------------------------------
| Id  | Operation                                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    87 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |    87 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | II   |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
SQL> alter index ii invisible;
索引已更改。
SQL> alter index ind invisible;
索引已更改。
SQL> select  a.index_name,a.index_type,a.visibility,status from dba_indexes a where table_name='T1';
INDEX_NAME                     INDEX_TYPE                  VISIBILIT STATUS
------------------------------ --------------------------- --------- --------
II                             NORMAL                                   INVISIBLE VALID
IND            FUNCTION-BASED NORMAL       INVISIBLE   VALID
---可以正常插入数据
SQL> insert into t1 select *From emp;
已创建12行。
SQL> select *from t1 where empno=7788;
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    87 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
ii invisible，优化器忽略了索引 ii
如果修改参数：
SQL> alter system set optimizer_use_invisible_indexes=true;
系统已更改。
SQL> select *from t1 where empno=7788;
执行计划
----------------------------------------------------------
Plan hash value: 2627886109
------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    87 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |    87 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | II   |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
SQL>  alter index ii visible;
索引已更改。

SQL>  alter index ind visible;
索引已更改。
SQL> select  a.index_name,a.index_type,a.visibility,status from dba_indexes a where table_name='T1';
INDEX_NAME                     INDEX_TYPE                  VISIBILIT STATUS
------------------------------ --------------------------- --------- --------
II                             NORMAL                                   VISIBLE   VALID
IND                 FUNCTION-BASED NORMAL       VISIBLE   VALID

结论：invisible index 会被优化器所忽略，但是可以设置参数OPTIMIZER_USE_INVISIBLE_INDEXES,优化器会索引，索引变成invisible，不影响数据的插入

1.7 、Re

naming an Index

SQL> alter index ii rename  to ind_i;
索引已更改。
SQL> select  a.index_name,a.index_type,a.visibility,status from dba_indexes a where table_name='T1';
INDEX_NAME                     INDEX_TYPE                  VISIBILIT STATUS
------------------------------ --------------------------- --------- --------
IND_I                          NORMAL                      VISIBLE   VALID
IND                            FUNCTION-BASED NORMAL       VISIBLE   VALID

1.8、Monitoring Index Usage

此方法只能监控此索引是否被使用过，每次 MONITORING USAGE开启后，V$OBJECT_USAGE 视图就会初始化一次重设，之前的清理就被重设.在 MONITORING 时，如果索引被使用，V$OBJECT_USAGE 里就会显示此索引被使用.
SQL> drop index ind_i;
索引已删除。
SQL> create index ii on t1(empno);
索引已创建。
SQL> alter index  ii monitoring usage;
索引已更改。
SQL> select *From  v$object_usage;
INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ -              ------------- --- ------------------- -------------------
II                                                            T1                             YES       NO                               05/21/2013 15:55:19
SQL> select *From t1 where empno=7788;
执行计划
----------------------------------------------------------
Plan hash value: 2627886109
------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    87 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |    87 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | II   |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
SQL> select *From  v$object_usage;
INDEX_NAME              TABLE_NAME          MON USE START_MONITORING    END_MONITORING
--------------------------- ------------------------------ --- --- ------------------- -------------------
II                                       T1                             YES                YES                          05/21/2013 15:55:19

参考：http://docs.oracle.com/cd/E11882_01/server.112/e25494/indexes004.htm#ADMIN13283

阅读全文

ITLife365 生活互动学习空间 - 互动学习空间

Good Luck To You!

oracle index 索引使用总结收藏2013-12-04 15:02:10

一个现有的索引可以重建,也可以coalesce，也可以删除新建，考虑其中的代价，来决定是那种方式。

1.7 、Re

naming an Index

« 2024年11月 »
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

ITLife365 生活 互动学习空间 - 互动学习空间

Good Luck To You!

oracle index 索引使用总结 收藏2013-12-04 15:02:10

一个现有的索引可以重建,也可以coalesce，也可以删除新建，考虑其中的代价，来决定是那种方式。

1.7 、Re

naming an Index

ITLife365 生活互动学习空间 - 互动学习空间

oracle index 索引使用总结收藏2013-12-04 15:02:10