Oracle11g温习-第十三章：索引

2013年4月27日星期六

10:46

1、索引（Index）的功能：对记录进行排序，加快表的查询速度

2、索引的分类

1）B-tree 索引（默认）

a、在一个大表上

b、建立在重复值比较少的列上，在做select查询时，返回记录的行数小于全部记录的4%

c、如果这一列经常用来做where子句和排序，也可以用来建立索引

d、一般用于OLTP

2） bitmap index

a、建立在重复值非常高的列上

b、在做DML 操作时，代价值比较高

c、一般用于OLAP 或DSS

—— B-tree 索引不能使用or连接的语句，bitmap index可以使用

3、建立索引：默认建立 B-tree index

4、建立索引表空间

SYS @ prod >create tablespace indexes datafile '/u01/app/oracle/oradata/prod/index01.dbf' size 50m autoextend on next 10m maxsize 50m extent management local uniform size 128k;

Tablespace created.

SQL> select file_id,file_name,tablespace_name from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME

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

1 /u01/app/oracle/oradata/prod/system01.dbf SYSTEM

2 /u01/app/oracle/oradata/prod/lx01.dbf LX01

3 /u01/app/oracle/oradata/prod/sysaux01.dbf SYSAUX

4 /u01/app/oracle/oradata/prod/users01.dbf USERS

7 /u01/app/oracle/oradata/prod/undotbs02.dbf UNDOTBS2

12 /u01/app/oracle/oradata/prod/index01.dbf INDEXES

SQL> create table test (id int,name varchar2(10),sex varchar2(4));

Table created.

SQL> begin

for i in 1..10000 loop

