邪恶八进制信息安全团队技术讨论组 » 常用数据库类{ Database Security } » 巨人脚下[ Oracle ] » [转载]oracle全文检索技术应用

ring04h 2006-6-27 17:30

[转载]oracle全文检索技术应用

<p>信息来源: IT168</p><p> 在Oracle8.0.x 中称为ConText ；在Oracle8i 中称为interMedia Text ；Oracle9i 中称为Oracle Text。<br /> Oracle Text 的应用领域有很多：<br /> 搜索文本：需要快捷有效搜索文本数据的应用程序<br /> 管理多种文档：允许搜索各种混和文档格式的应用程序,包括ord,excel,lotus 等<br /> 从多种数据源中检索文本：不仅来自Oracle 数据库中的文本数据,而且可以来自Internet和文件系统的文本数据<br /> 搜索XML 应用程序</p><p /><p /><div id="3"> <strong>2、搜索文本</strong></div><p /><p /><div id="4"> 不使用Oracle text 功能,也有很多方法可以在Oracle 数据库中搜索文本.可以使用标准的INSTR 函数和LIKE 操作符实现.</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="5">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">    connect scott</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">tiger
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">insert</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">into</span><span style="COLOR: #000000"> dept (deptno,dname,loc) </span><span style="COLOR: #0000ff">values</span><span style="COLOR: #000000"> (</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">50</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">公安软件事业部</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">四楼</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">insert</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">into</span><span style="COLOR: #000000"> dept (deptno,dname,loc) </span><span style="COLOR: #0000ff">values</span><span style="COLOR: #000000"> (</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">60</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">社保软件事业部</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">三楼</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">insert</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">into</span><span style="COLOR: #000000"> dept (deptno,dname,loc) </span><span style="COLOR: #0000ff">values</span><span style="COLOR: #000000"> (</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">70</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">人事部</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">二楼</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">insert</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">into</span><span style="COLOR: #000000"> dept (deptno,dname,loc) </span><span style="COLOR: #0000ff">values</span><span style="COLOR: #000000"> (</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">80</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">系统集成部</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">一楼</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">commit</span><span style="COLOR: #000000">;</span></div>
</div></pre></div><p /><div id="6"> -----------------------常用办法---------------------------------------</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="7">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> dept  </span><span style="COLOR: #0000ff">WHERE</span><span style="COLOR: #000000"> INSTR (dname, </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">软件</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">) </span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> dept  </span><span style="COLOR: #0000ff">WHERE</span><span style="COLOR: #000000"> dname </span><span style="COLOR: #808080">LIKE</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">%软件%</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">;</span></div>
</div></pre></div><p /><div id="8"> -----------------------全文检索办法------------------------------------</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="9">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">    connect ctxsys</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">ctxsys
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">BEGIN</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">设置词法分析器</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">    ctx_ddl.create_preference (</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">my_lexer</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">, </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">chinese_vgram_lexer</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">设置储存参数</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">    ctx_ddl.create_preference (</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">mystore</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">, </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">BASIC_STORAGE</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    ctx_ddl.set_attribute (</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">mystore</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">, </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">I_TABLE_CLAUSE</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">, </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">tablespace indx </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    ctx_ddl.set_attribute (</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">mystore</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">, </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">I_INDEX_CLAUSE</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">, </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">tablespace indx compress 2 </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">END</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    connect scott</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">tiger
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">CREATE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INDEX</span><span style="COLOR: #000000"> ind_dept_dname
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">ON</span><span style="COLOR: #000000"> dept( dname )
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    INDEXTYPE </span><span style="COLOR: #0000ff">is</span><span style="COLOR: #000000"> CTXSYS.CONTEXT
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    parameters(</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">lexer ctxsys.my_lexer storage ctxsys.mystore</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> dept </span><span style="COLOR: #0000ff">WHERE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">contains</span><span style="COLOR: #000000">(dname,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">软件</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">)</span><span style="COLOR: #808080">></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> dept </span><span style="COLOR: #0000ff">WHERE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">contains</span><span style="COLOR: #000000"> (dname, </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">软件 or 集成</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">) </span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">按分值排序</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"> score (</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">), a.</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> dept a </span><span style="COLOR: #0000ff">WHERE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">contains</span><span style="COLOR: #000000"> (dname, </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">公安 or 软件 or 事</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">, </span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">) </span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">ORDER</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">BY</span><span style="COLOR: #000000"> score (</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">) </span><span style="COLOR: #0000ff">DESC</span><span style="COLOR: #000000">;</span></div>
</div></pre></div><p /><div id="10"> -----------------------简单介绍-------------------------------------------</div><p /><p /><div id="11"> 1、Oracle Text 索引将文本打碎分成很多的记号（token）.<br /> 例如文本‘I Love <a href="http://www.itpub.net’/"><font color="#010000">www.itpub.net’</font></a>将会被分成I ,LOVE,WWW,ITPUB,NET 这样的记号（token）。</div><p /><p /><div id="12"> 2、在索引建好后，我们可以在该用户下查到Oracle 自动产生了以下几个表：<br /> （假设索引名为myindex）：DR$myindex$I、DR$myindex$K、DR$myindex$R、DR$myindex$N 其中以I表最重要</div><p /><p /><div id="13"> 3、建立索引的语法</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="14">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">CREATE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INDEX</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">schema.</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #0000ff">index</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">schema.</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #0000ff">table</span><span style="COLOR: #000000">(</span><span style="COLOR: #0000ff">column</span><span style="COLOR: #000000">) INDEXTYPE
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">IS</span><span style="COLOR: #000000"> ctxsys.context </span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">ONLINE</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    LOCAL </span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">(PARTITION [partition</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">PARAMETERS('paramstring')</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">, PARTITION [partition</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">PARAMETERS('paramstring')</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #000000">])]
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">PARAMETERS(paramstring)</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">PARALLEL n</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">UNUSABLE</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #000000">;</span></div>
</div></pre></div><p /><div id="15"> 4、如果想在两列(多列)中查找某个单词是否存在这两个列之一。<br /> 方法一：在不同的列上建索引<br /> 方法二：定制<br /> 连接ctxsys，只有ctxsys才能建立MULTI_COLUMN_DATASTORE</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="16">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">EXEC</span><span style="COLOR: #000000"> ctx_ddl.create_preference(</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">mymds</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">, </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">MULTI_COLUMN_DATASTORE</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">EXEC</span><span style="COLOR: #000000"> ctx_ddl.set_attribute(</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">mymds</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">, </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">columns</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">, </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">dname, loc</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);</span></div>
</div></pre></div><p /><div id="17"> 连接scott</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="18">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">CREATE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INDEX</span><span style="COLOR: #000000"> SCOTT.IND_DEPT_DNAME
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">ON</span><span style="COLOR: #000000"> SCOTT.DEPT(DNAME) INDEXTYPE </span><span style="COLOR: #0000ff">IS</span><span style="COLOR: #000000"> CTXSYS.CONTEXT
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    PARAMETERS (</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000"> DATASTORE CTXSYS.MYMDS LEXER SCOTT.MY_LEXER</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> dept
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">WHERE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">contains</span><span style="COLOR: #000000">(dname,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">人事 or 四楼</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">)</span><span style="COLOR: #808080">></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000">;</span></div>
</div></pre></div><p /><div id="19"> 5、指定存储参数<br /> 学习使用ORACLE TEXT MANAGER;</div><p /><p /><div id="20"> 6、设置词法分析器<br /> 学习使用ORACLE TEXT MANAGER;</div><p /><p /><div id="21"> 7、索引维护<br /> 对于CTXSYS.CONTEXT 索引，当应用程序对基表进行DML 操作后，对基表的索引维护是必须的。索引维护包括索引同步和索引优化。<br /> 当基表中的被索引文档发生insert、update、delete 操作的时候，基表的改变并不能马上影响到索引上直到同步索引。<br /> 7.1 同步和优化方法: 可以使用Oracle 提供的ctx_ddl 包同步和优化索引。<br /> 同步语法：</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="22">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">    ctx_ddl.sync_index(
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    idx_name </span><span style="COLOR: #808080">IN</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #000000">VARCHAR2</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">DEFAULT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">NULL</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    memory </span><span style="COLOR: #808080">IN</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #000000">VARCHAR2</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">DEFAULT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">NULL</span><span style="COLOR: #000000">,
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    part_name </span><span style="COLOR: #808080">IN</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #000000">VARCHAR2</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">DEFAULT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">NULL</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    parallel_degree </span><span style="COLOR: #808080">IN</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #000000">NUMBER</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">DEFAULT</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">);</span></div>
</div></pre></div><p /><div id="23"> idx_name 索引名称<br /> memory 指定同步索引需要的内存。默认是系统参数DEFAULT_INDEX_MEMORY 。指定一个大的内存时候可以加快索引效率和查询速度，且索引有较少的碎片<br /> part_name 同步哪个分区索引。<br /> parallel_degree 并行同步索引。设置并行度。<br /> 同步例子：</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="24">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">insert</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">into</span><span style="COLOR: #000000"> dept (deptno,dname,loc) </span><span style="COLOR: #0000ff">values</span><span style="COLOR: #000000"> (</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">71</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">销售部</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">二楼</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);</span></div>
</div></pre></div><p /><div id="25"> 使用2M 内存同步索引myindex:</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="26">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">BEGIN</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    ctx_ddl.sync_index (</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">ind_dept_dname</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">, </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">2M</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">END</span><span style="COLOR: #000000">;</span></div>
</div></pre></div><p /><div id="27"> 优化例子：</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="28">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">BEGIN</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    ctx_ddl.optimize_index (</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">ind_dept_dname</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">, </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">full</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">END</span><span style="COLOR: #000000">;</span></div>
</div></pre></div><p /><div id="29"> 7.2 使用job 定时同步和优化<br /> 用以下的两个job 来完成(该job 要建在和表同一个用户下) :<br /> -- 同步:</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="30">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">    VARIABLE jobno </span><span style="FONT-WEIGHT: bold; COLOR: #000000">number</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">BEGIN</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    DBMS_JOB.SUBMIT(:jobno,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">ctx_ddl.sync_index(</span><span style="COLOR: #ff0000">''</span><span style="COLOR: #ff0000">ind_dept_dname</span><span style="COLOR: #ff0000">''</span><span style="COLOR: #ff0000">);</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">,SYSDATE, </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">SYSDATE + (1/24/4)</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">commit</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">END</span><span style="COLOR: #000000">;</span></div>
</div></pre></div><p /><div id="31"> -- 优化:</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="32">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">    VARIABLE jobno </span><span style="FONT-WEIGHT: bold; COLOR: #000000">number</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">BEGIN</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    DBMS_JOB.SUBMIT(:jobno,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">ctx_ddl.optimize_index(</span><span style="COLOR: #ff0000">''</span><span style="COLOR: #ff0000">ind_dept_dname</span><span style="COLOR: #ff0000">''</span><span style="COLOR: #ff0000">,</span><span style="COLOR: #ff0000">''</span><span style="COLOR: #ff0000">FULL</span><span style="COLOR: #ff0000">''</span><span style="COLOR: #ff0000">);</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">,SYSDATE, </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">SYSDATE + 1</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">commit</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">END</span><span style="COLOR: #000000">;</span></div>
</div></pre></div><p /><div id="33"> 其中， 第一个job 的SYSDATE + (1/24/4)是指每隔15分钟同步一次，<br /> 第二个job 的SYSDATE+ 1 是每隔1 天做一次全优化。具体的时间间隔，你可以根据自己的应用的需要而定。</div>

查看完整版本: [转载]oracle全文检索技术应用

© 1999-2008 EvilOctal Security Team