Full Text Indexing using Oracle Text

原创 2007年09月15日 17:21:00

 

 

 

Full Text Indexing using Oracle Text
Oracle 
Text, previously know as interMedia Text and ConText, is an extensive full text indexing technology allowing you to efficiently query free text and produce document classification applications. In this article I'll only scratch the surface of this very complex feature.

CONTEXT Indexes 
CTXCAT Indexes 
CTXRULE Indexes 
Index Maintenance 
The examples in this article require access to the CTX_DDL package, which is granted as follows:

GRANT EXECUTE ON CTX_DDL TO <username>;
CONTEXT Indexes
The CONTEXT index type is used to index large amounts of text such as Word, PDF, XML, HTML or plain text documents. In this example we will store the data in a BLOB column, which allows us to store binary documents like Word and PDF as well as plain text. Using a CLOB is preferable if only plain text documents are being used.

First we build a sample schema to hold our data:

DROP TABLE my_docs;
DROP SEQUENCE my_docs_seq;
DROP PROCEDURE load_file_to_my_docs;

CREATE TABLE my_docs (
  id    NUMBER(10)     NOT NULL,
  name  VARCHAR2(200)  NOT NULL,
  doc   BLOB           NOT NULL
)
/

ALTER TABLE my_docs ADD (
  CONSTRAINT my_docs_pk PRIMARY KEY (id)
)
/

CREATE SEQUENCE my_docs_seq;

CREATE OR REPLACE DIRECTORY documents AS 
'C:work';
Next we load several files as follows:

CREATE OR REPLACE PROCEDURE load_file_to_my_docs (p_file_name  IN  my_docs.name%TYPE) AS
  v_bfile      BFILE;
  v_blob       BLOB;
BEGIN
  INSERT INTO my_docs (id, name, doc)
  VALUES (my_docs_seq.NEXTVAL, p_file_name, empty_blob())
  RETURN doc INTO v_blob;

  v_bfile := BFILENAME(
'DOCUMENTS', p_file_name);
  Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
  Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
  Dbms_Lob.Fileclose(v_bfile);

  COMMIT;
END;
/

EXEC load_file_to_my_docs(
'FullTextIndexingUsingOracleText9i.doc');
EXEC load_file_to_my_docs(
'FullTextIndexingUsingOracleText9i.asp');
EXEC load_file_to_my_docs(
'XMLOverHTTP9i.asp');
EXEC load_file_to_my_docs(
'UNIXForDBAs.asp');
EXEC load_file_to_my_docs(
'emp_ws_access.sql');
EXEC load_file_to_my_docs(
'emp_ws_test.html');
EXEC load_file_to_my_docs(
'9ivsSS2000forPerformanceV22.pdf');
Next we create a CONTEXT type index on the doc column and gather table statistics:

CREATE INDEX my_docs_doc_idx ON my_docs(doc) INDEXTYPE IS CTXSYS.CONTEXT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 
'MY_DOCS', cascade=>TRUE);
Finally we query table looking for documents with specific content:

SELECT SCORE(1) score, id, name
FROM   my_docs
WHERE  CONTAINS(doc, 
'SQL Server', 1) > 0
ORDER BY SCORE(1) DESC;

     SCORE         ID NAME
---------- ---------- ------------------------------------------------
       100        127 9ivsSS2000forPerformanceV22.pdf

1 row selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=2 Bytes=58)
   1    0   SORT (ORDER BY) (Cost=4 Card=2 Bytes=58)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 
'MY_DOCS' (Cost=2 Card=2 Bytes=58)
   3    2       DOMAIN INDEX OF 
'MY_DOCS_DOC_IDX' (Cost=0)


SELECT SCORE(1) score, id, name
FROM   my_docs
WHERE  CONTAINS(doc, 
'XML', 1) > 0
ORDER BY SCORE(1) DESC;

     SCORE         ID NAME
---------- ---------- ------------------------------------------------
        74        123 XMLOverHTTP9i.asp
         9        125 emp_ws_access.sql

2 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=2 Bytes=58)
   1    0   SORT (ORDER BY) (Cost=4 Card=2 Bytes=58)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 
