当前位置：Oracle研究中心 > 运维DBA > Oracle性能优化 >
【学习笔记】Oracle全文索引full text index的深入研究
时间:2016-11-18 22:51 来源:Oracle研究中心 作者:网络 点击: 次
天萃荷净
Oracle研究中心学习笔记：分享一篇关于Oracle数据库优化的笔记，详细介绍数据库全文索引full test index在执行计划中SQL语句的优化状态。
本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: full text index 探秘（1）
总结下：
1. DML操作较为频繁表，对全文索引一定影响，但是如果说必须使用该功能，那么也没办法,
2. 至于说为什么有一定影响，其实原理都一样的，因为逻辑读的消耗会增加，必然影响性能。
该功能是从oracle 9i引入的，10g，11g都分别有一定的改变，下一篇文章将会进行描述。
[oracle@roger ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Aug 28 17:17:45 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> CREATE USER text_idx IDENTIFIED BY text_idx;
USER created.
SQL> GRANT resource ,CONNECT,ctxapp TO text_idx;
GRANT succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_CLS TO text_idx;
GRANT succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_DDL TO text_idx;
GRANT succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_DOC TO text_idx;
GRANT succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO text_idx;
GRANT succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_QUERY TO text_idx;
GRANT succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_REPORT TO text_idx;
GRANT succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_THES TO text_idx;
GRANT succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO text_idx;
GRANT succeeded.
SQL> conn text_idx/text_idx
Connected.
SQL> CREATE TABLE docs (id NUMBER PRIMARY KEY, text VARCHAR2(500));
TABLE created.
SQL> INSERT INTO docs VALUES(1, 'http://www.itpub.net/thread-1475450-1-1.html');
1 ROW created.
SQL> INSERT INTO docs VALUES(2, 'http://www.yesky.com/285/1942785_1.shtml');
1 ROW created.
SQL> INSERT INTO docs VALUES(3, 'http://www.baidu.com/s tn=chenly082=4391');
1 ROW created.
SQL> INSERT INTO docs VALUES(4, 'file:///D:/oracle---官方文档/B19306_01/B19306_01/text.102/b14217/quicktour.htmi1008362');
1 ROW created.
SQL> INSERT INTO docs VALUES(5, 'file:///D:/oracle---官方文档/B19306_01/B19306_01/text.102/b14217/acase.htm#g637598');
1 ROW created.
SQL> INSERT INTO docs VALUES(6, 'http://database.51cto.com/art/201104/252898.htm');
1 ROW created.
SQL> commit;
Commit complete.
SQL> SELECT COUNT(*) FROM docs;
COUNT(*)
----------
6
SQL> col text FOR a50
SQL> SET LINES 120
SQL> SELECT id, text FROM docs WHERE CONTAINS(text, 'baidu', 1) > 0;
ID TEXT
---------- --------------------------------------------------
3 http://www.baidu.com/s tn=chenly082=4391
SQL> SET autot traceonly
SQL> SELECT id, text FROM docs WHERE CONTAINS(text, 'baidu', 1) > 0;
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 3588628665
----------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 277 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DOCS | 1 | 277 | 4 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | IDX_DOCS | | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - access("CTXSYS"."CONTAINS"("TEXT",'baidu',1)>0)
Note
-----
- dynamic sampling used FOR this statement
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo SIZE
500 bytes sent via SQL*Net TO client
400 bytes received via SQL*Net FROM client
2 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
1 ROWS processed
SQL> CREATE SEQUENCE seq_docs
2 minvalue 1
3 nomaxvalue
4 START WITH 7
5 INCREMENT BY 1
6 nocycle
7 cache 10;
SEQUENCE created.
SQL> BEGIN
2 FOR i IN 1..100 loop
3 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/');
4 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=210');
5 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAOracleoracleplus.netL, 'http://www.killdb.com/ p=216');
6 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=201');
7 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=196');
8 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=219');
9 commit;
10 END loop;
11 END;
12 /
PL/SQL PROCEDURE successfully completed.
SQL> SELECT COUNT(*) FROM docs;
COUNT(*)
----------
606
SQL> BEGIN
2 FOR i IN 1..1000 loop
3 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/');
4 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=210');
5 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=216');
6 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=201');
7 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=196');
8 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=219');
9 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=205');
10 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=206');
11 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=207');
12 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=199');
13 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=208');
14 commit;
15 END loop;
16 END;
17 /
PL/SQL PROCEDURE successfully completed.
SQL> SELECT COUNT(*) FROM docs;
COUNT(*)
----------
11606
SQL> SET timing ON
SQL> BEGIN
2 FOR i IN 1..10000 loop
3 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/');
4 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=210');
5 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=216');
6 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=201');
7 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=196');
8 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=219');
9 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=205');
10 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=206');
11 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=207');
12 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=199');
13 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=208');
14 commit;
15 END loop;
16 END;
17 /
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:03:43.23
SQL> SELECT COUNT(*) FROM docs;
COUNT(*)
----------
121606
Elapsed: 00:00:00.05
SQL> SELECT COUNT(*) FROM docs WHERE CONTAINS(text,'p=210') > 0;
COUNT(*)
----------
0
SQL> SELECT COUNT(*) FROM docs WHERE CONTAINS(text,'killdb') >0;
COUNT(*)
----------
0
SQL> EXEC CTX_DDL.SYNC_INDEX('idx_docs', '5m');
PL/SQL PROCEDURE successfully completed.
SQL> SELECT COUNT(*) FROM docs WHERE CONTAINS(text,'p=210') > 0;
COUNT(*)
----------
110500
SQL> SELECT COUNT(*) FROM docs WHERE CONTAINS(text,'killdb') > 0;
COUNT(*)
----------
121600
从这里看出 同步index的重要性了,下面来创建job 来进行定期的同步index以及优化。
SQL> CREATE OR REPLACE PROCEDURE sync_idx_docs AS
2 BEGIN
3 ctx_ddl.sync_index('idx_docs');
4 END;
5 /
PROCEDURE created.
SQL> VARIABLE job_no NUMBER;
SQL> BEGIN
2 DBMS_JOB.SUBMIT(:job_no,'sync_idx_docs();',
3 SYSDATE, 'SYSDATE + 1');
4 commit;
5 END;
6 /
PL/SQL PROCEDURE successfully completed.
SQL> CREATE OR REPLACE PROCEDURE optimize_idx_docs AS
2 BEGIN
3 ctx_ddl.optimize_index('idx_docs','FULL');
4 END;
5 /
PROCEDURE created.
SQL> VARIABLE job_no NUMBER;
SQL> BEGIN
2 DBMS_JOB.SUBMIT(:job_no,'optimize_idx_docs();',
3 SYSDATE, 'SYSDATE + 1');
4 commit;
5 END;
6 /
PL/SQL PROCEDURE successfully completed.
SQL> col INTERVAL FOR a25
SQL> SELECT JOB,LOG_USER,SCHEMA_USER,INTERVAL,LAST_DATE FROM user_jobs;
JOB LOG_USER SCHEMA_USER INTERVAL LAST_DATE
---------- ------------- --------------- ---------------- ---------
21 TEXT_IDX TEXT_IDX SYSDATE + 1 28-AUG-11
22 TEXT_IDX TEXT_IDX SYSDATE + 1 28-AUG-11
---启动如上2个job任务
SQL> EXECUTE dbms_job.run(21);
PL/SQL PROCEDURE successfully completed.
SQL> EXECUTE dbms_job.run(22);
PL/SQL PROCEDURE successfully completed.
SQL> ALTER TABLE docs ADD owner varchar2(10);
TABLE altered.
SQL> UPDATE docs SET owner='Roger';
121606 ROWS updated.
SQL> commit;
Commit complete.
下面来测试，如果表docs dml操作比较频繁，那么对于select语句来说，是否有较大的影响？
SQL> SELECT /*+ no_index(docs SYS_C005195)*/
2 COUNT(*)
3 FROM docs
4 WHERE contains(text, 'p=199') > 0
5 AND id > 10500
6 AND id < 10800;
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1092983528
-----------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 277 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 277 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| DOCS | 1 | 277 | 4 (0)| 00:00:01 |
|* 3 | DOMAIN INDEX | IDX_DOCS | | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - FILTER("ID">10500 AND "ID"<10800)
3 - access("CTXSYS"."CONTAINS"("TEXT",'p=199')>0)
Note
-----
- dynamic sampling used FOR this statement
---session 1
SQL> SHOW USER
USER IS "TEXT_IDX"
SQL> BEGIN
2 FOR i IN 1 .. 15000 loop
3 IF MOD(i, 2) = 0 THEN
4 UPDATE docs
5 SET owner = 'killdb'
6 WHERE id = i
7 AND id > 10000
8 AND id < 15000;
9 commit;
10 END IF;
11 END loop;
12 commit;
13 END;
14 /
PL/SQL PROCEDURE successfully completed.
---session 2
SQL> SELECT /*+ no_index(docs SYS_C005195)*/
2 id,owner,text
3 FROM docs
4 WHERE contains(text, 'p=199') > 0
5 AND id > 10700
6 AND id < 10800;
90 ROWS selected.
Elapsed: 00:00:00.19
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
17447 consistent gets
0 physical reads
1024 redo SIZE
4691 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.35
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
21322 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.76
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
19518 consistent gets
0 physical reads
1856 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.37
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
15409 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.30
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
20226 consistent gets
0 physical reads
64 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.32
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
17881 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.42
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
15728 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.49
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
17406 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.25
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
16768 consistent gets
0 physical reads
128 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.40
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
16515 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.34
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
16046 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.34
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
19300 consistent gets
0 physical reads
1856 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:02.92
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
19860 consistent gets
0 physical reads
1216 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.45
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
15569 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.42
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
15120 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.21
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
17201 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.14
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
18365 consistent gets
0 physical reads
384 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.59
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
17998 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.29
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
14515 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.11
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
15595 consistent gets
0 physical reads
704 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.23
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
15675 consistent gets
0 physical reads
768 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.20
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
15584 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.14
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
18380 consistent gets
0 physical reads
1344 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
从上面的测试不难看出，对于DML操作频繁的话，对全文索引来说，还是有较大的影响的。
SQL> BEGIN
2 FOR i IN 1 .. 15000 loop
3 IF MOD(i, 2) = 0 THEN
4 UPDATE docs
5 SET owner = 'google'
6 WHERE id = i
7 AND id > 50000
8 AND id < 10000;
9 commit;
10 END IF;
11 END loop;
12 commit;
13 END;
14 /
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:02.25
SQL> EXEC ctx_ddl.optimize_index('idx_docs','rebuild') ;
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:02.84
SQL> BEGIN
2 FOR i IN 1 .. 15000 loop
3 IF MOD(i, 2) = 0 THEN
4 UPDATE docs
5 SET owner = 'baidu'
6 WHERE id = i
7 AND id > 50000
8 AND id < 10000;
9 commit;
10 END IF;
11 END loop;
12 commit;
13 END;
14 /
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:02.23
SQL> EXEC ctx_ddl.optimize_index('idx_docs','FULL') ;
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:00.45
SQL> BEGIN
2 FOR i IN 1..10000 loop
3 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/');
4 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=210');
5 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=216');
6 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=201');
7 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=196');
8 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=219');
9 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=205');
10 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=206');
11 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=207');
12 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=199');
13 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=208');
14 commit;
15 END loop;
16 END;
17 /
BEGIN
*
ERROR at line 1:
ORA-29875: failed IN the execution OF the ODCIINDEXINSERT ROUTINE
ORA-20000: Oracle Text error:
DRG-50857: oracle error IN textindexmethods.ODCIIndexInsert
ORA-00604: error occurred at recursive SQL level 2
ORA-01013: USER requested cancel OF CURRENT operation
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 633
ORA-06512: at line 13
Elapsed: 00:01:09.73
SQL> SELECT COUNT(*) FROM docs;
COUNT(*)
----------
138898
Elapsed: 00:00:00.35
SQL> EXEC ctx_ddl.optimize_index('idx_docs','FULL') ;
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:00.48
SQL> BEGIN
2 FOR i IN 1..10000 loop
3 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/');
4 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=210');
5 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=216');
6 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=201');
7 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=196');
8 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=219');
9 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=205');
10 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=206');
11 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=207');
12 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=199');
13 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=208');
14 commit;
15 END loop;
16 END;
17 /
BEGIN
*
ERROR at line 1:
ORA-29875: failed IN the execution OF the ODCIINDEXINSERT ROUTINE
ORA-20000: Oracle Text error:
DRG-50857: oracle error IN textindexmethods.ODCIIndexInsert
ORA-01013: USER requested cancel OF CURRENT operation
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 633
ORA-06512: at line 12
Elapsed: 00:01:06.17
SQL> EXEC ctx_ddl.optimize_index('idx_docs','rebuild') ;
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:02.09
SQL> SELECT COUNT(*) FROM docs;
COUNT(*)
----------
156091
Elapsed: 00:00:00.01
有人说优化index的时候，使用rebuild比full快很多，但是我测试恰恰相反，不知道为啥。
最后总结下：
1. DML操作较为频繁表，对全文索引一定影响，但是如果说必须使用该功能，那么也没办法,
2. 至于说为什么有一定影响，其实原理都一样的，因为逻辑读的消耗会增加，必然影响性能。
该功能是从oracle 9i引入的，10g，11g都分别有一定的改变，下一篇文章将会进行描述。
Oracle研究中心
本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: full text index 探秘（1）
总结下：
1. DML操作较为频繁表，对全文索引一定影响，但是如果说必须使用该功能，那么也没办法,
2. 至于说为什么有一定影响，其实原理都一样的，因为逻辑读的消耗会增加，必然影响性能。
该功能是从oracle 9i引入的，10g，11g都分别有一定的改变，下一篇文章将会进行描述。
[oracle@roger ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Aug 28 17:17:45 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> CREATE USER text_idx IDENTIFIED BY text_idx;
USER created.
SQL> GRANT resource ,CONNECT,ctxapp TO text_idx;
GRANT succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_CLS TO text_idx;
GRANT succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_DDL TO text_idx;
GRANT succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_DOC TO text_idx;
GRANT succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO text_idx;
GRANT succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_QUERY TO text_idx;
GRANT succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_REPORT TO text_idx;
GRANT succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_THES TO text_idx;
GRANT succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO text_idx;
GRANT succeeded.
SQL> conn text_idx/text_idx
Connected.
SQL> CREATE TABLE docs (id NUMBER PRIMARY KEY, text VARCHAR2(500));
TABLE created.
SQL> INSERT INTO docs VALUES(1, 'http://www.itpub.net/thread-1475450-1-1.html');
1 ROW created.
SQL> INSERT INTO docs VALUES(2, 'http://www.yesky.com/285/1942785_1.shtml');
1 ROW created.
SQL> INSERT INTO docs VALUES(3, 'http://www.baidu.com/s tn=chenly082=4391');
1 ROW created.
SQL> INSERT INTO docs VALUES(4, 'file:///D:/oracle---官方文档/B19306_01/B19306_01/text.102/b14217/quicktour.htmi1008362');
1 ROW created.
SQL> INSERT INTO docs VALUES(5, 'file:///D:/oracle---官方文档/B19306_01/B19306_01/text.102/b14217/acase.htm#g637598');
1 ROW created.
SQL> INSERT INTO docs VALUES(6, 'http://database.51cto.com/art/201104/252898.htm');
1 ROW created.
SQL> commit;
Commit complete.
SQL> SELECT COUNT(*) FROM docs;
COUNT(*)
----------
6
SQL> col text FOR a50
SQL> SET LINES 120
SQL> SELECT id, text FROM docs WHERE CONTAINS(text, 'baidu', 1) > 0;
ID TEXT
---------- --------------------------------------------------
3 http://www.baidu.com/s tn=chenly082=4391
SQL> SET autot traceonly
SQL> SELECT id, text FROM docs WHERE CONTAINS(text, 'baidu', 1) > 0;
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 3588628665
----------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 277 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DOCS | 1 | 277 | 4 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | IDX_DOCS | | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - access("CTXSYS"."CONTAINS"("TEXT",'baidu',1)>0)
Note
-----
- dynamic sampling used FOR this statement
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo SIZE
500 bytes sent via SQL*Net TO client
400 bytes received via SQL*Net FROM client
2 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
1 ROWS processed
SQL> CREATE SEQUENCE seq_docs
2 minvalue 1
3 nomaxvalue
4 START WITH 7
5 INCREMENT BY 1
6 nocycle
7 cache 10;
SEQUENCE created.
SQL> BEGIN
2 FOR i IN 1..100 loop
3 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/');
4 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=210');
5 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAOracleoracleplus.netL, 'http://www.killdb.com/ p=216');
6 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=201');
7 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=196');
8 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=219');
9 commit;
10 END loop;
11 END;
12 /
PL/SQL PROCEDURE successfully completed.
SQL> SELECT COUNT(*) FROM docs;
COUNT(*)
----------
606
SQL> BEGIN
2 FOR i IN 1..1000 loop
3 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/');
4 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=210');
5 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=216');
6 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=201');
7 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=196');
8 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=219');
9 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=205');
10 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=206');
11 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=207');
12 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=199');
13 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=208');
14 commit;
15 END loop;
16 END;
17 /
PL/SQL PROCEDURE successfully completed.
SQL> SELECT COUNT(*) FROM docs;
COUNT(*)
----------
11606
SQL> SET timing ON
SQL> BEGIN
2 FOR i IN 1..10000 loop
3 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/');
4 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=210');
5 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=216');
6 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=201');
7 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=196');
8 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=219');
9 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=205');
10 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=206');
11 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=207');
12 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=199');
13 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=208');
14 commit;
15 END loop;
16 END;
17 /
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:03:43.23
SQL> SELECT COUNT(*) FROM docs;
COUNT(*)
----------
121606
Elapsed: 00:00:00.05
SQL> SELECT COUNT(*) FROM docs WHERE CONTAINS(text,'p=210') > 0;
COUNT(*)
----------
0
SQL> SELECT COUNT(*) FROM docs WHERE CONTAINS(text,'killdb') >0;
COUNT(*)
----------
0
SQL> EXEC CTX_DDL.SYNC_INDEX('idx_docs', '5m');
PL/SQL PROCEDURE successfully completed.
SQL> SELECT COUNT(*) FROM docs WHERE CONTAINS(text,'p=210') > 0;
COUNT(*)
----------
110500
SQL> SELECT COUNT(*) FROM docs WHERE CONTAINS(text,'killdb') > 0;
COUNT(*)
----------
121600
从这里看出 同步index的重要性了,下面来创建job 来进行定期的同步index以及优化。
SQL> CREATE OR REPLACE PROCEDURE sync_idx_docs AS
2 BEGIN
3 ctx_ddl.sync_index('idx_docs');
4 END;
5 /
PROCEDURE created.
SQL> VARIABLE job_no NUMBER;
SQL> BEGIN
2 DBMS_JOB.SUBMIT(:job_no,'sync_idx_docs();',
3 SYSDATE, 'SYSDATE + 1');
4 commit;
5 END;
6 /
PL/SQL PROCEDURE successfully completed.
SQL> CREATE OR REPLACE PROCEDURE optimize_idx_docs AS
2 BEGIN
3 ctx_ddl.optimize_index('idx_docs','FULL');
4 END;
5 /
PROCEDURE created.
SQL> VARIABLE job_no NUMBER;
SQL> BEGIN
2 DBMS_JOB.SUBMIT(:job_no,'optimize_idx_docs();',
3 SYSDATE, 'SYSDATE + 1');
4 commit;
5 END;
6 /
PL/SQL PROCEDURE successfully completed.
SQL> col INTERVAL FOR a25
SQL> SELECT JOB,LOG_USER,SCHEMA_USER,INTERVAL,LAST_DATE FROM user_jobs;
JOB LOG_USER SCHEMA_USER INTERVAL LAST_DATE
---------- ------------- --------------- ---------------- ---------
21 TEXT_IDX TEXT_IDX SYSDATE + 1 28-AUG-11
22 TEXT_IDX TEXT_IDX SYSDATE + 1 28-AUG-11
---启动如上2个job任务
SQL> EXECUTE dbms_job.run(21);
PL/SQL PROCEDURE successfully completed.
SQL> EXECUTE dbms_job.run(22);
PL/SQL PROCEDURE successfully completed.
SQL> ALTER TABLE docs ADD owner varchar2(10);
TABLE altered.
SQL> UPDATE docs SET owner='Roger';
121606 ROWS updated.
SQL> commit;
Commit complete.
下面来测试，如果表docs dml操作比较频繁，那么对于select语句来说，是否有较大的影响？
SQL> SELECT /*+ no_index(docs SYS_C005195)*/
2 COUNT(*)
3 FROM docs
4 WHERE contains(text, 'p=199') > 0
5 AND id > 10500
6 AND id < 10800;
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1092983528
-----------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 277 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 277 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| DOCS | 1 | 277 | 4 (0)| 00:00:01 |
|* 3 | DOMAIN INDEX | IDX_DOCS | | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - FILTER("ID">10500 AND "ID"<10800)
3 - access("CTXSYS"."CONTAINS"("TEXT",'p=199')>0)
Note
-----
- dynamic sampling used FOR this statement
---session 1
SQL> SHOW USER
USER IS "TEXT_IDX"
SQL> BEGIN
2 FOR i IN 1 .. 15000 loop
3 IF MOD(i, 2) = 0 THEN
4 UPDATE docs
5 SET owner = 'killdb'
6 WHERE id = i
7 AND id > 10000
8 AND id < 15000;
9 commit;
10 END IF;
11 END loop;
12 commit;
13 END;
14 /
PL/SQL PROCEDURE successfully completed.
---session 2
SQL> SELECT /*+ no_index(docs SYS_C005195)*/
2 id,owner,text
3 FROM docs
4 WHERE contains(text, 'p=199') > 0
5 AND id > 10700
6 AND id < 10800;
90 ROWS selected.
Elapsed: 00:00:00.19
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
17447 consistent gets
0 physical reads
1024 redo SIZE
4691 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.35
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
21322 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.76
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
19518 consistent gets
0 physical reads
1856 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.37
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
15409 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.30
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
20226 consistent gets
0 physical reads
64 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.32
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
17881 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.42
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
15728 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.49
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
17406 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.25
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
16768 consistent gets
0 physical reads
128 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.40
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
16515 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.34
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
16046 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.34
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
19300 consistent gets
0 physical reads
1856 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:02.92
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
19860 consistent gets
0 physical reads
1216 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.45
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
15569 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.42
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
15120 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.21
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
17201 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.14
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
18365 consistent gets
0 physical reads
384 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.59
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
17998 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.29
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
14515 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.11
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
15595 consistent gets
0 physical reads
704 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.23
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
15675 consistent gets
0 physical reads
768 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.20
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
15584 consistent gets
0 physical reads
0 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
SQL> /
90 ROWS selected.
Elapsed: 00:00:00.14
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
18380 consistent gets
0 physical reads
1344 redo SIZE
4689 bytes sent via SQL*Net TO client
455 bytes received via SQL*Net FROM client
7 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
90 ROWS processed
从上面的测试不难看出，对于DML操作频繁的话，对全文索引来说，还是有较大的影响的。
SQL> BEGIN
2 FOR i IN 1 .. 15000 loop
3 IF MOD(i, 2) = 0 THEN
4 UPDATE docs
5 SET owner = 'google'
6 WHERE id = i
7 AND id > 50000
8 AND id < 10000;
9 commit;
10 END IF;
11 END loop;
12 commit;
13 END;
14 /
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:02.25
SQL> EXEC ctx_ddl.optimize_index('idx_docs','rebuild') ;
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:02.84
SQL> BEGIN
2 FOR i IN 1 .. 15000 loop
3 IF MOD(i, 2) = 0 THEN
4 UPDATE docs
5 SET owner = 'baidu'
6 WHERE id = i
7 AND id > 50000
8 AND id < 10000;
9 commit;
10 END IF;
11 END loop;
12 commit;
13 END;
14 /
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:02.23
SQL> EXEC ctx_ddl.optimize_index('idx_docs','FULL') ;
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:00.45
SQL> BEGIN
2 FOR i IN 1..10000 loop
3 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/');
4 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=210');
5 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=216');
6 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=201');
7 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=196');
8 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=219');
9 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=205');
10 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=206');
11 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=207');
12 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=199');
13 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=208');
14 commit;
15 END loop;
16 END;
17 /
BEGIN
*
ERROR at line 1:
ORA-29875: failed IN the execution OF the ODCIINDEXINSERT ROUTINE
ORA-20000: Oracle Text error:
DRG-50857: oracle error IN textindexmethods.ODCIIndexInsert
ORA-00604: error occurred at recursive SQL level 2
ORA-01013: USER requested cancel OF CURRENT operation
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 633
ORA-06512: at line 13
Elapsed: 00:01:09.73
SQL> SELECT COUNT(*) FROM docs;
COUNT(*)
----------
138898
Elapsed: 00:00:00.35
SQL> EXEC ctx_ddl.optimize_index('idx_docs','FULL') ;
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:00.48
SQL> BEGIN
2 FOR i IN 1..10000 loop
3 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/');
4 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=210');
5 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=216');
6 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=201');
7 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=196');
8 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=219');
9 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=205');
10 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=206');
11 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=207');
12 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=199');
13 INSERT INTO docs(id, text) VALUES(seq_docs.NEXTVAL, 'http://www.killdb.com/ p=208');
14 commit;
15 END loop;
16 END;
17 /
BEGIN
*
ERROR at line 1:
ORA-29875: failed IN the execution OF the ODCIINDEXINSERT ROUTINE
ORA-20000: Oracle Text error:
DRG-50857: oracle error IN textindexmethods.ODCIIndexInsert
ORA-01013: USER requested cancel OF CURRENT operation
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 633
ORA-06512: at line 12
Elapsed: 00:01:06.17
SQL> EXEC ctx_ddl.optimize_index('idx_docs','rebuild') ;
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:02.09
SQL> SELECT COUNT(*) FROM docs;
COUNT(*)
----------
156091
Elapsed: 00:00:00.01
有人说优化index的时候，使用rebuild比full快很多，但是我测试恰恰相反，不知道为啥。
最后总结下：
1. DML操作较为频繁表，对全文索引一定影响，但是如果说必须使用该功能，那么也没办法,
2. 至于说为什么有一定影响，其实原理都一样的，因为逻辑读的消耗会增加，必然影响性能。
该功能是从oracle 9i引入的，10g，11g都分别有一定的改变，下一篇文章将会进行描述。
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle全文索引full text index的深入研究
本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/1288.html
