当前位置: 首页 > news >正文

oracle模糊查询:全文索引方式(三)

--test:

DATABASE:dwtest(210开发库)

SQL> desc iquery.lpx_kw_tmp02
Name               Type          Nullable Default Comments
------------------ ------------- -------- ------- --------
KEYWORDS           VARCHAR2(256) Y                        
COUNTRY_ID         VARCHAR2(10)  Y                        
COUNTRY_NAME       VARCHAR2(256) Y                        
REGION_ID          NUMBER        Y                        
REGIN_NAME         VARCHAR2(128) Y                        
PRE_REGIN_ID       NUMBER        Y                        
PRE_REGIN_ENAME    VARCHAR2(128) Y                        
SRH_PV_THIS_5MON   NUMBER        Y                        
CATEGORY_LEAF_ID   NUMBER        Y                        
CATEGORY_ROOT_ID   NUMBER        Y                        
CATEGORY_LEVEL2_ID NUMBER        Y                        
CATEGORY_LEVEL3_ID NUMBER        Y   

--从业务上利用     
CREATE TABLE iquery.lpx_kw_tmp02_bak TABLESPACE TBS_EN_DSS_2009 AS
SELECT keywords,
       REGIN_NAME,
       CATEGORY_ROOT_ID,
       SUM(SRH_PV_THIS_5MON) AS SRH_PV_THIS_5MON
  FROM iquery.lpx_kw_tmp02
 WHERE CATEGORY_ROOT_ID IS NOT NULL
 GROUP BY keywords,
       REGIN_NAME,
       CATEGORY_ROOT_ID;

SQL> desc iquery.lpx_kw_tmp02_bak
Name             Type          Nullable Default Comments
---------------- ------------- -------- ------- --------
KEYWORDS         VARCHAR2(256) Y                        
REGIN_NAME       VARCHAR2(128) Y                        
CATEGORY_ROOT_ID NUMBER        Y                        
SRH_PV_THIS_5MON NUMBER        Y 

SQL> select count(1) from iquery.lpx_kw_tmp02_bak;
 
  COUNT(1)
----------
   7799710
 

--模糊匹配'mechanical'
SQL>   SELECT a.regin_name
  2           ,SUM(a.srh_pv_this_5mon) AS srh_pv_this_5mon
  3      FROM iquery.lpx_kw_tmp02_bak a
  4     WHERE keywords LIKE '%mechanical%'
  5       AND category_root_id = 80
  6     group by regin_name
  7    ;
 
 
REGIN_NAME                                                                       SRH_PV_THIS_5MON
-------------------------------------------------------------------------------- ----------------
Western Europe                                                                                  3
North America                                                                                  10
Middle East                                                                                     1
OTHER                                                                                           1
Asia                                                                                           21
--耗时:2.828S


SQL>   SELECT /*+parallel(a 4) */a.regin_name
  2           ,SUM(a.srh_pv_this_5mon) AS srh_pv_this_5mon
  3      FROM iquery.lpx_kw_tmp02_bak a
  4     WHERE keywords LIKE '%mechanical%'
  5       AND category_root_id = 80
  6     group by regin_name
  7    ;
 
 
REGIN_NAME                                                                       SRH_PV_THIS_5MON
-------------------------------------------------------------------------------- ----------------
North America                                                                                  10
OTHER                                                                                           1
Western Europe                                                                                  3
Middle East                                                                                     1
Asia                                                                                           21

--耗时1.11S

SQL>   EXPLAIN PLAN FOR SELECT a.regin_name
  2           ,SUM(a.srh_pv_this_5mon) AS srh_pv_this_5mon
  3      FROM iquery.lpx_kw_tmp02_bak a
  4     WHERE keywords LIKE '%mechanical%'
  5       AND category_root_id = 80
  6     group by regin_name
  7    ;
 
Explained
 
SQL> select * from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3331152321
--------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Tim
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |  5390 |  1168K| 60651   (2)| 00:
|   1 |  HASH GROUP BY     |                  |  5390 |  1168K| 60651   (2)| 00:
|*  2 |   TABLE ACCESS FULL| LPX_KW_TMP02_BAK |  5390 |  1168K| 60649   (2)| 00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("CATEGORY_ROOT_ID"=80 AND "KEYWORDS" LIKE '%mechanical%')
Note
-----
   - 'PLAN_TABLE' is old version
   - dynamic sampling used for this statement
 
19 rows selected


--创建全文索引:
1、授权
以sys登录
SQL> grant ctxapp to etl;
 
