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
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 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| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | TEST_SEX_BITIND | | | | |
------------------------------------------------------------------------------------------------
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
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 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
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 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:
转载于:https://www.cnblogs.com/iyoume2008/p/7526340.html
相关资源：Oracle Database 9i10g11g编程艺术：深入数据库体系结构（第2版）--详细书签版