'MY_DOCS' (Cost=2 Card=2 Bytes=58)
   3    2       DOMAIN INDEX OF 
'MY_DOCS_DOC_IDX' (Cost=0)
CTXCAT Indexes
The CTXCAT index type is best suited to smaller text fragments that must be indexed along with other relational data. In this example the data will be stored in a VARCHAR2 column.

First we create a schema to hold the data:

DROP TABLE my_items;
DROP SEQUENCE my_items_seq;
EXEC CTX_DDL.DROP_INDEX_SET(
'my_items_iset');

CREATE TABLE my_items (
  id           NUMBER(10)      NOT NULL,
  name         VARCHAR2(200)   NOT NULL,
  description  VARCHAR2(4000)  NOT NULL,
  price        NUMBER(7,2)     NOT NULL
)
/

ALTER TABLE my_items ADD (
  CONSTRAINT my_items_pk PRIMARY KEY (id)
)
/

CREATE SEQUENCE my_items_seq;
Next we populate the schema with some dummy data:

BEGIN
  FOR i IN 1 .. 1000 LOOP
    INSERT INTO my_items (id, name, description, price)
    VALUES (my_items_seq.NEXTVAL, 
'Bike: '||i, 'Bike Description ('||i||')', i);
  END LOOP;

  FOR i IN 1 .. 1000 LOOP
    INSERT INTO my_items (id, name, description, price)
    VALUES (my_items_seq.NEXTVAL, 
'Car: '||i, 'Car Description ('||i||')', i);
  END LOOP;

  FOR i IN 1 .. 1000 LOOP
    INSERT INTO my_items (id, name, description, price)
    VALUES (my_items_seq.NEXTVAL, 
'House: '||i, 'House Description ('||i||')', i);
  END LOOP;

  COMMIT;
END;
/
Next we create a CTXCAT index on the DESCRIPTION and PRICE columns and gather table statistics. In order to create the index we must create an index-set with a sub-index for each column referenced by the CATSEARCH function:

EXEC CTX_DDL.CREATE_INDEX_SET(
'my_items_iset');
EXEC CTX_DDL.ADD_INDEX(
'my_items_iset','price');

CREATE INDEX my_items_name_idx ON my_items(description) INDEXTYPE IS CTXSYS.CTXCAT
PARAMETERS (
'index set my_items_iset');

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 
'MY_ITEMS', cascade=>TRUE);
Finally we query table looking for items with a description that contains our specified words and an appropriate price:

SELECT id, price, name
FROM   my_items
WHERE  CATSEARCH(description, 
'Bike''price BETWEEN 1 AND 5')> 0;

        ID      PRICE NAME
---------- ---------- ------------------------------------------------
         1          1 Bike: 1
         2          2 Bike: 2
         3          3 Bike: 3
         4          4 Bike: 4
         5          5 Bike: 5

5 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=150 Bytes=6000)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 
'MY_ITEMS' (Cost=2 Card=150 Bytes=6000)
   2    1     DOMAIN INDEX OF 
'MY_ITEMS_NAME_IDX'


SELECT id, price, name
FROM   my_items
WHERE  CATSEARCH(description, 
'Car''price BETWEEN 101 AND 105 ORDER BY price DESC')> 0;

        ID      PRICE NAME
---------- ---------- ------------------------------------------------
      1105        105 Car: 105
      1104        104 Car: 104
      1103        103 Car: 103
      1102        102 Car: 102
      1101        101 Car: 101

5 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=150 Bytes=6000)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 
'MY_ITEMS' (Cost=2 Card=150 Bytes=6000)
   2    1     DOMAIN INDEX OF 
'MY_ITEMS_NAME_IDX'
Every column used to restrict the selection or order the output in the CATSEARCH function should have a sub-index within the index-set. The CTXCAT index type is transactional so there is no need to synchronize the index.

CTXRULE Indexes
The CTXRULE index type can be used to build document classification applications.

First we must define our document categories and store them, along with a suitable query for the MATCHES function:

