一、创建索引
1.获取索引信息
SELECT INDEX_NAME, TABLE_NAME, UNIQUENESS, STATUS
FROM USER_INDEXES
WHERE TABLE_NAME = 'TEST_CONTENT'
ORDER BY INDEX_NAME
2.获取列索引信息
SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
FROM USER_IND_COLUMNS
WHERE TABLE_NAME = 'TEST_CONTENT'
3.创建B-树索引
CREATE [UNIQUE] INDEX index_name ON
table_name(column_name[,column_name ...])
TABLESPACE tab_space;
UNIQUE指定索引列中的值必须是唯一的。
index_name指定索引名。
table_name指定数据库表。
column_name指定要对哪个列创建索引。
tab_space指定存储该索引的表空间。
为TEST_SORT表中的NAME列创建B-树索引。
--创建前查询索引,执行以下SQL
SELECT INDEX_NAME, TABLE_NAME, UNIQUENESS, STATUS
FROM USER_INDEXES
WHERE TABLE_NAME = 'TEST_SORT'
ORDER BY INDEX_NAME;
SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
FROM USER_IND_COLUMNS
WHERE TABLE_NAME = 'TEST_SORT';
--输出结果为:
--1 TEST_SORT_ID TEST_SORT UNIQUE VALID
--和
--1 TEST_SORT_ID TEST_SORT ID
创建索引:
--创建索引
CREATE UNIQUE INDEX TEST_SORT_NAME ON TEST_SORT(NAME) TABLESPACE STUDYSPACE;
--创建完成后再执行上面两条查询SQL,结果为:
--1 TEST_SORT_ID TEST_SORT UNIQUE VALID
--2 TEST_SORT_NAME TEST_SORT UNIQUE VALID
--和
--1 TEST_SORT_ID TEST_SORT ID
--2 TEST_SORT_NAME TEST_SORT NAME
4.创建基于函数的索引
比如执行以下SQL
SELECT ID, NAME, ORDER_NUMBER
FROM TEST_SORT
WHERE NAME = UPPER('abc')
这个查询使用了函数UPPER(),因此不会使用上面创建的索引TEST_SORT_NAME。如果想让索引可以基于函数的结果使用,就必须创建基于函数的索引。
--创建函数索引
CREATE INDEX TEST_SORT_NAME_FUNC ON TEST_SORT(UPPER(NAME)) TABLESPACE STUDYSPACE;
--创建完成后查看索引信息为:
--1 TEST_SORT_ID TEST_SORT UNIQUE VALID
--2 TEST_SORT_NAME TEST_SORT UNIQUE VALID
--3 TEST_SORT_NAME_FUNC TEST_SORT NONUNIQUE VALID
--和
--1 TEST_SORT_ID TEST_SORT ID
--2 TEST_SORT_NAME TEST_SORT NAME
--3 TEST_SORT_NAME_FUNC TEST_SORT SYS_NC00004$
5.删除索引
--执行下面两条SQL语句,删除刚创建的两个索引
DROP INDEX TEST_SORT_NAME;
DROP INDEX TEST_SORT_NAME_FUNC;
--删除完成后查看索引信息为:
--1 TEST_SORT_ID TEST_SORT UNIQUE VALID
--和
--1 TEST_SORT_ID TEST_SORT UNIQUE VALID
6.位图索引
位图索引一般用于数据仓库中,数据仓库是包含大量数据的数据库。数据仓库中的数据一般使用很多查询来读取,但数据并不被很多并发事务所修改。数据仓库一般被组织机构用来进行商业智能分析。
位图索引的候选列是在很多查询中被引用但只包含小范围值的列。如果某列的不同值数量小于表中行数的1%,或者如果某列的值的重复次数多于100次,那么此列就是位图索引的候选列。
下面在TEST_CONTENT表的SORT_ID列上创建位图索引:
注:SORT_ID列与TEST_SORT表的主键关联,TEST_CONTENT表中有2100万条数据,TEST_SORT表中有近300条数据。
--创建位图索引
CREATE BITMAP INDEX I_TEST_CONTENT_SORT_ID ON TEST_CONTENT(SORT_ID)
--创建成功后查看索引信息:
--1 I_TEST_CONTENT_SORT_ID TEST_CONTENT NONUNIQUE VALID
--2 TEST_CONTENT_ID TEST_CONTENT UNIQUE VALID
--和
--1 TEST_CONTENT_ID TEST_CONTENT ID
--2 I_TEST_CONTENT_SORT_ID TEST_CONTENT SORT_ID
--执行SQL进行查询
SELECT S.ID AS SID, S.NAME, C.TITLE, C.SUBHEAD, C.SUMMARY
FROM TEST_CONTENT C, TEST_SORT S
WHERE C.SORT_ID = S.ID
AND C.SORT_ID = 170
--创建位图索引前要执行2分30秒左右,创建位图索引后,第一次执行只要0.15-0.2秒,后面要0.03秒左右。
删除索引同上:
DROP INDEX I_TEST_CONTENT_SORT_ID
二、何时创建索引
在需要从包含很多行的表中检索少数几行时,都应该对列创建索引。有一条基本准则是:当任何单个查询要检索的行少于或等于整个表行数的10%时,索引就非常有用。
好的索引候选列是那些对每个记录只包含唯一数字的列;差的索引候选列是那些只包含很小范围的数字代码的列。
Oracle数据库会为表的主键以及包含在唯一约束中的列自动创建B-树索引。
对于包含小范围值的列,可以使用“位图”索引。
由于性能方面的原因,通常应该将索引与表存储到不同的表空间。
1.何时创建索引
创建索引的基本原则:
1)对于经常以查询关键字为基础的表,并且该表中的行遵从均匀分布。
2)以查询关键字为基础,表中的行随机排序。
3)包含的列数相对比较少的表。
4)表中的大多数查询都包含相对简单的WHERE从句。
5)缓存命中率低,并且不需要操作系统缓存。
2.索引列和表达式的选择
1)WHERE从句频繁使用的关键字。
2)SQL语句中频繁用于进行表连接的关键字。
3)可选择性高的(重复性少的)关键字。
4)对于取值较少的关键字或表达式,不要采用标准的B+树索引,可以考虑建立位图索引。
5)不要将那些频繁修改的列作为过引列。
6)不要使用包含操作符或者函数的WHERE从句中的关键字作为索引列,如果需要可建立函数索引。
7)如果大量并发的INSERT、UPDATE、DELETE语句访问了父表或者子表,则考虑使用完整性约束的外部键作为索引。
8)在选择索引列时,还要考虑该索引所引起的INSERT、UPDATE、DELETE操作是否值得。
3.选择复合索引
在建立索引时采用了几个列作为索引,则在使用时也要按照建立时的顺序来描述。如:
CREATE INDEX i_temp ON temp(x,y,z)
则查询语句中的WHERE为:
SELECT * FROM temp WHERE x=c1 AND y=c2 AND z=c3
选择复合索引的关键字时,要遵循下列原则:
1)应该选择WHERE从句条件中频繁使用的关键字,并且这些关键字由AND操作符连接。
2)如果几个查询都选择相同的关键字集合,则考虑创建组合索引。
3)创建索引以后使得WHERE从句所使用的关键字能够组成前导部分。
4)如果某些关键字在WHERE从句中的使用频率较高,则考虑创建索引。
5)如果某些关键字在WHERE从句中的使用频率相当,则创建索引时考虑按照从高到低的顺序来说明关键字。
4.避免对大表的全表扫描
以下情况Oracle会全表扫描:
1)所查询的表没有索引
2)需要返回所有的行
3)带like并使用“%”这样的语句
4)对索引主列有条件限制,但使用了函数
--如
WHERE UPPER(city) = 'TOKYO'
5)带有is null和is not null 及!=等子句也导致全表扫描。
5.监视索引是否被使用
--修改索引,可以对索引进行监视
ALTER INDEX indexname MONITORING USAGE;
--检查索引使用情况
SELECT * FROM V$OBJECT_USAGE;
--删除不使用的索引
DROP INDEX indexname;