全文索引

最新推荐文章于 2024-07-25 16:44:49 发布
最新推荐文章于 2024-07-25 16:44:49 发布
阅读量720 收藏
点赞数
分类专栏： oracle基础 索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wll_1017/article/details/12163791
版权 
create table t1(id int,name varchar2(10));
create index ind_t1 on t1(name);
insert into t1 values(1,'Tom');
insert into t1 values(1,'Tom Tom');
insert into t1 select empno,ename from emp;
commit;

create table t2  as select * From t1;
create index ind_t2 on t2(name) indextype is ctxsys.context;

BEGIN
         DBMS_STATS.GATHER_TABLE_STATS(ownname => 'scott',
         tabname => 't1',
         estimate_percent =>100,
          method_opt => 'for all columns size 1',
        degree => 8,
         cascade=>TRUE
         );
         END;

BEGIN
         DBMS_STATS.GATHER_TABLE_STATS(ownname => 'scott',
         tabname => 't2',
         estimate_percent =>100,
          method_opt => 'for all columns size 1',
        degree => 8,
         cascade=>TRUE
         );
         END;

SQL> select  * From t2 where contains(name,'Tom')>0;
	ID NAME
---------- ----------
	 1 Tom
	 2 Tom Tom
Execution Plan
----------------------------------------------------------
Plan hash value: 1615937155

--------------------------------------------------------------------------------------
| Id  | Operation		    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	     |	   2 |	  20 |	   2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |	   2 |	  20 |	   2   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX		    | IND_T2 |	     |	     |	   0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CTXSYS"."CONTAINS"("NAME",'Tom')>0)
Statistics
----------------------------------------------------------
	 11  recursive calls
	  0  db block gets
	 22  consistent gets
	  0  physical reads
	  0  redo size
	660  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  2  rows processed

SQL> select * from t1 where name like '%Tom%';
	ID NAME
---------- ----------
	 1 Tom
	 2 Tom Tom
Execution Plan
----------------------------------------------------------
Plan hash value: 3418867520
--------------------------------------------------------------------------------------
| Id  | Operation		    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	     |	   1 |	  10 |	   2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |	   1 |	  10 |	   2   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN	    | IND_T1 |	   1 |	     |	   1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("NAME" LIKE '%Tom%' AND "NAME" IS NOT NULL)
Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	  4  consistent gets
	  0  physical reads
	  0  redo size
	660  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  2  rows processed
insert into t1 select * From t1;
insert into t1 select * From t1;
insert into t1 select * From t1;
insert into t1 select * From t1;
insert into t1 select * From t1;


insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;

BEGIN
         DBMS_STATS.GATHER_TABLE_STATS(ownname => 'scott',
         tabname => 't1',
         estimate_percent =>100,
          method_opt => 'for all columns size 1',
        degree => 8,
         cascade=>TRUE
         );
         END;

BEGIN
         DBMS_STATS.GATHER_TABLE_STATS(ownname => 'scott',
         tabname => 't2',
         estimate_percent =>100,
          method_opt => 'for all columns size 1',
        degree => 8,
         cascade=>TRUE
         );
         END;
SQL> select * from t1 where name like '%Tom%';


	ID NAME
---------- ----------
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
	 1 Tom Tom
64 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |   339 |  3390 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1	 |   339 |  3390 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("NAME" LIKE '%Tom%' AND "NAME" IS NOT NULL)
Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	 57  consistent gets
	  0  physical reads
	  0  redo size
       2272  bytes sent via SQL*Net to client
	567  bytes received via SQL*Net from client
	  6  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 64  rows processed

SQL> select  * From t2 where contains(name,'Tom')>0;
	ID NAME
---------- ----------
	 1 Tom
	 1 Tom Tom