Grant succeeded

以ctxsys登录
grant execute on ctx_ddl to etl;

2、建立索引
以etl连接
SQL> exec ctx_ddl.create_preference('etl_kw1','BASIC_LEXER');
 
PL/SQL procedure successfully completed
--其中:第一个参数可以随便命名,第二个参数必须是oracle规定的几个值之一。
--第二个参数可以是CHINESE_LEXER、chinese_vgram_lexer;CHINESE_LEXER建索引时间长、但查询速度、查询准确度都比chinese_vgram_lexer高

--建立后可以用如下方式查询:
SQL> SELECT * FROM CTX_PREFERENCES;
 
PRE_OWNER                      PRE_NAME                       PRE_CLASS                      PRE_OBJECT
------------------------------ ------------------------------ ------------------------------ ------------------------------
CTXSYS                         URL_DATASTORE                  DATASTORE                      URL_DATASTORE
CTXSYS                         FILE_DATASTORE                 DATASTORE                      FILE_DATASTORE
WKSYS                          WK_DATASTORE                   DATASTORE                      FILE_DATASTORE
CTXSYS                         DEFAULT_DATASTORE              DATASTORE                      DIRECT_DATASTORE
CTXSYS                         DIRECT_DATASTORE               DATASTORE                      DIRECT_DATASTORE
CTXSYS                         AUTO_FILTER                    FILTER                         AUTO_FILTER
CTXSYS                         MAIL_FILTER                    FILTER                         MAIL_FILTER
CTXSYS                         INSO_FILTER                    FILTER                         INSO_FILTER
CTXSYS                         NULL_FILTER                    FILTER                         NULL_FILTER
WKSYS                          WK_FILTER                      FILTER                         NULL_FILTER
CTXSYS                         DEFAULT_EXTRACT_LEXER          LEXER                          AUTO_LEXER
WKSYS                          WK_KOREAN_LEXER                LEXER                          KOREAN_MORPH_LEXER
WKSYS                          WK_LEXER                       LEXER                          MULTI_LEXER
ETL                            ETL_LEXER                      LEXER                          CHINESE_LEXER
ETL                            ETL_LPXUAN                     LEXER                          CHINESE_LEXER
ETL                            ETL_KW                         LEXER                          CHINESE_LEXER
CTXSYS                         MY_LEXER1                      LEXER                          CHINESE_VGRAM_LEXER
WKSYS                          WK_CHINESE_LEXER               LEXER                          CHINESE_VGRAM_LEXER
WKSYS                          WK_JAPANESE_LEXER              LEXER                          JAPANESE_VGRAM_LEXER
CTXSYS                         MY_LEXER                       LEXER                          BASIC_LEXER
 
PRE_OWNER                      PRE_NAME                       PRE_CLASS                      PRE_OBJECT
------------------------------ ------------------------------ ------------------------------ ------------------------------
CTXSYS                         DEFAULT_LEXER                  LEXER                          BASIC_LEXER
CTXSYS                         BASIC_LEXER                    LEXER                          BASIC_LEXER
WKSYS                          WK_BASIC_LEXER                 LEXER                          BASIC_LEXER
CTXSYS                         DEFAULT_WORDLIST               WORDLIST                       BASIC_WORDLIST
CTXSYS                         BASIC_WORDLIST                 WORDLIST                       BASIC_WORDLIST
WKSYS                          WK_WORDLIST                    WORDLIST                       BASIC_WORDLIST
CTXSYS                         MYSTORE                        STORAGE                        BASIC_STORAGE
CTXSYS                         DEFAULT_STORAGE                STORAGE                        BASIC_STORAGE
CTXSYS                         MYSTORE1                       STORAGE                        BASIC_STORAGE
WKSYS                          WK_STORAGE                     STORAGE                        BASIC_STORAGE
CTXSYS                         ENTITY_STORAGE_DR              STORAGE                        ENTITY_STORAGE
CTXSYS                         ENTITY_STORAGE_D               STORAGE                        ENTITY_STORAGE
CTXSYS                         ENTITY_STORAGE_R               STORAGE                        ENTITY_STORAGE
CTXSYS                         ENTITY_STORAGE                 STORAGE                        ENTITY_STORAGE
CTXSYS                         DEFAULT_CLASSIFIER             CLASSIFIER                     RULE_CLASSIFIER
CTXSYS                         DEFAULT_CLUSTERING             CLUSTERING                     KMEAN_CLUSTERING
 
36 rows selected

