ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 有什么办法可以提高like查询的性能

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

原创 Linux操作系统 作者:fengjin821 时间:2009-06-13 13:28:49 0 删除 编辑
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
.

 

Elapsed00: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

abc

 

SUBJECT

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

abc

ABC

振奋爱的激情方案ABC

智能ABC的错吗?

ABC全选

瓜果美容ABC

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

初夜abc

Re
abc

Re
abc

Re
abc

 

SUBJECT

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

abc

测试发贴
&quot;&quot;
abc

abc

 

25 rows selected
.

 

Elapsed00: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.
;



...

效果还可以

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

 

 

想了想,可能只要加了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博客 ” ,链接:http://blog.itpub.net/13165828/viewspace-606191/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-29

  • 博文量
    191
  • 访问量
    506524