Execution Plan
----------------------------------------------------------
Plan hash value: 1615937155
--------------------------------------------------------------------------------------
| Id  | Operation		    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	     |	 848 |	8480 |	   9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |	 848 |	8480 |	   9   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX		    | IND_T2 |	     |	     |	   0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CTXSYS"."CONTAINS"("NAME",'Tom')>0)
Statistics
----------------------------------------------------------
	 11  recursive calls
	  0  db block gets
	 19  consistent gets
	  0  physical reads
	  0  redo size
	660  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  2  rows processed

SQL> alter index ind_t2 rebuild parameters('sync');
Index altered.

SQL> select  * From t2 where contains(name,'Tom')>0;
	ID NAME
---------- ----------
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom
	 1 Tom
	 1 Tom Tom


64 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1615937155
--------------------------------------------------------------------------------------
| Id  | Operation		    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	     |	  63 |	 630 |	   7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |	  63 |	 630 |	   7   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX		    | IND_T2 |	     |	     |	   2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CTXSYS"."CONTAINS"("NAME",'Tom')>0)
Statistics
----------------------------------------------------------
	 11  recursive calls
	  0  db block gets
	 48  consistent gets
	  0  physical reads
	  0  redo size
       2272  bytes sent via SQL*Net to client
	567  bytes received via SQL*Net from client
	  6  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 64  rows processed


从上面的测试结果可以看出，全文索引的效果不是很好，不管数量是多还是少，逻辑读都比普通索引的全表扫要高。
为什么会这样呢？ 因为全文索引比表要大。
查看全文索引的大小：
SQL> SET LONG 2000000000
SQL> SET pagesize 2000
SQL> SELECT ctx_report.index_size('IND_T2') FROM DUAL;

CTX_REPORT.INDEX_SIZE('IND_T2')
--------------------------------------------------------------------------------
===========================================================================
			INDEX SIZE FOR SCOTT.IND_T2
===========================================================================
TABLE:				     SCOTT.DR$IND_T2$I
TABLESPACE NAME:		     USERS
BLOCKS ALLOCATED:							 8
BLOCKS USED:								 8
BYTES ALLOCATED:					 65,536 (64.00 KB)
BYTES USED:						 65,536 (64.00 KB)

LOB SEGMENT:			     SCOTT.SYS_LOB0000080748C00006$$
TABLE NAME:			     SCOTT.DR$IND_T2$I
LOB COLUMN:			     TOKEN_INFO
TABLESPACE NAME:		     USERS
BLOCKS ALLOCATED:							 8
BLOCKS USED:								 3
BYTES ALLOCATED:					 65,536 (64.00 KB)
BYTES USED:						 24,576 (24.00 KB)

INDEX (NORMAL): 		     SCOTT.DR$IND_T2$X
TABLE NAME:			     SCOTT.DR$IND_T2$I
TABLESPACE NAME:		     USERS
BLOCKS ALLOCATED:							 8
BLOCKS USED:								 4
BYTES ALLOCATED:					 65,536 (64.00 KB)
BYTES USED:						 32,768 (32.00 KB)

INDEX (LOB):			     SCOTT.SYS_IL0000080748C00006$$
TABLE NAME:			     SCOTT.DR$IND_T2$I
TABLESPACE NAME:		     USERS
BLOCKS ALLOCATED:							 8
BLOCKS USED:								 4
BYTES ALLOCATED:					 65,536 (64.00 KB)
BYTES USED:						 32,768 (32.00 KB)

INDEX (IOT):			     SCOTT.SYS_IOT_TOP_80751
TABLE NAME:			     SCOTT.DR$IND_T2$K
TABLESPACE NAME:		     USERS
BLOCKS ALLOCATED:							24
BLOCKS USED:								24
BYTES ALLOCATED:				       196,608 (192.00 KB)
BYTES USED:					       196,608 (192.00 KB)

INDEX (IOT):			     SCOTT.SYS_IOT_TOP_80756
TABLE NAME:			     SCOTT.DR$IND_T2$N
TABLESPACE NAME:		     USERS
BLOCKS ALLOCATED:							 8
BLOCKS USED:								 4
BYTES ALLOCATED:					 65,536 (64.00 KB)
BYTES USED:						 32,768 (32.00 KB)

