专栏首页Nicky's blogOracle索引知识学习笔记

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')); 
  • (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';
  • (3)、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; 
  • (4)、索引最新的数据块一般是在最右边

1.5、索引的缺点

  • 热快竞争:索引最新的数据块一般在最右边,而访问也一般是访问比较新的数据,所以容易造成热快竞争
  • 更新新增问题:索引本身是有序的,所以查询时候很快,但是更新时候就麻烦了,新增更新索引都需要保证排序

1.6、索引失效

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

  • 逻辑失效 逻辑失效是因为一些sql语法导致索引失效,比如加了一些函数,而索引列不是函数索引
  • 物理失效 物理失效是真的失效,比如被设置unusable属性,分区表的不规范操作也会导致索引失效等等情况
alter index index_name unusable;

二、索引分类介绍

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

2.1、位图索引

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

环境准备,位图索引性质适用于count时,效率最高

drop table t purge;
create table t as select * from dba_objects;
update t set object_id = rownum;
commit;

不用索引的情况:

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>

创建位图索引:

create bitmap index idx_bitm_t_status on t(status);

再次查询,走位图索引查询:

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、函数索引

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

环境准备:

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;

不走索引的查询:

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>

创建函数索引:

create index idx_status on t (case when status ='N' then 'No' end);

走函数索引的查询:

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>

注意要点:

自定义函数时要加上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;

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

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>

范围查询,发现不走反向索引查询

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.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 '%高兴%';

设置词法分析器

//设置词法分析器
BEGIN
  ctx_ddl.create_preference ('lexer1', 'chinese_vgram_lexer');
END;

解锁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;

建立全文索引

//删除全文索引
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');

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL索引知识学习笔记

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。

    SmileNicky
  • Oracle优化器基础知识

    本博客介绍一下属于oracle优化器范畴的一些基础知识,访问数据的方法,分为直接访问数据的方法和访问索引的方法两种,然后有了这些基础知识后,可以参考学习我的另外...

    SmileNicky
  • Oracle性能调优之虚拟索引用法简介

    版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u014427391/article/details/...

    SmileNicky
  • 【DB笔试面试549】在Oracle中,单列索引和复合索引分别是什么?

    按照索引列的个数,索引可以分为单列索引和复合索引。单列索引是基于单个列所建立的索引。复合索引(Composite Indexes),也称为连接索引、组合索引或多...

    小麦苗DBA宝典
  • 《T-SQL查询》读书笔记Part 3.索引的基本知识

    索引优化是查询优化中最重要的一部分,索引是一种用于排序和搜索的结构,在查找数据时索引可以减少对I/O的需要;当计划中的某些元素需要或是可以利用经过排序的数据时,...

    Edison Zhou
  • Java 后台开发面试题分享八

    count(*) 包括了所有的列,相当于行数,在统计结果的时候,不会忽略为 NULL 的值。

    RendaZhang
  • 一次 MySQL 索引面试,被面试官怼的体无完肤!

    之前有过一次面试,关于MySQL索引的原理及使用被面试官怼的体无完肤,立志要总结一番,然后一直没有时间(其实是懒……),准备好了吗?

    Java技术栈
  • 一文说清楚Mysql Innodb的B+树索引原理及其推理过程

    网上找了很多关于Innodb B+树索引原理的文章,但都不尽如意。基本都是列出了最后的结果,没有说清楚B+树的推理过程,让人看的云里雾里。本文会由浅入深的讲解B...

    诺浅
  • 优化页面访问速度(二) ——数据库优化

    数据库优化,主要包括数据表设计、索引、sql语句、表拆分、数据库服务器架构等方向的优化。

    用户1327360
  • mysql复合索引、普通索引总结

    对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支...

    居士

扫码关注云+社区

领取腾讯云代金券