oracle基础知识--全文检索
?全文检索(oracle text)
Oracle Text使Oracle9i具备了强大的文本检索能力和智能化的文本管理能力，Oracle Text是Oracle9i采用的新名称，在oracle8/8i中被称为oracle intermedia text，oracle8以前是oracle context cartridge。Oracle Text的索引和查找功能并不局限于存储在数据库中的数据。它可以对存储于文件系统中的文档进行检索和查找，并可检索超过150种文档类型，包括Microsoft Word、PDF和XML。Oracle Text查找功能包括模糊查找、词干查找（搜索mice和查找mouse）、通配符、相近性等查找方式，以及结果分级和关键词突出显示等。你甚至可以增加一个词典，以查找搭配词，并找出包含该搭配词的文档。 Oracle text 需要为可检索的数据项建立索引，用户才能够通过搜索查找内容，索引进程是根据管道建模的，在这个管道中，数据经过一系列的转换后，将其关键字会添加到索引中。该索引进程分为多个阶段，如下图 1.数据检索（Datastore）：只是将数据从数据存储（例如web页面、数据库大型对象或本地文件系统）中取出，然后作为数据流传送到下一个阶段。 2. 过滤(Filter)：过滤器负责将各种文件格式的数据转换为纯文本格式，索引管道中的其他组件只能处理纯文本数据，不能识别 Ms word 或 excel 等文件格式。 3. 分段(Sectioner)：分段器添加关于原始数据项结构的元数据。 4. 词法分析(Lexer)：根据数据项的语言将字符流分为几个字词。 5. 索引(Index)：最后一个阶段将关键字添加到实际索引中。 测试环境： Linux AS release 4 (Nahant Update 3)， oracle10g（10.2.0.2.0） 内容简介： 本文档主要以实验为主，文档中包含了大量的实验例子，部分测试用例来自document，部分来自网友的测试，所有的例子都在oracle10g中测试通过。
(1). oracle text 9i之前，Oracle Text不是默认安装的，必须手工安装。检查数据库中是否有ctxsys用户和ctxapp脚色，如果没有这个用户和角色，这意味着你在创建数据库时没有安装oracle text功能，需要先配置上该功能。 9i安装text --创建表空间 SQL>create tablespace drsys datafile '/opt/oracle10g/oradata/10gtest/drsys01.dbf' size 100m; SQL> connect / as sysdba --创建ctxsys用户 SQL> @?/ctx/admin/dr0csys password default_tablespace_name temporary_tablespace_name SQL> connect ctxsys/password --创建数据字典 SQL> @?/ctx/admin/dr0inst @?/ctx/lib/libctxx9.so SQL> @?/ctx/admin/defaults/drdefus.sql ; Oracl0g安装text: SQL> connect / as sysdba SQL> @?/ctx/admin/catctx.sql ctxsys TBS_DRSYS temp unlock Conn ctxsys/ctxsys SQL> @?/ctx/admin/defaults/drdefel.sql (2). oratext Alter user ctxsys identified by ctxsys account unlock（10g默认安装，帐号被锁定）; Create tablespace oratext datafile '/opt/oracle10g/oradata/10gtest/orat
ext01.dbf' size 2000m ; Create user oratext identified by oratect default tablespace oratext temporary tablespace temp; Alter database tempfile '/opt/oracle10g/oradata/10gtest/temp01.dbf' resize 2000m; SQL>Create user oratext identified by oratext default tablespace oratext temporary tablespace temp; Grant resource, connect, ctxapp to oratext; Grant execute on ctxsys.ctx_cls to oratext; Grant execute on ctxsys.ctx_ddl to oratext; Grant execute on ctxsys.ctx_doc to oratext; Grant execute on ctxsys.ctx_output to oratext; Grant execute on ctxsys.ctx_query to oratext; Grant execute on ctxsys.ctx_report to oratext; Grant execute on ctxsys.ctx_thes to oratext; Grant execute on ctxsys.ctx_ulexer to oratext;
查看系统默认的oracle text参数 Select pre_name, pre_object from ctx_preferences (3). Oracle Text Oracle text 索引将文本中所有的字符转化成记号（token），如https://www.wendangku.net/doc/cb624757.html,会转化成www,taobao,com这样的记号。 Oracle10g里面支持四种类型的索引，context,ctxcat,ctxrule,ctxxpath 索引类型 描述 查询操作符 CONTEXT 用于对含有大量连续文本数据进行检索。支持word、html、xml、text等很多数据格式。支持范围（range）分区，支持并行创建索引（Parallel indexing）的索引类型。支持类型：VARCHAR2, CLOB, BLOB, CHAR, BFILE, XMLType, and URIType. DML操作后，需要CTX_DDL.SYNC_INDEX 手工同步索引 如果有查询包含多个词语，直接用空格隔开(如 oracle itpub) CONTAINS CTXCAT 适用于混合查询语句（如查询条件包括产品id，价格，描述等）。适合于查询较小的具有一定结构的文本段。 具有事务性。DML操作后，索引会自动进行同步。 操作符：and,or,>,<, =,between,in CATSEARCH CTXRULE Use CTXRULE index to build a document classification or routing application. The CTXRULE index is an index created on a table of queries, where the queries define the classification or routing criteria. MATCHES CTXXPATH Create this index when you need to speed up existsNode() queries on an XMLType column. 1. Context Oracle text索引把全部的word转化成记号，context 索引的架构是反向索引（inverted index）,每个记号都映射着包含它自己的文本位置，如单词dog可能会有如下的条目 Dog Doc1 Doc3 Doc5 这表示dog在文档doc1，doc3，doc5中都出现过。索引建好之后，系统中会自动产生如下DR$MYINDEX$I,DR$MYINDEX$K,DR$MYINDEX$R,DR$MYINDEX$X,MYTABLE5个表(假设表为mytable,索引为myindx)。Dml操作后，context索引不会自动同步，需要利用ctx_ddl.sync_index手工同步索引。
例子： Create table docs (id number primary key, text varchar2(200)); Insert into docs values(1, 'california is a state in the us.'); Insert into docs values(2, 'paris is a city in france.'); Insert into docs values(3, 'france is in europe.'); Commit; / --建立context 索引 Create index idx_docs on docs(text) i
ndextype is ctxsys.context parameters ('filter ctxsys.null_filter section group ctxsys.html_section_group'); --查询 Column text format a40; Select id, text from docs where contains(text, 'france') > 0; id text ---------- ------------------------------- 3 france is in europe. 2 paris is a city in france. --继续插入数据 Insert into docs values(4, 'los angeles is a city in california.'); Insert into docs values(5, 'mexico city is big.'); commit; Select id, text from docs where contains(text, 'city') > 0;--新插入的数据没有查询到 id text -------------------------------------------- 2 paris is a city in france. --索引同步 begin ctx_ddl.sync_index('idx_docs', '2m'); end; --查询 Column text format a50; Select id, text from docs where contains(text, 'city') > 0; --查到数据 id text ----------------------------------------------- 5 mexico city is big. 4 los angeles is a city in california. 2 paris is a city in france. -- or操作符
Select id, text from docs where contains(text, 'city or state ') > 0; --and 操作符 Select id, text from docs where contains(text, 'city and state ') > 0; 或是 Select id, text from docs where contains(text, 'city state ') > 0; --score表示得分，分值越高，表示查到的数据越精确 SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'oracle', 1) > 0; Context类型的索引不会自动同步，这需要在进行Dml后，需要手工同步索引。与context索引相对于的查询操作符为contains 语法： Contains( [schema.]column, text_query varchar2 [,label number]) Return number; indextype is ctxsys.context: 表示所定义的索引类型为context 2. Ctxcat 用在多列混合查询中 Ctxcat可以利用index set建立一个索引集，把一些经常与ctxcat查询组合使用的查询列添加到索引集中。比如你在查询一个商品名时，还需要查询生产日期，价格，描述等，你可可以将这些列添加到索引集中。oracle 将这些查询封装到catsearch操作中，从而提高全文索引的效率。在一些实时性要求较高的交易上，context的索引不能自动同步显然是个问题，ctxcat则会自动同步索引（原文：This example creates a catalog index for an auction site that sells electronic equipment such as cameras and CD players. New inventory is added everyday and item descriptions, bid dates, and prices must be stored together.The application requires good response time for mixed queries. The key is to determine what columns users frequently search so that we can create a suitable CTXCAT index. Queries on this type of index are issued with the CATSEARCH operator.） 例子：
Create table auction(Item_id number,Title varchar2(100),Category_id number,Price number,Bid_close date); Insert into auction values(1, 'nikon cam
era', 1, 400, '24-oct-2002'); Insert into auction values(2, 'olympus camera', 1, 300, '25-oct-2002'); Insert into auction values(3, 'pentax camera', 1, 200, '26-oct-2002'); Insert into auction values(4, 'canon camera', 1, 250, '27-oct-2002'); Commit; / --确定你的查询条件(很重要) Determine that all queries search the title column for item descriptions --建立索引集 begin ctx_ddl.create_index_set('auction_iset'); ctx_ddl.add_index('auction_iset','price'); /* sub-index a*/ end; --建立索引 Create index auction_titlex on auction(title) indextype is ctxsys.ctxcat parameters ('index set auction_iset'); Column title format a40; Select title, price from auction where catsearch(title, 'camera', 'order by price')> 0; Title price --------------- ---------- Pentax camera 200 Canon camera 250 Olympus camera 300 Nikon camera 400 Insert into auction values(5, 'aigo camera', 1, 10, '27-oct-2002'); Insert into auction values(6, 'len camera', 1, 23, '27-oct-2002'); commit; / --测试索引是否自动同步 Select title, price from auction where catsearch(title, 'camera', 'price <= 100')>0; Title price --------------- ---------- aigo camera 10 len camera 23 添加多个子查询到索引集： begin
ctx_ddl.drop_index_set('auction_iset'); ctx_ddl.create_index_set('auction_iset'); ctx_ddl.add_index('auction_iset','price'); /* sub-index A */ ctx_ddl.add_index('auction_iset','price, bid_close'); /* sub-index B */ end; drop index auction_titlex; Create index auction_titlex on auction(title) indextype is ctxsys.ctxcat parameters ('index set auction_iset'); SELECT * FROM auction WHERE CATSEARCH(title, 'camera','price = 200 order by bid_close')> 0; SELECT * FROM auction WHERE CATSEARCH(title, 'camera','order by price, bid_close')> 0; 任何的Dml操作后，Ctxcat的索引会自动进行同步，不需要手工去执行，与ctxcat索引相对应的查询操作符是catsearch. 语法： Catsearch( [schema.]column, Text_query varchar2, Structured_query varchar2, Return number; 例子： catsearch(text, 'dog', 'foo > 15') catsearch(text, 'dog', 'bar = ''SMITH''') catsearch(text, 'dog', 'foo between 1 and 15') catsearch(text, 'dog', 'foo = 1 and abc = 123') 3. Ctxrule The function of a classification application is to perform some action based on document content. These actions can include assigning a category id to a document or sending the document to a user. The result is classification of a document.
例子： Create table queries (query_id number,query_string varchar2(80)); insert into queries values (1, 'oracle'); insert into queries values (2, 'larry or ellison'); insert into queries values (3, 'oracle and text'); insert into queries values (4, 'market share'); commit; --建立索引 Create index queryx on queries(query_string) indextype is ctxsys.ctxrule; Column query_string format a35; Select query_id,query_string from queries
where matches(query_string, 'oracle announced that its market share in databases increased over the last year.')>0; query_id query_string ---------- ----------------------------------- 1 oracle 4 market share 4. Ctxxpath Create this index when you need to speed up existsNode() queries on an XMLType column 以上是oracle text 索引的几种类型，通常用的比较多的是context和ctxcat索引。 语法参考: https://www.wendangku.net/doc/cb624757.html,/docs/cd/B19306_01/text.102/b14218/csql.htm#i997226
接下来，我们来重新看一下索引的内部处理流程,先从Datasotre开始 Datastore 从哪儿得到数据 Filter 数据如何转化成文本 Lexer 正在索引什么语言 Wordlist 如何展开词根和模糊查询 Storage 索引怎么存储 Stop List 哪些词和主题不被索引 Section Group 允许分区查询吗，如何定义文档区段 (4). Datastore属性 数据检索负责将数据从数据存储（例如 web 页面、数据库大型对象或本地文件系统）中取出，然后作为数据流传送到下一个阶段。Datastore包含的类型有Direct datastore, Multi_column_datastore, Detail_datastore, File_datastore, Url_datastore, User_datastore, Nested_datastore。 DIRECT_DATASTORE 检索表（数据存储在单列中） MULTI_COLUMN_DATASTORE 检索表（数据存储在多列中） DETAIL_DATASTORE Data is stored internally in the text column. Document consists of one or more rows stored in a text column in a detail table, with header information stored in a master table. FILE_DATASTORE 检索本地操作系统上的文档 NESTED_DATASTORE 检索嵌套表 URL_DATASTORE 检索Internet地址 USER_DATASTORE Documents are synthesized at index time by a user-defined stored procedure. 1. Direct datastore 支持存储数据库中的数据,单列查询.没有attributes属性 支持类型：char, varchar, varchar2, blob, clob, bfile,or xmltype. 例子：
Create table mytable(id number primary key, docs clob); Insert into mytable values(111555,'this text will be indexed'); Insert into mytable values(111556,'this is a direct_datastore example'); Commit; --建立 direct datastore Create index myindex on mytable(docs) indextype is ctxsys.context parameters ('datastore ctxsys.default_datastore'); Select * from mytable where contains(docs, 'text') > 0; 2. Multi_column_datastore 适用于索引数据分布在多个列中 the column list is limited to 500 bytes 支持number和date类型，在索引之前会先转化成textt raw and blob columns are directly concatenated as binary data. 不支持long, long raw, nchar, and nclob, nested table 例子： Create table mytable1(id number primary key, doc1 varchar2(400),doc2 varchar2(400),doc3 varchar2(400)); Insert into mytable1 values(1,'this text will be indexed
','following example creates a multi-column ','denotes that the bar column '); Insert into mytable1 values(2,'this is a direct_datastore example','use this datastore when your text is stored in more than one column','the system concatenates the text columns'); Commit; / --建立 multi datastore Begin Ctx_ddl.create_preference('my_multi', 'multi_column_datastore'); Ctx_ddl.set_attribute('my_multi', 'columns', 'doc1, doc2, doc3'); End; --建立索引 Create index idx_mytable on mytable1(doc1)indextype is ctxsys.context parameters('datastore my_multi') Select * from mytable1 where contains(doc1,'direct datastore')>0; Select * from mytable1 where contains(doc1,'example creates')>0; --只更新从表，看是否能查到更新的信息 Update mytable1 set doc2='adladlhadad this datastore when your text is stored test' where id=2; Begin Ctx_ddl.sync_index('idx_mytable'); End; Select * from mytable1 where contains(doc1,'adladlhadad')>0; --没有记录 Update mytable1 set doc1='this is a direct_datastore example' where id=2; --更新主表
Begin Ctx_ddl.sync_index('idx_mytable');--同步索引 End; Select * from mytable1 where contains(doc1,'adladlhadad')>0; -查到从表的更新 对于多列的全文索引可以建立在任意一列上，但是，在查询时指定的列必须与索引时指定的列保持一致，只有索引指定的列发生修改，oracle才会认为被索引数据发生了变化，仅修改其他列而没有修改索引列，即使同步索引也不会将修改同步到索引中. 3. Detail_datastore 适用于主从表查询（原文：use the detail_datastore type for text stored directly in the database in detail tables, with the indexed text column located in the master table） 因为真正被索引的是从表上的列，选择主表的那个列作为索引并不重要，但是选定之后，查询条件中就必须指明这个列 主表中的被索引列的内容并没有包含在索引中 DETAIL_DATASTORE属性定义 binary Specify TRUE for Oracle Text to add no newline character after each detail row. Specify FALSE for Oracle Text to add a newline character (\n) after each detail row automatically. detail_table 从表 detail_key 从表外键 detail_lineno Specify the name of the detail table sequence column. detail_text 从表索引列 例子： create table my_master –建立主表 (article_id number primary key,author varchar2(30),title varchar2(50),body varchar2(1)); create table my_detail –建立从表 (article_id number, seq number, text varchar2(4000), constraint fr_id foreign key (ARTICLE_ID) references my_master (ARTICLE_ID)); --模拟数据 insert into my_master values(1,'Tom','expert on and on',1); insert into my_master values(2,'Tom','Expert Oracle Database Architecture',2); commit; insert into my_detail values(1,1,'Oracle will find the undo information for this transaction either in the cached undo segment blocks (most likely) or on disk '); insert into my_detail values(1,2,'if they have been flushed (
more likely for very large transactions).'); insert into my_detail values(1,3,'LGWR is writing to a different device, then there is no
contention for redo logs'); insert into my_detail values(2,1,'Many other databases treat the log files as'); insert into my_detail values(2,2,'For those systems, the act of rolling back can be disastrous'); commit; --建立 detail datastore begin ctx_ddl.create_preference('my_detail_pref', 'DETAIL_DATASTORE'); ctx_ddl.set_attribute('my_detail_pref', 'binary', 'true'); ctx_ddl.set_attribute('my_detail_pref', 'detail_table', 'my_detail'); ctx_ddl.set_attribute('my_detail_pref', 'detail_key', 'article_id'); ctx_ddl.set_attribute('my_detail_pref', 'detail_lineno', 'seq'); ctx_ddl.set_attribute('my_detail_pref', 'detail_text', 'text'); end; --创建索引 CREATE INDEX myindex123 on my_master(body) indextype is ctxsys.context parameters('datastore my_detail_pref'); select * from my_master where contains(body,'databases')>0 --只更新从表信息，看是否还能查到 update my_detail set text='undo is generated as a result of the DELETE, blocks are modified, and redo is sent over to the redo log buffer' where article_id=2 and seq=1 begin ctx_ddl.sync_index('myindex123','2m'); --同步索引 end; select * from my_master where contains(body,'result of the DELETE')>0 –没有查到刚才的更新 --跟新从表后，更新主表信息 update my_master set body=3 where body=2 begin ctx_ddl.sync_index('myindex123','2m'); end; select * from my_master where contains(body,'result of the DELETE')>0 –查到数据 如果更新了子表中的索引列，必须要去更新主表索引列来使oracle认识到被索引数据发生变化（这个可以通过触发器来实现）。 4. File_datastore 适用于检索本地服务器上的文件（原文：The FILE_DATASTORE type is used for text stored in files accessed through the local file system.） 多个路径标识：Unix下冒号分隔开如path1:path2:pathn Windows下用分号;分隔开 create table mytable1(id number primary key, docs varchar2(2000)); insert into mytable1 values(111555,'1.txt');
insert into mytable1 values(111556,'2.log'); commit; --建立 file datastore begin ctx_ddl.create_preference('COMMON_DIR','FILE_DATASTORE'); ctx_ddl.set_attribute('COMMON_DIR','PATH','/opt/tmp'); end; --建立索引 create index myindex on mytable1(docs) indextype is ctxsys.context parameters ('datastore COMMON_DIR'); select * from mytable1 where contains(docs,'oracle')>0; --查询 Oracle只支持在file_datastore中设置的路径中进行搜索，不支持指定路径的子目录（如/opt/tmp/log），如果文件发生了变化，只能是通过修改索引列的方式来通知oracle，被索引数据已经发生了变化。 5. Url_datastore 适用于检索internet上的信息，数据库中只需要存储相应的url就可以 timeout Specify the timeout in seconds. The valid range is 15 to 3600 seconds. The default is 30. maxthreads Specify the maximum number of
threads that can be running simultaneously. Use a number between 1and 1024. The default is 8. urlsize Specify the maximum length of URL string in bytes. Use a number between 32 and 65535. The default is 256. maxurls Specify maximum size of URL buffer. Use a number between 32 and 65535. The defaults is 256. maxdocsize Specify the maximum document size. Use a number between 256 and 2,147,483,647 bytes (2 gigabytes). The defaults is 2,000,000. http_proxy Specify the host name of http proxy server. Optionally specify port number with a colon in the form hostname:port. ftp_proxy Specify the host name of ftp proxy server. Optionally specify port number with a colon in the form hostname:port. no_proxy Specify the domain for no proxy server. Use a comma separated string of up to 16 domain names. 例子： create table urls(id number primary key, docs varchar2(2000)); insert into urls values(111555,'https://www.wendangku.net/doc/cb624757.html,'); insert into urls values(111556,'https://www.wendangku.net/doc/cb624757.html,'); insert into urls values(111557,'https://www.wendangku.net/doc/cb624757.html,'); insert into urls values(111558,'https://www.wendangku.net/doc/cb624757.html,');
commit; / --url datastore begin ctx_ddl.create_preference('URL_PREF','URL_DATASTORE'); ctx_ddl.set_attribute('URL_PREF','Timeout','300'); end; --建立索引 create index datastores_text on urls (docs) indextype is ctxsys.context parameters ( 'Datastore URL_PREF' ); select * from urls where contains(docs,'Aix')>0 若相关的url不存在，oracle并不会报错，只是查询的时候找不到数据而已。 oracle中仅仅保存被索引文档的url地址，如果文档本身发生了变化，必须要通过修改索引列（url地址列）的方式来告知oracle，被索引数据已经发生了变化。 6. User_datastore Use the USER_DATASTORE type to define stored procedures that synthesize documents during indexing. For example, a user procedure might synthesize author, date, and text columns into one document to have the author and date information be part of the indexed text. 7. Nested_datastore 全文索引支持将数据存储在嵌套表中 8. 参考脚本 --建立direct_store Create index myindex on mytable(docs) indextype is ctxsys.context parameters ('datastore ctxsys.default_datastore'); --建立mutil_column_datastore Begin Ctx_ddl.create_preference('my_multi', 'multi_column_datastore'); Ctx_ddl.set_attribute('my_multi', 'columns', 'doc1, doc2, doc3'); End; Create index idx_mytable on mytable1(doc1)indextype is ctxsys.context parameters('datastore my_multi') --建立file_datafilestore begin ctx_ddl.create_preference('COMMON_DIR','FILE_DATASTORE'); ctx_ddl.set_attribute('COMMON_DIR','PATH','/opt/tmp');
end; create index myindex on mytable1(docs) indextype is ctxsys.context parameters ('datastore COMMON_DIR'); --建立url_datastore begin ctx_ddl.create_preference('URL_PREF','URL_DATASTORE'); ctx_ddl.set_attribute('URL_PREF','Timeout','300'); end; create index datastores_text on urls (docs) indextype is ctxsys.context parameters
( 'Datastore URL_PREF' ); (5). Filter属性 过滤器负责将各种文件格式的数据转换为纯文本格式，索引管道中的其他组件只能处理纯文本数据，不能识别 microsoft word 或 excel 等文件格式，filter有charset_filter、inso_filter、null_filter、user_filter、procedure_filter几种类型。 CHARSET_FILTER Character set converting filter AUTO_FILTER Auto filter for filtering formatted documents NULL_FILTER No filtering required. Use for indexing plain text, HTML, or XML documentsMAIL_FILTER Use the MAIL_FILTER to transform RFC-822, RFC-2045 messages in to indexable text. USER_FILTER User-defined external filter to be used for custom filtering PROCEDURE_FILTER User-defined stored procedure filter to be used for custom filtering. 1. CHARSET_FILTER 把文档从非数据库字符转化成数据库字符（原文：Use the CHARSET_FILTER to convert documents from a non-database character set to the character set used by the database） create table hdocs ( id number primary key, fmt varchar2(10), cset varchar2(20), text varchar2(80) ); begin
cxt_ddl.create.preference('cs_filter', 'CHARSET_FILTER'); ctx_ddl.set_attribute('cs_filter', 'charset', 'UTF8'); end insert into hdocs values(1, 'text', 'WE8ISO8859P1', '/docs/iso.txt'); insert into hdocs values (2, 'text', 'UTF8', '/docs/utf8.txt'); commit; create index hdocsx on hdocs(text) indextype is ctxsys.context parameters ('datastore ctxsys.file_datastore filter cs_filter format column fmt charset column cset'); 2. NULL_FILTER 默认属性，不进行任何过滤 oracle不建议对html、xml和plain text使用auto_filter参数，oracle建议你使用null_filter和section group type --建立null filter create index myindex on docs(htmlfile) indextype is ctxsys.context parameters('filter ctxsys.null_filter section group ctxsys.html_section_group'); Filter的默认值会受到索引字段类型和datastore的类型的影响，对于存储在数据库中的varchar2、char和clob字段中的数据，oracle自动选择了null_filtel,若datastore的属性设置为file_datastore，oracle会选择 auto_filter作为默认值。 3. AUTO_FILTER 通用的过滤器，适用于大部分文档，包括PDF和Ms word，过滤器还会自动识别出plain-text, HTML, XHTML, SGML和XML文档 Create table my_filter (id number, docs varchar2(1000)); Insert into my_filter values (1, 'Expert Oracle Database Architecture.pdf'); Insert into my_filter values (2, '1.txt'); Insert into my_filter values (3, '2.doc'); commit; / --建立 file datastore Begin ctx_ddl.create_preference('test_filter', 'file_datastore'); ctx_ddl.set_attribute('test_filter', 'path', '/opt/tmp'); End; --错误信息表 select * from CTX_USER_INDEX_ERRORS --建立 auto filter
Create index idx_m_filter on my_filter (docs) indextype is ctxsys.context parameters ('datastore test_filter filter ctxsys.auto_filter'); select * from my_filter where contains(docs,'oracle')>0 AUT
O_FILTER能自动识别出大部分格式的文档，我们也可以显示的通过column来指定文档类型，有text,binary,ignore，设置为binary的文档使用auto_filter，设置为text的文档使用null_filter，设置为ignore的文档不进行索引。 create table hdocs (id number primary key,fmt varchar2(10),text varchar2(80)); insert into hdocs values(1, 'binary', '/docs/myword.doc'); insert in hdocs values (2, 'text', '/docs/index.html'); insert in hdocs values (2, 'ignore', '/docs/1.txt'); commit; create index hdocsx on hdocs(text) indextype is ctxsys.context parameters ('datastore ctxsys.file_datastore filter ctxsys.auto_filter format column fmt'); 4. MAIL_FILTER 通过mail_filter把RFC-822,RFC-2045信息转化成索引文本 限制： 文档必须是us-ascii 长度不能超过1024bytes document must be syntactically valid with regard to RFC-822 5. USER_FILTER Use the USER_FILTER type to specify an external filter for filtering documents in a column 6. PROCEDURE_FILTER Use the PROCEDURE_FILTER type to filter your documents with a stored procedure. The stored procedure is called each time a document needs to be filtered. 7. 参考脚本 --建立null filter create index myindex on docs(htmlfile) indextype is ctxsys.context parameters('filter ctxsys.null_filter section group ctxsys.html_section_group'); --建立 auto filter Create index idx_m_filter on my_filter (docs) indextype is ctxsys.context parameters ('datastore test_filter filter ctxsys.auto_filter');
Filter错误记录表：CTX_USER_INDEX_ERRORS (6). Lexer属性 Oracle全文检索的lexer属性用于处理各种不同的语言，最基本的英文使用basic_lexer，中文则可以使用chinese_vgram_lexer或chinese_lexer。 BASIC_LEXER 英文或西方语言（使用空格作为分词界定） MULTI_LEXER 多语言版本 CHINESE_VGRAM_LEXER中文版本 CHINESE_LEXER 更智能的中文版本 JAPANESE_VGRAM_LEXERLexer for extracting tokens from Japanese text. JAPANESE_LEXER Lexer for extracting tokens from Japanese text. KOREAN_MORPH_LEXER Lexer for extracting tokens from Korean text. USER_LEXER Lexer you create to index a particular language. WORLD_LEXER Lexer for indexing tables containing documents of different languages; autodetects languages in a document 1. Basic_lexer basic_lexer属性支持如英语、德语、荷兰语、挪威语、瑞典语等以空格作为界限的语言（原文：Use the BASIC_LEXER type to identify tokens for creating Text indexes for English and all other supported whitespace-delimited languages.） Create table my_lex (id number, docs varchar2(1000)); Insert into my_lex values (1, 'this is a example for the basic_lexer'); Insert into my_lex values (2, 'he following example sets Printjoin characters '); Insert into my_lex values (3, 'To create the INDEX with no_theme indexing and with printjoins characters'); Insert into my_lex values (4, '中华人民共和国'); Insert into my_lex values (5, '中国淘宝软件'); Inser
t into my_lex values (6, '测试basic_lexer是否支持中文'); Commit; / --建立basic_lexer begin ctx_ddl.create_preference('mylex', 'BASIC_LEXER');
ctx_ddl.set_attribute ('mylex', 'printjoins', '_-'); --保留_ -符号 ctx_ddl.set_attribute ( 'mylex', 'index_themes', 'NO'); ctx_ddl.set_attribute ( 'mylex', 'index_text', 'YES'); ctx_ddl.set_attribute ('mylex','mixed_case','yes'); --区分大小写 end; create index indx_m_lex on my_lex(docs) indextype is ctxsys.context parameters('lexer mylex'); Select id from my_lex where contains(docs, 'no_theme') > 0; select docs from my_lex where contains(docs,'中国')>0 2. Mutil_lexer 支持多种语言的文档，比如你可以利用这个lexer来定义包含Endlish,German和Japanese的文档（原文：Use MULTI_LEXER to index text columns that contain documents of different languages. For example, you can use this lexer to index a text column that stores English, German, and Japanese documents.）建立一个multi_lexer属性的索引，并通过language列设置需要索引的语言，Oracle会根据language列的内容去匹配add_sub_lexer过程中指定的语言标识符，如果匹配的上，就使用该sub_lexer作为索引的lexer，如果没有找到匹配的，就使用default语言作为索引的lexer列，注意客户端nls_language，可能会影响lexer的选择 Select * from v$nls_parameters where parameter = 'NLS_LANGUAGE'; alter session set nls_language='simplified chinese'; alter session set nls_language='american'; create table globaldoc ( doc_id number primary key,lang varchar2(3),text clob); --建立multi_lexer begin ctx_ddl.create_preference('english_lexer','basic_lexer'); ctx_ddl.set_attribute('english_lexer','index_themes','yes'); ctx_ddl.set_attribute('english_lexer','theme_language','english'); ctx_ddl.create_preference('german_lexer','basic_lexer'); ctx_ddl.set_attribute('german_lexer','composite','german'); ctx_ddl.set_attribute('german_lexer','mixed_case','yes'); ctx_ddl.set_attribute('german_lexer','alternate_spelling','german'); ctx_ddl.create_preference('japanese_lexer','japanese_vgram_lexer'); ctx_ddl.create_preference('global_lexer', 'multi_lexer'); ctx_ddl.add_sub_lexer('global_lexer','default','english_lexer'); ctx_ddl.add_sub_lexer('global_lexer','german','german_lexer','ger'); ctx_ddl.add_sub_lexer('global_lexer','japanese','japanese_lexer','jpn'); end; create index globalx on globaldoc(text) indextype is ctxsys.context parameters ('lexer global_lexer language column lang'); 3.
chinese_vgram_lexerchinese_lexer basic_lexer只能识别出被空格、标点和回车符分隔出来的部分，如果要对中文内容进行索引的话，就必须使用chinese_vgram_lexer或是chinese_lexer Chinese_lexer相比chinese_vgram_lexer有如下的优点： 产生的索引更小 更好的查询响应时间 产生更接近真实的索引切词，使得查询精度更高 支持停用词 因为chinese_lexer采用不同的算法来标记tokens, 建立索引的时间
要比chinese_vgram_lexer长. 字符集：支持al32utf8，zhs16cgb231280，zhs16gbk，zhs32gb18030，zht32euc，zht16big5 zht32tris， zht16mswin950，zht16hkscs，utf8 --建立chinese lexer Begin ctx_ddl.create_preference('my_chinese_vgram_lexer', 'chinese_vgram_lexer'); ctx_ddl.create_preference('my_chinese_lexer', 'chinese_lexer'); End; -- chinese_vgram_lexer Create index ind_m_lex1 on my_lex(docs) indextype is ctxsys.context Parameters ('lexer ctxsys.my_chinese_vgram_lexer'); Select * from t where contains(docs, '中国') > 0; -- chinese_lexer Create index ind_m_lex2 on my_lex(docs) indextype is ctxsys.context Parameters ('lexer ctxsys.my_chinese_lexer'); Select * from t where contains(docs, '中国') > 0; 4. User_lexer Use USER_LEXER to plug in your own language-specific lexing solution. This enables you to define lexers for languages that are not supported by Oracle Text. It also enables you to define a new lexer for a language that is supported but whose lexer is inappropriate for your application. 5 Default_lexer 如果数据库在建立的时候指定的是中文则default_lexer为chinese_vgram_lexer，如果是英文，则default_lexer为basic_lexer 6. Query_procedure This callback stored procedure is called by Oracle Text as needed to tokenize words in the query. A space-delimited group of characters (excluding the query operators) in the query will be identified by Oracle Text as a word. 7.
参考脚本 --建立basic_lexer begin ctx_ddl.create_preference('mylex', 'BASIC_LEXER'); ctx_ddl.set_attribute ('mylex', 'printjoins', '_-'); --保留_ -符号 ctx_ddl.set_attribute ('mylex','mixed_case','yes'); --区分大小写 end; create index indx_m_lex on my_lex(docs) indextype is ctxsys.context parameters('lexer mylex'); --建立 chinese_vgram_lexer或是chinese_lexer Begin ctx_ddl.create_preference('my_chinese_vgram_lexer', 'chinese_vgram_lexer'); ctx_ddl.create_preference('my_chinese_lexer', 'chinese_lexer'); End; -- chinese_vgram_lexer Create index ind_m_lex1 on my_lex(docs) indextype is ctxsys.context Parameters ('lexer ctxsys.my_chinese_vgram_lexer'); (7). Section Group属性 Section group支持查询包含内部结构的文档（如html、xml文档等），可以指定对文档的某一部分进行查询,你可以将查询范围限定在标题head中。在html、xml等类似结构的文档中，除了用来显示的内容外，还包括了大量用于控制结构的标识，而这些标识可能是不希望被索引的，这就是section group的一个主要功能(原文：In order to issue WITHIN queries on document sections, you must create a section group before you define your sections) NULL_SECTION_GROUP 系统默认参数，不进行任何过滤，有SENTENCE ，PARAGRAPH属性 BASIC_SECTION_GROUP 支持
g/end-tag pair in an XML document. The section names derived from XML tags are case sensitive as in XML. Attribute sections are created automatically for XML tags that have attributes. Attribute sections are named in the form tag@attribute. Stop sections, empty tags, processing instructions, and
comments are not indexed. The following limitations apply to automatic section groups:? You cannot add zone, field, or special sections to an automatic section group. ? You can define a stop section that applies only to one particular type; that is, if you have two different XML DTDs, both of which use a tag called FOO, you can define (TYPE1)FOO to be stopped, but(TYPE2)FOO to not be stopped. ? The length of the indexed tags, including prefix and namespace, cannot exceed 64 bytes. Tags longer than this are not indexed. PATH_SECTION_GROUP Use this group type to index XML documents. Behaves like the AUTO_SECTION_GROUP. The difference is that with this section group you can do path searching with the INPATH and HASPATH operators. Queries are also case-sensitive for tag and attribute names. Stop sections are not allowed. NEWS_SECTION_GROUP Use this group for defining sections in newsgroup formatted documents according to RFC 1036. 1. Null_section_group 系统默认，不进行任何节的过滤 例子： Create table my_sec (id number, docs varchar2(100)); Insert into my_sec values (1, 'a simple section group, test null_section_group attribute.'); Insert into my_sec values (2, 'this record one, can be query in nornal'); Insert into my_sec values (4, 'this record are tested for the query in paragraph'); Commit; / --定义null_section_group Create index ind_m_sec on my_sec(docs) indextype is ctxsys.context parameters ('section group ctxsys.null_section_group'); Select * from my_sec where contains(docs, 'record and query') > 0;
--要预先定义sentence或paragraph'，否则查询会出错 Select * from my_sec where contains(docs, '(record and query) within sentence') > 0; Begin ctx_ddl.create_section_group('test_null', 'null_section_group'); ctx_ddl.add_special_section('test_null', 'sentence'); ctx_ddl.add_special_section('test_null', 'paragraph'); End; drop index ind_m_sec; Create index ind_m_sec on my_sec(docs) indextype is ctxsys.context parameters ('section group test_null'); Select * from my_sec where contains(docs, '(record and query) within sentence') > 0; Select * from my_sec where contains(docs, '(record and query) within paragraph') > 0; 2. Basic_section_group basic_section_group才是支持节搜索的最基础的一种属性，但是它只支持以
Commit; / Create index ind_my_sec1 on my_sec1(docs) indextype is ctxsys.context; Select * from my_sec1 where contains (docs, 'heading') > 0; --定义basic_section_group Begin Ctx_ddl.create_section_group('test_basic', 'basic_section_group'); End; drop index ind_my_sec1; Create index ind_my_sec1 on my_sec1(docs) indextype is ctxsys.context parameters ('section group test_basic'); Select * from my_sec1 where contains (docs, 'heading') > 0; Select * from my_sec1 where contains (docs, 'context') > 0; Select * from my_sec1 where contains (docs, 'use') > 0; 节搜索的另一个主要功能就是可以限制查询的范围，上面的文档包含了两部分，标题和正文，其中标题使用标签
Begin ctx_ddl.add_zone_section('test_basic', 'head', 'heading'); End; Create index ind_my_sec1 on my_sec1(docs) indextype is ctxsys.context parameters ('section group test_basic'); Select * from my_sec1 where contains (docs, 'title') > 0; --在head里面查询 Select * from my_sec1 where contains (docs, 'title within head') > 0; 3. Html_section_group Html文档具有很多不规范的表示方法，oracle建议使用html_section_group以便能够得到更好的识别 --定义html_section_group begin ctx_ddl.create_section_group('htmgroup', 'HTML_SECTION_GROUP'); end; create index myindex on docs(htmlfile) indextype is ctxsys.context parameters('filter ctxsys.null_filter section group htmgroup'); 无论是field_section还是zone_section，表示文档的tag标签都是大小写敏感的，其大小写需要和原文中匹配 4. Xml_section_group Xml文档的格式要求比html文档严谨、规范，这也使得xml_section_group比html_section_group具有了更多的功能 : Create table my_sec2 (id number, docs varchar2(1000)); Insert into my_sec2 values (1, 'context.xml'); commit; / --定义xml_section_group Begin ctx_ddl.create_preference('test_file', 'file_datastore'); ctx_ddl.set_attribute('test_file', 'path', '/opt/tmp'); ctx_ddl.create_section_group('test_html', 'html_section_group'); ctx_ddl.create_section_group('test_xml', 'xml_section_group'); End; Create index ind_t_docs on my_sec2 (docs) indextype is ctxsys.context parameters('datastore ctxsys.test_file filter ctxsys.null_filter section group ctxsys.test_xml')
Select * from my_sec2 where contains (docs, 'complete') > 0; Begin ctx_ddl.add_attr_section('test_xml', 'name', 'const@name'); End; Select * from my_sec2 where contains (docs, 'complete within name') > 0; 5. Auto_section_group Xml_section_group的增强型，对于xml_section_group用户需要自己添加需要定义的节组，而使用auto_section_group，则oracle会自动添加节组以及属性信息 6. Path_section_group 和auto_section_group十分类似，path_section_group比auto_section_group增加了haspath和 inpath操作，但是path_secti
on_group不支持add_stop_section属性 7. 参考脚本 --建立null_section_group Create index ind_m_sec on my_sec(docs) indextype is ctxsys.context parameters ('section group ctxsys.null_section_group'); --建立basic_section_group Begin Ctx_ddl.create_section_group('test_basic', 'basic_section_group'); End; Begin ctx_ddl.add_zone_section('test_basic', 'head', 'heading'); --设定节查询 End; Create index ind_my_sec1 on my_sec1(docs) indextype is ctxsys.context parameters ('section group test_basic'); --建立Html_section_group begin ctx_ddl.create_section_group('htmgroup', 'HTML_SECTION_GROUP'); end; create index myindex on docs(htmlfile) indextype is ctxsys.context parameters('filter ctxsys.null_filter section group htmgroup'); --建立Xml_section_group Begin ctx_ddl.create_section_group('test_xml', 'xml_section_group'); End;
Create index ind_t_docs on my_sec2 (docs) indextype is ctxsys.context parameters('filter ctxsys.null_filter section group ctxsys.test_xml') (8). Storage属性 Oracle全文检索通常会生成一系列的辅助表，生成规则是dr$+索引名+$+表用途标识，由于这些表是oracle自动生成的，通常没有办法为这些表指定存储空间。为构造text索引所生成的辅助表指定表空间、存储参数（use the storage preference to specify tablespace and creation parameters for tables associated with a text index），oracle提供了单一的存储类型basic_storage。 在mytable1表中建立了全文索检索myindex，系统中会自动产生如下5个表：DR$MYINDEX$I,DR$MYINDEX$K,DR$MYINDEX$R,DR$MYINDEX$X,MYTABLE1 AttributeAttribute Valuei_table_clause 指定dr$indexname$I的存储参数 k_table_clause 指定dr$indexname$K的存储参数，K表存储键值映射表（keymap） r_table_clause 指定dr$indexname$R的存储参数，R表存储rowid表 The default clause is: 'LOB(DATA) STORE AS (CACHE)' n_table_clause 指定dr$indexname$N的存储参数，N表存储负键值链表（negative list ）i_index_clause 指定dr$indexname$X 的存储参数，I表存储索引数据表(index data table） The default clause is: 'COMPRESS 2' which instructs Oracle Text to compress this index table. 另：oracle建议使用COMPRESS 2以节省空间存储 p_table_clause 只有在设置BASIC_WORDLIST的SUBSTRING_INDEX属性才有用，P 表示是索引组织表 1. 参考脚本 --建立basic storage Begin Ctx_ddl.create_preference('mystore', 'basic_storage'); --建立storage Ctx_ddl.set_attribute('mystore', --设置参数 'i_table_clause', 'tablespace foo storage (initial 1k)'); Ctx_ddl.set_attribute('mystore', 'k_table_clause', 'tablespace foo storage (initial 1k)'); Ctx_ddl.set_attribute('mystore', 'r_table_clause', 'tablespace users storage (initial 1k) lob
(data) store as (disable storage in row cache)');
Ctx_ddl.set_attribute('mystore', 'n_table_clause', 'tablespace foo storage (initial 1k)'); Ctx_ddl.set_attribute('mystore', 'i_index_clause', 'tablespace foo storage (initial 1k) compress 2'); Ctx_ddl.set_attribute('mystore', 'p_table_clause', 'tablespace foo storage (initial 1k)'); End; --建立索引 Create index indx_m_word on my_word(docs) indextype is ctxsys.context parameters('storage mystore'); (9). Wordlist属性 Oracle全文检索的wordlist属性用来设置模糊查询和同词根查询，wordlist属性还支持子查询和前缀查询，oracle的wordlist属性只有basic_wordlist一种（原文：Use the wordlist preference to enable the query options such as stemming, fuzzy matching for your language. You can also use the wordlist preference to enable substring and prefix indexing, which improves performance for wildcard queries with CONTAINS and CATSEARCH.） 1. 例子： Create table my_word (id number, docs varchar2(1000)); Insert into my_word values (1, 'Specify the stemmer used for word stemming in Text queries'); Insert into my_word values (2, 'Specify which fuzzy matching routines are used for the column'); Insert into my_word values (3, 'Fuzzy matching is currently supported for English'); Insert into my_word values (4, 'Specify a default lower limit of fuzzy score. Specify a number between 0 and 80'); Insert into my_word values (5, 'Specify TRUE for Oracle Text to create a substring index matched.'); commit; / --建立wordlist Begin ctx_ddl.drop_preference('mywordlist'); ctx_ddl.create_preference('mywordlist', 'basic_wordlist'); ctx_ddl.set_attribute('mywordlist','fuzzy_match','english'); --模糊匹配，英语
ctx_ddl.set_attribute('mywordlist','fuzzy_score','0'); --匹配得分 ctx_ddl.set_attribute('mywordlist','fuzzy_numresults','5000'); ctx_ddl.set_attribute('mywordlist','substring_index','true'); --左查询，适用%to,%to% ctx_ddl.set_attribute('mywordlist','stemmer','english'); --词根 ctx_ddl.set_attribute('mywordlist', 'prefix_index', 'true'); --右查询，适用t0% End; Create index indx_m_word on my_word(docs) indextype is ctxsys.context parameters('wordlist mywordlist'); --例子 Select docs from my_word where contains(docs,'$match')>0 ; --词根查询 Select docs from my_word where contains(docs,'MA%')>0; --匹配查询 2. document上的例子 create table quick( quick_id number primary key, text varchar(80) ); --- insert a row with 10 expansions for 'tire%' insert into quick ( quick_id, text ) values ( 1, 'tire tirea tireb tirec tired tiree tiref tireg tireh tirei tirej'); commit; / begin Ctx_Ddl.Create_Preference('wildcard_pref', 'BASIC_WORDLIST'); ctx_ddl.set_attribute('wildcard_pref', 'wildcard_maxterms', 100) ; end; / create index wildcard_idx on quick(text) indextype is ctxsys.context
parameters ('Wordlist wildcard_pref') ; select quick_id from quick where contains ( text, 'tire%' ) > 0; drop index wildcard_idx ; begin Ctx_Ddl.Drop_Preference('wildcard_pref'); Ctx_Ddl.Create_Preference('wildcard_pref', 'BASIC_WORDLIST'); ctx_ddl.set_attribute('wildcard_pref', 'wildcard_maxterms', 5) ;--限制最大的匹配数，如果超过这个数量，查询出现报错 end; / create index wildcard_idx on quick(text) indextype is ctxsys.context parameters ('Wordlist wildcard_pref') ; select quick_id from quick where contains ( text, 'tire%' ) > 0; 3. 参考脚本 --建立wordlist
begin ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST'); ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE'); --定义wordlist的参数 end; --删除wordlist begin ctx_ddl.drop_preference('mywordlist'); end; (9). Stoplist属性 Stoplist允许屏蔽某些常用的词，比如is，a，this，对这些词进行索引用处不大，系统默认会使用和数据库语言相对应的停用词库（原文：Stoplists identify the words in your language that are not to be indexed. In English, you can also identify stopthemes that are not to be indexed. By default, the system indexes text using the system-supplied stoplist that corresponds to your database language.），Oracle text提供最常用的停用词库语言包括English, French, German, Spanish, Chinese, Dutch, and Danish 分别有basic_stoplist，empty_stoplist，default_stoplist，multi_stoplist几种类型 1. Basic_stoplist 建立用户自定义的停用词库，文档中关于stoplist的介绍相当少，只有寥寥的数行 例子： Create table my_stop (id number, docs varchar2(1000)); Insert into my_stop values (1, 'Stoplists identify the words in your language that are not to be indexed.'); Insert into my_stop values (2, 'ou can also identify stopthemes that are not to be indexed'); Commit; / --建立basic stoplist Begin Ctx_ddl.create_stoplist('test_stoplist', 'basic_stoplist'); End; Create index ind_m_stop on my_stop(docs) indextype is ctxsys.context parameters ('stoplist test_stoplist'); Select * from my_stop where contains(docs, 'words') > 0;
Begin Ctx_ddl.add_stopword('test_stoplist', 'language'); --添加停用词 ctx_ddl.sync_index('ind_m_stop', '2m'); --同步索引 End; Select * from my_stop where contains(docs, 'language') > 0; --添加停用词，同步索引后发现还是能查到，需要重新建立索引才能生效 Drop index ind_m_stop; Create index ind_m_stop on my_stop(docs) indextype is ctxsys.context parameters ('stoplist test_stoplist'); Select * from my_stop where contains(docs, 'language') > 0; --停用词生效 添加停用词，同步索引后发现还是能查到，需要索引才能生效。 2. Empty_stoplist 停用词库没有任何停用词，适用于不需要过滤的查询中，如不需要过滤is this,a等。 2. Default_stoplist 建立basic_stoplist后，里面不包含任何的停用词，而default_stopl
ist在basic_stoplist的基础上增加了预定义的默认停用词，对于不同的语言，默认的停用词库数据也不一样 例子： Create table my_stop (id number, docs varchar2(1000)); Insert into my_stop values (1, 'Stoplists identify the words in your language that are not to be indexed.'); Insert into my_stop values (2, 'ou can also identify stopthemes that are not to be indexed'); Commit; / --建立lexer，不同lexer属性会默认不同的停用词库 Begin ctx_ddl.create_preference('test_b_lexer', 'basic_lexer'); End; drop index ind_m_word； --建立默认停用词default_stoplist Create index ind_m_word on my_stop(docs) indextype is ctxsys.context Parameters ('lexer test_b_lexer stoplist ctxsys.default_stoplist'); --检查默认词库中是否存在 Select * from my_stop where contains(docs, 'the') > 0; Select * from my_stop where contains(docs, 'stopthemes') > 0; --往默认词库中添加停用词 conn ctxsys/ctxsys; Begin ctx_ddl.add_stopword('default_stoplist', 'stopthemes'); --增加停用词
ctx_ddl.add_stopword('default_stoplist', 'words'); ctx_ddl.remove_stopword('default_stoplist', 'words');--删除停用词 End; --添加后需重新建立索引才能生效 conn oratext/oratext; drop index ind_m_word; Create index ind_m_word on my_stop(docs) indextype is ctxsys.context Parameters ('lexer test_b_lexer stoplist ctxsys.default_stoplist'); Select * from my_stop where contains(docs, 'words') > 0; Select * from my_stop where contains(docs, 'stopthemes') > 0; --相关数据字典 Select * from ctx_preferences where pre_name = 'DEFAULT_LEXER'; Select * from ctx_stopwords where spw_stoplist = 'DEFAULT_STOPLIST'; 4. multi_stoplist 多语言停用词，适用在文档中包含不同的语言（A multi-language stoplist is useful when you use the MULTI_LEXER to index a table that contains documents in different languages, such as English, German, and Japanese） 增加停用词时，可以为停用词指定某种语言，只对指定的语言生效，默认情况下停用词对任何语言都是生效的。 --建立multi_stoplist begin ctx_ddl.create_stoplist('multistop1', 'MULTI_STOPLIST'); ctx_ddl.add_stopword('multistop1', 'Die', 'german'); ctx_ddl.add_stopword('multistop1', 'Or', 'english'); end; 添加停用词，同步索引后发现还是能查到，需要索引才能生效。 5. 参考脚本 --建立stoplist： Begin Ctx_ddl.create_stoplist('test_stoplist', 'basic_stoplist'); End; --删除stoplist： begin ctx_ddl.drop_stoplist(' test_stoplist '); end; --增加停用词 ctx_ddl.add_stopword('default_stoplist', 'stopthemes'); --增加停用词 --删除停用词
ctx_ddl.remove_stopword('default_stoplist', 'words');--删除停用词 (10). Theme主题查询 主题查询的概念是根据文档的含义，而不仅仅是根据某个词的匹配程度来返回查询结果的。比如查询about(’US politics’)可能会返回‘US presidential elections’ 和 ‘US foreign policy’之类的结果
（原文：An ABOUT query is a query on a document theme. A document theme is a concept that is sufficiently developed in the text. For example, an ABOUT query on US politics might return documents containing information about US presidential elections and US foreign policy. Documents need not contain the exact phrase US politics to be returned.） 10g只支持两种主题查询语言：English，French 例子： --在context中启用主题查询 BEGIN CTX_DDL.CREATE_PREFERENCE('TEST_ABOUT', 'BASIC_LEXER'); CTX_DDL.SET_ATTRIBUTE('TEST_ABOUT', 'INDEX_THEMES', 'YES'); CTX_DDL.SET_ATTRIBUTE('TEST_ABOUT', 'INDEX_TEXT', 'YES'); END; CREATE INDEX IND_m_about ON my_about(DOCS) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER CTXSYS.TEST_ABOUT'); --查询 SELECT * FROM my_about WHERE CONTAINS(DOCS, 'ABOUT(US politics)') > 0; (11). Highlighting 高亮显示 并不是说将内容高亮显示，而是返回所有命中词在文档中的位置和命中词本身的长度。这样用户在得到文档的同时，还得到了需要高亮显示的内容的长度和偏移量，真正的显示工作需要由用户来完成（原文：In Oracle Text query applications, you can present selected documents with query terms highlighted for text queries or with themes highlighted for ABOUT queries） 例子： Create table my_high (id number primary key, docs varchar2(1000)); insert into my_high values (1, 'this is a oracle text example. And oracle is the key word.');
insert into my_high values (2, '
$ORACLE_HOME/ctx/sample/script/drjobdml.sql --后台同步脚本（9i中有，10g不知道放哪儿了，文档有问题） SQL> @drjobdml myindex 360 --表示以周期360分钟同步索引myindex 或通过创建索引加参数实现 --表示每小时同步一次，内存16m CREATE INDEX IND_m_high ON my_high(DOCS) INDEXTYPE IS CTXSYS.CONTEXT parameters ('sync (EVERY "TRUNC(SYSDATE)+ 1/24") memory 16m ') parallel 2 online; (确认这个时间间隔内索引同步能完成，否则，sync job将处于挂起状态) --还可以是 sync
(manual) --手工同步，默认 sync (on commit) --dml后立即同步 --通过job定义同步 declare job number; begin dbms_job.submit(job, 'ctx_ddl.sync_index(''ind_m_high'');', --索引名 interval => 'SYSDATE+1/1440'); --1分钟一次 commit; dbms_output.put_line('job ' || job || ' has been submitted.'); end; 4.索引碎片： 刚开始建立索引后，DOG可能是如下的条目 DOG DOC1 DOC3 DOC5 新的文档增加到表后，新行会同步到索引中去， 假设新文档中Doc7中的DOG被同步到索引中去，DOG可能变成如下条目 DOG DOC1 DOC3 DOC5 DOG DOC7 随后的DML操作可能变成： DOG DOC1 DOC3 DOC5 DOG DOC7 DOG DOC9 DOG DOC11 这就产生了碎片，需要进行索引的优化 查看索引碎片 create table output (result CLOB); declare x clob := null; begin ctx_report.index_stats('idx_auction', x); insert into output values (x); commit; dbms_lob.freetemporary(x);
end; select * from output 5.索引优化： 快速fast优化和全部full优化的区别是，快速优化不会删除没用的、过期的数据，而full会删除老的数据(deleted rows) --快速优化 begin ctx_ddl.optimize_index('myidx','FAST'); end; --全部优化 begin ctx_ddl.optimize_index('myidx','FULL'); end; --对单个记号进行优化，默认是full模式 begin ctx_ddl.optimize_index('myidx','token', TOKEN=>'Oracle'); end; 6.Online参数限制： at the very beginning or very end of this process, dml might fail. online is supported for context indexes only. online cannot be used with parallel. --online索引的时候后面必须要加parameters，否则会失败 alter index IND_m_high rebuild online parameters ('sync memory 16m' ) 7.更改索引的属性，但不进行索引重建 Replaces the existing preference class settings, including SYNC parameters, of the index with the settings from new_preference. Only index preferences and attributes are replaced. The index is not rebuilt. ALTER INDEX myidx REBUILD PARAMETERS('replace metadata transactional'); alter index idx_auction_db1 rebuild PARAMETERS('REPLACE METADATA SYNC(ON COMMIT)') ; 参考文档： https://www.wendangku.net/doc/cb624757.html,/docs/cd/B19306_01/text.102/b14218/csql.htm#CIHBFDCE 8.Score： --表示得分，分值越高，表示查到的数据越精确 SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'oracle', 1) > 0; --根据得分来排序 SELECT SCORE(1), title from news WHERE CONTAINS(text, 'oracle', 1) > 0 AND issue_date >= ('01-OCT-97') ORDER BY SCORE(1) DESC;
9.分析表： ANALYZE TABLE
- oracle常用命令大全
- 全文检索系统整体方案说明
- oracle数据库索引的理解与总结
- 整合全文检索系统解决方案
- ORACLE全文索引同步优化存储过程和JOB(精品)
- 一种全文检索技术的思路和实现
- Oracle查询语句基本命令一
- 全文检索系统整体方案
- OracleBtree、位图、全文索引三大索引性能比较及优缺点汇总分析
- oracle域索引简介
- 使用Oracle全文索引搜索文本
- 史上最全资料(oracle全文检索说明(全))
- Oracle全文索引技术-使用说明文档解析
- 基于语义的全文搜索引擎
- 全文检索系统整体方案
- 全文检索系统整体方案设计
- 全文检索方案
- oracle全文检索
- Oracle查询表名及模糊查询
- 全文检索系统整体方案设计