索引

oracle213

---

---

索引

q索引是与表相关的一个可选结构

q用以提高 SQL 语句执行的性能

q减少磁盘I/O

q使用 CREATE INDEX 语句创建索引

q在逻辑上和物理上都独立于表的数据

qOracle 自动维护索引

q索引分为：B树索引（平衡树索引）、位图索引（数据仓库中用的多）。

qB树索引分为：唯一索引、组合索引、反向键索引、基于函数的索引

qanalyze index <index_name> validate structure;

查看index_stats表中的pct_used列的值，如果pct_used的值过低，说明在索引中存在碎片，

可以重建索引，来提高pct_used的值，减少索引中的碎片。

索引 碎片的产生：由于索引建立以后对数据进行了DML了，那么就会让索引块找不到对应的数据块；这样索引块中就存在无效的索引这样就产生了碎片

司 索引重建：1. 删除后重新建立 2. rebuild

SQL> begin

2 for i in 1.. 1300000 loop

3 insert into test_index values(ltrim( to_char(i ,'00000009')));

4 if mod (i,100) = 0 then

5 commit;

6 end if;

7 end loop;

8 end;

9 /

SQL>

PL/SQL procedure successfully completed

SQL> commit;

Commit complete

SQL>create index index_test on test_index(id);

Index created

SQL> select * from user_indexes ui where ui.INDEX_NAME='INDEX_TEST';

INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME

------------------------------ --------------------------- --------------------

INDEX_TEST NORMAL HR TEST_INDEX

SQL> select * from user_ind_columns uic where uic.INDEX_NAME='INDEX_TEST';

INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------------- ------------- ----------- -------

INDEX_TEST TEST_INDEX ID 1 10 10 ASC

SQL>analyze index index_test validate structure;//分析索引

Index analyzed

SQL> select is_.name, is_.pct_used fromindex_statsis_ where is_.name='INDEX_TEST';

NAME PCT_USED

------------------------------ ----------

INDEX_TEST 90

SQL>

SQL> delete from test_index where rownum < 500000;

499999 rows deleted

SQL> commit;

Commit complete

SQL> analyze index index_test validate structure;

Index analyzed

SQL> select is_.name, is_.pct_used from index_stats is_ where is_.name='INDEX_TEST';

NAME PCT_USED

------------------------------ ----------

INDEX_TEST 69

SQL>alter index index_test rebuild;//重建索引

Index altered

SQL> analyze index index_test validate structure;

Index analyzed

SQL> select is_.name, is_.pct_used from index_stats is_ where is_.name='INDEX_TEST';

NAME PCT_USED

------------------------------ ----------

INDEX_TEST 90

SQL>

唯一索引：

唯一索引确保在定义索引的列中没有重复值

Oracle 自动在表的主键列上创建唯一索引

使用CREATE UNIQUE INDEX语句创建唯一索引

SQL> CREATE UNIQUE INDEX item_index

ON itemfile (itemcode);

组合索引：

q组合索引是在表的多个列上创建的索引

q索引中列的顺序是任意的

q如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度

SQL> CREATE INDEX comp_index

ON itemfile(p_category, itemrate);

反向键索引：

q反向键索引反转索引列键值的每个字节

q通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上

比如学号是1001,1002,1003,1004——>1001,2001,3001,4001这样的形式存在于索引块中。

q创建索引时使用REVERSE关键字

SQL> CREATE INDEX rev_index

ON itemfile (itemcode) REVERSE;

SQL> ALTER INDEX rev_index REBUID NOREVERSE;

位图索引：

q位图索引适合创建在低基数列(即使条目非常多但是情况非常少：男、女 这样的数据)上

q位图索引不直接存储ROWID值，而是存储字节位到ROWID的映射

q节省空间占用

q如果索引列被经常更新的话，不适合建立位图索引

q总体来说，位图索引适合于数据仓库中，不适合OLTP中

SQL> CREATE BITMAP INDEX bit_index

ON order_master (orderno);

基于函数的索引：

q基于一个或多个列上的函数或表达式创建的索引

q表达式中不能出现聚合函数

q不能在LOB类型的列上创建

q创建时必须具有 QUERY REWRITE 权限

SQL> CREATE INDEXlowercase_idxON toys (LOWER(toyname));

SQL> SELECTtoyid FROM toyWHERELOWER(toyname)='doll';

