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
|
||
|
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: |
