SQL> create table tab_big as select * from dba_objects;
SQL> insert into tab_big select * from tab_big;
SQL> /
SQL> select count(*) from tab_big;
COUNT(*)
----------
199092
SQL> commit;
=============================================
索引
SQL> select count(*) from tab_big where OBJECT_ID=30001;
COUNT(*)
----------
4
SQL> set autotrace traceonly
SQL> select * from tab_big where OBJECT_ID=30001;
* 1 | TABLE ACCESS FULL (全表扫描)
2823 consistent gets(逻辑读2823次)
SQL> create index idx001 on tab_big(OBJECT_ID); (创建一个索引)
SQL> select * from tab_big where OBJECT_ID=30001;(再次执行)
|* 2 | INDEX RANGE SCAN (索引范围扫描)
99 consistent gets (逻辑读99次)
SQL> drop index idx001;(删除索引)
SQL> create index idx001 on tab_big(OBJECT_ID) tablespace users;
(索引也会存储到表空间中,指定表空间,索引太多增删改就会慢,操作中会先读索引再读表)
===================================================
索引扫描方式
1.索引唯一扫描（index unique scan).这种扫描发生在主键或者唯一索引上，根据键值可以唯一确定要访问的记录，这种扫描方式因为返回的记录数少，能够快速定位记录，扫描效率较高
2.索引范围扫描（index range scan).这种一般发生在返回多个值的时候，如where条件中>and <或者非唯一索引中的=时，范围扫描要求返回的结果集不能太多，否则不能从索引扫描上获取益处，因为从索引只能获得rowid与索引列的值，，有可能还需要根据rowid回表一条条的去找行的其他数据，除非不需要回表便能从索引上获得必需的数据。
使用index rang scan的3种情况：
(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)。
(b) 在组合索引上，只使用部分列进行查询，导致查询出多行。
(c) 对非唯一索引列上进行的任何查询。
3.索引全扫描（index full scan)这种方式中，ORACLE会按照索引值的顺序全部扫描该索引，类似全表扫描，效率不高。CBO根据统计数据得知进行全索引扫描比进行全表扫描更有效时，才能进行全索引扫描，而且此时查询出的数据都必须可以从索引中直接得到。
4.索引快速扫描（index fast full scan).与索引全扫描不同，不按照索引值的顺序来访问，而是直接读取索引块来访问索引数据。这种存取方法中，可以使用多块读功能。也可以直接使用并行读方法以便于获得最大吞吐量与缩短执行时间。
5.索引跳跃式扫描（index skip scan) .复合索引中第一个字段类型少，而第二个字段唯一，当条件中用到第二个字段时，将跳过第一个字段。
实际OLTP系统中只有索引唯一扫描和索引范围扫描。
===================================================
B树索引 根-->支-->叶
索引中row id 跟表中有对应关系
move后表 rowid变化 所以索引实效
位图索引:(用的少,尤其在增删改比较多的时候)
主要针对大量相同值的列而创建(例如：类别，操作员，部门ID,库房ID等)
把相同的放成一组
好处:这种方式存储数据,相对于B*Tree索引,占用的空间非常小,创建和使用非常快
坏处:增删改时会锁一组数据
SQL> create index idx001 on tab_big(OBJECT_ID) nologging;(不产生日志)
------------------------------------
大型索引得运行很久,放到后台运行(生产系统常用)
[oracle@sq123 ~]$ vi test.sh
#!/bin/bash
sqlplus -S /nolog >result.log <<EOF
set heading off feedback off pagesize 0 verify off echo off
conn sys/123456 as sysdba
create index idx002 on tab_big(OBJECT_ID);
exit
EOF
[oracle@sq123 ~]$ chmod 777 test.sh
SQL> drop index idx001;
[oracle@sq123 ~]$ nohup sh test.sh &(后台运行nohup,即使关闭会话也会后台运行)
查看索引
SQL> select INDEX_NAME,INDEX_TYPE from user_indexes where TABLE_NAME='TAB_BIG';
====================================================
位图索引
SQL> show parameter bitmap
可以把create_bitmap_area_size(位图索引用的缓存区)调大创建速度快
SQL> select 100*1024*1024 from dual;
100*1024*1024
-------------
104857600
SQL> alter system set create_bitmap_area_size=104857600 scope=spfile;(修改大小为100M)
SQL> select distinct OBJECT_TYPE from tab_big;
(distinct过滤掉重复的值)
开启新的会话
SQL> set autotrace traceonly
SQL> select distinct OBJECT_TYPE from tab_big;(重新运行)
10973 consistent gets
2740 physical reads
SQL> create bitmap index idx009 on tab_big(OBJECT_TYPE) nologging;(创建位图索引)
SQL> select distinct OBJECT_TYPE from tab_big;(重新运行)
129 consistent gets
46 physical reads
(删除位图索引,再对OBJECT_TYPE创建 B树索引,会发现逻辑读多出很多)
---------------------------------------------------------
online
SQL> create index t11 on t1(object_id);
(没有加online,如果创建索引的过程中 插入数据 不等待)
SQL> create index t11 on t1(object_id) online;
(如果创建索引的过程中 插入数据 等待)
-----------------------------------------------------------
B树的函数索引
SQL> desc tab_big;
SQL> select max(TIMESTAMP) from t1;
SQL> select count(*) from t1 where substr(TIMESTAMP,1,10)='2012-04-24';
(substr 为截取,1,10为从1开始到10个字符)
COUNT(*)
----------
420
2.
SQL> set autotrace traceonly
SQL> select count(*) from tab_big where substr(TIMESTAMP,1,10)='2011-04-05';
2814 consistent gets(逻辑读)
3.
SQL> create index idx101 on tab_big(TIMESTAMP) nologging online parallel 2;
(parallel 并行,安cpu算,服务器多cpu 看服务器的空闲资源有多少)
4.重新查看(还是全表扫描)
SQL> set autotrace traceonly
SQL> select count(*) from tab_big where substr(TIMESTAMP,1,10)='2011-04-05';
(结果没有变 逻辑读还是很高)
5.创建函数索引
SQL> drop index idx101;
SQL> create index idx101 on t1(substr(TIMESTAMP,1,10)) nologging online parallel 2;
6.
SQL> set autotrace traceonly
SQL> select count(*) from tab_big where substr(TIMESTAMP,1,10)='2011-04-05';
结果值降了
只有oracle有函数,位图 索引
-------------------------------
复合索引
如果where条件为2个
创建2个 单一条件索引,性能没有创建一个复合索引好
因为单的会搜索2次,复合一次 20 ICOL$
---------------------------------
重新组织索引
场景:表上增删改太多会有很多碎片,应该重新组织,或move后 索引实效需要重新组织.
1.查看索引
SQL> select t.index_name,t.status from user_indexes t;
IDX101 VALID
2.移动
SQL> alter table tab_big move;
SQL> select t.index_name,t.status from user_indexes t;
(重新查看)
IDX101 UNUSABLE(索引失效)
3.
SQL> set autotrace traceonly
SQL> select count(*) from tab_big where substr(TIMESTAMP,1,10)='2011-04-05';
(结果没有用上索引 逻辑读很多)
4.重新组织索引(rebuild其实就是先删再创建)
SQL> alter index IDX101 rebuild nologging parallel 2;
SQL> select t.index_name,t.status from user_indexes t;
IDX101 VALID(又生效了 valid有效的意思)
5.SQL> set autotrace traceonly
SQL> select count(*) from tab_big where substr(TIMESTAMP,1,10)='2011-04-05';
(发现结果 逻辑读又很少)
--------------------------------------
索引排序
系统默认索引排序为asc
可以设置为倒序 desc
SQL> drop index idx001;
SQL> create index idx001 on tab_big (object_id desc);
**DB2 支持双向索引 oracle不支持**
SQL> select object_id from order by 1 desc;
(改为倒序 效率相对高,一般用在日期列,显示最新信息)
-------------------------------------------
索引组织表
索引组织表(IOT)不仅可以存储数据，还可以存储为表建立的索引。索引组织表的数据是根据主键排序后的顺序进行排列的，这样就提高了访问的速度。但是这是由牺牲插入和更新性能为代价的(每次写入和更新后都要重新进行重新排序)。
create table indexTable(
ID varchar2 ( 10 ),
NAME varchar2 ( 20 ),
constraint pk_id primary key ( ID )
)
organization index;
创建IOT时，必须要设定主键，否则报错
SQL> insert into indexTable (id,name)select object_id,owner from dba_objects;
SQL> commit;
--------------------------------------------
反转索引:
反转索引在存储键值的时候，先把键值反转，再进行存储，比如abcd就反转为dcba，一般反转索引用来解决热块，原理就是利用键值反转，把索引块打乱，把热点分散到不同的索引块。
create index a111 on a123(id) reverse;
---------------------------------------------
压缩索引:
oracle 索引压缩（key compression）是oracle 9i 中引入的一项新特性。该特性可以压缩索引或者索引组织表中的重复键值，从而节省存储空间。非分区的unique 索引和non-unique（至少两列）索引都能够被压缩。bitmap 索引不能够进行压缩。
在oracle 索引压缩中有几个比较纠结的术语，需要说明一下。索引压缩是通过将索引中的键值拆分成两部分实现的，也就是grouping piece 也称作prefix 和 unique piece 也称作suffix 。grouping piece 是用来压缩的被unique piece 共享的部分。如果键值不能提供unique piece，那么oracle 将会使用rowid 来唯一标识。只有B-tree 索引的叶子节点能够被压缩，分支节点不能够被压缩。索引压缩是在单个block 中完成的，不能够跨blocks进行索引压缩。grouping piece (prefix) 和 unique piece (suffix) 存储在同一个索引 block 中。
具体prefix 和 suffix 是怎么划分的呢？默认prefix 长度等于索引列的数量减去1。当然我们可以人为控制prefix 的长度，非唯一索引的最大prefix 长度等于索引列的数量。唯一索引的最大prefix 长度等于索引列的数量减去1。比如，假设索引有三个列：
默认的时候：prefix (column1,column2) suffix (column3)
如果有以下几组键值（1,2,3),（1,2,4),(1,2,7),(1,3,5),(1,3,4),(1,4,4) 那么在prefix中重复的（1,2），(1,3) 将会被压缩至保留一份。
索引压缩适合于那些键值重复率高的索引，这样才能够达到压缩键值，节省存储空间目的。索引压缩以后一个索引块可以存放更多的键值，这样当进行full index scan,full fast index scan 的时候IO性能会更好，但是CPU的负载会增加，至于总体的性能就要看IO性能的提高和CPU负载增加那个是主要方面了。
SQL> create table o1 as select * from dba_objects;
SQL> create table o2 as select * from dba_objects;
SQL> create table o3 as select * from dba_objects;
SQL> create index o1_i on o1(object_id) compress 1; (1为压缩一列)
SQL> create index o2_i on o2(object_id);
SQL> select index_name,compression,leaf_blocks from user_indexes
2 where index_name in ('O1_I','O2_I');
INDEX_NAME COMPRESS LEAF_BLOCKS
------------------------------ -------- -----------
O1_I ENABLED 152
O2_I DISABLED 110
结果:object_id都是唯一的 压缩以后索引反而占用了更大的空间，还不如不压缩。
----------------------------------------------
监控索引
1.
SQL> create index idx002 on tab_big(object_name);
SQL> create index idx003 on tab_big(created);
2.启动监控功能
SQL> select 'alter index sys.'||index_name||' monitoring usage;' from user_indexes where table_name='FF';
(用sql语句 生成监控sql语句)
3.开始监控
SQL> alter index sys.IDX001 monitoring usage;
SQL> alter index sys.IDX002 monitoring usage;
SQL> alter index sys.IDX003 monitoring usage;
4.查看监控信息表
SQL> select * from v$object_usage;
MON(监控状态) USE(有没有被使用过)
yes no
5.
SQL> select count(*) from tab_big where object_id=30001;
6.重新查看
SQL> select * from v$object_usage;
(对应的监控 USE 状态为也是)
取消监控
SQL> alter index sys.IDX001 nomonitoring usage;
----------------
删除索引
alter index idx001 unusable;
(unusable索引失效 增删改时索引就不更新了)
(用在数据仓库或大数据更新 ,先让索引失效,然后重新组织表,效率会高很多)
查看索引的大小
SQL> select SEGMENT_NAME,BYTES from user_segments t where t.segment_type='INDEX';
======================================
监控索引使用
SQL> create table tt as select * from dba_objects;
SQL> create index itt on tt(object_id);
SQL> select * from tt where object_id=22;
通过library cache数据
SQL> SELECT OBJECT_NAME
FROM V$SQL_PLAN A, V$SQLAREA B
WHERE A.SQL_ID = B.SQL_ID
AND A.OBJECT_TYPE = 'INDEX'
AND OBJECT_OWNER IN ('SYS', 'SCOTT');
---------------------
分区索引(分区表下使用)
删除分区 全局就实效了,所以一般用本得分区索引
1.先创建hash分区表(表名为test6)
2.插入些数据
3.创建本地分区表
SQL> create index idx_par on test6(ID) local;
(local 为本地有几个分区 就绑定几个索引)
4.查看分区索引
SQL> select * from user_ind_partitions;
---------------------------------
create tablespace ts01 logging datafile '/oracle/app/oradata/TEST/ts01.dbf' size 100m;
create tablespace ts02 logging datafile '/oracle/app/oradata/TEST/ts02.dbf' size 100m;
create tablespace ts03 logging datafile '/oracle/app/oradata/TEST/ts03.dbf' size 100m;
create tablespace ts04 logging datafile '/oracle/app/oradata/TEST/ts04.dbf' size 100m;
创建范围分区表
SQL> create table test123 partition by range(object_id)
(
partition p1 values less than (10000) tablespace ts01,
partition p2 values less than (20000) tablespace ts02,
partition p3 values less than (50000) tablespace ts03,
partition p4 values less than (maxvalue) tablespace ts04)
as select * from dba_objects;
创建全局分区表索引
SQL> create index idx123 on test123(object_id)
global partition by range(object_id)
(
partition idx_1 values less than(10000) tablespace ts01,
partition idx_2 values less than(25000) tablespace ts02,
partition idx_3 values less than(50000) tablespace ts03,
partition idx_4 values less than(maxvalue) tablespace ts04);
SQL> set autotrace traceonly;
SQL> select * from test123 where object_id=3001;
SQL> drop index idx123;
------------------------------------------------------
创建本地分区表索引
SQL> create index idx123 on test123(object_id) local;
select index_name,PARTITIONED from dba_indexes where table_name='TEST123';
(显示类型为分区)
SQL> desc user_part_indexes
本文转自陈继松 51CTO博客，原文链接：http://blog.51cto.com/chenjisong/1737389，如需转载请自行联系原作者
