Oracle数据库管理（17）——索引

索引是与表相关的一个可选结构。
可以提高SQL语句执行的性能、减少磁盘I/O。索引对于表的作用相当于目录对于书的作用。
索引在逻辑和物理上都独立于表的数据。
Oracle自动维护索引。

索引的分类

索引分为：B树索引（平衡树索引）、位图索引。B树索引分为：唯一索引、组合索引、反向键索引、基于函数的索引。

创建索引

创建标准语法：
create index 索引名 on 表名(列名);
//先创建一个表
SQL> create table student(sid number,sname varchar2(20),age number);
Table create
//创建一个索引。创建索引时不加任何参数就会创建B树索引。
SQL> create index ind_1 on student(sid);
Index created
//查看索引信息
SQL> select * from user_indexes;
INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME                     TABLE_TYPE  UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME                 INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN  FREELISTS FREELIST_GROUPS   PCT_FREE LOGGING     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS     NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE                                   INSTANCES                                PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE USER_STATS DURATION        PCT_DIRECT_ACCESS ITYP_OWNER                     ITYP_NAME                      PARAMETERS                                                                       GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX IOT_REDUNDANT_PKEY_ELIM DROPPED VISIBILITY DOMIDX_MANAGEMENT SEGMENT_CREATED
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- ---------- ----------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- ------- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- ------------- ---------------------------------------- ---------------------------------------- ----------- --------- --------- --------- ----------- ----------- ---------------- ---------- --------------- ----------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------ ------------- --------------- -------------- ---------- ----------------------- ------- ---------- ----------------- ---------------
PK_DEPT                        NORMAL                      SCOTT                          DEPT                           TABLE       UNIQUE     DISABLED                  USERS                                   2        255          65536     1048576           1  2147483645                                                                              10 YES              0           1             4                       1                       1                 1 VALID             4           4 2021/1/13 22: 1                                        1                                        NO          N         N         N         DEFAULT     DEFAULT     DEFAULT          NO                                                                                                                                                                                          YES                                                       NO         NO                      NO      VISIBLE                      YES
PK_EMP                         NORMAL                      SCOTT                          EMP                            TABLE       UNIQUE     DISABLED                  USERS                                   2        255          65536     1048576           1  2147483645                                                                              10 YES              0           1            14                       1                       1                 1 VALID            14          14 2021/1/13 22: 1                                        1                                        NO          N         N         N         DEFAULT     DEFAULT     DEFAULT          NO                                                                                                                                                                                          YES                                                       NO         NO                      NO      VISIBLE                      YES
IND_1                          NORMAL                      SCOTT                          STUDENT                        TABLE       NONUNIQUE  DISABLED                  USERS                                   2        255                                                                                                                                 10 YES              0           0             0                       0                       0                 0 VALID             0           0 2022/1/5 21:0 1                                        1                                        NO          N         N         N         DEFAULT     DEFAULT     DEFAULT          NO                                                                                                                                                                                          NO                                                        NO         NO                      NO      VISIBLE                      NO
//查看索引列信息
SQL> select * from user_ind_columns u where u.INDEX_NAME = 'IND_1';
INDEX_NAME                     TABLE_NAME                     COLUMN_NAME                                                                      COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------------- ------------- ----------- -------
IND_1                          STUDENT                        SID                                                                                            1            22           0 ASC

分析索引

analyze index 索引名 valdate structure;
//分析索引
SQL> analyze index ind_1 validate structure;
Index analyzed
//查看index_stats表中的ptc_used的值
SQL> select name,pct_used from index_stats where name = 'ind_1';
NAME                             PCT_USED
------------------------------ ----------

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

Index altered

查看index_stats表中的ptc_used的值，如果pct_used的值过低，说明在索引中存在碎片，可以重建索引，来提高pct_used的值，减少索引中的碎片。

唯一索引

索引定义的列中没有重复值。

创建唯一索引语法：
create unique index 索引名 on 表名(列名);

唯一索引列可以插入多个空值。

组合索引

表中的若干列组合的结果唯一。例如学生表中“班级ID”和“班内编号”组合。
组合索引常用在有过的条件的查询时。例如“select * from 学生 weher 班级ID=1 and 班内编号=1

组合索引语法：
create index 索引名 on 表名(列1，列2,......);

反向键索引

反向键索引反转索引列键值的每个字节。例如键值为“123”、“124”、“125”，则索引存放为“321”、“421”、“521”。
通常建立在值是连续增长的列上，使数据均匀的分布在整个索引上。
创建索引时使用reverse关键字。

创建反向键索引语法：
create index 索引名 on 表名(列名) reverse;

位图索引

位图索引适合建立在低基数列上。即便条目非常多，但该列的取值非常少，例如“季度”列只有“一季度”、“二季度”、“三季度”、“四季度”四种取值，该列就叫做低基数列。
位图索引不直接存放rowid，而是存储字节位到rowid的映射。
节省索引空间占用。
如果索引列经常被更新的话，不适合建立位图索引。
位图索引适合用于数据仓库中，不适合用于OLTP中。

创建位图索引语法：
create bitmap index 索引名 on 表名(列名);