Union View Optimizer Creates Temp Table

Union View Optimizer Creates Temp Table

Post by Brian Fost » Thu, 25 Sep 2003 22:54:38


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 from
v_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 select
x1.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
 
 
 

Union View Optimizer Creates Temp Table

Post by Mark D. St » Fri, 26 Sep 2003 01:47:23


It's probably far TOO simple. With so few records, and no filter, you are
likely to skip all indexes in favour of sequential scans.


Can we see the query plan for the real query then?

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+
| Mark D. Stock mailto: XXXX@XXXXX.COM |//////// /|
| Mydas Solutions Ltd http://www.yqcomputer.com/ |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+

sending to informix-list

 
 
 

Union View Optimizer Creates Temp Table

Post by Brian Fost » Sun, 28 Sep 2003 07:00:15

Thank for the reply mark.

I was able to get the Optimizer to abandon the temp-table approach after
reorganizing the logic a bit. The original view had several queries against
large tables combined with 'Union All'. Each of the queries were grouping
with aggregate expressions and contained 'having' clauses. I moved the
aggregation logic to a secondary view and the optimizer no longer has a
problem with it.




some
I
file://|