我有一个查询,如下所示:
select <field list> from <table list> where <join conditions> and <condition list> and PrimaryKey in (select PrimaryKey from <table list> where <join list> and <condition list>) and PrimaryKey not in (select PrimaryKey from <table list> where <join list> and <condition list>)
子选择查询都有自己的多个子选择查询,我没有显示,以免混乱语句.
我团队的开发者之一认为一个观点会更好.我不同意,sql语句使用程序传入的变量(基于用户的登录ID).
在使用视图时应该使用sql语句吗?有什么样的性能增益问题是在自己的运行sql语句与常规表相对于视图. (请注意,所有联接/条件与索引列相关,因此不应该是一个问题.)
编辑澄清…
这是我正在使用的查询:
select obj_id from object where obj_id in( (select distinct(sec_id) from security where sec_type_id = 494 and ( (sec_usergroup_id = 3278 and sec_usergroup_type_id = 230) or (sec_usergroup_id in (select ug_gi_id from user_group where ug_ui_id = 3278) and sec_usergroup_type_id = 231) ) and sec_obj_id in ( select obj_id from object where obj_ot_id in (select of_ot_id from obj_form left outer join obj_type on ot_id = of_ot_id where ot_app_id = 87 and of_id in (select sec_obj_id from security where sec_type_id = 493 and ( (sec_usergroup_id = 3278 and sec_usergroup_type_id = 230) or (sec_usergroup_id in (select ug_gi_id from user_group where ug_ui_id = 3278) and sec_usergroup_type_id = 231) ) ) and of_usage_type_id = 131 ) ) ) ) or (obj_ot_id in (select of_ot_id from obj_form left outer join obj_type on ot_id = of_ot_id where ot_app_id = 87 and of_id in (select sec_obj_id from security where sec_type_id = 493 and ( (sec_usergroup_id = 3278 and sec_usergroup_type_id = 230) or (sec_usergroup_id in (select ug_gi_id from user_group where ug_ui_id = 3278) and sec_usergroup_type_id = 231) ) ) and of_usage_type_id = 131 ) and obj_id not in (select sec_obj_id from security where sec_type_id = 494) )
解决方法
根据数据库供应商,一般来说,针对视图执行查询会将视图中定义的sql与Where子句谓词和Order By子句排序表达式相结合,该表达式附加到您传递给View的sql中,以提出一个组合的完整SQL查询执行.然后执行它就好像它已经被传递给查询processsor,所以应该没有区别.
视图是一种组织工具,而不是性能增强工具.
When an sql statement references a nonindexed view,the parser and query optimizer analyze the source of both the sql statement and the view and then resolve them into a single execution plan. There is not one plan for the sql statement and a separate plan for the view.