title: Unwanted queries merge in Oracle 10g
body: <p>I am working on Oracle Database 10g Release 10.2.0.5.0. I have view like:
      </p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE VIEW some_view
  (
      A,
      B
  )
  AS
      SELECT A, B
      FROM table_a
      WHERE condition_a
  UNION ALL
      SELECT A, B
      FROM table_b
      WHERE condition_b;
  </code></pre>
  
  <p>and some database function <code>some_db_package.foo()</code>. My problem is that when I execute query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT A, some_db_package.foo(B) val
  FROM some_view
  WHERE some_db_package.foo(B) = 0;
  </code></pre>
  
  <p>Oracle is merging conditions from query and <code>some_view</code>, so I am getting something like:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT A, some_db_package.foo(B) val
  FROM table_a
  WHERE some_db_package.foo(B) = 0 AND condition_a
  UNION ALL
  SELECT A, some_db_package.foo(B) val
  FROM table_b
  WHERE some_db_package.foo(B) = 0 AND condition_b;
  </code></pre>
  
  <p><code>some_db_package.foo()</code> executes on all rows from <code>table_a</code> and <code>table_b</code> and I would like to execute <code>some_db_package.foo()</code> only on filtered (by <code>condition_a</code> and <code>condition_b</code>) rows. Is there any way to do that (i.e. by changing sql query or <code>some_view</code> definition) assuming that I can not use optimizer hints in query?</p>
  
  <hr>
  
  <p>Problem solved. Just to summmarize:</p>
  
  <ol>
  <li><p><code>some_db_package.foo()</code> - for given event and date range counts event's errors which occured between dates (foo() access tables), so it is deterministic only when <code>sysdate &gt; dateTo</code>.</p></li>
  <li><p><code>select * from ( SELECT A, some_db_package.foo(B) val FROM some_view )</code> does not make difference.</p></li>
  <li><p>Actually I do not need <code>UNION ALL</code> and I did test with <code>UNION</code>, but stil the same result.</p></li>
  <li><p><code>with some_view_set as (select A, B from some_view) select * from ( select A, some_db_package.foo(B) val from some_view_set ) where val = 0</code> does not make difference.</p></li>
  <li><p>I did test with optimizer hints and unfortunately Oracle ignored them.</p></li>
  <li><p>Using <code>ROWNUM &gt;= 1</code> in <code>some_view</code> was the solution for my problem.</p></li>
  </ol>
  
  <p>Thank you for help, I really appreciate it.</p>