[20160706]like % 绑定变量.txt

最新推荐文章于 2021-02-18 12:07:35 发布
最新推荐文章于 2021-02-18 12:07:35 发布
阅读量151 收藏
点赞数

[20160706]like  % 绑定变量.txt

--最近一直在优化一个项目，程序中存在大量的like模糊查询，例子：
/* Formatted on 2016/7/6 11:10:55 (QP5 v5.252.13127.32867) */
SELECT PATIENT_ID
      ,NAME
      ,RIS_NO
      ,SEX
      ,EXAM_ITEM
      ,DATE_OF_BIRTH
      ,LASTSAVEUSER
      ,EXAM_PARA
      ,PATIENT_LOCAL_ID
      ,MODALITY
      ,EXAM_CLASS
      ,EXAM_SUB_CLASS
      ,DEVICE
      ,RESULT_STATUS
      ,EXAM_DATE_TIME
      ,STUDY_UID
      ,LASTSAVETIME
      ,CHECKUSER
      ,NORMALNAME
      ,CHECKSTATE
      ,OUT_URL
      ,CHECKUSERID
      ,CREATEUSER
      ,CHECKTIME
      ,PRINT_STATUS
      ,IS_ABNORMAL
  FROM REPORTQUERY
WHERE     patient_id LIKE :V001
       AND (LASTSAVETIME BETWEEN :V002 AND :V003)
       AND (checkstate = :V004)
       AND (modality = :V005)
       AND ROWNUM < 1001

SQL_ID        CHILD_NUMBER WAS NAME                   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- --------------------
222m95hh0kvgh            1 YES :V001                         1        128 2016-07-02 10:53:34 VARCHAR2(128)   %TJ1431070%
                           YES :V002                         2         32 2016-07-02 10:53:34 VARCHAR2(32)    2016-07-02 00:00:00
                           YES :V003                         3         32 2016-07-02 10:53:34 VARCHAR2(32)    2016-07-02 23:59:59
                           YES :V004                         4       2000 2016-07-02 10:53:34 VARCHAR2(2000)  40
                           YES :V005                         5        128 2016-07-02 10:53:34 VARCHAR2(128)   US

--//REPORTQUERY是视图，本来选择patient_id是很快的查询，由于前面的%,导致执行计划选择LASTSAVETIME，而这个范围是1天(有时候查询1个月)，导致
--//大量的逻辑读。我自己做一些测试想看看使用绑定变量的情况。

1.环境：
SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.建立测试例子：
create table tx (id number,v varchar2(20),nv nvarchar2(20),pad varchar2(200));
insert into tx select rownum,lpad(rownum,6,'0'),lpad(rownum,6,'0'),lpad('x',200,'x') from dual connect  by level<=1e5;
commit ;
create index i_tx_v on tx(v) PCTFREE 50;
create index i_tx_nv on tx(nv) PCTFREE 50;
execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 'tx',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);

3.测试：
SCOTT@book> column pad noprint
SCOTT@book> alter session set statistics_level=all;
Session altered.

SCOTT@book> select * from tx where v like '%000042%';
        ID V                    NV
---------- -------------------- ----------------------------------------
        42 000042               000042

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  c9z5cg37cu8yz, child number 0
-------------------------------------
select * from tx where v like '%000042%'
Plan hash value: 40191160
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |   888 (100)|          |      1 |00:00:00.06 |    3275 |
|*  1 |  TABLE ACCESS FULL| TX   |      1 |   5000 |  1103K|   888   (1)| 00:00:11 |      1 |00:00:00.06 |    3275 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TX@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("V" LIKE '%000042%' AND "V" IS NOT NULL))

--如果查询select * from tx where v like '000042%';是可以使用索引的。

4.如果使用绑定变量呢?
SCOTT@book> variable x varchar2(20);
SCOTT@book> exec :x := '%000042%';
PL/SQL procedure successfully completed.

SCOTT@book> select * from tx where v like :x;
        ID V                    NV
---------- -------------------- ----------------------------------------
        42 000042               000042

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  87rcszfzcz5gu, child number 0
-------------------------------------
select * from tx where v like :x
Plan hash value: 3964412060
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |       |   187 (100)|          |      1 |00:00:00.07 |     464 |    461 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TX     |      1 |   5000 |  1103K|   187   (0)| 00:00:03 |      1 |00:00:00.07 |     464 |    461 |
|*  2 |   INDEX RANGE SCAN          | I_TX_V |      1 |   5000 |       |    25   (0)| 00:00:01 |      1 |00:00:00.07 |     463 |    461 |
-----------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TX@SEL$1
   2 - SEL$1 / TX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - (VARCHAR2(30), CSID=852): '%000042%'
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("V" LIKE :X)
       filter("V" LIKE :X)

--可以发现绑定变量是使用索引的。有点奇怪的是估计返回相同的行相同，而带入参数选择索引，估计索引群集因子很小。

--把索引建立大一些看看。
SCOTT@book> alter index i_tx_v rebuild pctfree 90;
Index altered.

SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 'tx',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);
PL/SQL procedure successfully completed.

