lfree介绍的是一种方法；RudolfLu曾经也介绍过一种方法，就是根据搜索字段+rowid建立一个物理表，对这个物理表进行全表，相较ffs会更快一些，不过需要定期刷新物理表，适用于变化较少，查询要求实时性要求不高的环境。

其实也可以用Oracle的全文检索技术，可能会有更好的结果，看我以下的一个例子：

PHP code:
1.这是模糊查询的结果

SQL> select count(*) from jivemessage where subject like '%abc%';

COUNT(*)

----------

13

Elapsed: 00:00:15.31

2.这是全文检索的结果：需要配置ORACLE Text

Text也不是实时的，它的index也是定时更新的。

CTXCAT类型的索引是实时的,而且适合长度不很长的字段.

SQL> select count(*) from jivemessage where contains(subject,'abc')>0;

COUNT(*)

----------

25

Elapsed: 00:00:00.03

3.模糊查询的内容

SQL> select subject from jivemessage where subject like '%abc%';

SUBJECT

------------------------------------------------------------------------

abc

测试发贴""abc

abc

Re: abc

Re: abc

Re: abc

<testabcde>

初夜abc

abcd

abc

abc

SUBJECT

--------------------------------------------------------------------------------

哈哇abc

http;//baoxing.168abc.com

13 rows selected.

Elapsed: 00:00:03.29

4.也许这是我们更想要的结果：需要配置ORACLE Text

SQL> select subject from jivemessage where contains(subject,'abc')>0;

SUBJECT

------------------------------------------------------------------------

【游戏】把你的名字的首字母用智能ABC打出，看能出来什么？

游戏——把你的名字首字母用智能ABC打出来

智能ABC暗藏杀机

ABC

ABC

ABC

ABC

ABC

ＡＢＣ

哈哇abc

abc

SUBJECT

------------------------------------------------------------------------

abc

ABC

振奋爱的激情方案ABC

智能ABC的错吗？

ABC全选

瓜果美容ABC

经典英文歌曲ABC，不好你拿版砖砍我，好就回帖顶一下！

初夜abc

Re: abc

Re: abc

Re: abc

SUBJECT

------------------------------------------------------------------------

abc

测试发贴""abc

abc

25 rows selected.

Elapsed: 00:00:00.04

SQL>

----------------------------------------------------------------------------------------

顺手做了个测试：根据搜索字段+rowid建立一个物理表

PHP code:


CREATE TABLE test_ffs NOLOGGING AS SELECT * FROM ALL_OBJECTS ;


 INSERT /*+append */ INTO test_ffs  SELECT * FROM test_ffs ;


  COMMIT;


 INSERT /*+append */ INTO test_ffs  SELECT * FROM test_ffs ;


  COMMIT;


 INSERT /*+append */ INTO test_ffs  SELECT * FROM test_ffs ;


  COMMIT;


 INSERT /*+append */ INTO test_ffs  SELECT * FROM test_ffs  ;


  COMMIT;


CREATE INDEX object_name ON test_ffs(object_name) ;





SELECT


       org.*


  FROM test_ffs org, (SELECT /*+no_merge index_ffs(test_ffs  object_name ) */


                             ROWID AS r, object_name 


                        FROM test_ffs


                       WHERE object_name LIKE '%A%' ) tmp


 WHERE org.ROWID = tmp.r ;





...

效果还可以

----------------------------------------------------------------------------------------

想了想，可能只要加了index_ffs，如果存在该index，就全索引扫描，

like '%%'是不能走range/unique index，而不是不能走index_ffs

SQL> CREATE INDEX i_dept_name ON SCOTT.DEPT(DNAME);

索引已创建。

SQL> select /*+ index_ffs(a i_dept_name) */ rowid
  2  from scott.dept a
  3  where dname like '%A%';

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=4 Bytes=64)
1 0 INDEX (FAST FULL SCAN) OF 'I_DEPT_NAME' (NON-UNIQUE) (Cost
      =4 Card=4 Bytes=64)

SQL>

----------------------------------------------------------------------------------------

试了一把rolling的

SQL> r
  1  SELECT
  2       org.*
  3 FROM test_ffs org, (SELECT /*+no_merge index_ffs(test_ffs  object_name ) */
  4                            ROWID AS r, object_name
  5                         FROM test_ffs
  6                      WHERE object_name LIKE '%A%' ) tmp
  7*  WHERE org.ROWID = tmp.r

