创建索引

在你自己的方案中创建索引，最少需要符合下列条件之一：

1.    待创建索引的表或簇属于你自己的schema。

2.    具体有待创建索引的表的索引权限。

3.    具有CREATE ANY INDEX系统权限。

如果要创建一个表在别的方案下，需要满足一下条件：

1.    具有CREATE ANY INDEX系统权限。

2.    别的方案的拥有者具有相应表空间的索引，索引分区空间配额，或者具有UNLIMITED TABLESPACE系统权限。

 

显式创建索引：

你可以使用语句create index 显式创建索引（区别于完整性约束的自动创建索引）。

CREATE INDEX emp_ename ON emp(ename)

TABLESPACE users

STORAGE (INITIAL 20K

NEXT 20k

PCTINCREASE 75);

你可以明确指定存储参数和表空间，如果不指定将使用默认的参数和表空间。

 

显式创建唯一索引：

唯一索引保证索引列上不会两行具有相同的值。使用CREATE UNIQUE INDEX语句创建唯一索引。

CREATE UNIQUE INDEX dept_unique_index ON dept (dname)

TABLESPACE indx;

你也可以创建一个唯一完整性约束，数据库将自动强制在唯一键上创建一个唯一索引。

 

指定约束产生的索引：

在创建唯一或者主键约束时可以控制索引的选项：

1.    指定数据库中已存在的索引来强制约束。

2.    使用CREATE INDEX语句来创建索引并强制约束。

 

Example 1:

CREATE TABLE a (

a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));

Example 2:

CREATE TABLE b(

b1 INT,

b2 INT,

CONSTRAINT bu1 UNIQUE (b1, b2)

USING INDEX (create unique index bi on b(b1, b2)),

CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);

Example 3:

CREATE TABLE c(c1 INT, c2 INT);

CREATE INDEX ci ON c (c1, c2);

ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;

 

创建索引的时候收集统计信息：

下列语句在创建索引的时候收集索引，表，列的统计信息。

CREATE INDEX emp_ename ON emp(ename)

COMPUTE STATISTICS;

 

创建大型索引：

当你创建一个非常大的索引的时候，按照下列步骤为索引的创建分配一个大的临时表空间。

1.   使用CREATE TABLESPACE 或者 CREATE TEMPORARY TABLESPACE 语句创建一个临时表空间。

2.   在ALTER USER语句中指定TEMPORARY TABLESPACE选项指定临时表空间。

3.   创建索引的时候使用CREATE INDEX语句。

4.   创建索引完成以后，可以使用ALTER USER语句重置为原来的临时表空间，然后使用DROP TABLESPACE语句删除这个新建的表空间。

这些步骤可以避免使的共享的临时表空间扩展到不合理的很大的值，影响性能。

 

Online方式创建索引：

你可以使用online方式创建或者重建索引。这种方式可以让你在修改基表的同时创建或者重建这个表上的索引。使用online方式的时候你可以执行DML操作，DDL不允许。使用online方式的时候不支持并行操作。

 

CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;

 

创建函数索引：

函数索引在限定一个函数或表达式计算出来的结果值的时候能够加速查询。函数或表达式的值预先计算好存放在索引中。

创建函数索引要求COMPATIBLE参数设置为8.1.0.0.0或更高，如果这个索引基于一个用户自定义的函数，那么这个函数必须比较为DETERMINISTIC。如果这个函数索引中使用的函数属于别的用户，那么你必须具有这个函数的EXECUTE对象权限。

此外，使用函数索引必须：

1.    索引创建后表必须已经分析。

2.    查询必须保证索引表达式不会有任何NULL值，因为NULL值不存储在索引中。

 

CREATE INDEX area_index ON rivers (area(geo));

 

SELECT id, geo, area(geo), desc

FROM rivers

WHERE Area(geo) >5000;

 

因为函数索引是基于函数的，因此如果函数被改变，索引就有可能失效。如果函数重新编译有效了，你可以使用ALTER INDEX...ENABLE启用函数索引。或者使用ALTER INDEX...DISABLE

停用函数索引，在你对函数体进行修改等操作的时候考虑做此操作。

 

创建键压缩索引：

创建索引时使用键压缩可以避免重复存储列的前缀值。键压缩把键列分成前缀和后缀。压缩通过索引块中所有的后缀条目对应共享一个前缀条目来完成。（Compression is achieved by sharing the prefix entries among all the suffix entries in an index block.）这种共享使得每个索引块在提升性能的同时存储更多的键值，大量节省空间。

键压缩在一下情况下有用：

1． 你有一个非唯一索引加入rowid使得键唯一。如果使用了压缩，那么重复的键作为前缀存储在索引块中而不含rowid。剩下的行做为后缀条目只有rowid。

2． 你有一个唯一多列索引。

使用COMPRESS关键字启用压缩，前缀的长度可以指定。

CREATE INDEX emp_ename ON emp(ename)

TABLESPACE users

COMPRESS 1;

 

也可以在重建索引的时候指定。使用NOCOMPRESS停用压缩。

ALTER INDEX emp_ename REBUILD NOCOMPRESS;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/517786/viewspace-661534/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/517786/viewspace-661534/