SCOTT@book> select * from tx where v like :x;
        ID V                    NV
---------- -------------------- ----------------------------------------
        42 000042               000042

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  87rcszfzcz5gu, child number 0
-------------------------------------
select * from tx where v like :x
Plan hash value: 3964412060
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |       |   307 (100)|          |      1 |00:00:00.07 |    2862 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TX     |      1 |   5000 |  1103K|   307   (0)| 00:00:04 |      1 |00:00:00.07 |    2862 |
|*  2 |   INDEX RANGE SCAN          | I_TX_V |      1 |   5000 |       |   145   (0)| 00:00:02 |      1 |00:00:00.07 |    2861 |
--------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TX@SEL$1
   2 - SEL$1 / TX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - (VARCHAR2(30), CSID=852): '%000042%'
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("V" LIKE :X)
       filter("V" LIKE :X)
--//这样并没有改变群集因子,cost增加不大。

--//oracle 估计返回5000行，占5000/100000=5%.oracle也是选择索引范围扫描。
--//这种情况是选择索引好还是全表扫描还呢？主要看返回的行数量。
--//oracle很奇怪，前面的直接打入文字变量，估计返回也是5000，但是执行计划选择的是全表扫描。

SCOTT@book> exec :x := '%00%';
PL/SQL procedure successfully completed.

Plan hash value: 3964412060
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |       |   307 (100)|          |  12520 |00:00:00.08 |    4242 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TX     |      1 |   5000 |  1103K|   307   (0)| 00:00:04 |  12520 |00:00:00.08 |    4242 |
|*  2 |   INDEX RANGE SCAN          | I_TX_V |      1 |   5000 |       |   145   (0)| 00:00:02 |  12520 |00:00:00.07 |    2923 |
--------------------------------------------------------------------------------------------------------------------------------

--执行计划不变。重新建立表ty。大乱顺序，提高索引的群集因子。

SCOTT@book> create table ty as select * from tx order by DBMS_RANDOM.VALUE;
Table created.

create index i_ty_v on ty(v) PCTFREE 10;
create index i_ty_nv on ty(nv) PCTFREE 10;

--分析表，忽略。
SCOTT@book> exec :x := '%00%';
PL/SQL procedure successfully completed.

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  50t5atjp1pjh9, child number 0
-------------------------------------
select * from ty where v like :x
Plan hash value: 1260447134
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |   893 (100)|          |  12520 |00:00:00.07 |    3284 |   3226 |
|*  1 |  TABLE ACCESS FULL| TY   |      1 |   5000 |  1103K|   893   (1)| 00:00:11 |  12520 |00:00:00.07 |    3284 |   3226 |
-----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TY@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - (VARCHAR2(30), CSID=852): '%00%'
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("V" LIKE :X)

--可以看到群集因子对执行计划影响很大。实际上这种执行计划如果返回行很少，使用索引更佳。

--//我个人的建议最好少用模糊查询，开发往往说这个是用户要求，实际上如果最后很慢了，用户往往抱怨不断。我自己很久以前就遇到类似的情况。
--//我还建议开发可以做一个按钮，缺省做精确查询，用户要模糊查询必须选中它。如果模糊很慢，以后用户自然会少用，可惜大部分开发不愿意做.....
--//当然现在有许多方法来稳定执行计划。sql profile ,spm都可以实现。但是实际上这种是以扫描整个索引为代价，而且是单块读。
--//我以前的做法就是人为修改统计信息，lock统计，增加表的块数量，导致执行计划选择索引，因为大多数情况返回的行很少，很多查询基本没有意义。
--//比如你查询 name like '%李%'，这样返回很多行，用户不大可能在里面查找需要的信息。

SCOTT@book> EXECUTE SYS.DBMS_STATS.set_table_stats (OWNNAME=>user, TABNAME=>'ty', numblks=> 8888888);
PL/SQL procedure successfully completed.

SCOTT@book> select * from ty where v like :x;
        ID V                    NV
---------- -------------------- ----------------------------------------
        42 000042               000042

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  50t5atjp1pjh9, child number 1
-------------------------------------
select * from ty where v like :x
Plan hash value: 1299836486
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |       |  5014 (100)|          |      1 |00:00:00.06 |     255 |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TY     |      1 |   5000 |  1103K|  5014   (1)| 00:01:01 |      1 |00:00:00.06 |     255 |      1 |
|*  2 |   INDEX RANGE SCAN          | I_TY_V |      1 |   5000 |       |    14   (0)| 00:00:01 |      1 |00:00:00.06 |     254 |      0 |
-----------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TY@SEL$1
   2 - SEL$1 / TY@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - (VARCHAR2(30), CSID=852): '%000042%'
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("V" LIKE :X)
       filter("V" LIKE :X)

--总之，开发写代码不要太随意了，认真思考可能产生的结果。看问题看得长远一些。
--当使用模糊like时，里面的查询条件一定要注意。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2121592/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2121592/

