title: Performance of VIEW vs. SQL statement
body: <p>I have a query that goes something like the following:</p>
  
  <pre><code>select &lt;field list&gt; 
  from &lt;table list&gt;
  where &lt;join conditions&gt;
  and &lt;condition list&gt;
  and PrimaryKey in (select PrimaryKey from &lt;table list&gt;
      where &lt;join list&gt; 
      and &lt;condition list&gt;)
  and PrimaryKey not in (select PrimaryKey from &lt;table list&gt;
      where &lt;join list&gt;
      and &lt;condition list&gt;)
  </code></pre>
  
  <p>The sub-select queries both have multiple sub-select queries of their own that I'm not showing so as not to clutter the statement.</p>
  
  <p>One of the developers on my team thinks a view would be better.  I disagree in that the SQL statement uses variables passed in by the program (based on the user's login Id).</p>
  
  <p>Are there any hard and fast rules on when a view should be used vs. using a SQL statement?  What kind of performance gain issues are there in running SQL statements on their own against regular tables vs. against views.  (Note that all the joins / where conditions are against indexed columns, so that shouldn't be an issue.)</p>
  
  <p>EDIT for clarification...</p>
  
  <p>Here's the query I'm working with:</p>
  
  <pre><code>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)
  )
  </code></pre>