TABLE:				     SCOTT.DR$IND_T2$R
TABLESPACE NAME:		     USERS
BLOCKS ALLOCATED:							 8
BLOCKS USED:								 8
BYTES ALLOCATED:					 65,536 (64.00 KB)
BYTES USED:						 65,536 (64.00 KB)

LOB SEGMENT:			     SCOTT.SYS_LOB0000080753C00002$$
TABLE NAME:			     SCOTT.DR$IND_T2$R
LOB COLUMN:			     DATA
TABLESPACE NAME:		     USERS
BLOCKS ALLOCATED:							24
BLOCKS USED:								24
BYTES ALLOCATED:				       196,608 (192.00 KB)
BYTES USED:					       196,608 (192.00 KB)

INDEX (LOB):			     SCOTT.SYS_IL0000080753C00002$$
TABLE NAME:			     SCOTT.DR$IND_T2$R
TABLESPACE NAME:		     USERS
BLOCKS ALLOCATED:							 8
BLOCKS USED:								 4
BYTES ALLOCATED:					 65,536 (64.00 KB)
BYTES USED:						 32,768 (32.00 KB)

TOTALS FOR INDEX SCOTT.IND_T2
---------------------------------------------------------------------------
TOTAL BLOCKS ALLOCATED: 					       104
TOTAL BLOCKS USED:							83
TOTAL BYTES ALLOCATED:				       851,968 (832.00 KB)
TOTAL BYTES USED:				       679,936 (664.00 KB)

从上面可以看出，全文索引插入的数据都是在临时表中，需要手动同步到正式表中去alter index ind_t2 rebuild parameters('sync')，这样做的好处是，插入数据后，不会对索引进行锁定，不影正式表中对数据的操作，比较适合于数据仓库。


