Linux-Informix Forum

Temp table and Dynamic Hash Joins

Posted By: PETER DIAZ DE LEON
Date: Thursday, 19 September 2002, at 11:02 a.m.

I have an select statement that does and outer join
on two temp tables. The two temp tables both have
indexs on the columns that aregoing to be joined.
Table a has 378000 rows. Table b has 374000 rows.
Update statistics has also been run on the two temp tables.

If I do a select like the following:

select a.invoice,
a.sales_date,
a.customer_lname,
a.customer_fname,
a.charges,
b.receipts,
(a.charges - b.receipts) balance
from a, outer b
where a.invoice = b.invoice
into temp x1 with no log;

All of my temp dbspaces become 100% allocated. But not 100% used.
So when I go to create an index on the invoice field in table x1
I receive an out of disk space error.

However, if I do the following:

create temp table x1
(
invoice integer,
sales_date date,
customer_lname char(30),
customer_fname char(20),
charges decimal,
receipts decimal,
balance decimal
) with no log;

insert into x1
select a.invoice,
a.sales_date,
a.customer_lname,
a.customer_fname,
a.charges,
b.receipts,
(a.charges - b.receipts) balance
from a, outer b
where a.invoice = b.invoice;

Less that 10% of my temp dbspaces get filled up.

I looked at the sql explain output of both selects and there
isn't a difference. In both cases a dynamic hash join is created.

What going on????????

Thanks

Peter

RH 7.2
Kernel 2.4.18
1 GB memory
7 temp dbspaces (2 GB in total)
Informix IDS 9.21.UC4
2-CPU

Linux-Informix Forum is maintained by Administrator with WebBBS 5.12.