表的索引

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;

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;

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 215,539评论 6赞 497
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 91,911评论 3赞 391
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 161,337评论 0赞 351
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 57,723评论 1赞 290
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,795评论 6赞 388
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,762评论 1赞 294
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,742评论 3赞 416
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,508评论 0赞 271
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,954评论 1赞 308
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,247评论 2赞 331
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,404评论 1赞 345
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,104评论 5赞 340
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,736评论 3赞 324
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,352评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,557评论 1赞 268
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,371评论 2赞 368
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,292评论 2赞 352

表的索引

推荐阅读更多精彩内容