DROP TABLE my_doc_categories;
DROP TABLE my_categories;
DROP SEQUENCE my_categories_seq;
DROP TABLE my_docs;
DROP SEQUENCE my_docs_seq;

CREATE TABLE my_categories (
  id        NUMBER(10)      NOT NULL,
  category  VARCHAR2(30)    NOT NULL,
  query     VARCHAR2(2000)  NOT NULL
)
/

ALTER TABLE my_categories ADD (
  CONSTRAINT my_categories_pk PRIMARY KEY (id)
)
/

CREATE SEQUENCE my_categories_seq;

INSERT INTO my_categories VALUES(my_categories_seq.NEXTVAL, 
'Oracle''ABOUT(Oracle)');
INSERT INTO my_categories VALUES(my_categories_seq.NEXTVAL, 
'SQL Server''ABOUT(SQL Server)');
INSERT INTO my_categories VALUES(my_categories_seq.NEXTVAL, 
'UNIX''ABOUT(UNIX)');
Next we create a table to hold our documents:

CREATE TABLE my_docs (
  id    NUMBER(10)     NOT NULL,
  name  VARCHAR2(200)  NOT NULL,
  doc   CLOB           NOT NULL
)
/

ALTER TABLE my_docs ADD (
  CONSTRAINT my_docs_pk PRIMARY KEY (id)
)
/

CREATE SEQUENCE my_docs_seq;
Then we create an intersection table to resolve the many-to-many relationship between documents and categories:

CREATE TABLE my_doc_categories (
  my_doc_id       NUMBER(10)  NOT NULL,
  my_category_id  NUMBER(10)  NOT NULL
)
/

ALTER TABLE my_doc_categories ADD (
  CONSTRAINT my_doc_categories_pk PRIMARY KEY (my_doc_id, my_category_id)
)
/
Next we create a BEFORE INSERT trigger on the MY_DOCS table to automatically assign the documents to the relevant categories as they are being inserted. The MATCHES function is used to decide if the document matches any of our gategory queries. The resulting cursor is used to insert the matches into the intersect table:

CREATE OR REPLACE TRIGGER my_docs_trg
  BEFORE INSERT ON my_docs
  FOR EACH ROW
BEGIN
  FOR c1 IN (SELECT id
             FROM   my_categories
             WHERE  MATCHES(query, :new.doc)>0)
  LOOP
    BEGIN
      INSERT INTO my_doc_categories(my_doc_id, my_category_id)
      VALUES (:new.id, c1.id);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/
Next we create the CTXRULE index to support the trigger. For completeness we also create a CONTEXT index on the document itself, although this is not involved in the category assignment process:

CREATE INDEX my_categories_query_idx ON my_categories(query) INDEXTYPE IS CTXSYS.CTXRULE;
CREATE INDEX my_docs_doc_idx ON my_docs(doc) INDEXTYPE IS CTXSYS.CONTEXT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 
'MY_CATEGORIES', cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 
'MY_DOCS', cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 
'MY_DOC_CATEGORIES', cascade=>TRUE);
Finally we test the mechanism by inserting some rows and checking the classification:

INSERT INTO my_docs (id, name, doc)
VALUES (my_docs_seq.NEXTVAL, 
'Oracle Document''This document constains the word Oracle!');

INSERT INTO my_docs (id, name, doc)
VALUES (my_docs_seq.NEXTVAL, 
'SQL Server Document''This document constains the words SQL Server!');

INSERT INTO my_docs (id, name, doc)
VALUES (my_docs_seq.NEXTVAL, 
'UNIX Document''This document constains the word UNIX!');

INSERT INTO my_docs (id, name, doc)
VALUES (my_docs_seq.NEXTVAL, 
'Oracle UNIX Document''This document constains the word UNIX and the word Oracle!');


COLUMN name FORMAT A30;
SELECT a.name, b.category
FROM   my_docs a,
       my_categories b,
       my_doc_categories c
WHERE  c.my_doc_id      = a.id
AND    c.my_category_id = b.id;

NAME                           CATEGORY
------------------------------ ------------------------------
Oracle Document                Oracle
SQL Server Document            SQL Server
UNIX Document                  UNIX
Oracle UNIX Document           UNIX
Oracle UNIX Document           Oracle

