创建索引（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:在索引中不能说明这一参数。因为索引记录必须以正确的顺序存放，所以用户不能控制何时向索引块中插入索引数据行。


例:


SQL> select index_name,table_name,tablespace_name,index_type,uniqueness,status from dba_indexes where owner='SCOTT';


INDEX_NAME TABLE_NAME TABLESPACE_N INDEX_TYPE UNIQUENES STATUS
---------- ---------- ------------ ---------- --------- --------
PK_DEPT    DEPT       USERS        NORMAL     UNIQUE    VALID
PK_EMP     EMP        USERS        NORMAL     UNIQUE    VALID




SQL> select index_name,table_name,column_name,index_owner,table_owner
  2  from dba_ind_columns
  3  where table_owner='SCOTT';




INDEX_NAME TABLE_NAME COLUMN_NAME  INDEX_OWNE TABLE_OWNE
---------- ---------- ------------ ---------- ----------
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 longshen;


Index created.




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 longshen;


Index created.




SQL> select index_name,table_name,tablespace_name,index_type,uniqueness,status from dba_indexes where owner='SCOTT';


INDEX_NAME TABLE_NAME TABLESPACE_N INDEX_TYPE UNIQUENES STATUS
---------- ---------- ------------ ---------- --------- --------
PK_DEPT    DEPT       USERS        NORMAL     UNIQUE    VALID
EMP_JOB_ID EMP        LONGSHEN     BITMAP     NONUNIQUE VALID
X


EMP_ENAME_ EMP        LONGSHEN     NORMAL     NONUNIQUE VALID
IDX


PK_EMP     EMP        USERS        NORMAL     UNIQUE    VALID






SQL> select index_name,table_name,column_name,index_owner,table_owner
  2  from dba_ind_columns where table_owner='SCOTT';


INDEX_NAME TABLE_NAME COLUMN_NAME  INDEX_OWNE TABLE_OWNE
---------- ---------- ------------ ---------- ----------
EMP_ENAME_ EMP        ENAME        SCOTT      SCOTT
IDX


EMP_JOB_ID EMP        JOB          SCOTT      SCOTT
X


PK_EMP     EMP        EMPNO        SCOTT      SCOTT
PK_DEPT    DEPT       DEPTNO       SCOTT      SCOTT








SQL> select index_name,pct_free,pct_increase,initial_extent,next_extent from
  2  dba_indexes
  3  where owner='SCOTT';


INDEX_NAME   PCT_FREE PCT_INCREASE INITIAL_EXTENT NEXT_EXTENT
---------- ---------- ------------ -------------- -----------
PK_DEPT            10                       65536
EMP_JOB_ID         20            0         106496     1048576
X


EMP_ENAME_         20            0         106496     1048576
IDX


PK_EMP             10                       65536