注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Memory extender

Beautiful Day..

 
 
 

日志

 
 

Bind Variable  

2010-10-15 15:14:49|  分类: SQL&PL/SQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
原来Tom说的一定要使用Bind Variable是指动态SQL时。
摘录:
Never never never do dynamically what you can do statically in plsql!!! 
...so obviously static sql is faster ...

Well, in order to use bind variables -- just use PLSQL variables!!! PLSQL is awesome for 
this reason, every reference to a PLSQL variable is in fact a BIND VARIABLE.

...
With this query -- the value that is hard coded is also IMMUTABLE, it'll never change.  This query 
is shareable -- everyone that runs the procedure, runs the same exact sql.  Its perfect.  No need 
to bind in this particular case.

It is when you are dynamically building a query -- putting hard coded literals in there -- that you 
MUST bind.

The link is my book actually.  I go over it time and time and time again.  There are many things to 
consider - increased parse times due to hard parses instead of soft, long lines to get into the 
library cache due to excessive latching caused by hard parses, excessive CPU used to optimize 
queries, trashing of the shared pool since it fills up with unique statements that will never be 
reused.....

A query like this:

 select * from emp where empno = 5;

If executed over and over and over -- is just as shareable as:

 select * from emp where empno = :x;


You need not bind that particular one  -- if you execute it OVER and OVER and OVER.  It is when 
people build queries on the fly (using EXECUTE IMMEDIATE or DBMS_SQL in plsql) that you want to 
take care to BIND values.

Anytime you have STATIC sql in PLSQL -- your job is done.  All plsql variable references will be 
bound and any literals in there are OK cause the query will be reused over and over again.



摘自
  评论这张
 
阅读(671)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017