4

I have a performance problem on a UNION ALL view. The problem can be solved by rewriting the view in two separate views but that kind of defeats the purpose of creating a view.

Here is a simple test case (Oracle 11.2.0.3.0). The real queries use about 10 different tables instead of just 3.

CREATE TABLE t0 (id number, ref_id number);
CREATE INDEX i0 on t0(id);
CREATE TABLE t1 (id number, amount number);
CREATE INDEX i1 on t1(id);
CREATE TABLE t2 (id number, amount number);
CREATE INDEX i2 on t2(id);


insert into t0 select rownum, rownum * 10 from dual connect by rownum <= 100000;
insert into t1 select rownum, rownum * 10 from dual connect by rownum <= 100000;
insert into t2 select rownum, rownum * 10 from dual connect by rownum <= 100000;



CREATE OR REPLACE VIEW v2
AS
SELECT id, sum(amount) AS total_amount
FROM t1
GROUP BY id;

CREATE OR REPLACE VIEW v3
AS
SELECT id
      ,sum(amount) as total_amount
FROM (SELECT id, amount
      FROM t1
      UNION ALL
      SELECT id, amount
      FROM t2)
GROUP BY id
HAVING sum(amount) <> 0
;

CREATE OR REPLACE view v1
AS
SELECT *
FROM v2
UNION ALL
SELECT *
FROM v3;

The following query uses 766 gets. Adding push_pred(a) does nothing for it.

select --+ first_rows
*
from t0, v1 a
where t0.ref_id = a.id
  and t0.id = 1;

Next query bottoms out at 16 gets although it does the same thing as the first one, only scans t0 two times instead of one.

select --+ first_rows
*
from t0, v2
where  t0.ref_id = v2.id
  and t0.id = 1
union all
select *
from t0, v3
where  t0.ref_id = v3.id
  and t0.id = 1;

What am I missing?

jva
  • 2,797
  • 1
  • 26
  • 41

0 Answers0