oracle full text index 探秘（1）
<DIV>原文链接 <a href="http://www.killdb.com/?p=222" target="_blank">http://www.killdb.com/?p=222</A></DIV><DIV> </DIV>
<DIV>$ sqlplus "/as sysdba"</DIV>
<DIV>SQL*Plus: Release 10.2.0.4.0 - Production on Sun Aug 28 17:17:45 2011</DIV>
<DIV>Copyright (c) 1982, 2007, Oracle. All Rights Reserved.</DIV>
<DIV>Connected to an idle instance.</DIV>
<DIV>SQL> startup<BR>ORACLE instance started.</DIV>
<DIV>Total System Global Area 167772160 bytes<BR>Fixed Size 1266392 bytes<BR>Variable Size 71306536 bytes<BR>Database Buffers 92274688 bytes<BR>Redo Buffers 2924544 bytes<BR>Database mounted.<BR>Database opened.<BR>SQL> create user text_idx identified by text_idx;</DIV>
<DIV>User created.</DIV>
<DIV>SQL> <BR>SQL> grant resource ,connect,ctxapp to text_idx;</DIV>
<DIV>Grant succeeded.</DIV>
<DIV>SQL> <BR>SQL> GRANT EXECUTE ON CTXSYS.CTX_CLS TO text_idx;<BR>GRANT EXECUTE ON CTXSYS.CTX_DDL TO text_idx;<BR>GRANT EXECUTE ON CTXSYS.CTX_DOC TO text_idx;<BR>GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO text_idx;<BR>GRANT EXECUTE ON CTXSYS.CTX_QUERY TO text_idx;<BR>GRANT EXECUTE ON CTXSYS.CTX_REPORT TO text_idx;<BR>GRANT EXECUTE ON CTXSYS.CTX_THES TO text_idx;<BR>GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO text_idx;</DIV>
<DIV>Grant succeeded.</DIV>
<DIV>SQL> <BR>Grant succeeded.</DIV>
<DIV>SQL> <BR>Grant succeeded.</DIV>
<DIV>SQL> <BR>Grant succeeded.</DIV>
<DIV>SQL> <BR>Grant succeeded.</DIV>
<DIV>SQL> <BR>Grant succeeded.</DIV>
<DIV>SQL> <BR>Grant succeeded.</DIV>
<DIV>SQL> <BR>Grant succeeded.</DIV>
<DIV>SQL> <BR>SQL> conn text_idx/text_idx<BR>Connected.<BR>SQL> CREATE TABLE docs (id NUMBER PRIMARY KEY, text VARCHAR2(500));</DIV>
<DIV>Table created.<BR>SQL> INSERT INTO docs VALUES(1, 'http://www.itpub.net/thread-1475450-1-1.html');</DIV>
<DIV>1 row created.</DIV>
<DIV>SQL> INSERT INTO docs VALUES(2, 'http://www.yesky.com/285/1942785_1.shtml');<BR>INSERT INTO docs VALUES(3, 'http://www.baidu.com/s?tn=chenly082=4391');</DIV>
<DIV>1 row created.</DIV>
<DIV>SQL> INSERT INTO docs VALUES(4, 'file:///D:/oracle---官方文档/B19306_01/B19306_01/text.102/b14217/quicktour.htmi1008362'); <BR>1 row created.</DIV>
<DIV>SQL> <BR>INSERT INTO docs VALUES(5, 'file:///D:/oracle---官方文档/B19306_01/B19306_01/text.10<BR>1 row created.</DIV>
<DIV>SQL> 2/b14217/acase.htm#g637598'); <BR>INSERT INTO docs VALUES(6, 'http://database.51cto.com/art/201104/252898.htm');</DIV>
<DIV>1 row created.</DIV>
<DIV>SQL> <BR>1 row created.</DIV>
<DIV>SQL> <BR>SQL> commit;</DIV>
<DIV>Commit complete.</DIV>
<DIV>SQL> select count(*) from docs;</DIV>
<DIV> COUNT(*)<BR>----------<BR> 6</DIV>
<DIV>SQL> col text for a50<BR>SQL> set lines 120<BR>SQL> SELECT id, text FROM docs WHERE CONTAINS(text, 'baidu', 1) > 0;</DIV>
<DIV> ID TEXT<BR>---------- --------------------------------------------------<BR> 3 <a href="http://www.baidu.com/s?tn=chenly082=4391" target="_blank">http://www.baidu.com/s?tn=chenly082=4391</A></DIV>
<DIV>SQL> <BR>SQL> set autot traceonly<BR>SQL> SELECT id, text FROM docs WHERE CONTAINS(text, 'baidu', 1) > 0;</DIV>
<DIV><BR>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 3588628665</DIV>
<DIV>----------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>----------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 277 | 4 (0)| 00:00:01 |<BR>| 1 | TABLE ACCESS BY INDEX ROWID| DOCS | 1 | 277 | 4 (0)| 00:00:01 |<BR>|* 2 | DOMAIN INDEX | IDX_DOCS | | | 4 (0)| 00:00:01 |<BR>----------------------------------------------------------------------------------------</DIV>
<DIV>Predicate Information (identified by operation id):<BR>---------------------------------------------------</DIV>
<DIV> 2 - access("CTXSYS"."CONTAINS"("TEXT",'baidu',1)>0)</DIV>
<DIV>Note<BR>-----<BR> - dynamic sampling used for this statement</DIV>
<DIV><BR>Statistics<BR>----------------------------------------------------------<BR> 11 recursive calls<BR> 0 db block gets<BR> 18 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 500 bytes sent via SQL*Net to client<BR> 400 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</DIV>
<DIV><BR>SQL> create sequence seq_docs<BR> 2 minvalue 1<BR> 3 nomaxvalue<BR> 4 start with 7<BR> 5 increment by 1<BR> 6 nocycle<BR> 7 cache 10;</DIV>
<DIV>Sequence created.</DIV>
<DIV>SQL> begin<BR> 2 for i in 1..100 loop<BR> 3 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/'); <BR> 4 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=210'); <BR> 5 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=216'); <BR> 6 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=201'); <BR> 7 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=196'); <BR> 8 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=219'); <BR> 9 commit;<BR> 10 end loop;<BR> 11 end;<BR> 12 /</DIV>
<DIV>PL/SQL procedure successfully completed.</DIV>
<DIV>SQL> <BR>SQL> <BR>SQL> select count(*) from docs;</DIV>
<DIV> COUNT(*)<BR>----------<BR> 606</DIV>
<DIV>SQL> begin<BR> 2 for i in 1..1000 loop<BR> 3 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/'); <BR> 4 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=210'); <BR> 5 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=216'); <BR> 6 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=201'); <BR> 7 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=196'); <BR> 8 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=219'); <BR> 9 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=205'); <BR> 10 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=206'); <BR> 11 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=207'); <BR> 12 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=199'); <BR> 13 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=208'); <BR> 14 commit;<BR> 15 end loop;<BR> 16 end;<BR> 17 /</DIV>
<DIV>PL/SQL procedure successfully completed.</DIV>
<DIV>SQL> <BR>SQL> select count(*) from docs;</DIV>
<DIV> COUNT(*)<BR>----------<BR> 11606</DIV>
<DIV>SQL> <BR>SQL> set timing on<BR>SQL> begin<BR> 2 for i in 1..10000 loop<BR> 3 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/'); <BR> 4 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=210'); <BR> 5 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=216'); <BR> 6 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=201'); <BR> 7 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=196'); <BR> 8 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=219'); <BR> 9 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=205'); <BR> 10 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=206'); <BR> 11 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=207'); <BR> 12 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=199'); <BR> 13 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=208'); <BR> 14 commit;<BR> 15 end loop;<BR> 16 end;<BR> 17 /</DIV>
<DIV>PL/SQL procedure successfully completed.</DIV>
<DIV>Elapsed: 00:03:43.23<BR>SQL> <BR>SQL> select count(*) from docs;</DIV>
<DIV> COUNT(*)<BR>----------<BR> 121606</DIV>
<DIV>Elapsed: 00:00:00.05<BR>SQL> <BR>SQL> select count(*) from docs where CONTAINS(text,'p=210') > 0;</DIV>
<DIV> COUNT(*)<BR>----------<BR> 0</DIV>
<DIV>SQL> select count(*) from docs where CONTAINS(text,'killdb') >0;</DIV>
<DIV> COUNT(*)<BR>----------<BR> 0</DIV>
<DIV>SQL> EXEC CTX_DDL.SYNC_INDEX('idx_docs', '5m');</DIV>
<DIV>PL/SQL procedure successfully completed.</DIV>
<DIV>SQL> select count(*) from docs where CONTAINS(text,'p=210') > 0;</DIV>
<DIV> COUNT(*)<BR>----------<BR> 110500</DIV>
<DIV>SQL> <BR>SQL> SELECT count(*) FROM docs WHERE CONTAINS(text,'killdb') > 0;</DIV>
<DIV> COUNT(*)<BR>----------<BR> 121600</DIV>
<DIV>从这里看出 同步index的重要性了,下面来创建job 来进行定期的同步index以及优化。<BR>SQL> create or replace procedure sync_idx_docs as <BR> 2 begin <BR> 3 ctx_ddl.sync_index('idx_docs'); <BR> 4 end; <BR> 5 /</DIV>
<DIV>Procedure created.</DIV>
<DIV>SQL> VARIABLE job_no number; <BR>SQL> BEGIN <BR> 2 DBMS_JOB.SUBMIT(:job_no,'sync_idx_docs();', <BR> 3 SYSDATE, 'SYSDATE + 1'); <BR> 4 commit; <BR> 5 END; <BR> 6 / </DIV>
<DIV>PL/SQL procedure successfully completed.</DIV>
<DIV>SQL> create or replace procedure optimize_idx_docs as <BR> 2 begin <BR> 3 ctx_ddl.optimize_index('idx_docs','FULL'); <BR> 4 end; <BR> 5 / </DIV>
<DIV>Procedure created.</DIV>
<DIV>SQL> VARIABLE job_no number; <BR>SQL> BEGIN <BR> 2 DBMS_JOB.SUBMIT(:job_no,'optimize_idx_docs();', <BR> 3 SYSDATE, 'SYSDATE + 1'); <BR> 4 commit; <BR> 5 END; <BR> 6 / </DIV>
<DIV>PL/SQL procedure successfully completed.</DIV>
<DIV>SQL> <BR>SQL> col INTERVAL for a25<BR>SQL> select JOB,LOG_USER,SCHEMA_USER,INTERVAL,LAST_DATE from user_jobs;</DIV>
<DIV> JOB LOG_USER SCHEMA_USER INTERVAL LAST_DATE<BR>---------- ------------- --------------- ---------------- ---------<BR> 21 TEXT_IDX TEXT_IDX SYSDATE + 1 28-AUG-11<BR> 22 TEXT_IDX TEXT_IDX SYSDATE + 1 28-AUG-11</DIV>
<DIV><BR>---启动如上2个job任务<BR>SQL> execute dbms_job.run(21); <BR>execute dbms_job.run(22); <BR>PL/SQL procedure successfully completed.</DIV>
<DIV>SQL> </DIV>
<DIV>PL/SQL procedure successfully completed.</DIV>
<DIV>SQL> <BR>SQL> alter table docs add owner varchar2(10);</DIV>
<DIV>Table altered.</DIV>
<DIV>SQL> update docs set owner='Roger';</DIV>
<DIV>121606 rows updated.</DIV>
<DIV>SQL> commit;</DIV>
<DIV>Commit complete.</DIV>
<DIV>SQL> </DIV>
<DIV>下面来测试，如果表docs dml操作比较频繁，那么对于select语句来说，是否有较大的影响？</DIV>
<DIV>SQL> select /*+ no_index(docs SYS_C005195)*/<BR> 2 count(*)<BR> 3 from docs<BR> 4 where contains(text, 'p=199') > 0<BR> 5 and id > 10500<BR> 6 and id < 10800;<BR>Elapsed: 00:00:00.09</DIV>
<DIV>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 1092983528</DIV>
<DIV>-----------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-----------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 277 | 4 (0)| 00:00:01 |<BR>| 1 | SORT AGGREGATE | | 1 | 277 | | |<BR>|* 2 | TABLE ACCESS BY INDEX ROWID| DOCS | 1 | 277 | 4 (0)| 00:00:01 |<BR>|* 3 | DOMAIN INDEX | IDX_DOCS | | | 4 (0)| 00:00:01 |<BR>-----------------------------------------------------------------------------------------</DIV>
<DIV>Predicate Information (identified by operation id):<BR>---------------------------------------------------</DIV>
<DIV> 2 - filter("ID">10500 AND "ID"<10800)<BR> 3 - access("CTXSYS"."CONTAINS"("TEXT",'p=199')>0)</DIV>
<DIV>Note<BR>-----<BR> - dynamic sampling used for this statement</DIV>
<DIV><BR>---session 1<BR>SQL> show user<BR>USER is "TEXT_IDX"<BR>SQL> begin<BR> 2 for i in 1 .. 15000 loop<BR> 3 if mod(i, 2) = 0 then<BR> 4 update docs<BR> 5 set owner = 'killdb'<BR> 6 where id = i<BR> 7 and id > 10000<BR> 8 and id < 15000;<BR> 9 commit;<BR> 10 end if;<BR> 11 end loop;<BR> 12 commit;<BR> 13 end;<BR> 14 /</DIV>
<DIV>PL/SQL procedure successfully completed.</DIV>
<DIV>---session 2<BR>SQL> select /*+ no_index(docs SYS_C005195)*/<BR> 2 id,owner,text<BR> 3 from docs<BR> 4 where contains(text, 'p=199') > 0<BR> 5 and id > 10700<BR> 6 and id < 10800;</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.19</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 17447 consistent gets<BR> 0 physical reads<BR> 1024 redo size<BR> 4691 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.35</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 21322 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>/90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.76</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 19518 consistent gets<BR> 0 physical reads<BR> 1856 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> </DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.37</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 15409 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.30</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 20226 consistent gets<BR> 0 physical reads<BR> 64 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.32</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 17881 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.42</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 15728 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.49</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 17406 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.25</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 16768 consistent gets<BR> 0 physical reads<BR> 128 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.40</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 16515 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.34</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 16046 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.34</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 19300 consistent gets<BR> 0 physical reads<BR> 1856 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:02.92</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 19860 consistent gets<BR> 0 physical reads<BR> 1216 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.45</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 15569 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.42</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 15120 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.21</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 17201 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.14</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 18365 consistent gets<BR> 0 physical reads<BR> 384 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.59</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 17998 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.29</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 14515 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.11</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 15595 consistent gets<BR> 0 physical reads<BR> 704 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.23</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 15675 consistent gets<BR> 0 physical reads<BR> 768 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.20</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 15584 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> /</DIV>
<DIV>90 rows selected.</DIV>
<DIV>Elapsed: 00:00:00.14</DIV>
<DIV>Statistics<BR>----------------------------------------------------------<BR> 13 recursive calls<BR> 0 db block gets<BR> 18380 consistent gets<BR> 0 physical reads<BR> 1344 redo size<BR> 4689 bytes sent via SQL*Net to client<BR> 455 bytes received via SQL*Net from client<BR> 7 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 90 rows processed</DIV>
<DIV>SQL> </DIV>
<DIV><BR>从上面的测试不难看出，对于DML操作频繁的话，对全文索引来说，还是有较大的影响的。</DIV>
<DIV><BR>SQL> begin<BR> 2 for i in 1 .. 15000 loop<BR> 3 if mod(i, 2) = 0 then<BR> 4 update docs<BR> 5 set owner = 'google'<BR> 6 where id = i<BR> 7 and id > 50000<BR> 8 and id < 10000;<BR> 9 commit;<BR> 10 end if;<BR> 11 end loop;<BR> 12 commit;<BR> 13 end;<BR> 14 /</DIV>
<DIV>PL/SQL procedure successfully completed.</DIV>
<DIV>Elapsed: 00:00:02.25<BR>SQL> exec ctx_ddl.optimize_index('idx_docs','rebuild') ;</DIV>
<DIV>PL/SQL procedure successfully completed.</DIV>
<DIV>Elapsed: 00:00:02.84<BR>SQL> begin<BR> 2 for i in 1 .. 15000 loop<BR> 3 if mod(i, 2) = 0 then<BR> 4 update docs<BR> 5 set owner = 'baidu'<BR> 6 where id = i<BR> 7 and id > 50000<BR> 8 and id < 10000;<BR> 9 commit;<BR> 10 end if;<BR> 11 end loop;<BR> 12 commit;<BR> 13 end;<BR> 14 /</DIV>
<DIV>PL/SQL procedure successfully completed.</DIV>
<DIV>Elapsed: 00:00:02.23<BR>SQL> exec ctx_ddl.optimize_index('idx_docs','FULL') ; </DIV>
<DIV>PL/SQL procedure successfully completed.</DIV>
<DIV>Elapsed: 00:00:00.45<BR>SQL> </DIV>
<DIV>SQL> begin<BR> 2 for i in 1..10000 loop<BR> 3 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/'); <BR> 4 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=210'); <BR> 5 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=216'); <BR> 6 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=201'); <BR> 7 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=196'); <BR> 8 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=219'); <BR> 9 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=205'); <BR> 10 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=206'); <BR> 11 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=207'); <BR> 12 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=199'); <BR> 13 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=208'); <BR> 14 commit;<BR> 15 end loop;<BR> 16 end;<BR> 17 /</DIV>
<DIV>begin<BR>*<BR>ERROR at line 1:<BR>ORA-29875: failed in the execution of the ODCIINDEXINSERT routine<BR>ORA-20000: Oracle Text error:<BR>DRG-50857: oracle error in textindexmethods.ODCIIndexInsert<BR>ORA-00604: error occurred at recursive SQL level 2<BR>ORA-01013: user requested cancel of current operation<BR>ORA-06512: at "CTXSYS.DRUE", line 160<BR>ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 633<BR>ORA-06512: at line 13</DIV>
<DIV><BR>Elapsed: 00:01:09.73</DIV>
<DIV>SQL> select count(*) from docs;</DIV>
<DIV> COUNT(*)<BR>----------<BR> 138898</DIV>
<DIV>Elapsed: 00:00:00.35<BR>SQL> exec ctx_ddl.optimize_index('idx_docs','FULL') ;</DIV>
<DIV>PL/SQL procedure successfully completed.</DIV>
<DIV>Elapsed: 00:00:00.48<BR>SQL> begin<BR> 2 for i in 1..10000 loop<BR> 3 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/'); <BR> 4 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=210'); <BR> 5 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=216'); <BR> 6 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=201'); <BR> 7 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=196'); <BR> 8 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=219'); <BR> 9 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=205'); <BR> 10 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=206'); <BR> 11 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=207'); <BR> 12 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=199'); <BR> 13 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/?p=208'); <BR> 14 commit;<BR> 15 end loop;<BR> 16 end;<BR> 17 /</DIV>
<DIV>begin<BR>*<BR>ERROR at line 1:<BR>ORA-29875: failed in the execution of the ODCIINDEXINSERT routine<BR>ORA-20000: Oracle Text error:<BR>DRG-50857: oracle error in textindexmethods.ODCIIndexInsert<BR>ORA-01013: user requested cancel of current operation<BR>ORA-06512: at "CTXSYS.DRUE", line 160<BR>ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 633<BR>ORA-06512: at line 12</DIV>
<DIV><BR>Elapsed: 00:01:06.17</DIV>
<DIV>SQL> exec ctx_ddl.optimize_index('idx_docs','rebuild') ;</DIV>
<DIV>PL/SQL procedure successfully completed.</DIV>
<DIV>Elapsed: 00:00:02.09<BR>SQL> select count(*) from docs;</DIV>
<DIV> COUNT(*)<BR>----------<BR> 156091</DIV>
<DIV>Elapsed: 00:00:00.01<BR>SQL> <BR>有人说优化index的时候，使用rebuild比full快很多，但是我测试恰恰相反，不知道为啥。</DIV>
<DIV>最后总结下：</DIV>
<DIV>1. DML操作较为频繁表，对全文索引一定影响，但是如果说必须使用该功能，那么也没办法,<BR>2. 至于说为什么有一定影响，其实原理都一样的，因为逻辑读的消耗会增加，必然影响性能。</DIV>
<DIV>该功能是从oracle 9i引入的，10g，11g都分别有一定的改变，下一篇文章将会进行描述。</DIV>
页:
[1]