这样上面的查询就会变快了，索引是在列的小写函数上建立的；查询快不快关键在于where的过滤好不好

重建索引：

qALTER INDEXindex_nameREBUILD [ONLINE] [NOLOGGING] [COMPUTE STATISTICS];

其中：ONLINE使得在重建索引过程中，用户可用对原来的索引进行修改；

NOLOGGING表示在重建过程中产生最少的重做条目redoEntry；

COMPUTE STATISTICS表示在重建过程中就生成了oracle

优化器所需的统计信息，避免了索引重建之后再进行analyze或dbms_stats来收集统计信息。

索引的分区：

q可以将索引存储在不同的分区中

q与分区有关的索引有三种类型：

q局部分区索引 － 在分区表上创建的索引，在每个表分区上创建独立的索引，索引的分区范围与表一致

• SQL> create table test_partition (
  2 id number(20),name varchar2(20),account number(20)) partition by range(account)(
  3 partition part01 values less than (100),
  4 partition part02 values less than (200),
  5 partition part03 values less than(maxvalue));
  Table created
  
  
  SQL> create index test_partition_index on test_partition(account) local;
  Index created
  
  
  SQL> select ui.INDEX_NAME,ui.index_type,ui.PARTITIONED from user_indexes ui where ui.INDEX_NAME='TEST_PARTITION_INDEX';
  INDEX_NAME INDEX_TYPE PARTITIONED
  ------------------------------ --------------------------- -----------
  TEST_PARTITION_INDEX NORMAL YES
  
  
  SQL> select uip.index_name,uip.partition_name,uip.high_value from user_ind_partitions uip where uip.index_name='TEST_PARTITION_INDEX';
  INDEX_NAME PARTITION_NAME HIGH_VALUE
  ------------------------------ ------------------------------ --------------------------------------------------------------------------------
  TEST_PARTITION_INDEX PART01 100
  TEST_PARTITION_INDEX PART02 200
  TEST_PARTITION_INDEX PART03 MAXVALUE
  
  
  SQL> select utp.table_name,utp.partition_name,utp.high_value from user_tab_partitions utp where utp.table_name='TEST_PARTITION';
  TABLE_NAME PARTITION_NAME HIGH_VALUE
  ------------------------------ ------------------------------ --------------------------------------------------------------------------------
  TEST_PARTITION PART01 100
  TEST_PARTITION PART02 200
  TEST_PARTITION PART03 MAXVALUE
  
  
  SQL>

q全局分区索引 － 在分区表或非分区表上创建的索引，索引单独指定分区的范围，与表的分区范围或是否分区无关

• SQL> create index index_partition_global on test_partition(account) global;
  create index index_partition_global on test_partition(account) global
  ORA-01408: 此列列表已索引
  
  
  SQL> drop index test_partition_index ;
  Index dropped
  
  
  SQL> create index index_partition_global on test_partition(account) global partition by range(account) (
  2 partition part1 values less than (200),
  3 partition part2 values less than(maxvalue));
  Index created
  
  
  SQL> select uip.index_name,uip.partition_name,uip.high_value from user_ind_partitions uip where uip.index_name='INDEX_PARTITION_GLOBAL';
  INDEX_NAME PARTITION_NAME HIGH_VALUE
  ------------------------------ ------------------------------ --------------------------------------------------------------------------------
  INDEX_PARTITION_GLOBAL PART1 200
  INDEX_PARTITION_GLOBAL PART2 MAXVALUE
  
  
  SQL>

q全局非分区索引 － 在分区表上创建的全局普通索引，索引没有被分区

• SQL> create index index_partition_global on test_partition(account) global;
  Index created
  
  
  SQL> select ui.INDEX_NAME,ui.index_type,ui.PARTITIONED from user_indexes ui where ui.INDEX_NAME='INDEX_PARTITION_GLOBAL';
  INDEX_NAME INDEX_TYPE PARTITIONED
  ------------------------------ --------------------------- -----------
  INDEX_PARTITION_GLOBAL NORMAL NO
  
  
  SQL>

获取索引信息：

q与索引有关的数据字典视图有：

qUSER_INDEXES － 用户创建的索引的信息

qUSER_IND_PARTITIONS － 用户创建的分区索引的信息

qUSER_IND_COLUMNS － 与索引相关的表列的信息

SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME

FROM USER_IND_COLUMNS

ORDER BY INDEX_NAME, COLUMN_POSITION;