sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置: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-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle全文索引full text index的深入研究

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1288.html

Oracle研究中心

关键词:

详细介绍Oracle full text index

Oracle数据库索引优化案例

Oracle全文索引使用方法