已选择8287行。

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT ptimizer=CHOOSE (Cost=151 Card=1792 Bytes=
      254464)

1 0 HASH JOIN (Cost=151 Card=1792 Bytes=254464)
2 1    VIEW (Cost=4 Card=1792 Bytes=12544)
3 2    INDEX (FAST FULL SCAN) OF 'OBJECT_NAME' (NON-UNIQUE) (
      Cost=4 Card=1792 Bytes=43008)

4 1    TABLE ACCESS (FULL) OF 'TEST_FFS' (Cost=134 Card=35834 B
      ytes=4837590)

Statistics
----------------------------------------------------------
      0  recursive calls
      18  db block gets
   1750  consistent gets
      0  physical reads
      0  redo size
   957705  bytes sent via SQL*Net to client
   72815  bytes received via SQL*Net from client
      554  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   8287  rows processed

SQL>

----------------------------------------------------------------------------------------

拿rollingpig的数据做了个例子：

SQL> CREATE TABLE test_ffs NOLOGGING AS SELECT * FROM ALL_OBJECTS ;

Table created

SQL>  INSERT /*+append */ INTO test_ffs  SELECT * FROM test_ffs ;

32071 rows inserted

SQL> COMMIT;

Commit complete

SQL>  INSERT /*+append */ INTO test_ffs  SELECT * FROM test_ffs ;

64142 rows inserted

SQL> COMMIT;

Commit complete

SQL>  INSERT /*+append */ INTO test_ffs  SELECT * FROM test_ffs ;

128284 rows inserted

SQL> COMMIT;

Commit complete

SQL>  INSERT /*+append */ INTO test_ffs  SELECT * FROM test_ffs  ;

256568 rows inserted

SQL> COMMIT;

Commit complete

SQL> CREATE INDEX object_name ON test_ffs(object_name) ;

Index created

SQL> set timing on
SQL> set autot traceonly
SQL>  Select /*+first_rows()*/
  2       org.*
  3 FROM test_ffs org, (SELECT /*+no_merge index_ffs(test_ffs  object_name )
*/
  4                            ROWID AS r, object_name
  5                         FROM test_ffs
  6                      WHERE object_name LIKE '%SAXA%' ) tmp
  7 WHERE org.ROWID = tmp.r ;

已选择32行。

已用时间:  00: 00: 01.03

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT ptimizer=HINT: FIRST_ROWS (Cost=1858 Card=
      1854 Bytes=263268)

1 0 NESTED LOOPS (Cost=1858 Card=1854 Bytes=263268)
2 1    VIEW (Cost=4 Card=1854 Bytes=12978)
3 2    INDEX (FAST FULL SCAN) OF 'OBJECT_NAME' (NON-UNIQUE) (
      Cost=4 Card=1854 Bytes=44496)

4 1    TABLE ACCESS (BY USER ROWID) OF 'TEST_FFS' (Cost=1 Card=
      1 Bytes=135)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   2507  consistent gets
      0  physical reads
      180  redo size
   3129  bytes sent via SQL*Net to client
      525  bytes received via SQL*Net from client
      4  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      32  rows processed

SQL> Select /*+first_rows()*/
  2       org.*
  3 FROM test_ffs org, (SELECT /*+no_merge index(test_ffs  object_name ) */
  4                            ROWID AS r, object_name
  5                         FROM test_ffs
  6                      WHERE object_name LIKE '%SAXA%' ) tmp
  7 WHERE org.ROWID = tmp.r ;

已选择32行。

已用时间:  00: 00: 01.02

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT ptimizer=HINT: FIRST_ROWS (Cost=1856 Card=
      1854 Bytes=263268)

1 0 NESTED LOOPS (Cost=1856 Card=1854 Bytes=263268)
2 1    VIEW (Cost=2 Card=1854 Bytes=12978)
3 2    INDEX (RANGE SCAN) OF 'OBJECT_NAME' (NON-UNIQUE) (Cost
      =2 Card=1854 Bytes=44496)

4 1    TABLE ACCESS (BY USER ROWID) OF 'TEST_FFS' (Cost=1 Card=
      1 Bytes=135)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   2484  consistent gets
      0  physical reads
      0  redo size
   3129  bytes sent via SQL*Net to client
      525  bytes received via SQL*Net from client
      4  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      32  rows processed

SQL>

我特地限制让取出的数据较少，这时用index hint，可以走INDEX (RANGE SCAN)，效果略微好一些

ITPub博客

有什么办法可以提高like查询的性能

最新文章