Oracle 创建索引
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
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 106496 1048576
EMP_JOB_IDX 20 0 106496 1048576