Unwanted queries merge in Oracle 10g

Question

I am working on Oracle Database 10g Release 10.2.0.5.0. I have view like:

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;

and some database function some_db_package.foo(). My problem is that when I execute query:

SELECT A, some_db_package.foo(B) val
FROM some_view
WHERE some_db_package.foo(B) = 0;

Oracle is merging conditions from query and some_view, so I am getting something like:

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;

some_db_package.foo() executes on all rows from table_a and table_b and I would like to execute some_db_package.foo() only on filtered (by condition_a and condition_b) rows. Is there any way to do that (i.e. by changing sql query or some_view definition) assuming that I can not use optimizer hints in query?

---

Problem solved. Just to summmarize:

1. some_db_package.foo() - for given event and date range counts event's errors which occured between dates (foo() access tables), so it is deterministic only when sysdate > dateTo.

2. select * from ( SELECT A, some_db_package.foo(B) val FROM some_view ) does not make difference.

3. Actually I do not need UNION ALL and I did test with UNION, but stil the same result.

4. 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 does not make difference.

5. I did test with optimizer hints and unfortunately Oracle ignored them.

6. Using ROWNUM >= 1 in some_view was the solution for my problem.

Thank you for help, I really appreciate it.

Answer 1

ROWNUM is usually the best way to stop optimizer transformations. Hints are difficult to get right - the syntax is weird and buggy and there are many potential transformations that need to be stopped. There are other ways to re-write the query, but ROWNUM is generally the best way because it is documented to work this way. ROWNUM has to evaluate last to be used in Top-N queries, you can always rely on it to prevent query blocks from being merged.

Sample schema

drop table table_a;
drop table table_b;

create table table_a(a number, b number);
create table table_b(a number, b number);

insert into table_a select level, level from dual connect by level <= 10;
insert into table_b select level, level from dual connect by level <= 10;

begin
    dbms_stats.gather_table_stats(user, 'table_a');
    dbms_stats.gather_table_stats(user, 'table_b');
end;
/

--FOO takes 1 second each time it is executed.
create or replace function foo(p_value number) return number is
begin
    dbms_lock.sleep(1);
    return 0;
end;
/

--BAR is fast, but the optimizer doesn't know it.
create or replace function bar(p_value number) return number is
begin
    return p_value;
end;
/

--This view returns 2 rows.
CREATE OR REPLACE VIEW some_view AS
    SELECT A, B
    FROM table_a
    WHERE a = bar(1)
UNION ALL
    SELECT A, B
    FROM table_b
    WHERE a = bar(2);

Slow query

This query takes 20 seconds to run, implying the function is evaluated 20 times.

SELECT A, foo(B) val
FROM some_view
WHERE foo(B) = 0;

The explain plan shows the conditions are merged, and it appears that the conditions are evaluated from left to right (but don't rely on this always being true!).

explain plan for
SELECT A, foo(B) val
FROM some_view
WHERE foo(B) = 0;

select * from table(dbms_xplan.display);


Plan hash value: 4139878329

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |     1 |     6 |     5   (0)| 00:00:01 |
|   1 |  VIEW               | SOME_VIEW |     1 |     6 |     5   (0)| 00:00:01 |
|   2 |   UNION-ALL         |           |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| TABLE_A   |     1 |     6 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| TABLE_B   |     1 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("FOO"("B")=0 AND "A"="BAR"(1))
   4 - filter("FOO"("B")=0 AND "A"="BAR"(2))

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing

Fast query

Add a seemingly redundant ROWNUM predicate that does nothing except prevent transformations.

CREATE OR REPLACE VIEW some_view2 AS
    SELECT A, B
    FROM table_a
    WHERE a = bar(1)
    AND ROWNUM >= 1 --Prevent optimizer transformations, for performance.
UNION ALL
    SELECT A, B
    FROM table_b
    WHERE a = bar(2)
    AND ROWNUM >= 1 --Prevent optimizer transformations, for performance.
;

Now the query only takes 4 seconds, the function is only run 4 times.

SELECT A, foo(B) val
FROM some_view2
WHERE foo(B) = 0;

In the new explain plan it's clear that the FOO function is evaluated last, after most of the filtering is complete.

explain plan for
SELECT A, foo(B) val
FROM some_view2
WHERE foo(B) = 0;

select * from table(dbms_xplan.display);


Plan hash value: 4228269064

------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     2 |    52 |     6   (0)| 00:00:01 |
|*  1 |  VIEW                 | SOME_VIEW2 |     2 |    52 |     6   (0)| 00:00:01 |
|   2 |   UNION-ALL           |            |       |       |            |          |
|   3 |    COUNT              |            |       |       |            |          |
|*  4 |     FILTER            |            |       |       |            |          |
|*  5 |      TABLE ACCESS FULL| TABLE_A    |     1 |     6 |     3   (0)| 00:00:01 |
|   6 |    COUNT              |            |       |       |            |          |
|*  7 |     FILTER            |            |       |       |            |          |
|*  8 |      TABLE ACCESS FULL| TABLE_B    |     1 |     6 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FOO"("B")=0)
   4 - filter(ROWNUM>=1)
   5 - filter("A"="BAR"(1))
   7 - filter(ROWNUM>=1)
   8 - filter("A"="BAR"(2))

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing

Ben's idea to make the function DETERMINISTIC may also help reduce the function calls.