insert into test values(i,'user’||i,'M');

end loop;

end;

SQL> begin

for i in 10001..20000 loop

insert into test values(i,'user’||i,'F');

end loop;

end;

SQL> select count(*) from test;

COUNT(*)

----------

20000

——建立b-tree 索引

SQL> create index test_sex_idx on test(sex) tablespace indexes;

Index created.

——分析索引结构

SQL> analyze index test_sex_idx validate structure;

Index analyzed.

SQL> select index_name,index_type,tablespace_name,blevel,leaf_blocks,num_rows

from user_indexes where index_name='TEST_SEX_IDX';

INDEX_NAME INDEX_TYPE TABLESPACE_NAME BLEVEL LEAF_BLOCKS NUM_ROWS

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

TEST_SEX_IDX NORMAL INDEXES 1 37 20000

【——BLEVEL 索引的深度（高度 =深度+1）

——LEAF_BLOCKS，使用的索引块】

SQL> select index_name,table_name,column_name from user_ind_columns

where index_name='TEST_SEX_IDX';

INDEX_NAME TABLE_NAME COLUMN_NAME

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

TEST_SEX_IDX TEST SEX

SQL> select /*+ index (test TEST_SEX_IXD)*/ name,sex from test where sex='F';

10000 rows selected.

Execution Plan

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

Plan hash value: 1357081020

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

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

| 0 | SELECT STATEMENT | | 10000 | 97K| 15 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TEST | 10000 | 97K| 15 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("SEX"='F')

Statistics

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

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

0 bytes sent via SQL*Net to client

0 bytes received via SQL*Net from client

0 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10000 rows processed

SQL> drop index test_sex_idx;

Index dropped.

——建立位图索引

SQL> create bitmap index test_sex_bitind on test(sex) tablespace indexes;

Index created.

——分析索引结构

SQL> analyze index test_sex_bitind validate structure;

Index analyzed.

SQL> select index_name,index_type,tablespace_name,blevel,leaf_blocks,num_rows from user_indexes where index_name='TEST_SEX_BITIND';

INDEX_NAME INDEX_TYPE TABLESPACE_NAME BLEVEL LEAF_BLOCKS NUM_ROWS

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

TEST_SEX_BITIND BITMAP INDEXES 0 1 2

——在重复值高的列上适合建立bitmap的索引

SQL> select index_name,table_name,column_name from user_ind_columns

where index_name='TEST_SEX_BITIND';

INDEX_NAME TABLE_NAME COLUMN_NAME

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

TEST_SEX_BITIND TEST SEX

SQL> select /*+ index (test TEST_SEX_BITIND)*/ name,sex from test where sex='F';

10000 rows selected.

Execution Plan

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

Plan hash value: 2624764158

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

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

| 0 | SELECT STATEMENT | | 10000 | 97K| 85 (0)| 00:00:02 |

| 1 | TABLE ACCESS BY INDEX ROWID | TEST | 10000 | 97K| 85 (0)| 00:00:02 |

| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |

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

Predicate Information (identified by operation id):

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

3 - access("SEX"='F')

Statistics

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

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

0 bytes sent via SQL*Net to client

0 bytes received via SQL*Net from client

0 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10000 rows processed

SQL> drop index TEST_SEX_BITIND;

Index dropped.

5、基于函数的索引

SQL> conn scott/tiger

Connected.

SQL> set autotrace on;

SQL> create index emp_ename_ind on emp(ename) tablespace indexes;

Index created.

SQL> select * from emp where ename='SCOTT';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

Execution Plan

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

Plan hash value: 3220259315

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

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

| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | EMP_ENAME_IND | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ENAME"='SCOTT')

Note

-----

- dynamic sampling used for this statement

Statistics

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

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

0 bytes sent via SQL*Net to client

0 bytes received via SQL*Net from client

0 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

——通过函数访问，索引无效

SQL> select * from emp where lower(ename)='scott';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

Execution Plan

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

Plan hash value: 3956160932

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

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

| 0 | SELECT STATEMENT | | 1 | 87 | 15 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 15 (0)| 00:00:01 | ——索引无效

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

Predicate Information (identified by operation id):

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

1 - filter(LOWER("ENAME")='scott')

Note

-----

- dynamic sampling used for this statement

Statistics

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

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

0 bytes sent via SQL*Net to client

0 bytes received via SQL*Net from client

0 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

——建立函数索引

SQL> create index emp_ename_funind on emp(lower(ename)) tablespace indexes;

Index created.

SQL> select * from emp where lower(ename)='scott';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

Execution Plan

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

Plan hash value: 519807088

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

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

| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | EMP_ENAME_FUNIND | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access(LOWER("ENAME")='scott')

Note

-----

- dynamic sampling used for this statement

Statistics

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

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

0 bytes sent via SQL*Net to client

0 bytes received via SQL*Net from client

0 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

6、索引监控

SQL> conn scott/tiger

Connected.

SQL> alter index emp_ename_funind monitoring usage;

Index altered.

SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME TABLE_NAME MONITORIN USED

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

EMP_ENAME_FUNIND EMP YES NO

SQL> select * from emp where LOWER(ename)='scott';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

SQL> select * from emp where lower(ename)='scott';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME TABLE_NAME MONITORIN USED

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

EMP_ENAME_FUNIND EMP YES YES

7、【反向索引----------用于建立索引的列值是连续的或通过序列生成时，避免索引存放到集中的leaf block上,避免生成热块】。

1）建立反向索引

SQL> create index r_empno_ind on test(empno) reverse;

Index created.

——【对于emp表里empno列来说，因为客户ID号顺序递增，所以为了均衡索引数据分布，应在该列上建立反向索引。】

2）重建索引

SQL> alter index r_empno_ind rebuild reverse;

Index altered.

8、与索引有关的视图

DBA_INDEXES:

DBA_IND_COLUMNS:

V$OBJECT_USAGE:

相关阅读:
使用 Dockerfile 定制镜像
 UVA 10298 Power Strings 字符串的幂（KMP，最小循环节）
UVA 11090 Going in Cycle!! 环平均权值（bellman-ford，spfa，二分）
LeetCode Best Time to Buy and Sell Stock 买卖股票的最佳时机（DP）
LeetCode Number of Islands 岛的数量（DFS，BFS）
LeetCode Triangle 三角形（最短路）
LeetCode Swap Nodes in Pairs 交换结点对（单链表）
LeetCode Find Minimum in Rotated Sorted Array 旋转序列找最小值（二分查找）
HDU 5312 Sequence (规律题)
LeetCode Letter Combinations of a Phone Number 电话号码组合
原文地址：https://www.cnblogs.com/iyoume2008/p/7526340.html