《收获,不止SQL优化》读书笔记 (9)

范围查询时,组合索引最佳顺序一般是将等值查询的列置前

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';

Oracle不能同时在索引根的两段寻找最大值和最小值

set autotrace on select max(object_id) , min(object_id) from t;

笛卡尔乘积写法:

set autotrace on select max, min from (select max(object_id) max from t ) a , (select min(object_id) min from t ) b;

索引最新的数据块一般是在最右边

索引的缺点

热快竞争:索引最新的数据块一般在最右边,而访问也一般是访问比较新的数据,所以容易造成热快竞争

更新新增问题:索引本身是有序的,所以查询时候很快,但是更新时候就麻烦了,新增更新索引都需要保证排序

索引失效

索引失效分为逻辑失效和物理失效

逻辑失效
逻辑失效是因为一些sql语法导致索引失效,比如加了一些函数,而索引列不是函数索引

物理失效
物理失效是真的失效,比如被设置unusable属性,分区表的不规范操作也会导致索引失效等等情况

alter index index_name unusable;

索引分类:BTree索引、位图索引、函数索引、反向索引、全文索引

位图索引:位图索引储存的就是比特值

函数索引:就是将一个函数计算的结果存储在行的列中

自定义函数的情况,要加上deterministic关键字

自定义一个函数:

create or replace function f_addusl(i int) return int is begin return(i + 1); end;

建函数索引

create index idx_ljb_test on t(f_addusl(id));

出现:ORA-30553:函数不能确定

方法:加上deterministic关键字

create or replace function f_addusl(i int) return int deterministic is begin return(i + 1); end;

在自定义函数代码更新时,对应的函数索引也要重建,否则不能用到原来的函数索引

反向索引:反向索引其实也是BTree索引的一种特例,不过在列中字节会反转的(反向索引是为了避免热快竞争,比如索引列中存储的列值是递增的,比如250101,250102,按照BTree索引的特性,一般是按照顺序存储在索引右边的,所以容易形成热快竞争,而反向索引可以避免这种情况,因为反向索引是这样存储的,比如101052,201052,这样列值就距离很远了,避免了热快竞争)

反向索引不能用到范围查询

全文索引:所谓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字符集

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 '%高兴%'; //设置词法分析器 BEGIN ctx_ddl.create_preference ('lexer1', 'chinese_vgram_lexer'); END; //授权 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 ljb/ljb; //删除全文索引 drop index idx_content; //查看数据文件信息 select * from v$datafile; //建立全文索引 CREATE INDEX idx_content ON t(object_name) indextype is ctxsys.context parameters('lexer lexer1'); //执行同步命令 exec ctx_ddl.sync_index('idx_content','20M'); Oracle表连接

两个表之间的表连接方法有排序合并连接、嵌套循环连接、哈希连接、笛卡尔连接

排序合并连接(merge sort join)

嵌套循环连接(Nested loop join)

哈希连接(Hash join)

笛卡尔连接(Cross join)

【表连接方法特性区别】

(1)表访问次数区别

使用Hint语法强制使用nl

select /*+ leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id = t2.id and t1.id in (17,19);

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/wpxgdz.html