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

lfree

最多使用ffs，其他没有什么好方法！

lfree

全索引扫描。
例子：

select a,b,c from foo whee d like '%sdsds%'

建立索引包含d,a,b,c.
讲的再具体一点，将索引当作表，扫描整个索引就可以知道结果。
前提就是至少有一个字段不能为null。

lfree

我想起来我们的一个程序也有这样的现象，开发人员是这样做的。

select * from foo where id in (select id from foo where name like '%ddd%' )

建立id,name索引。这样可以减少执行时间。

lfree

这是一个极端的例子，为了使用索引，我修改了optimizer_index_cost_adj 与optimizer_index_caching
参数，我建立deptno+dname的索引并分析了表。
scott@ORCL> CREATE UNIQUE INDEX SCOTT.I_DEPT_DEPTNO_DNAME ON SCOTT.DEPT
  2  (DEPTNO, DNAME);
Index created.
scott@ORCL> Analyze Table SCOTT.DEPT Compute Statistics ;
Table analyzed.
scott@ORCL> ALTER SESSION SET optimizer_index_cost_adj = 1;
Session altered.
scott@ORCL> ALTER SESSION SET optimizer_index_caching = 100 ;
Session altered.
scott@ORCL> SELECT *
  2    FROM scott.dept
  3   WHERE deptno IN (SELECT deptno
  4                      FROM scott.dept
  5                     WHERE dname LIKE '%A%');

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=24)
   1    0   NESTED LOOPS (Cost=2 Card=1 Bytes=24)
   2    1     INDEX (FULL SCAN) OF 'I_DEPT_DEPTNO_DNAME' (UNIQUE) (Cost=26 Card=1 Bytes=9)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=5 Bytes=75)
   4    3       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

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

scott@ORCL>

实际上这样执行的前提条件就是like返回的记录很少，就可以，否则读取量可能没有全表扫描好。

lfree

问题并不能这么讲，我仅仅说明这样是可以执行的，你可以想像一下，假设
like的结果返回几条记录，速度是很快的，至少避开了全表扫描。等一下我再找一个实际的例子。
最主要的看的具体的实际应用。

lfree

另外讲一下实际上在CBo下使用IN会出现许多种可能的执行计划。
不要把一切都看死了。
http://www.fortunecity.com/skysc ... ml/ioug/sql_in.html

lfree

最初由 lijietz 发布
[B]ALTER SESSION SET optimizer_index_cost_adj = 1;？？？？？
都如你这般，cbo想不走index都难。
。。^_^。。

参数被你这么一修改，所有执行计划全都要变。。 [/B]

可以使用hint，我仅仅是举例。

lfree

最初由 rollingpig 发布
[B]

Good idea,

select * from foo where rowid in (select /*+index_ffs(foo ind_name) */rowid from foo where name like '%ddd%' ) [/B]

总算还遇到一个知己。我给承认你的方法更好！

lfree

scott@ORCL> set autotrace trace
scott@ORCL> CREATE INDEX SCOTT.i_dept_name
  2   ON SCOTT.DEPT(DNAME)
  3  ;

Index created.

scott@ORCL> Analyze Table SCOTT.DEPT Compute Statistics ;

Table analyzed.

scott@ORCL> select * from scott.dept where
  2  rowid in (
  3  select /*+ index_ffs(a i_dept_dname) */
  4  rowid from scott.dept a where dname like '%A%')
  5  ;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=36)
   1    0   NESTED LOOPS (Cost=2 Card=1 Bytes=36)
   2    1     INDEX (FULL SCAN) OF 'I_DEPT_NAME' (NON-UNIQUE) (Cost=1 Card=1 Bytes=15)
   3    1     TABLE ACCESS (BY USER ROWID) OF 'DEPT' (Cost=1 Card=5 Bytes=105)




Statistics
----------------------------------------------------------
         48  recursive calls
          0  db block gets
         27  consistent gets
          0  physical reads
          0  redo size
        678  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          4  rows processed

scott@ORCL>

lfree

第二次执行更快。一致读的更少，实际上这个立即的前提条件就是like返回的记录要很少，执行计划才有力。

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