如何对Oracle10g 数据库全文索引查询进行优化？

问题描述：

1.职位表 c_recruit（大约数据量13W），全文索引表 fulltexthelp（大约数据量13W），企业表 c_info（大约数据量8W）

2.关联关系——
由于应用要求提供全文搜索的功能，于是将一些必要的搜索内容加入到 全文索引表 fulltexthelp 当中，关键字查询得到结果之后，通过全文索引表的 id 字段与职位表中的 recruit_id 字段进行关联，从而获取职位的信息，而后根据 职位的企业编号company_id字段来获取企业信息

3.测试：
在使用JMeter做20个用户的压力测试时，出现CPU达到85%以上占用率的问题，随后使用任务计划查看SQL执行过程细节，发现在全文搜索过程中CPU计算消耗占了整个SQL的80%

在此，小生向各位大虾求教，有没有什么办法能够将SQL的CPU占用率降下来？或者有什么启发性的建议，也望大虾们不吝赐教！谢谢！

建表SQL见下面跟帖~~

全文检索的查询语句如下：

SELECT *
  FROM (SELECT tmp.*, ROWNUM rn
          FROM (SELECT
                         /*+index(r C_RECRUIT_PK) */
                         r.recruit_id, r.job_name_cn, r.salary, i.company_id, i.company_name
                    FROM c_recruit r, fulltexthelp f, c_info i
                   WHERE r.recruit_id = f.ID
                     AND f.company_id = i.company_id
                     AND f.contract_status > 0
                     AND f.recruit_type = 1
                     AND r.release_status = 1
                     AND r.recruit_type = 1
                     AND r.release_time >= SYSDATE - 60
                     AND f.release_time >= SYSDATE - 60
                     AND r.release_time + r.hold_day >= SYSDATE
                     AND r.approved_status = 1
                     AND r.release_status = 1
                     AND i.is_lock_flag = 0
                     AND i.approved_status = 1
                     AND contains (f.fulltext, '销售') > 0
                ORDER BY f.release_time DESC, f.ID DESC) tmp
         WHERE 1 = 1 AND ROWNUM <= 100)
 WHERE 1 = 1 AND rn >= 1

库表c_recruit 建表sql如下：

CREATE TABLE TEST.C_RECRUIT
(
  RECRUIT_ID                 NUMBER(12)         NOT NULL,
  COMPANY_ID                 NUMBER(12)         NOT NULL,
  JOB_CODE                   VARCHAR2(100 BYTE),
  JOB_NAME_CN                VARCHAR2(100 BYTE),
  JOB_NAME_EN                VARCHAR2(100 BYTE),
  JOB_DETAIL_CN              CLOB,
  SALARY                     NUMBER(2),
  APPROVED_OP                VARCHAR2(100 BYTE),
  RELEASE_TIME               DATE,
  HOLD_DAY                   NUMBER(5),
  APPROVED_STATUS            NUMBER(1)          DEFAULT 0,
  RELEASE_STATUS             NUMBER(1)          NOT NULL,
  IS_MUST_EN_RESUME          NUMBER(1),
  ORDER_ID                   NUMBER(12),
  RECRUIT_TYPE               NUMBER(3)          DEFAULT 1,
  RELATIVE_ID                NUMBER(12),
  UPDATE_TIME                DATE,
  JOB_TYPES1                 NUMBER(8),
  JOB_TYPES2                 NUMBER(8),
  JOB_TYPES3                 NUMBER(8)
)
TABLESPACE TEST
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
LOGGING
NOCOMPRESS
LOB (JOB_DETAIL_EN) STORE AS
      ( TABLESPACE  TEST
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION  0
        NOCACHE
        STORAGE    (……)
      )
  LOB (JOB_DETAIL_CN) STORE AS
      ( TABLESPACE  TEST
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION  0
        NOCACHE
        STORAGE    (……)
      )
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX TEST.INDX_RECR_RELEASE_TIME ON TEST.C_RECRUIT
(RELEASE_TIME)
LOGGING
TABLESPACE TEST
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (……)
NOPARALLEL
REVERSE;

CREATE INDEX TEST.INDX_RECR_WORKPLACE ON TEST.C_RECRUIT
(WORK_PLACE_NAME)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('lexer ctxcat_lexer STOPLIST CTXSYS.EMPTY_STOPLIST')
NOPARALLEL;

CREATE UNIQUE INDEX TEST.C_RECRUIT_PK ON TEST.C_RECRUIT
(RECRUIT_ID)
LOGGING
TABLESPACE TEST
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (……)
NOPARALLEL;

CREATE INDEX TEST.INDX_RECR_JT1 ON TEST.C_RECRUIT
(JOB_TYPES1)
LOGGING
TABLESPACE TEST
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (……)
NOPARALLEL;

CREATE INDEX TEST.INDX_RECR_JT2 ON TEST.C_RECRUIT
(JOB_TYPES2)
LOGGING
TABLESPACE TEST
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (……)
NOPARALLEL;

