关于对象重新编译的问题

• 问题
• 编译
• 重新
• 对象
• 关于
• 过程
• 没有
• 回复
• 一个
• sel

[color=red:dfc2685962][b:dfc2685962]     奇怪的问题[/b:dfc2685962][/color:dfc2685962]
在你的数据库使用过程中遇到过以下的奇怪现象吗？
假设过程sp_select的功能是从某用户表中取得数据：
create procedure sp_select 
as
select * from test
go
但是过程执行的结果与手工执行select * from test的结果就是不一样！
又譬如你使用alter table语句为表test增加了一列，但过程的输出总是不会显示出新加的列！
这些问题我在三年前的一次应用测试中首次遇到，其后又遇到两三次，当今天我的同事又来问我这个问题时，我觉得有必要总结一下了。

 joey 回复于：2003-09-09 18:11:09[color=red:52017db352][b:52017db352]     问题的模拟[/b:52017db352][/color:52017db352]
如果你没有碰到过此类的问题，那么不妨先模拟一下这两个问题，然后再来寻求解决的办法。
为了方便下文的引用，我们称着两个问题为sp_rename问题和alter table问题。
lsp_rename问题的模拟
1)首先创建所有的过程与表
create table test ( a char(4))
insert into test select ‘OLD’
create proc sp_select as select * from test
试一下exec sp_select，输出为:
a           
 ---- 
 OLD
(1 row affected)
(return status = 0)
现在一切正常
2)将表test改名，并新建一个名为test的表(注意，新的表中没有数据)
sp_rename test, test_old
create table test ( a char(4))
insert into test select ‘NEW’
3)测试
这时问题出现了
select * from test的输出：
a    
 ---- 
 NEW  
(1 row affected)
而sp_select的输出为：
a    
 ---- 
 OLD  
(1 row affected)
(return status = 0)
sp_select仍然显示老表中的数据，而非表test中的内容
lalter table问题的模拟
1)首先创建所有的过程与表
create table test ( a char(4))
insert into test select ‘OLD’
create proc sp_select as select * from test
试一下exec sp_select，输出为:
a           
 ---- 
 OLD
(1 row affected)
(return status = 0)

2)给表test加一列
alter table test add b int null
update test set b = 1
3)测试，同样sp_select的输出与期望不符
sp_select的输出：
a
 ----
 OLD
(1 row affected)
(return status = 0)
select * from test的输出
a    b           
 ---- ----------- 
 OLD    1 
(1 row affected)
两个问题都模拟完了，如果你没有明白这两个例子，那么不妨亲手试一下，直到理解了再往下看吧。

 joey 回复于：2003-09-09 18:11:45[color=red:df598033da][b:df598033da]     问题的原因[/b:df598033da][/color:df598033da]
这两个问题的根本表现都在于手工执行select语句的输出都正确，而执行过程的输出却仍然指向原来的老表！
原因就在于手工的select语句是即时编译的，而过程是在创建时编译的。不仅如此，过程引用一个对象时，使用的是对象的Object ID，而非表名。
先用以上的原因来解释一下sp_rename问题吧：
表test创建时，假设ObjectID=999，创建sp_select过程后，编译的结果中包含了以下的操作 à 从ObjectID为999的表中取数据。
然后，sp_rename test, test_old改名后，请注意，sp_rename并不影响表的objectID，这时test_old表的ObjectID为999。
接下来创建了一个新的test表，它显然分配了新的ObjectID，假设为1000。
由于手工执行select * from test为即时编译，因此它会从ObjectID为1000的表中取得数据，而由于过程sp_select没有重新编译，则仍然会从ObjectID为999的表(老表)中去取数据，这样两者的结果就不一致了。
alter table的问题和ObjectID并没有关系，问题产生的原因同样和编译有关，在创建过程时，编译器将过程中的语句展开了，于是:
select * from test就变成了select a from test
尽管随后给表test添加了一列，但由于没有重新编译，因此执行过程时仍然只显示一列而非两列。

 joey 回复于：2003-09-09 18:12:10[color=red:b1d7cdba0a][b:b1d7cdba0a]     问题的解决[/b:b1d7cdba0a][/color:b1d7cdba0a]
