索引的操作

1. 建立索引
SCOTT@orcl>create index emp_job_idx on emp(job);
Index created.
2. 从数据字典查询索引
SCOTT@orcl>select index_name,table_owner from user_indexes;
INDEX_NAME     TABLE_OWNER
-------------------- ------------------------------
PK_EMP     SCOTT
EMP_JOB_IDX     SCOTT
PK_DEPT     SCOTT
3. 创建索引表空间
SYS@orcl>create tablespace index_tbs datafile '/u01/app/oracle/oradata/orcl/index_tbs1.dbf' size 100M autoextend on;
Tablespace created.
4. 创建表多列索引
SCOTT@orcl>create index emp_ename_sal_idx on emp(ename,sal) tablespace index_tbs;
Index created.
5. 查看所建索引的信息
SCOTT@orcl>select index_name,index_type,table_name,table_owner,tablespace_name from user_indexes;
INDEX_NAME     INDEX_TYPE  TABLE_NAME
-------------------- -------------------- --------------------
TABLE_OWNER     TABLESPACE_NAME
-------------------- --------------------
PK_EMP     NORMAL  EMP
SCOTT     USERS
EMP_JOB_IDX     NORMAL  EMP
SCOTT     USERS
EMP_ENAME_SAL_IDX    NORMAL  EMP
SCOTT     INDEX_TBS
6. 查询与索引列相关的信息
16:08:42 SCOTT@orcl>col index_name for a20
16:08:55 SCOTT@orcl>col table_name for a20
16:09:02 SCOTT@orcl>col column_name for a30
16:09:25 SCOTT@orcl>select index_name,table_name,column_name from user_ind_columns;
INDEX_NAME     TABLE_NAME  COLUMN_NAME
-------------------- -------------------- ------------------------------
EMP_ENAME_SAL_IDX    EMP  SAL
EMP_ENAME_SAL_IDX    EMP  ENAME
EMP_JOB_IDX         EMP  JOB
PK_DEPT           DEPT  DEPTNO
PK_EMP           EMP  EMPNO
监控索引的使用
1. 启动对索引的监控
16:09:58 SCOTT@orcl>alter index EMP_ENAME_SAL_IDX monitoring usage;
Index altered.
2. 模拟进行查询
16:36:06 SCOTT@orcl>select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
ENAME
----------
JAMES
FORD
MILLER
14 rows selected.
16:36:08 SCOTT@orcl>select sal from emp;
       SAL
----------
       800
      1600
      1250
      2975
      1250
      2850
      2450
      3000
      5000
      1500
      1100
       SAL
----------
       950
      3000
      1300
14 rows selected.
16:35:07 SCOTT@orcl>SelecT * from emp;
     EMPNO ENAME      JOB       MGR HIREDATE    SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK      7902 17-DEC-80    800
20
      7499 ALLEN      SALESMAN      7698 20-FEB-81   1600        300
30
      7521 WARD       SALESMAN      7698 22-FEB-81   1250        500
30
     EMPNO ENAME      JOB       MGR HIREDATE    SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER      7839 02-APR-81   2975
20
      7654 MARTIN     SALESMAN      7698 28-SEP-81   1250       1400
30
      7698 BLAKE      MANAGER      7839 01-MAY-81   2850
30
     EMPNO ENAME      JOB       MGR HIREDATE    SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER      7839 09-JUN-81   2450
10
      7788 SCOTT      ANALYST      7566 19-APR-87   3000
20
      7839 KING       PRESIDENT   17-NOV-81   5000
10
     EMPNO ENAME      JOB       MGR HIREDATE    SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN      7698 08-SEP-81   1500 0
30
      7876 ADAMS      CLERK      7788 23-MAY-87   1100
20
      7900 JAMES      CLERK      7698 03-DEC-81    950
30
     EMPNO ENAME      JOB       MGR HIREDATE    SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST      7566 03-DEC-81   3000
20
      7934 MILLER     CLERK      7782 23-JAN-82   1300
10
14 rows selected.
3. 终止监控该索引
16:38:06 SCOTT@orcl>alter index emp_ename_sal_idx nomonitoring usage;
Index altered.
4. 通过数据字典v$object_usage查看emp_ename_sal_idx索引的使用情况
16:39:44 SCOTT@orcl>col index_name for a20
16:39:53 SCOTT@orcl>col table_name for a20
16:40:01 SCOTT@orcl>col monitoring for a20
16:40:15 SCOTT@orcl>select index_name,table_name,monitoring,used from v$object_usage;
INDEX_NAME     TABLE_NAME  MONITORING       USE
-------------------- -------------------- -------------------- ---
EMP_ENAME_SAL_IDX      EMP       NO         YES               表名为emp，现在是非监控状态，已被use
5. 查看索引的监控周期
16:53:47 SCOTT@orcl>col index_name for a20
16:53:56 SCOTT@orcl>col table_name for a20
16:53:59 SCOTT@orcl>col START_MONITORING for a10
16:54:02 SCOTT@orcl>col END_MONITORING  for a10
16:54:06 SCOTT@orcl>select index_name,table_name,START_MONITORING,END_MONITORING from v$object_usage;
INDEX_NAME     TABLE_NAME  START_MONI END_MONITO
-------------------- -------------------- ---------- ----------
EMP_ENAME_SAL_IDX    EMP  05/22/2015 05/22/2015
                    16:32:55   16:38:16
