表中将近有1100万数据,很多时候,我们要执行 字符串匹配,在SQL语句中,我们通常运用 like来达到我们搜索的目标。但经过实际测试发觉,like的效率与instr函数差别相当大。下面是一些测试结果:
SQL> set timing on
SQL> select count(*) from t where instr(title,’手册’)>0;
COUNT(*)
———-
65881
Elapsed: 00:00:11.04
SQL> select count(*) from t where title like ‘%手册%’;
COUNT(*)
———-
65881
Elapsed: 00:00:31.47
SQL> select count(*) from t where instr(title,’手册’)=0;
COUNT(*)
———-
11554580
Elapsed: 00:00:11.31
SQL> select count(*) from t where title not like ‘%手册%’;
COUNT(*)
———-
11554580
注:
instr(title,’手册’)>0 相当于like
instr(title,’手册’)=0 相当于not like
--------------------------------------------------------------------------------------------------------
SQL> create table test as select * from dba_objects;
表已创建。
SQL> select owner,object_name from test where owner like 'ROBINSON%';
OWNER OBJECT_NAME
------------------------------------------------------------ ------------------------------
ROBINSON TEST
ROBINSON T2
ROBINSON T1
ROBINSON T3
ROBINSON T4
ROBINSON HEX_TO_BIN
已选择6行。
SQL> create index i_owner on test(owner);
索引已创建。
SQL> update test set wner='SYSMAN' where owner!='ROBINSON';
已更新50353行。
SQL> update test set wner='SYSTEM' where object_id>1000 and object_id<1020;
已更新19行。
SQL> commit;
提交完成。
SQL> update test set wner='SCOTT' where object_id>2000 and object_id<2005;
已更新4行。
SQL> select distinct owner from test;
OWNER
------------------------------------------------------------
ROBINSON
SYSTEM
SCOTT
SYS
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ROBINSON',
3 tabname => 'TEST',
4 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
5 method_opt => 'for all columns size repeat',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /
PL/SQL 过程已成功完成。
SQL> set autot traceonly
SQL> select owner,object_name from test where owner not like 'SYS%';
已选择10行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25180 | 737K| 142 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST | 25180 | 737K| 142 (3)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER" NOT LIKE 'SYS%')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
从此处可以看到选择了10行,但是却走了全表扫描,现在对其加HINT提示
SQL> select /*+ INDEX (test I_OWNER) */ owner,object_name from test where owner not like 'SYS%';
已选择10行。
执行计划
----------------------------------------------------------
Plan hash value: 469605390
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25180 | 737K| 490 (1)| 00:00:06 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 25180 | 737K| 490 (1)| 00:00:06 |
|* 2 | INDEX FULL SCAN | I_OWNER | 25180 | | 140 (3)| 00:00:02 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER" NOT LIKE 'SYS%')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
我们发现,强制加HINT提示所花的成本比全表扫描成本更高,这里要想优化这条SQL 必须改like
SQL> drop index i_owner;
索引已删除。
SQL> create index f_owner on test(instr(owner,'SYS',1,1)) ;
索引已创建。
SQL> select owner,object_name from test where instr(owner,'SYS',1,1)=0;
已选择10行。
执行计划
----------------------------------------------------------
Plan hash value: 535182827
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 504 | 15120 | 52 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 504 | 15120 | 52 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | F_OWNER | 201 | | 49 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(INSTR("OWNER",'SYS',1,1)=0)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
当使用函数索引后,COST由原来的142下降到现在的52
总结:对于LIKE语句,我们可以使用instr函数来进行SQL调优
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/robinson1988/archive/2010/03/23/5408253.aspx
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21916216/viewspace-630315/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21916216/viewspace-630315/