Oracle数据库索引

 

一、创建索引

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;