问题的原因知晓了，如何解决？从以上的分析中我们知道，导致问题的原因在于过程没有重新编译，如何能使得过程重新编译，我的答案是重建与原表相关的所有过程。这不是唯一的答案(后面会提到)，但却是最好的和最广泛使用的解决方法。大家可以自己做一下验证，这里不再赘述。
在三年前，当我用重建的方法解决这个问题时，心中不免产生了小小的疑问，难道没有更好的解决方法？如果涉及原表的过程是一个，尚且好办，如果是很多过程，那可真是相当麻烦。首先我们必须找出所有与原表有关的过程，使用系统过程sp_depends就可以达到目的。有趣的是，即使用sp_rename将原表改名(例如由原来的test改为test_old)，sp_depends test_old的输出和在改名前sp_depends test的结果完全一样，这进一步证实了前面的推测，即过程引用一个对象时，使用的是对象的Object ID，而非表名。找到所有相关的过程后，删除他们，然后重建。重建的话当然需要建立过程的脚本，如果你的版本管理做得好的话那没有问题，否则还是采用最保险的方法：先用defncopy倒出过程，这样可以保证过程是最新的。还有过程一定就能删除成功吗，曾经遇到过这样的情况，在生产系统中升级最新的过程，由于这个过程是最核心的业务，客户端几乎时刻在调用，因此，删除了好几次也不成功。想想如果更新了一个过程，而其它的过程没有更新是什么情况！这些步骤，漏掉一步，漏掉一个对象都可能导致问题！#p#分页标题#e#

肯定有更好的解决方法！我记得有一个叫什么compile的命令，我在手册中查找了一下，果然找到了一些与编译有关的命令和过程。

另外 这样的问题在 mssql 数据库不会出现，
在mssql 数据库中，编译器不会修改你select * from ...的语句中的 * 为具体的列名 因此 从这里可以看出， 从 mssql 移植程序到 sybase 的人要改变观念，屏气写 select * from 的陋习！     [/size:ce847676a7]











 joey 回复于：2003-09-18 13:36:13同意zhangyh123 的解释
[quote:846b2a1bf2]任何 
select * from table 在数据库保存并编译的时候，都被数据库编译器替换为 
select col1,cole .....coln 的形式，被替换成具体的列名，新增加列后 
新列名不会自动出现在这里 
因此，新增加的列为什么 没有出来，原因就很清楚了。[/quote:846b2a1bf2]
我并非说这是重新编译导致的问题，而是说重新编译可以解决这些问题。
也就是说，只有重新编译，stored proc 才能刷新底层表发生的变化。

sp_recompile -- Causes each stored procedure and trigger that uses the named table to be
recompiled the next time it runs.
execute … [with recompile] -- forces compilation of a new plan.
看着名字都象啊，可惜不是！！！我无论如何试都不行。
我实在奇怪既然不是这个功能，为什么会取一些如此贴切的名字！用挂羊头，买狗肉来形容再恰当不过了。
我不甘心，继续搜索，终于找到了以下的说明：
If stored procedures using select * reference a table that has been altered, no new columns appear in the result set, even if you use the with recompile option or the with recompile option to execute. You must drop the procedure and re-create it to include these new columns.
After renaming a table or any of its columns, use sp_depends to determine
which procedures, triggers, and views depend on the table, and redefine
these objects.
You must drop the procedure and re-create it.对了，我需要的就是这句。exec … with recompile和sp_recompile都没用，记住，重建，只有重建！！！
前面提到还有其它的方法，是的，有！但是用这样的方法实在是无奈，顺便提提，最好不要用。这个另类的方法源于我找到了以下的描述:
Changing the locking scheme while other users are active on the system
may have the following effects on user activity:
• Query plans in the procedure cache that access the table will be
recompiled the next time they are run.
……
看来改变表的锁策略可以使得相关的过程重新编译，例如表的所策略最初为Allpages，
你可以使用alter table先改为Datapages，这时执行相关过程就会导致对象的重新编译，再改回Allpages。但锁策略这么重要的参数怎能随便改动，况且有时还会导致索引的重建，反而得不偿失。

 joey 回复于：2003-09-09 18:12:43[color=red:3d23c520b6][b:3d23c520b6]     问题的避免[/b:3d23c520b6][/color:3d23c520b6]