5 rows selected.
The output shows that the documents have been assigned to the correct categories. Note, the "Oracle UNIX Document" document has been assigned to both the "Oracle" and "UNIX" categories.

Index Maintenance
Not all Oracle Text indexes are automatically updated as records are added or deleted. To synchronize the index with the table you must call:

SQL> EXEC CTX_DDL.SYNC_INDEX(
'my_docs_doc_idx');
Regular synchronizations of the index can be automated using the DBMS_JOB package. The following script is provided to make this task easier:

$ORACLE_HOME/ctx/sample/script/drjobdml.sql
It can be called from SQL*Plus whilst logged on as the index owner as follows:

SQL> @drjobdml.sql index-name interval-mins
SQL> @drjobdml.sql my_docs_doc_idx 60
Regular synchronization of text indexes can cause fragmentation which affects query performance. To correct this situation the index can be rebuilt or optimized. Index optimization can be performed in three basic modes (FAST, FULL or TOKEN). The FAST mode compacts fragmented rows but does not remove old data:

BEGIN
  CTX_DDL.OPTIMIZE_INDEX(
'my_docs_doc_idx','FAST');
END;
/
The FULL mode optimizes either the entire index or a portion of it, with old data removed:

BEGIN
  CTX_DDL.OPTIMIZE_INDEX(
'my_docs_doc_idx','FULL');
END;
/
The TOKEN mode perfoms a full optimization for a specific token:

BEGIN
  CTX_DDL.OPTIMIZE_INDEX(
'my_docs_doc_idx','TOKEN', token=>'Oracle');
END;
/
For further information see:

相关文章推荐

mysql索引类型Normal,Unique,Full Text区别及索引方法Btree,Hash的区别

mysql索引类型Normal,Unique,Full Text区别Normal:表示普通索引,大多数情况下都可以使用Unique:约束唯一标识数据库表中的每一条记录,即在单表中不能用每条记录是唯一的...

iOS/Android SQLite 全文检索——FTS (Full Text Search)

前言我们的APP部分功能为了满足用户离线使用搜索的场景,使用了内置SQLite数据库的方式,随着内容的日益丰富,数据库记录快速增多,导致搜索速度明显变慢,为了提升搜索速度,给我们的数据做了全文检索的支...

mysql索引类型normal,unique,full text,索引方式btree索引和hash

问题1: mysql索引类型normal,unique,full text的区别是什么? normal:表示普通索引 unique:表示唯一的,不允许重复的索引,如果该字段信息保证不会重复...

PostgreSQL 8.3.1 全文检索(Full Text Search)

原文出自:http://blog.csdn.net/hbzy0215/archive/2009/03/17/3998402.aspx保存以供学习,谢谢原创作者  PostgreSQL 8.3.1  全...
  • CYXLZZS
  • CYXLZZS
  • 2011年02月22日 11:56
  • 1031

elasticsearch---search in depth之full-text search

full-text中两个关键因素:analyzed和relevence

阅读笔记:Building a Distributed Full-Text Index for the Web

这篇文章主要讲的是对海量互联网数据建立分布式索引的一些注意的问题和解决方案,涉及到服务器的物理架构、倒排索引的具体结构以及各个索引服务器之间的通信之间的问题。文章主要由三个创新的地方: 对建立索引的过...
  • eaglex
  • eaglex
  • 2011年04月25日 22:26
  • 1808

Compressed Representations of Sequences and Full-Text Indexes(序列的压缩表达和全文索引)

1 Introduction Recent years have witnessed an increasing interest on succinct data structures. Th...

SQL Server 全文索引查询T-SQL学习笔记之一(Full-text index)

这段时间为了提高海量字符串数据的查询效率,我对字段添加了全文索引。首先全文索引相对于传统的索引是有区别的,这是因为传统的索引主要是以首字母开始建立的索引,处理like 'keword%'这样的查询会很...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Full Text Indexing using Oracle Text
举报原因:
原因补充:

(最多只允许输入30个字)