Oracle 创建索引

Oracle创建索引

Oracle在创建索引时要遵循以下的原则：

 平衡查询和DML的需要。在易挥发（DML操作频繁）的表上尽量减少索引的数量，因为索引虽然加快了查询的速度，但却降低了DML操作速度。

 将其放入单独的表空间，不要与表、临时段或还原（回滚）段放在一个表空间，因为索引段会与这些段竞争输入/输出（I/O）。

 使用统一的EXTENT尺寸：数据块尺寸的5倍，或表空间的MINIMUM EXTENT的尺寸。这样做的目的是为了减少系统的转换时间。

 对大索引可以考虑使用NOLOGGING。这样做的目的是通过减少REDO操作来提高系统的效率，但是如果一旦系统发生崩溃，则该索引一般是无法进行完全灰度的。不过问题也不是很大，因为真正的数据还在表中，所以可以通过重建该索引来恢复与之前完全相同的效果。

 索引的INITRANS参数通常应该比相对应表的高。以为索引项要比表中的数据行小的多，所以一个数据块可以存放更多的索引项（记录）。

创建索引的命令格式：

CREATE (UNIQUE|BITMAP) INDEX [用户名.]索引名

ON [用户名.]表名

（列名 [ASC | DESC] [,列名 [ASC| DESC ] ]…）

[TABLESPACE 表空间名]

[PCTFREE 正整型数]

[INITRANS 正整型数]

[MAXTRANS 正整型数]

[存储子句]

[LOGGING | NOLOGGING]

[NOSORT]

其中，

 UNIQUE：说明该索引是唯一索引，默认是非唯一的

 ASC：说明所创建的索引为升序

 DESC：说明所创建的索引为降序

 表空间名：说明将要创建的索引的表空间名

 PCTFREE：在创建索引时每一个块中预留的空间

 INITRANS：在每一个块中预分配的事物记录数，默认值为2

 MAXTRANS：在每一个块中可以分配的事物记录数的上限，默认为255

 存储子句：说明在索引中EXTENTS怎样分配

 LOGGING：说明在创建索引是和以后的索引操作中要记录联机重做日志文件（默认）

 NOLOGGING：说明索引的创建和一些数据装入操作将不记录联机重做日志文件

 NOSORT：数据库中所存的数据行已经按升序排好，因此在创建索引时不需要再排序了

 PCTUSED：在索引中不能说明该参数。因为索引记录必须以正确的顺序存放，所以用户不能控制何时向索引块中插入索引数据行

使用数据字典的dba_indexes获取有关scott用户的索引基于表、所在的表空间、索引的类型和索引的状态等信息。

SQL> select index_name,table_name,tablespace_name,index_type,

uniqueness,status from dba_indexes

where owner = 'SCOTT' AND INDEX_NAME not like 'SYS%'

INDEX_NAME TABLE_NAME TABLESPACE_NAME INDEX_TYPE UNIQUENES STATUS

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

PK_DEPT DEPT USERS NORMAL UNIQUE VALID

PK_EMP EMP USERS NORMAL UNIQUE VALID

使用数据字典dba_ind_columns获得scott用户的索引所基于表和列的等信息。

SQL>select index_name,table_name,column_name,index_owner,table_owner

2 from dba_ind_columns

3* where table_owner = 'SCOTT' and index_name not like 'SYS%'

INDEX_NAME TABLE_NAME COLUMN_NAME INDEX_OWNER TABLE_OWNER

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

PK_EMP EMP EMPNO SCOTT SCOTT

PK_DEPT DEPT DEPTNO SCOTT SCOTT

创建索引：

SQL> CREATE INDEX scott.emp_ename_idx

2 ON scott.emp(ename)

3 PCTFREE 20

4 STORAGE(INITIAL 100K NEXT 100K

5 PCTINCREASE 0 MAXEXTENTS 100)

6* TABLESPACE PIONEER_INDEX

索引已创建。

SQL> CREATE BITMAP INDEX scott.emp_job_idx

2 ON scott.emp(job)

3 PCTFREE 20

4 STORAGE(INITIAL 100K NEXT 100K

5 PCTINCREASE 0 MAXEXTENTS 100)

6 TABLESPACE PIONEER_INDEX;

查看所创建的索引的存储参数是不是按着命令设置的：

SQL> select index_name,pct_free,pct_increase,initial_extent,next_extent

2 from dba_indexes

3 where owner = 'SCOTT' AND INDEX_NAME not like 'SYS%';

INDEX_NAME PCT_FREE PCT_INCREASE INITIAL_EXTENT NEXT_EXTENT

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

PK_DEPT 10 65536

PK_EMP 10 65536

EMP_ENAME_IDX 20 0 1096 1048576

EMP_JOB_IDX 20 0 1096 1048576