基于解决问题的最好方法就是绕过问题的原则。我不得不静下来想想这些问题真的值得我们注意吗？这些问题真的那么容易遇到吗？如果我的红色富康老是在上下班途中被误认为TAXI，为什么不考虑换成BMW!!!(更正：BMW应为奥拓)
sp_rename什么时候用呢，回想三年前我用的时候是因为测试，为了保存以前的数据，先将其改名，然后建立了一个新表，同样名字，结构，在其中产生了一些测试数据。看来，在测试中备份数据是一个使用sp_rename的理由，毕竟比select into倒新表中好用。
还有其它的用法吗？为此我在sybase网站上搜索了一下sybase newsgroup。其中有一个贴子挺有意思，它的大致用途是为了发布一个每日更新的日报表。为此，作者做了以下的工作：
1)首先形成需要发布的数据，名为DATA.TXT
2)建立一个与需要发布的表LIVE_REPORT_TABLE同样结构的表EMPTY_REPORT_TABLE
3)将DATA.TXT中的数据BCP倒入EMPTY_REPORT_TABLE
4)drop LIVE_REPORT_TABLE
5)sp_rename EMPTY_REPORT_TABLE , LIVE_REPORT_TABLE
这里的用法采用了与我们平时的用法完全相反的思路。作者在此基础上又结合了动态View的方法，详细的描述请看：#p#分页标题#e#
http://forums.sybase.com/cgi-bin/webnews.cgi?cmd=item-6471&group=sybase.public.sqlserver.general&bypass_cache=2
再来看alter table问题。减少此类问题的最好方法就是良好的设计，但也无法避免。更重要的是，即使你重建这个表，过程仍会顽固的取得编译时认定的列，除非重建此过程。
看来，这个问题是无法回避的了，不过如果你理解了这个问题，也就没有什么大不了的了。

 joey 回复于：2003-09-09 18:13:11[color=red:d6b59edf86][b:d6b59edf86] 还有其它的问题吗？[/b:d6b59edf86][/color:d6b59edf86]
大家也许注意到，我在整篇文章中一直在使用过程这个词，而不是存储过程。因为，变化的表影响的实际上包括存储过程，trigger和view。因此重建的对象就应包含上述提到的所有对象。还有，sp_rename不光可以改表，还可以改存储过程，trigger等等，那么这时你是否需要重新编译相关的所有对象，例如存储过程调用存储过程的情况。没办法，又做了一下测试，惊奇的发现，这时没有问题(只做了存储过程调用存储过程的情况)。由于视图与表的相似性，推测可能也会用影响，测试也验证了我的想法。看来也不是什么时候都错的，可是这种表现的不一致性着实令人懊恼，就如同某人平时一贯对你冷若冰霜，突然有天对你低声下气，还不如整天不理我的好，我还会觉得此人傲得有骨气。
除了sp_rename问题和alter table问题，还有其它与此相关的问题吗？经过一番搜索，又找到了以下的描述：
If you unpartition a table, recompile the query plans of any dependent procedures. Unpartitioning does not automatically recompile procedures.
原来unpartition一个已分区的表时，也需要重新编译相关的过程，只不过这种情况比较少见。
看来，重新编译不仅仅是影响新增的列，变化的表名那么简单，还有过程的执行计划，也许还有其它。
[b:d6b59edf86][/b:d6b59edf86]

 joey 回复于：2003-09-09 18:13:55[color=red:a3f31c8988]  [b:a3f31c8988]  结束语[/b:a3f31c8988][/color:a3f31c8988]
