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