CREATE INDEX TEST.INDX_RECR_JT3 ON TEST.C_RECRUIT
(JOB_TYPES3)
LOGGING
TABLESPACE TEST
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (……)
NOPARALLEL;

CREATE INDEX TEST.INDX_KEEPTIME ON TEST.C_RECRUIT
("RELEASE_TIME"+"HOLD_DAY")
LOGGING
TABLESPACE TEST
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (……)
NOPARALLEL;

CREATE INDEX TEST.RECRUIT_CMPID_INDEX ON TEST.C_RECRUIT
(COMPANY_ID)
LOGGING
TABLESPACE TEST
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (……)
NOPARALLEL;


ALTER TABLE TEST.C_RECRUIT ADD (
  CONSTRAINT C_RECRUIT_PK
 PRIMARY KEY
 (RECRUIT_ID)
    USING INDEX
    TABLESPACE TEST
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (……));

库表 FULLTEXTHELP 的建表 SQL：

CREATE TABLE TEST.FULLTEXTHELP
(
  ID               NUMBER(12)                   NOT NULL,
  RECRUIT_TYPE     NUMBER(1)                    NOT NULL,
  FULLTEXT         CLOB,
  CONTRACT_STATUS  NUMBER(3)                    DEFAULT 0                     NOT NULL,
  COMPANY_ID       NUMBER(12),
  RELEASE_TIME     DATE,
  HOLD_DAY         NUMBER(5)
)
TABLESPACE TEST
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (……)
NOLOGGING
NOCOMPRESS
LOB (FULLTEXT) STORE AS
      ( TABLESPACE  TEST
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION  0
        NOCACHE
        STORAGE    (……)
      )
NOCACHE
NOPARALLEL
MONITORING;

CREATE INDEX TEST.INDEX_FTH_RELEASETIME ON TEST.FULLTEXTHELP
(RELEASE_TIME)
LOGGING
TABLESPACE TEST
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (……)
NOPARALLEL;

CREATE INDEX TEST.INDEX_FTH_COMPANYID ON TEST.FULLTEXTHELP
(COMPANY_ID)
LOGGING
TABLESPACE TEST
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (……)
NOPARALLEL;

CREATE INDEX TEST.INDX_FTH_FULLTEXT ON TEST.FULLTEXTHELP
(FULLTEXT)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('lexer ctxcat_lexer STOPLIST CTXSYS.EMPTY_STOPLIST')
NOPARALLEL;

CREATE UNIQUE INDEX TEST.FULLTEXTHELP_PK ON TEST.FULLTEXTHELP
(ID)
LOGGING
TABLESPACE TEST
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (……)
NOPARALLEL;

CREATE INDEX TEST.INDX_FTH_RI ON TEST.FULLTEXTHELP
(RELEASE_TIME, ID)
LOGGING
TABLESPACE TEST
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (……)
NOPARALLEL;

CREATE INDEX TEST.INDX_FTH_RH ON TEST.FULLTEXTHELP
("RELEASE_TIME"+"HOLD_DAY")
LOGGING
TABLESPACE TEST
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (……)
NOPARALLEL;

ALTER TABLE TEST.FULLTEXTHELP ADD (
  CONSTRAINT FULLTEXTHELP_PK
 PRIMARY KEY
 (ID)
    USING INDEX
    TABLESPACE TEST
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (……));

LZ可以试着把contains查询条件去掉，确认瓶颈是否在contains语句上
如果，的确是因为这个语句的问题，就应该想办法让contains语句成为最后一个被使用的查询条件，
（这也取决于你的优化策略）
不过，我注意到SQL语句中有一个关于“C_RECRUIT_PK”的HINT，很是不解

SQL用ROW_NUMBER()重写如下：

SELECT tmp.*
FROM (
    SELECT 
         /*+index(r C_RECRUIT_PK) */
        ROW_NUMBER() OVER(ORDER BY f.release_time DESC, f.ID DESC) AS ROW_NO,
         r.recruit_id, 
         r.job_name_cn, 
         r.salary, 
         i.company_id, 
         i.company_name
    FROM c_recruit r
    INNER JOIN fulltexthelp f ON 
        r.recruit_id = f.ID
    INNER JOIN c_info i ON
        f.company_id = i.company_id
    WHERE 
        AND contains (f.fulltext, '销售') > 0
        AND f.contract_status > 0
        AND f.recruit_type = 1
        AND r.release_status = 1
        AND r.recruit_type = 1
        AND r.release_time >= SYSDATE - 60
        AND f.release_time >= SYSDATE - 60
        AND r.release_time + r.hold_day >= SYSDATE
        AND r.approved_status = 1
        AND r.release_status = 1
        AND i.is_lock_flag = 0
        AND i.approved_status = 1
) tmp
WHERE tmp.ROW_NO >= 1 
    AND tmp.ROW_NO <= 100

我个人认为“contains (f.fulltext, '销售') > 0”不应该会引起大量的实时运算
所以我怀疑LZ的CONTEXT索引同步机制可能有问题

关于CONTEXT索引，可以参照这篇文章：
http://epub.itpub.net/4/1.htm