oracle like模糊查询的优化测试

来源：互联网发布：手机淘宝开店编辑：程序博客网时间：2024/05/13 19:58

三种like模糊查询的优化：
第一种：like 'a%'这种，直接可走索引
第二种：like '%a'这种，需要用reverse反转函数改写SQL，再建立函数索引，如下：
改写前:
   select * from test where owner like '%SCOTT'
   改写后：
   select * from test where reverse(owner) like reverse('%SCOTT');
第三种：like '%a%'这种的优化，进行如下测试：

原SQL:
SELECT A0.*
FROM g.wp A0
where A0.ptc_str_12TYPEINFOWABCDT like '%中ABCDdddddddd机%';

OS下执行：
export NLS_LANG=AMERICAN_AMERICA.utf8

SQL> set line 1000
SQL> set pagesize 1000
SQL> set autotrace traceonly
SQL> set timing on
SQL> SELECT A0.*
2 FROM g.wp A0
3 where A0.ptc_str_12TYPEINFOWABCDT like '%中ABCDdddddddd机%';

4694 rows selected.

Elapsed: 00:03:47.72

Execution Plan
----------------------------------------------------------
Plan hash value: 1382574215

----------------------------------------------------------------------------
| Id | Operation         | Name   | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |   348K|   330M|   323K (1)| 01:04:46 |
|* 1 | TABLE ACCESS FULL| WABCDT |   348K|   330M|   323K (1)| 01:04:46 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A0"."PTC_STR_12TYPEINFOWABCDT" IS NOT NULL AND
"A0"."PTC_STR_12TYPEINFOWABCDT" LIKE '%中ABCDdddddddd机%')

Statistics
----------------------------------------------------------
         29 recursive calls
          0 db block gets
    1292784 consistent gets
      39729 physical reads
          0 redo size
    3087832 bytes sent via SQL*Net to client
      52158 bytes received via SQL*Net from client
       4696 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       4694 rows processed
为了降低从磁盘中读取数据对查询时间的影响，再执行第二次：

SQL> SELECT A0.*
2 FROM g.wp A0
3 where A0.ptc_str_12TYPEINFOWABCDT like '%中ABCDdddddddd机%';

4694 rows selected.

Elapsed: 00:00:33.84
Execution Plan
----------------------------------------------------------
Plan hash value: 1382574215

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A0"."PTC_STR_12TYPEINFOWABCDT" IS NOT NULL AND
"A0"."PTC_STR_12TYPEINFOWABCDT" LIKE '%中ABCDdddddddd机%')

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
    1292776 consistent gets
          0 physical reads
          0 redo size
    3087832 bytes sent via SQL*Net to client
      52158 bytes received via SQL*Net from client
       4696 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       4694 rows processed

改写SQL:

SELECT A0.*
FROM g.wp A0
where instr(A0.ptc_str_12TYPEINFOWABCDT,'中ABCDdddddddd机')>0;

SQL> SELECT A0.*
2 FROM g.wp A0
3 where instr(A0.ptc_str_12TYPEINFOWABCDT,'中ABCDdddddddd机')>0;

4694 rows selected.

Elapsed: 00:03:36.91

Execution Plan
----------------------------------------------------------
Plan hash value: 1382574215

----------------------------------------------------------------------------
| Id | Operation         | Name   | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |   434K|   412M|   323K (1)| 01:04:46 |
|* 1 | TABLE ACCESS FULL| WABCDT |   434K|   412M|   323K (1)| 01:04:46 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(INSTR("A0"."PTC_STR_12TYPEINFOWABCDT",'中ABCDdddddddd机')>0)

Statistics
----------------------------------------------------------
         29 recursive calls
          0 db block gets
    1292793 consistent gets
      39727 physical reads
          0 redo size
    3087832 bytes sent via SQL*Net to client
      52158 bytes received via SQL*Net from client
       4696 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       4694 rows processed

建立函数索引：
create index gplm.fx_WABCDT_1 on g.wp(instr(ptc_str_12TYPEINFOWABCDT,'中ABCDdddddddd机')) tablespace gplmx parallel 16;
alter index gplm.fx_WABCDT_1 noparallel;

SQL> SELECT A0.*
2 FROM g.wp A0
3 where instr(A0.ptc_str_12TYPEINFOWABCDT,'中ABCDdddddddd机')>0;

4694 rows selected.

Elapsed: 00:00:02.31

Execution Plan
----------------------------------------------------------
Plan hash value: 3331311615