SQL> create index idx_item_kw ON iquery.lpx_kw_tmp02_bak(keywords) indextype is ctxsys.context parameters('lexer etl_kw1');
 
Index created
--parameters括号中都第一个值lexer是固定的,第二个值是上一步建立的preference名称

--耗时:5056S

3、定期同步索引(同步索引为增量同步)
--context这种类型的索引需要手工同步
--同步:根据新增记录的文本内容更新全文搜索的索引。
SQL> create or replace procedure rel_kw_sync_index as
      begin
      ctx_ddl.sync_index('idx_item_rel_kw');
      end;
      /

Procedure created.

Elapsed: 00:00:00.08
SQL> VARIABLE jobno number;
SQL> BEGIN
2 DBMS_JOB.SUBMIT(:jobno,'rel_kw_sync_index();',
3 SYSDATE, 'SYSDATE + (1/24/4)');
4 commit;
5 END;
6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.27

4、定期优化索引
--优化:根据被删除记录清除全文搜索索引中的垃圾
SQL> create or replace procedure rel_kw_optimize_index as
   2   begin
   3   ctx_ddl.optimize_index('rel_kw_sync_index','FULL');
   4   end;
   5   /

SQL> VARIABLE jobno number;
SQL> BEGIN
2 DBMS_JOB.SUBMIT(:jobno,'rel_kw_optimize_index();',
3 SYSDATE, 'SYSDATE + 1');
4 commit;
5 END;
6 /
Procedure created.



全文检索的缺点是不能实时保证查询的正确性,所以要合理地定期同步索引和优化索引。

--造测试数据
DROP TABLE iquery.lpx_kw_test;
CREATE TABLE iquery.lpx_kw_test TABLESPACE TBS_EN_DSS_2009
AS
SELECT SUBSTR(keywords, INSTR(keywords, ' ', 1)+1, INSTR(keywords, ' ', 1, 2) - INSTR(keywords, ' ', 1) - 1) AS kw, a.* 
  FROM iquery.lpx_kw_tmp02_bak1  a
 WHERE length(keywords) - length(REPLACE(keywords, ' ', '')) > 2 AND ROWNUM < 1000 + 1;


--根据行业和大洲进行匹配
DROP TABLE iquery.lpx_kw_tmp03;
CREATE TABLE iquery.lpx_kw_tmp03(
 KW                                                 VARCHAR2(256)
 ,REGIN_NAME                                         VARCHAR2(128)
 ,CATEGORY_ROOT_ID                                   NUMBER
 ,SRH_PV_THIS_5MON                                   NUMBER
);
set serveroutput ON;

DECLARE 
  cnt               NUMBER;
  start_date        DATE;
  end_date          DATE;
 
CURSOR c1
IS
 select kw, keywords, regin_name, category_root_id FROM iquery.lpx_kw_test;
 
BEGIN

 cnt := 0;
 start_date := SYSDATE;
 
 FOR ref_cursor IN c1
 LOOP 
 INSERT INTO iquery.lpx_kw_tmp03
 SELECT ref_cursor.kw
        ,ref_cursor.regin_name
        ,ref_cursor.category_root_id
        ,SUM(a.srh_pv_this_5mon) AS srh_pv_this_5mon
   FROM iquery.lpx_kw_tmp02_bak a   --全文索引
  WHERE contains(a.keywords, ref_cursor.kw) > 0
    AND a.category_root_id = ref_cursor.category_root_id
    AND a.regin_name = ref_cursor.regin_name
  GROUP BY ref_cursor.kw
        ,ref_cursor.regin_name
        ,ref_cursor.category_root_id;
 COMMIT;
 cnt := cnt + 1;
 
 END LOOP;
 end_date := SYSDATE;
 
 DBMS_OUTPUT.PUT_LINE('total records:'||cnt);
 DBMS_OUTPUT.PUT_LINE('total time(ms):'||ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 1000));
 DBMS_OUTPUT.PUT_LINE('avg time(ms):'||ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 1000)/cnt);
END;
/


total records:100
total time(ms):34000
avg time(ms):340

PL/SQL procedure successfully completed.

 

 

