热门标签
热门文章
- 1Set集合(Java) 及底层原理_set集合的底层原理
- 2CorelDRAW2024官方中文版重磅发布更新_coreldraw 2024启动界面
- 3Spring Boot_spring boot springtoolsuite4 vm option
- 4事实表和维度表是怎么造数据_数据仓库建模方法论
- 5【数据结构】带你深入栈和队列，轻松实现各种接口功能
- 6Linux下DISPLAY环境变量的作用_linux display的用法
- 7揭秘 .npmrc 文件：NPM 包管理器的秘密武器
- 8【面经笔记】哈希表_哈希表有14个桶新元素31的位置
- 9MySQL从入门到入魔，总结我的学习历程，给有需要的人看！
- 10Win2008(IIS+PHP7.2.33)无法显示详细错误(始终显示500错误)或提示The FastCGI process exited unexpectedly_php7.2 phpoffice错误500
当前位置: article > 正文
Oracle SQL调优系列之索引知识学习笔记_oracle 叶子,茎,根
作者：繁依Fanyi0 | 2024-08-15 18:51:05
赞
踩
oracle 叶子,茎,根
一、Oracle索引简介
在看《收获，不止SQL优化》一书，并根据书中例子进行实践，整理成笔记
1.1 索引分类
Oracle索引分为BTree索引、位图索引、反向索引、函数索引、全文索引等等。
1.2 索引数据结构
Oracle索引中最常用的是BTree索引，所以就以BTree索引为例，讲一下BTree索引，BTree索引数据结构是一种二叉树的结构，索引由根块(Root)、茎块(Branch)、叶子块(Leaf)组成，其中叶子块主要存储索引列具体值(Key Column Value)以及能定位到数据块具体位置的Rowid，茎块和根块主要保存对应下级对应索引
1.3 索引特性
索引特性：
- 索引本身是有序的
- 索引本身能存储列值
1.4 索引使用注意要点
- (1)、仅等值无范围查询时，组合的顺序不影晌性能
drop table t purge;
create table t as select * from dba objects;
update t set object_id=rownum ;
commit;
create index idx_id_type on t(object_id, object_type) ;
create index idx_type_id on t(object_type , object_id) ;
set autotrace off;
alter session set statistics_level=all ;
select /*+index(t idx_id_type)*/ * from t where object_id=20 and object_type='TABLE';
select * from table(dbms_xplan.display cursor(null , null , 'allstats last'));
select /*+index(t,idx_type id)*/ * from t where object_id=20 and object_type= 'TABLE';
select * from table(dbms_xplan.display cursor(null , null , 'allstats last'));
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- (2)、范围查询时，组合索引最佳顺序一般是将等值查询的列置前
select /*+index (t, idx_id_type)*/ * from t where object_id>=20 and object_id<2000 and
object_type='TABLE';
select /*+index (t , idx_type_id) */ * from t where object_id>=20 and object_id<2000
and object type='TABLE';
- 1
- 2
- 3
- 4
- 5
- (3)、Oracle不能同时在索引根的两段寻找最大值和最小值
set autotrace on
select max(object_id) , min(object_id) from t;
- 1
- 2
笛卡尔乘积写法：
set autotrace on
select max, min
from (select max(object_id) max from t ) a ,
(select min(object_id) min from t ) b;
- 1
- 2
- 3
- 4
- 5
- 6
- (4)、索引最新的数据块一般是在最右边
1.5、索引的缺点
- 热快竞争：索引最新的数据块一般在最右边，而访问也一般是访问比较新的数据，所以容易造成热快竞争
- 更新新增问题：索引本身是有序的，所以查询时候很快，但是更新时候就麻烦了，新增更新索引都需要保证排序
1.6、索引失效
索引失效分为逻辑失效和物理失效
- 逻辑失效
逻辑失效是因为一些sql语法导致索引失效，比如加了一些函数，而索引列不是函数索引 - 物理失效
物理失效是真的失效,比如被设置unusable属性,分区表的不规范操作也会导致索引失效等等情况
alter index index_name unusable;
- 1
二、索引分类介绍
索引分类：BTree索引、位图索引、函数索引、反向索引、全文索引
2.1、位图索引
位图索引：位图索引储存的就是比特值
环境准备，位图索引性质适用于count时，效率最高
drop table t purge;
create table t as select * from dba_objects;
update t set object_id = rownum;
commit;
- 1
- 2
- 3
- 4
不用索引的情况：
SQL> set autotrace on SQL> select count(*) from t; COUNT(*) ---------- 72016 执行计划 ---------------------------------------------------------- Plan hash value: 2966233522 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 288 (1)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T | 86565 | 288 (1)| 00:00:04 | ------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) 统计信息 ---------------------------------------------------------- 4 recursive calls 0 db block gets 1111 consistent gets 0 physical reads 0 redo size 432 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
创建位图索引：
create bitmap index idx_bitm_t_status on t(status);
- 1
- 2
再次查询，走位图索引查询：
SQL> set autotrace on SQL> select count(*) from t; COUNT(*) ---------- 72016 执行计划 ---------------------------------------------------------- Plan hash value: 4272013625 -------------------------------------------------------------------------------- ----------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------------------- ----------- | 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | BITMAP CONVERSION COUNT | | 86565 | 5 (0)| 00:00:01 | | 3 | BITMAP INDEX FAST FULL SCAN| IDX_BITM_T_STATUS | | | | -------------------------------------------------------------------------------- ----------- Note ----- - dynamic sampling used for this statement (level=2) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 432 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
注意要点：
位图索引更新列容易造成死锁，所以查询比较多列才适合建位图索引，更新比较多的列就尽量不要建索引
1.2、函数索引
函数索引：就是将一个函数计算的结果存储在行的列中
环境准备：
drop table t purge;
create table t (id int, status varchar2(2));
insert into t select 1,'N' from dual;
insert into t select rownum ,'Y' from dual connect by rownum <1000;
commit;
- 1
- 2
- 3
- 4
- 5
不走索引的查询：
SQL> set autotrace on SQL> select * from t where (case when status='N' then 'No' end)='No'; ID STAT ---------- ---- 1 N 执行计划 ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 1 | 16 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(CASE "STATUS" WHEN 'N' THEN 'No' END ='No') Note ----- - dynamic sampling used for this statement (level=2) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 486 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
创建函数索引：
create index idx_status on t (case when status ='N' then 'No' end);
- 1
- 2
走函数索引的查询：
SQL> select * from t where (case when status='N' then 'No' end)='No'; ID STAT ---------- ---- 1 N 执行计划 ---------------------------------------------------------- Plan hash value: 3908194542 -------------------------------------------------------------------------------- ---------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- ---------- | 0 | SELECT STATEMENT | | 10 | 200 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 10 | 200 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_STATUS | 4 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- ---------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(CASE "STATUS" WHEN 'N' THEN 'No' END ='No') Note ----- - dynamic sampling used for this statement (level=2) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 486 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
注意要点：
自定义函数时要加上deterministic 关键字，不然不能建立函数索引
建立一个自定义函数：
create or replace function f_addusl(i int) return int is
begin
return(i + 1);
end;
- 1
- 2
- 3
- 4
尝试建立函数索引：
create index idx_ljb_test on t(f_addusl(id));
- 1
- 2
提示：ORA-30553:函数不能确定
用deterministic 关键字，就可以建立函数索引
create or replace function f_addusl(i int) return int deterministic is
begin
return(i + 1);
end;
- 1
- 2
- 3
- 4
在自定义函数代码更新时，对应的函数索引也要重建，否则不能用到原来的函数索引
1.3、反向索引
反向索引：反向索引其实也是BTree索引的一种特例，不过在列中字节会反转的(反向索引是为了避免热快竞争，比如索引列中存储的列值是递增的，比如250101,250102，按照BTree索引的特性，一般是按照顺序存储在索引右边的，所以容易形成热快竞争，而反向索引可以避免这种情况，因为反向索引是这样存储的，比如101052,201052，这样列值就距离很远了，避免了热快竞争)
反向索引不能用到范围查询
SQL> set autotrace on SQL> select * from t where created=sysdate; 未选定行 执行计划 ---------------------------------------------------------- Plan hash value: 913247507 -------------------------------------------------------------------------------- --------------- | Id | Operation | Name | Rows | Bytes | Cost (%C PU)| Time | -------------------------------------------------------------------------------- --------------- | 0 | SELECT STATEMENT | | 12 | 2484 | 286 (0)| 00:00:04 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 12 | 2484 | 286 (0)| 00:00:04 | |* 2 | INDEX RANGE SCAN | IDX_REV_CREATED | 346 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- --------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CREATED"=SYSDATE@!) Note ----- - dynamic sampling used for this statement (level=2) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 1191 bytes sent via SQL*Net to client 408 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
范围查询，发现不走反向索引查询
SQL> select * from t where created>= sysdate-10 and created <= sysdate-1; OWNER ------------------------------------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID ------------------------------------------------------------ ---------- DATA_OBJECT_ID OBJECT_TYPE CREATED -------------- -------------------------------------- -------------- LAST_DDL_TIME TIMESTAMP STATUS TE GE SE -------------- -------------------------------------- -------------- -- -- -- NAMESPACE EDITION_NAME ---------- ------------------------------------------------------------ SYS ICOL$ 20 2 TABLE 15-6月 -19 02-4月 -10 2010-04-02:13:18:38 VALID N N N 1 SYS I_USER1 46 46 INDEX 14-6月 -19 OWNER ------------------------------------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID ------------------------------------------------------------ ---------- DATA_OBJECT_ID OBJECT_TYPE CREATED -------------- -------------------------------------- -------------- LAST_DDL_TIME TIMESTAMP STATUS TE GE SE -------------- -------------------------------------- -------------- -- -- -- NAMESPACE EDITION_NAME ---------- ------------------------------------------------------------ 02-4月 -10 2010-04-02:13:18:38 VALID N N N 4 SYS CON$ 28 28 TABLE 13-6月 -19 02-4月 -10 2010-04-02:13:18:38 VALID N N N 1 SYS OWNER ------------------------------------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID ------------------------------------------------------------ ---------- DATA_OBJECT_ID OBJECT_TYPE CREATED -------------- -------------------------------------- -------------- LAST_DDL_TIME TIMESTAMP STATUS TE GE SE -------------- -------------------------------------- -------------- -- -- -- NAMESPACE EDITION_NAME ---------- ------------------------------------------------------------ UNDO$ 15 15 TABLE 12-6月 -19 02-4月 -10 2010-04-02:13:18:38 VALID N N N 1 SYS C_COBJ# 29 29 CLUSTER 11-6月 -19 02-4月 -10 2010-04-02:13:18:38 VALID N N N OWNER ------------------------------------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID ------------------------------------------------------------ ---------- DATA_OBJECT_ID OBJECT_TYPE CREATED -------------- -------------------------------------- -------------- LAST_DDL_TIME TIMESTAMP STATUS TE GE SE -------------- -------------------------------------- -------------- -- -- -- NAMESPACE EDITION_NAME ---------- ------------------------------------------------------------ 5 SYS I_OBJ# 3 3 INDEX 10-6月 -19 02-4月 -10 2010-04-02:13:18:38 VALID N N N 4 SYS PROXY_ROLE_DATA$ OWNER ------------------------------------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID ------------------------------------------------------------ ---------- DATA_OBJECT_ID OBJECT_TYPE CREATED -------------- -------------------------------------- -------------- LAST_DDL_TIME TIMESTAMP STATUS TE GE SE -------------- -------------------------------------- -------------- -- -- -- NAMESPACE EDITION_NAME ---------- ------------------------------------------------------------ 25 25 TABLE 09-6月 -19 02-4月 -10 2010-04-02:13:18:38 VALID N N N 1 SYS I_IND1 41 41 INDEX 08-6月 -19 02-4月 -10 2010-04-02:13:18:38 VALID N N N 4 OWNER ------------------------------------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID ------------------------------------------------------------ ---------- DATA_OBJECT_ID OBJECT_TYPE CREATED -------------- -------------------------------------- -------------- LAST_DDL_TIME TIMESTAMP STATUS TE GE SE -------------- -------------------------------------- -------------- -- -- -- NAMESPACE EDITION_NAME ---------- ------------------------------------------------------------ SYS I_CDEF2 54 54 INDEX 07-6月 -19 02-4月 -10 2010-04-02:13:18:38 VALID N N N 4 已选择9行。 执行计划 ---------------------------------------------------------- Plan hash value: 1322348184 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 12 | 2484 | 292 (2)| 00:00:04 | |* 1 | FILTER | | | | | | |* 2 | TABLE ACCESS FULL| T | 12 | 2484 | 292 (2)| 00:00:04 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(SYSDATE@!-10<=SYSDATE@!-1) 2 - filter("CREATED">=SYSDATE@!-10 AND "CREATED"<=SYSDATE@!-1) Note ----- - dynamic sampling used for this statement (level=2) 统计信息 ---------------------------------------------------------- 5 recursive calls 0 db block gets 1112 consistent gets 0 physical reads 0 redo size 1770 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 9 rows processed SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
1.4、全文索引
全文索引：所谓Oracle全文索引是通过Oracle词法分析器(lexer)将所有的表意单元term存储dr$开头的表里并存储term出现的位置、次数、hash值等等信息，Oracle提供了basic_lexer(针对英语)、chinese_vgram_lexer(汉语分析器)、chinese_lexer(新的汉语分析器)。
- basic_lexer：是一种适用于英文的分析器，根据空格或者标点符号将词元分离，不管对于中文来说是没有空格的，所以这种分析器不适合中文
- chinese_vgram_lexer：这是一种原先专门的中文分析器，支持所有的汉字字符集，比如zhs16gbk单点。这种分析器，分析过程是按字为单元进行分析的，举个例子，“索引本身是有序的”，按照这种分析器，会分成词元“索”、“索引”、“引本”、“本身”、“身是”、“是有”、“有序”、“序的”、“的”这些词元，然后你发现像“序的”这些词在中文中基本是不成立的，不过这种Oracle分析器本身就不认识中文，所以只能全部分析，很明显效率是不好的
- chinese_lexer：这是一种新的中文分析器，前面提到chinese_vgram_lexer这种分析器虽然支持所有的中文字符集，但是效率不高，所以chinese_lexer是对其的改进版本，这种分析器认识很多中文词汇，能够比较快查询，提高效率，不过这种分析器只能支持utf-8字符集
Oracle的全文索引具体可以采用通配符查找、模糊匹配、相关分类、近似查找、条件加权和词意扩充等方法
环境准备
drop table t purge;
create table t as select * from dba_objects where object_name is not null;
update t set object_name ='高兴' where rownum<=2;
commit;
select * from t where object_name like '%高兴%';
- 1
- 2
- 3
- 4
- 5
设置词法分析器
//设置词法分析器
BEGIN
ctx_ddl.create_preference ('lexer1', 'chinese_vgram_lexer');
END;
- 1
- 2
- 3
- 4
解锁ctxsys用户，同时给你的测试账号(我这里用scott)授权使用ctx_ddl
//解锁ctxsys用户同时授权
grant ctxapp to scott;
alter user ctxsys account unlock;
alter user ctxsys identified by ctxsys;
connect ctxsys/ctxsys;
grant execute on ctx_ddl to scott;
connect scott/11;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
建立全文索引
//删除全文索引
drop index idx_content;
//查看数据文件信息
select * from v$datafile;
//建立全文索引
CREATE INDEX idx_content ON t(object_name) indextype is ctxsys.context parameters('lexer lexer1');
- 1
- 2
- 3
- 4
- 5
- 6
注意要点：更新数据时候记得执行全文索引同步命令，否则将看不到更新数据
exec ctx_ddl.sync_index('idx_content','20M');
- 1
- 2
- 3
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/繁依Fanyi0/article/detail/984799
推荐阅读
相关标签