重建索引
1. 重建索引
17:02:30 SCOTT@orcl>alter index EMP_ENAME_SAL_IDX rebuild;
Index altered.
2. 重建索引并且迁移其他表空间
17:03:59 SCOTT@orcl>alter index PK_DEPT rebuild tablespace index_tbs;
Index altered.
3. 使用数据字典user_indexes验证重建索引有效性及表空间迁移变化
17:06:23 SCOTT@orcl>col index_name for a20
17:06:33 SCOTT@orcl>col table_name for a20
17:06:42 SCOTT@orcl>col tablespace_name for a20
17:07:07 SCOTT@orcl>select index_name,table_name,tablespace_name,status from user_indexes;
INDEX_NAME     TABLE_NAME  TABLESPACE_NAME      STATUS
-------------------- -------------------- -------------------- --------
PK_EMP              EMP        USERS       VALID
EMP_JOB_IDX            EMP        USERS       VALID
EMP_ENAME_SAL_IDX       EMP        INDEX_TBS     VALID
PK_DEPT              DEPT      INDEX_TBS     VALID
4. 重建索引EMP_ENAME_SAL_IDX并且修改存储参数
17:07:55 SCOTT@orcl>alter index EMP_ENAME_SAL_IDX rebuild pctfree 20 storage(next 100k);
Index altered.
5. 联机重建索引，这样的方式重建索引用户可以执行dml操作，但是不能执行ddl操作
17:13:16 SCOTT@orcl>alter index EMP_ENAME_SAL_IDX rebuild online;
Index altered.
维护索引
1. 查询当前索引的参数设置
17:37:56 SCOTT@orcl>select index_name,pct_free,pct_increase,INITIAL_EXTENT,next_extent from user_indexes;
INDEX_NAME       PCT_FREE PCT_INCREASE INITIAL_EXTENT NEXT_EXTENT
-------------------- ---------- ------------ -------------- -----------
PK_EMP              10             65536         1048576
EMP_JOB_IDX            10             65536         1048576
EMP_ENAME_SAL_IDX        20             65536         106496
PK_DEPT              10             65536         1048576
2. 通过rebuild修改索引参数
17:39:43 SCOTT@orcl>alter index PK_EMP rebuild tablespace index_tbs pctfree 20 storage(next 100k);
Index altered.
3. 查询修改后索引PK_EMP的参数
17:43:25 SCOTT@orcl>select index_name,STATUS,TABLESPACE_NAME,PCT_FREE from user_indexes;
INDEX_NAME          STATUS   TABLESPACE_NAME     PCT_FREE
-------------------- -------- -------------------- ----------
PK_EMP            VALID    INDEX_TBS          20
EMP_JOB_IDX          VALID    USERS            10
EMP_ENAME_SAL_IDX     VALID    INDEX_TBS          20
PK_DEPT            VALID    INDEX_TBS          10
4. 手动增加索引磁盘空间
17:44:06 SCOTT@orcl>alter index PK_EMP allocate extent;
Index altered.
oracle对每个默认的extent区段数为1，此时为索引PK_EMP增加一个区段，所以该索引有两个区段
5. 查询索引PK_EMP 的区段信息
17:52:07 SCOTT@orcl>select segment_name,segment_type,tablespace_name,extents from user_segments where segment_type='INDEX' and segment_name like 'PK%';
SEGMENT_NAME     SEGMENT_TYPE    TABLESPACE_NAME     EXTENTS
-------------------- --------------- -------------------- ----------
PK_DEPT     INDEX     INDEX_TBS   1
PK_EMP     INDEX     INDEX_TBS   2
6. 合并索引碎片
17:53:04 SCOTT@orcl>alter index EMP_ENAME_SAL_IDX coalesce;
Index altered. 
通过合并索引碎片，可以释放部分磁盘空间，也是维护磁盘的主要方法
删除索引
1. 删除索引EMP_ENAME_SAL_IDX
17:54:35 SCOTT@orcl>drop index EMP_ENAME_SAL_IDX;
Index dropped.