-------------------------------------------------------------------------------------------
| Id | Operation                   | Name        | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   434K|   412M| 12577   (1)| 00:02:31 |
|   1 | TABLE ACCESS BY INDEX ROWID| WABCDT      |   434K|   412M| 12577   (1)| 00:02:31 |
|* 2 |   INDEX RANGE SCAN          | FX_WABCDT_1 | 78135 |       |   145   (1)| 00:00:02 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(INSTR("PTC_STR_12TYPEINFOWABCDT",'中ABCDdddddddd机')>0)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       9603 consistent gets
          0 physical reads
          0 redo size
    3085926 bytes sent via SQL*Net to client
      52158 bytes received via SQL*Net from client
       4696 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       4694 rows processed

逻辑读从 1292776减少到9603，执行时间由33.84s降低到02.31s。但若用绑定变量的情况，不适合这种方法。

若用绑定变量的情况:
指定hint：
SELECT /*+index(A0 CLS_INDEX) */ A0.*
FROM WABCDT A0
where A0.ptc_str_12TYPEINFOWTPART like '中ABCDdddddddd机';
Elapsed: 00:00:05.00

Execution Plan
----------------------------------------------------------
Plan hash value: 4025305430

-----------------------------------------------------------------------------------------
| Id | Operation                   | Name      | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   348K|   330M|   141K (1)| 00:28:20 |
|   1 | TABLE ACCESS BY INDEX ROWID| WABCDT    |   348K|   330M|   141K (1)| 00:28:20 |
|* 2 |   INDEX FULL SCAN           | CLS_INDEX |   356K|       | 44775   (1)| 00:08:58 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("A0"."PTC_STR_12TYPEINFOWTPART" IS NOT NULL AND
"A0"."PTC_STR_12TYPEINFOWTPART" LIKE '中ABCDdddddddd机')

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      55985 consistent gets
          0 physical reads
          0 redo size
    3085926 bytes sent via SQL*Net to client
      52158 bytes received via SQL*Net from client
       4696 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       4694 rows processed

上面有用到index full scan，逻辑读减少很多，由1292784减少到55985，若所有需要访问的数据都在内存中，那么执行比较快，上面执行时间为5秒，反之，若需要的数据从磁盘中读取，是比较慢，所以采用将索引和表的数全部keep到cache中。

先查看db_keep_cache_size大小和索引及表的大小，若keep cache不足以存放表和索引，则需要调大参数。
SQL> show parameter keep

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
control_file_record_keep_time        integer     7
db_keep_cache_size                   big integer 15G

SQL> col segment_name for a20;
SQL> select segment_name,segment_type,bytes/1024/1024 "size(MB)" from dba_segments where owner='GPLM' and segment_name in ('WABCDT','CLS_INDEX');

SEGMENT_NAME         SEGMENT_TYPE         size(MB)
-------------------- ------------------ ----------
WABCDT               TABLE                   10075
CLS_INDEX            INDEX                     367

将索引和表keep到内存中：
alter index gplm.CLS_INDEX storage(buffer_pool keep);
alter table gplm.wtpart storage(buffer_pool keep);
alter table gplm.wtpart cache;

SQL> select index_name,buffer_pool from dba_indexes where index_name='CLS_INDEX';

INDEX_NAME BUFFER_
------------------------------ -------
CLS_INDEX KEEP

SQL> select owner,table_name,CACHE,BUFFER_POOL from dba_tables where table_name='WABCDT' and owner='GPLM';

OWNER TABLE_NAME CACHE BUFFER_
------------------------------ ------------------------------ -------------------- -------
GPLM WABCDT Y KEEP

上面显示已有正确keep表和索引到cache中。这样就可确保除第一次外，其它每次访问的数据都能从内存中获取。

看下count(*)的情况：

SQL> SELECT count(*)
2 FROM WABCDT A0
3 where A0.ptc_str_12TYPEINFOWABCDT like '%中ABCDdddddddd机%';

Elapsed: 00:00:02.04

Execution Plan
----------------------------------------------------------
Plan hash value: 3203503055

-----------------------------------------------------------------------------------
| Id | Operation             | Name      | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |    11 | 12209   (2)| 00:02:27 |
|   1 | SORT AGGREGATE       |           |     1 |    11 |            |          |
|* 2 |   INDEX FAST FULL SCAN| CLS_INDEX |   348K| 3742K| 12209   (2)| 00:02:27 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("A0"."PTC_STR_12TYPEINFOWABCDT" IS NOT NULL AND
"A0"."PTC_STR_12TYPEINFOWABCDT" LIKE '%中ABCDdddddddd机%')

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

上面用了INDEX FAST FULL SCAN，走索引的地方是conut(*),并不是where条件。

0 0