写了两个多小时，终于写完了，其间做了一些实验，实验的结果总结如下：
lsp_rename 不改变对象的ObjectID
lsp_rename不会导致对象重新编译
lexec … with recompile也不会
lreboot数据库更不会
l改变lock scheme可以导致重新编译，但最后别用
l问题只出现在表/视图改变时的情况，在过程改变的情况下，相关的过程不会受影响
l最后，记住，重建是解决此类问题的最佳方法
其它的等待你的补充了。

joey 
2003-9-9

 joey 回复于：2003-09-09 18:15:38[color=red:f12a696fef][b:f12a696fef]勘误表[/b:f12a696fef][/color:f12a696fef]
结束语中,sp_rename不会导致对象重新编译应为sp_recompile不会导致对象重新编译

 joey 回复于：2003-09-09 18:19:28[color=red:5135088f18][b:5135088f18]勘误表(续)[/b:5135088f18][/color:5135088f18]
问题的模拟一节中 注意，新的表中没有数据应为注意，新的表数据为NEW
结束语中但最后别用应为但最好别用

 Blackrose 回复于：2003-09-09 22:35:09我坚信

sp_recompile 是会 导致对象重新编译的

 chenfeng825 回复于：2003-09-09 22:52:49有时recompile不起作用的,重建时最保险的办法.(如果保存有原码)其实没细看全部内容,不过总结非常认同

 joey 回复于：2003-09-10 09:41:29[quote:037e855e55]     我坚信 

sp_recompile 是会 导致对象重新编译的[/quote:037e855e55] 

起始昨天我就试过不行，为了确认，今日有测试了一次，还是不行的。
起始手册中说可以recompile,也许只是优化了某一部分，并不能解决我们这里提到的问题

 joey 回复于：2003-09-17 11:53:39评为精华了，  
提点小小意见，这是我自己写的，不是zt的.
放到原创精华更合适的

 chenfeng825 回复于：2003-09-17 11:59:01不要太贪心，呵呵。鼓励原创！

 qjhadm 回复于：2003-09-17 13:06:13确实不错.对待这样一个小问题做如此深刻的分析,精神另我佩服

 solofeng 回复于：2003-09-17 19:34:32呵呵joey   比我强，上次我也是由于sp_rename的问题导致我们数据库出现很大的问题，不过后来知道是没有讲存储过程recomplie，但是我没有做这么细致的分析？佩服

 zhangyh123 回复于：2003-09-17 20:44:48[quote:37dfb84738="Blackrose"]我坚信

sp_recompile 是会 导致对象重新编译的[/quote:37dfb84738]     

我一般 只 sp_recompile table  
从来没有使用 sp_recompile procedure 
不过还有点怀疑，明天自己验证看看。

 Eisen 回复于：2003-09-18 09:13:11sp_recomplie后面只能跟 table的名字，不能针对某个procedure进行recompile的。

#p#分页标题#e# zhangyh123 回复于：2003-09-18 09:27:31[size=18:ce847676a7]关于 在 存储过程中使用 select * from table  以及新增加字段后新列为什么不出现的问题，我这里给一个比较正确的认识：
在检查你的存储过程文本中，可以看到，任何 
select * from table 在数据库保存并编译的时候，都被数据库编译器替换为 
  select col1,cole .....coln  的形式，被替换成具体的列名，新增加列后
新列名不会自动出现在这里
因此，新增加的列为什么 没有出来，原因就很清楚了。并不是重编译与否的问题，
我同意 blackrose 的看法 ，数据库存储过程肯定重新编译。

重新编译的对象应该是第一次数据库保存与编译时展开的数据库脚本，而不是开发人员写的 select * 因此 重新编译后并没有新列是很正常的，它只对展开列名
的sp 进行编译。

我认为缺失列的原因并不是楼主所说的原因（没有重新编译，而是编译的对象是什么！），请广大网友务必不要搞 混淆！

也请斑竹仔细检查，否则。。。