SQL 语句中对于like 的调优
来源:互联网 发布:python 考试系统怎么写 编辑:程序博客网 时间:2024/06/13 04:47
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 owner='SYSMAN' where owner!='ROBINSON';
已更新50353行。
SQL> update test set owner='SYSTEM' where object_id>1000 and object_id<1020;
已更新19行。
SQL> commit;
提交完成。
SQL> update test set owner='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行。
--------------------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - access(INSTR("OWNER",'SYS',1,1)=0) 当使用函数索引后,COST由原来的142下降到现在的52 总结:对于LIKE语句,我们可以使用instr函数来进行SQL调优
执行计划
----------------------------------------------------------
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 |
---------------------------------------------------------------------------------------
---------------------------------------------------
统计信息
----------------------------------------------------------
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
- SQL 语句中对于like 的调优
- sql中like语句
- sql 中like语句
- hql语句中like查询 对于 / ' " % 的处理
- hql语句中like查询 对于 / ' " % 的处理
- sql 语句中对like 的优化
- SQL的like语句
- jdbc对于模糊查询like语句嵌套变量的sql语句写法
- access 中sql语句之“like” 语句的注意写法
- access 中sql语句之“like” 语句的注意写法
- sql中like语句详解
- sql中like语句详解
- sql中like语句详解
- delphi中sql语句like
- c#中sql语句中传递Like参数的写法
- sql语句like的用法
- Like SQL语句的优化
- sql语句like的用法
- WScript.Shell 的run和exec在运行taskkill的区别
- 如何让一个button只能点一下?防止点击多下而重复提交?
- 数据结构 栈以及出栈算法
- 在C#中完成海量数据的批量插入和更新
- 改善你的jQuery的25个小贴士
- SQL 语句中对于like 的调优
- 软件设计师与嵌入式系统设计师
- 基于mini2440的qtopia应用程序开发之一:搭建平台+友善之臂的错误分析
- o3d教程3 - 模型变换
- Symbian OS 写入专用APN
- 客户端AI和服务器端AI
- (转)C++中extern “C”含义深层探索
- 友善micro2440(s3c2440) U-boot移植过程
- ExtJs与JSON格式数据绑定的方法