Oracle SQL调优系列之索引知识学习笔记

举报
yd_273762914 发表于 2020/12/03 00:10:47 2020/12/03
【摘要】 文章目录 一、Oracle索引简介1.1 索引分类1.2 索引数据结构1.3 索引特性1.4 索引使用注意要点1.5、索引的缺点1.6、索引失效 二、索引分类介绍2.1、位图索引1.2、函数索引1.3、反向索引1.4、全文索引 一、Oracle索引简介 在看《收获，不止SQL优化》一书，并根据书中例子进行实践，整理成笔记 1.1 索引分类 ...

文章目录

一、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
    •

文章来源: smilenicky.blog.csdn.net，作者：smileNicky，版权归原作者所有，如需转载，请联系作者。

原文链接：smilenicky.blog.csdn.net/article/details/90706124

【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
Oracle SQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入