表的索引
create table weisi_t as select * from dba_objects;
set autotrace on
set timing on
set linesize 200
select * from weisi_t where object_id=10;
create index index_weisi on weisi_t(object_id);
select * from weisi_t where object_id=10;
update weisi_t set object_id=10 where 1=1;
1：索引的分类
1）btree(balance tree)索引
索引三大特点：高度低、存储列值、结构有序
2）位图索引
3）函数索引
2：详解BTREE索引
2.1）BTREE索引的结构和原理
原理：索引入口头部、列长度、列值、ROWID、
ROWID:数据详细地址，通过ROWID快速定位某行具体的数据位置，64（A-Z,a-z,0-9,+,/),A=0，a=27,0=53,+=62,/=63。
六位 data object_id
不等于object_id
select object_id,data_object_id from dba_objects;
三位 相对文件号
select file#,Rfile#,ts#,name from v$datafile
六位 数据块号
范围：数据文件
三位 行号
rowid转化为文件号和块号
select
dbms_rowid.rowid_object('AAASUVAABAAAU8LAAl') object,
dbms_rowid.rowid_relative_fno('AAASUVAABAAAU8LAAl') file_num,
dbms_rowid.rowid_block_number('AAASUVAABAAAU8LAAl') block_num,
dbms_rowid.rowid_row_number('AAASUVAABAAAU8LAAl') row_number
from dual;
从ROWID推出各种的ORACLE数据限制
ROWID：以64位编码显示，但是以二进制的方式存储
rowid用10个字节存储，即80bit
32bit data_object_id 4G个对象
10bit 相对文件号 一个表空间最多只有1023个文件，不是1024，因为没有O文件号
22bit 块号 一个数据文件最大4M个BLOCK块 及32G
16bit 行号 64K行数据
alter system dump datafile 1 block 85771;
select file#,Rfile#,ts#,name from v$datafile
select spid from v$process where addr in (select paddr from v$session where
sid=(select sid from v$mystat where rownum=1));
show parameter dump
select object_name,dump(object_name,1018) from weisi_t where object_id=222;
select rowid,id,code,name,dump(name,1018) from characterset_test;
AAASDEAABAAAVUxAAB
select
dbms_rowid.rowid_object('AAASDEAABAAAVUxAAB') object,
dbms_rowid.rowid_relative_fno('AAASDEAABAAAVUxAAB') file_num,
dbms_rowid.rowid_block_number('AAASDEAABAAAVUxAAB') block_num ,
dbms_rowid.rowid_row_number('AAASDEAABAAAVUxAAB') row_number
from dual;
alter system dump datafile 1 block 87345;
select spid from v$process where addr in (select paddr from v$session where
sid=(select sid from v$mystat where rownum=1));
select object_type from dba_objects group by object_type order by 1;
select * from dba_index
2.2）索引结构
索引是物理结构还是逻辑结构？
2.3）索引的三大特性
1：索引高度低
2.3.1.1)构建大表和普通表
create table normal_t as select * from dba_objects;
create table normal_t_100 as select * from dba_objects where rownum< 100;
create table big_t as select * from dba_objects;
insert into big_t select * from big_t;
insert into big_t select * from big_t;
insert into big_t select * from big_t;
insert into big_t select * from big_t;
insert into big_t select * from big_t;
insert into big_t select * from big_t;
insert into big_t select * from big_t;
select count(1) from big_t;
2.3.1.2)创建索引
create index big_t_index on big_t(object_id);
create index normal_index on normal_t(object_id);
create index normal_t_100_index on normal_t_100(object_id);
select segment_name,sum(bytes)/1024/1024 M from user_segments
where segment_name in ('BIG_T_INDEX','NORMAL_INDEX','NORMAL_T_100_INDEX')group by segment_name;
2.3.1.3) 查看索引高度
col index_name format a40
set linesize 150
select
index_name
,blevel
,leaf_blocks
,num_rows
,distinct_keys
from user_ind_statistics
where table_name in ('BIG_T','NORMAL_T','NORMAL_T_100');
2.3.1.4) 索引是性能提升利器
cat $ORACLE_HOME/sqlplus/admin/plustrce.sql
sqlplus / as sysdba
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
grant plustrace to weisi;
set autotrace on
set linesize 200
set timing on
select * from big_t where object_id=111;
create index big_t_index on big_t(object_id);
select * from big_t where object_id=111;
select * from normal_t where object_id=111;
select * from normal_t_100 where object_id=111;
2.3.1.5) 分区索引(误区)
分区索引：全局索引和局部索引
分区条件和索引不同字段
create table part_t
partition by range(object_id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (30000),
partition p4 values less than (40000),
partition p5 values less than (50000),
partition p6 values less than (60000),
partition p7 values less than (70000),
partition p_max values less than (maxvalue)
)
as select * from dba_objects;
create index part_t_local on part_t(data_object_id) local;
create table part_t1
partition by range(object_id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (30000),
partition p4 values less than (40000),
partition p5 values less than (50000),
partition p6 values less than (60000),
partition p7 values less than (70000),
partition p_max values less than (maxvalue)
)
as select * from dba_objects;
create index part_t1_index on part_t1(data_object_id);
col segment_name format a25
col partition_name format a25
col segment_type format a25
set linesize 200
select segment_name,partition_name,segment_type
from user_segments
where segment_name in ('PART_T','PART_T1','PART_T_LOCAL','PART_T1_INDEX');
set autotrace traceonly
set linesize 200
select * from part_t where data_object_id=222 ;
select * from part_t1 where data_object_id=222;
select * from part_t where data_object_id=222 and object_id=10;
select * from part_t1 where data_object_id=222 and object_id=10;
create index normal_t_index on normal_t(data_object_id);
select * from normal_t where data_object_id=222;
--查看索引高度
col index_name format a40
set linesize 150
select
index_name
,blevel
,leaf_blocks
,num_rows
,distinct_keys
from user_ind_statistics
where table_name in ('PART_T','PART_T1','NORMAL_T')
order by index_name;
修改意见：加上分区条件
select * from part_t where data_object_id=222 and object_id=222;
2：索引存储列值
drop index normal_t_index;
drop table normal_t;
create table normal_t as select * from dba_objects;
1.count(*)优化
set autotrace traceonly
set linesize 200
select count(*) from normal_t;
create index normal_t_index on normal_t(object_id);
select count(*) from normal_t;
select count(*) from normal_t where object_id is not null;
drop table normal_t;
create table normal_t as select * from dba_objects where 1=2;
alter table normal_t modify object_id not null;
insert into normal_t select * from dba_objects;
select count(*) from normal_t;
任何情况下，使用索引一定比全表扫描块吗？
select * from normal_t;
select /*+ index(NORMAL_T_INDEX) */ * from NORMAL_T where object is not null;
2.avg问题
drop index normal_t_index;
drop table normal_t;
create table normal_t as select * from dba_objects;
set autotrace traceonly
set linesize 200
select avg(object_id) from normal_t;
create index normal_t_index on normal_t(object_id);
select avg(object_id) from normal_t;
3：索引本身是有序
drop index normal_t_index;
drop table normal_t;
create table normal_t as select * from dba_objects;
3.1）min/max优化
set autotrace on
set linesize 200
create index normal_t_index on normal_t(object_id);
select min(object_id) from normal_t;
select max(object_id) from normal_t;
为什么是两次一致性读？
select count(*) from normal_t;
如果NORMAL_T表，记录增加100倍，MIN,MAX的逻辑读是否会大量增加？
select
index_name
,blevel
,leaf_blocks
,num_rows
,distinct_keys
from user_ind_statistics
where table_name='NORMAL_T';
3.2）min/max性能陷阱：INDEX FAST FULL SCAN，INDEX FULL SCAN (MIN/MAX)
select min(object_id),max(object_id) from normal_t;--是否能用到索引
select min(object_id),max(object_id) from normal_t where object_id is not null;
select min(object_id),max(object_id) from normal_t是否等价于
select min(object_id) from normal_t;
select max(object_id) from normal_t;
select min,max from (select min(object_id) min from normal_t) a,(select max(object_id) max from normal_t) b;jianli
3.3）order by 优化
drop index normal_t_index;
drop table normal_t;
create table normal_t as select * from dba_objects;
set autotrace traceonly
set linesize 200
select * from normal_t where object_id<70000;
select * from normal_t where object_id<70000 order by object_id;
1：无ORDER BY 语句没有排序，ORDER BY 语句有排序，1 sorts (memory)
2：无ORDER BY 的语句COST 290，完成时间4秒，ORDER BY 语句3250 ，完成时间40秒
3：无ORDER BY 的一致读5555,ORDER BY 一致读1038 ，有排序的比没排序的一致读少
一致性读的多运行时间少，一致性读的少，运行时间长，SQL运行长短看COST值，不看一致性读
COST值越小性能越高，ORACLE执行计划选择是由COST值决定的。
4:排序需要消耗大量的性能
优化方法：
create index normal_t_index on normal_t(object_id);
select * from normal_t where object_id<70000 order by object_id;
select * from big_t where object_id<70000 order by object_id;
4 索引回表优化
4.1) 索引回表读 TABLE ACCESS BY INDEX ROWID
drop index normal_t_index;
drop table normal_t;
create table normal_t as select * from dba_objects;
create index normal_t_index on normal_t(object_id);
set autotrace on
set linesize 200
select * from normal_t where object_id< 100;
TABLE ACCESS BY INDEX ROWID
select object_id from normal_t where object_id<100;
无TABLE ACCESS BY INDEX ROWID
解决方法：1：改变SQL写法，只取需要的数据
2：建立联合索引，联合索引尽量不超过三个字段
select object_name,object_id from normal_t where object_id<100;
create index normal_t_index_2 on normal_t(object_name,object_id);
select object_name,object_id from normal_t where object_id<100;
create index normal_t_index_3 on normal_t(object_id,object_name);
5 联合索引
set autotrace on
set linesize 200
create table normal_t as select * from dba_objects;
create index normal_t_index on normal_t(object_id);
select object_name from normal_t where object_id=100;
create index normal_t_index_2 on normal_t(object_name,object_id);
select object_name from normal_t where object_id=100;
create index normal_t_index_2 on normal_t(object_id,object_name);
select object_name from normal_t where object_id=100;
6 索引监控
select * from v$object_usage;
select object_name from big_t where object_id=100;
select * from v$object_usage;
alter index big_t_index monitoring usage;
alter index big_t_index nomonitoring usage;
select object_name from big_t where object_id=100;
select * from v$object_usage;
6 建立索引引发的问题
1) 排序
2）锁
create index big_t_index on big_t(object_id);
select sid from v$mystat where rownum=1;
select * from v$lock where sid=23
update big_t set object_id=100 where object_id=10;
select sid,type,id1,id2,
decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,
decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')
request_mode,block
from v$lock
6 索引的危害影响
drop index normal_t_index;
drop table normal_t;
create table normal_t as select * from dba_objects where 1=2;
set timing on
insert into normal_t select * from dba_objects ;
create index normal_t_index on normal_t(object_id);
insert into normal_t select * from dba_objects ;
先删除索引，再插入，最后在重建索引
DML语句对索引的影响
1) insert into
2) delete
3) update
3：位图索引
set autotrace on
alter table big_t modify object_id not null;
select count(1) from big_t;
create bitmap index index_batmap_t_status on big_t(object_type);
select * from dba_indexes where table_name='BIG_T';
select count(1) from big_t;
select object_type,count(1) from big_t group by object_type;
select object_id,count(1) from big_t group by object_id;
插入
SESSION 1 插入
insert into big_t select * from dba_objects;
session 2 插入
insert into big_t select * from dba_objects;
位图索引试用场合
1:位图索引列大量重复
2:表极少更新
1）位图索引列重复情况少
create table normal_t as select * from dba_objects;
create bitmap index index_batmap_t_object_id on normal_t(object_id);
set autotrace on
select /* +full(normal_t)*/count(1) from normal_t;
select /* +index(index_batmap_t_object_id)*/count(1) from normal_t;
