Union View Optimizer Creates Temp Table
Hi,
I have gathered from previous posts, that the optimizer will not create a
temp table to process a view if it does not have to eliminate duplicates
(union all). I have not found that to be the case. Can anyone shed some
light on this? The example below is a very simple case. My production
tables have several million rows (A & B) and the view is an unusable pig. I
am running 9.20 UC3.
TIA
Brian
EXAMPLE:
create table TMPA(cl_id char(12));
create table TMPB(cl_id char(12));
create table TMPC(cl_id char(12));
Create unique index idx_cla on tmpa(cl_id);
Create unique index idx_clb on tmpb(cl_id);
Create unique index idx_clc on tmpc(cl_id);
create view v_test(cl_id)as
select cl_id from tmpa
union all
select cl_id from tmpb;
insert into tmpa values ('XXX');
insert into tmpc values ('XXX');
update statistics medium for table tmpa;
update statistics medium for table tmpb;
update statistics medium for table tmpc;
set explain on;
select c.cl_id fromv_test t, tmpc c
where t.cl_id = c.cl_id;
Optimizer Output:
================
QUERY:
------
create view v_test (cl_id) as select x0.cl_id from tmpa x0 union all selectx1.cl_id from tmpb x1 ;
Estimated Cost: 4
Estimated # of Rows Returned: 2
1) tmpa: INDEX PATH
(1) Index Keys: cl_id (Key-Only) (Serial, fragments: ALL)
Union Query:
------------
1) tmpb: INDEX PATH
(1) Index Keys: cl_id (Key-Only) (Serial, fragments: ALL)
QUERY:
------
select c.cl_id from
v_test t, tmpc c
where t.cl_id = c.cl_id
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) (Temp Table For View): SEQUENTIAL SCAN
2) c: INDEX PATH
(1) Index Keys: cl_id (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: (Temp Table For View).cl_id = c.cl_id
NESTED LOOP JOIN