通过对表中继续增加数据后查看
SQL> select * from t1 where name like '%Tom%';
8192 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 | 21709 |   212K|   276   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1	 | 21709 |   212K|   276   (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("NAME" LIKE '%Tom%' AND "NAME" IS NOT NULL)
Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
       7410  consistent gets
	  0  physical reads
	  0  redo size
     216876  bytes sent via SQL*Net to client
       6529  bytes received via SQL*Net from client
	548  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
       8192  rows processed
SQL> select  * From t2 where contains(name,'Tom')>0;
8320 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1615937155
--------------------------------------------------------------------------------------
| Id  | Operation		    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	     |	4119 | 41190 |	1588   (0)| 00:00:20 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |	4119 | 41190 |	1588   (0)| 00:00:20 |
|*  2 |   DOMAIN INDEX		    | IND_T2 |	     |	     |	1377   (0)| 00:00:17 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CTXSYS"."CONTAINS"("NAME",'Tom')>0)

Statistics
----------------------------------------------------------
	 11  recursive calls
	  0  db block gets
       6638  consistent gets
	  0  physical reads
	  0  redo size
     219900  bytes sent via SQL*Net to client
       6617  bytes received via SQL*Net from client
	556  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
       8320  rows processed
SQL> select count(8) from t1;
  COUNT(8)
----------
    868352

通过查看可以看出，t1表和t2表中的数量大小868352，全文索引的逻辑读比普通索引的逻辑读要低，但是代价比较高。





wluckdog
关注
专栏目录
MYSQL----全文索引
喝咖啡的猫的博客
04-21 408
全文索引 全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值，类似于搜索引擎做的事情，而不是简单的where条件匹配。注意ＭＹＩＳＡＭ支持，Ｉｎｎｏｄｂ也支持了 ...
全文索引详解
shanjairui的博客
08-22 569
Mysql全文索引 前言: Mysql有诸多索引类型，本篇详解Mysql的全文索引。 使用全文索引之前我们先看一下使用全文索引的要求 1. MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引； 2. MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引; 3. 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索...
MySQL 之全文索引
热门推荐
潜心做事的博客
04-14 11万+
最近在复习数据库索引部分，看到了 fulltext，也即全文索引，虽然全文索引在平时的业务中用到的不多，但是感觉它有点儿意思，所以花了点时间研究一下，特此记录。 引入 概念 通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。 你可能会说，用 like...
MySql 全文索引
最新发布
Cike___的博客
07-25 1910
Mysql 的全文索引主要用于全文字段的检索场景，支持 char、varchar、text 几个字段加全文索引，仅支持 InNoDB 与 MyISAM 引擎。
全文索引笔记
weixin_30375427的博客
12-05 115
已过去很长时间了，把这里的WORD传上来吧！！ 全文索引 1、CONTAINS a) 区分大小写 b) Where CONTAINS(Description, ' "bean curd" ') c) AND ，AND NOT，OR，NEAR, d) CONTAINS( address, '"HEIBEI provi...
mysql全文索引
sebeefe的博客
09-11 902
深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则近万的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！因此收集整理了一份《Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
MySQL - 全文索引
骆驼胡杨的博客
07-21 904
配置文件my.ini（Windows10默认路径C\ProgramData\MySQL\MySQLServer8.0）中增加如下配置项，同时。在上述语句中，关键字CREATEFULLTEXTINDEX表示用来创建全文索引。
MySQL创建全文索引分享
12-16
MySQL全文索引是一种提高数据库查询性能的技术，尤其适用于大规模文本数据的检索。它通过分词技术和特定的算法，分析文本中的关键词频率和重要性，从而快速定位到匹配的记录。在MySQL中，全文索引主要应用于MYISAM...
Oracle自带全文索引
12-14
Oracle自带的全文索引是一种强大的文本搜索工具，尤其在处理大量文档和文章的搜索场景中，能够显著提升查询性能和用户体验。全文索引利用了Oracle数据库的内部机制，通过词法分析器对文本进行解析，然后创建索引，...
oracle10g全文索引自动同步语句使用方法
09-10
在Oracle 10g数据库系统中，全文索引是一种高效的数据检索工具，它允许用户通过自然语言查询来搜索表中的文本数据。Oracle提供了多种全文索引类型，其中Context类型的全文索引尤其受到关注，因为它支持复杂的文本...
MySQL全文索引、联合索引、like查询、json查询速度哪个快
09-08
本文将深入探讨全文索引、联合索引、LIKE查询以及JSON查询这四种不同方法在实际应用中的速度比较，并通过实例进行详细解析。 首先，全文索引（Full-text Index）是MySQL提供的一种特殊类型的索引，专门用于提高全文...
Oracle全文索引
虎猫
06-28 134
对cmng_custominfo 表中的address字段做全文检索： 1，在oracle9201中需要创建一个分词的东西： BEGIN ctx_ddl.create_preference ('SMS_ADDRESS_LEXER', 'CHINESE_LEXER'); --ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer...
SQL2005全文索引
虎猫
06-25 158
现在我们项目要查询一张商品表，由于商品描述太长，使用like 搜索的时候特别费时，别人告诉我使用全文索引，我网上查看了一下，感觉挺简单的，而且速度特别快。     全文索引为在字符串数据中进行复杂的词搜索提供有效支持。全文索引存储关于重要词和这些词在特定列中的位置的信息。全文查询利用这些信息，可快速搜索包含具体某个词或一组词的行。     全文索引包含在全文目录中。每个数据库可以包含一个或多个全...
Oracle全文索引技术详解
"Oracle全文索引是Oracle数据库中用于高效处理文本数据的一种功能，它从Oracle 7.3版本开始引入，并在后续版本中不断演进。Oracle Text在Oracle 9i及以后的版本中成为标准功能，允许用户在创建数据库实例时自动安装...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值