cuixun7780
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Android中的SQL查询语句LIKE绑定参数问题解决办法（sqlite数据库）
09-04
本文将详细介绍如何解决在Android的SQLite数据库中使用LIKE绑定参数的问题。 首先，我们来看一个错误示例： ```java String myname = "abc"; String sql = "select * from mytable where name like '?%'"; Cursor ...
spawn-fcgi-1.6.4.exe for windows
12-01
2. **添加环境变量**：为了方便在命令行中调用`spawn-fcgi.exe`，需要将其路径添加到系统环境变量`PATH`中。 3. **安装依赖**：在Windows环境下，可能需要安装Perl解释器，因为`spawn-fcgi`在某些情况下会使用Perl...
动态SQ中like 条件子句中使用绑定变量
csdm93728的博客
09-29 464
create table test as select to_char(col) col,owner, object_name, subobject_name, object_id, data_object_id, object...
Oracle硬解析与软解析：码硬解析的改进方法之二使用绑定变量
07-04 306
绑定变量要求变量名称，数据类型以及长度是一致，否则无法使用软解析 绑定变量（bind variable）是指在DML语句中使用一个占位符，即使用冒号后面紧跟变量名的形式，如下 select * from em...
SQL模糊查询时LIKE怎样跟变量
a5826556的博客
02-18 3746
一般模糊查询语句如下： SELECT 字段 FROM 表 WHERE 某字段 Like 条件 其中关于条件，SQL提供了四种匹配模式： % ：表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。 比如 SELECT * FROM [user] WHERE u_name LIKE ‘%三%’ 如何将%三%的值作为变量使用？ “SELECT * FROM fbAPI WHERE shuju LIKE ‘%{}%’”.format(变量名) 下面用python作为
[备查].NET Like模式匹配
weixin_33795833的博客
07-09 117
2019独角兽企业重金招聘Python工程师标准>>> ...
.NET中LIKE语句
smpig的专栏
03-13 3363
错误: string query = @"SELECT  gx.GXBM ,                                     gx.MC ,                                     gx.QDZC ,                                     gx.ZDZC ,
PLSQL绑定变量用法小结[收集].pdf
10-11
在Oracle数据库的PL/SQL环境中，绑定变量是一种优化SQL语句执行效率的重要技术。它主要应用于动态SQL或者多次执行的SQL语句，以减少数据库的解析开销，提高系统性能。本文将详细讲解PLSQL中的绑定变量及其用法。 ...
ele-like-main.zip
07-07
"ele-like-main.zip"是一个压缩包，据其标题所示，它包含了仿造饿了么移动端应用的源代码。饿了么是一款知名的在线餐饮订餐平台，它的移动应用因其优秀的用户体验和功能完整性而广受赞誉。这个项目可能是为了教学、...
微信小程序练习demo：数据绑定条件渲染练习117.zip
05-04
在小程序中，数据绑定使用Mustache-like语法，例如`{{ }}`，将变量或表达式嵌入到模板中。这种方式使得UI能够实时反映出数据的变化，减少了手动操作DOM的复杂性。在"wx-small-program-master"这个项目中，你可能会...
.net标准化查询中使用like的用法
muximuxi_kgsecond的专栏
12-26 473
使用like的时候语句应该这样写： “Select * From table Where field like ’%‘+@field+’%‘”； 而不是    “Select * From table Where field like %@field%”. 知道是识别不了%.具体原因不知道..但是特别要感谢这个博主..为了写这个的正确行使,我花了1个小时在教头烂耳
jdbc中使用like模糊查询时使用绑定变量的方法
wsyyyyy的博客
01-05 1302
@RequestMapping(value="/local/instances", method = RequestMethod.GET) public Map getLocalInstances( @RequestParam String page,@RequestParam String limit,@RequestParam(required = false) String searchi...
ACCESS中SQL语句之“like” 语句写法
aolia2000的专栏
08-02 1270
http://www.zxbc.cn/html/20080408/33138.html   一般sql中的like语句，通配符是%， 但select * from table1 where name like "zh%" 一句在access中没有效，需要改成：selec
ORACLE 绑定变量用法总结
热门推荐
朝着梦想 渐行前进
11-06 4万+
之前对ORACLE中的变量一直没个太清楚的认识，比如说使用：、&、&&、DEIFINE、VARIABLE……等等。今天正好闲下来，上网搜了搜相关的文章，汇总了一下，贴在这里，方便学习。 ==================================================================================  在oracle 中，对于一
Java中使用绑定变量的方法其好处
风起~尘扬
11-05 1171
Java中使用绑定变量的方法其好处
string sql = "select * from parts where 1=1"; if (houseid.Text.Length > 0) { sql += " and 库房编号='" + houseid.Text + "'";} if (partsid.Text.Length > 0) {sql += " and 零件号='" + partsid.Text + "'";} if (partsname.Text.Length > 0) {sql += " and 零件名 like '%" + partsname.Text + "%'";} BindDgv(sql);
最新发布
06-12
其中，首先定义了一个字符串变量 `sql`，用于存储 SQL 查询语句。然后通过 `if` 判断语句，对 `sql` 进行动态拼接，根据用户输入的不同查询条件，拼接出不同的 SQL 语句。最后，调用 `BindDgv` 方法，将拼接出来的 ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值