oracle中如何修改索引,Oracle教程之管理索引（四）--Oracle修改索引

1、修改索引段存储参数

03:31:28 SQL> alter index scott.indx_ename deallocate unused;

Index altered.

03:31:36 SQL> alter index scott.indx_ename allocate extent(size 1m);

Index altered.

2、重建索引

03:32:48 SQL> alter index indx_ename rebuild;

Index altered.

3、联机重建索引

使用rebuild选项重建索引时，如果其他用户正在表上执行DML操作，那么重建索引将会失败，并显示错误信息“ORA-00054:资源正忙，但指 定一NOWAIT方式获取资源”。为了最小化DML操作的影响，重建索引时，可以使用REBUILD ONLINE选项。示例如下：

ALTER INDEX department_dname REBUILD ONLINE;

4、合并索引

03:37:40 SQL> alter index indx_ename coalesce;

Index altered.

5、删除索引

03:37:40 SQL> drop   index    indx_ename;

6、验证索引

ANALYZE INDEX orders_region_id_idx VALIDATE STRUCTURE;

7、索引监控

SQL> conn scott/tiger

Connected.

SQL> alter index emp_ename_funind monitoring usage;

Index altered.

SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME                     TABLE_NAME      MONITORIN USED

------------------------------ --------------- --------- ---------

EMP_ENAME_FUNIND               EMP             YES       NO

10:01:29 SQL> select * from emp where LOWER(ename)='scott';

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

SQL> select * from emp where lower(ename)='scott';

EMPNO ENAME           JOB                    MGR HIREDATE                   SAL       COMM     DEPTNO

---------- --------------- --------------- ---------- ------------------- ---------- ---------- ----------

7788 SCOTT           ANALYST               7566 1987-04-19 00:00:00       3000                    20

SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME                     TABLE_NAME      MONITORIN USED

------------------------------ --------------- --------- ---------

EMP_ENAME_FUNIND               EMP             YES       YES