Oracle实现全文检索,其机制其实很简单。即通过Oracle专利的词法分析器(lexer),将文章中所有的表意单元(Oracle称为 term)找出来,记录在一组以dr$开头的表中,同时记下该term出现的位置、次数、hash值等信息。检索时,Oracle从这组表中查找相应的 term,并计算其出现频率,根据某个算法来计算每个文档的得分(score),即所谓的‘匹配率’。而lexer则是该机制的核心,它决定了全文检索的 效率。Oracle针对不同的语言提供了不同的lexer,而我们通常能用到其中的三个:
     basic_lexer:针对英语。它能根据空格和标点来将英语单词从句子中分离,还能自动将一些出现频率过高已经失去检索意义的单词作为‘垃圾’处理, 如if , is等,具有较高的处理效率。但该lexer应用于汉语则有很多问题,由于它只认空格和标点,而汉语的一句话中通常不会有空格,因此,它会把整句话作为一 个term,事实上失去检索能力。以‘中国人民站起来了’这句话为例,basic_lexer分析的结果只有一个term ,就是‘中国人民站起来了’。此时若检索‘中国’,将检索不到内容。
     chinese_vgram_lexer:专门的汉语分析器,支持所有汉字字符集 (ZHS16CGB231280ZHS16GBKZHT32EUCZHT16BIG5ZHT32TRISZHT16MSWIN950ZHT16HKSCSUTF8)。 该分析器按字为单元来分析汉语句子。‘中国人民站起来了’这句话,会被它分析成如下几个term: ‘中’,‘中国’,‘国人’,‘人民’,‘民站’,‘站起’,起来’,‘来了’,‘了’。可以看出,这种分析方法,实现算法很简单,并且能实现‘一网打 尽’,但效率则是差强人意。

相关文章:

  • oracle模糊查询:分区局部全文索引方式(四)
  • 动态链接及静态链接
  • BTrace实战
  • windows下安装配置hadoop
  • JavaScript(jQuery)实现打印英文格式日期
  • eclipse运行hadoop wordcount example
  • linux6.5环境下安装python
  • protobuf-2.5.0的下载与安装
  • ibatis入门
  • 将DataTable转换为ListT对象遇到问题:类型“System.Int64”的对象无法转换为类型“System.Int32”。...
  • php无限分类
  • java servlet实例
  • POJ 3154 Graveyard【多解,数论,贪心】
  • C++ 浅谈 strlen 与 sizeof的区别
  • 产品设计的原则之一:精简你的产品
  • 《Javascript高级程序设计 (第三版)》第五章 引用类型
  • 【跃迁之路】【699天】程序员高效学习方法论探索系列(实验阶段456-2019.1.19)...
  • CAP 一致性协议及应用解析
  • const let
  • ECMAScript6(0):ES6简明参考手册
  • Java IO学习笔记一
  • Material Design
  • PermissionScope Swift4 兼容问题
  • Python语法速览与机器学习开发环境搭建
  • rabbitmq延迟消息示例
  • scala基础语法(二)
  • Spring声明式事务管理之一:五大属性分析
  • 从零到一:用Phaser.js写意地开发小游戏(Chapter 3 - 加载游戏资源)
  • 关键词挖掘技术哪家强(一)基于node.js技术开发一个关键字查询工具
  • 前端存储 - localStorage
  • 体验javascript之美-第五课 匿名函数自执行和闭包是一回事儿吗?
  • 关于Kubernetes Dashboard漏洞CVE-2018-18264的修复公告
  • # Java NIO(一)FileChannel
  • #【QT 5 调试软件后,发布相关:软件生成exe文件 + 文件打包】
  • #gStore-weekly | gStore最新版本1.0之三角形计数函数的使用
  • (4)Elastix图像配准:3D图像
  • (超简单)使用vuepress搭建自己的博客并部署到github pages上
  • (代码示例)使用setTimeout来延迟加载JS脚本文件
  • (附源码)springboot教学评价 毕业设计 641310
  • (黑马出品_高级篇_01)SpringCloud+RabbitMQ+Docker+Redis+搜索+分布式
  • (剑指Offer)面试题41:和为s的连续正数序列
  • (六)DockerCompose安装与配置
  • (免费领源码)python#django#mysql校园校园宿舍管理系统84831-计算机毕业设计项目选题推荐
  • (一)pytest自动化测试框架之生成测试报告(mac系统)
  • (译)2019年前端性能优化清单 — 下篇
  • (转)IOS中获取各种文件的目录路径的方法
  • (转)shell中括号的特殊用法 linux if多条件判断
  • ... 是什么 ?... 有什么用处?
  • .Net 基于.Net8开发的一个Asp.Net Core Webapi小型易用框架
  • .netcore 获取appsettings
  • .NetCore部署微服务(二)
  • /run/containerd/containerd.sock connect: connection refused
  • /usr/bin/perl:bad interpreter:No such file or directory 的解决办法
  • ??myeclipse+tomcat
  • @font-face 用字体画图标