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
查看本栏目更多精彩内容:http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/