select *  into memory\expandedgroup from expandedgroup;

CREATE INDEX IF NOT EXISTS "memory\group_code" 
ON "memory\expandedgroup" ("group_code") COMPRESS DUPBYTE;

CREATE INDEX IF NOT EXISTS "memory\group_el" 
ON "memory\expandedgroup" ("group_el") COMPRESS DUPBYTE;

CREATE INDEX IF NOT EXISTS "memory\element" 
ON "memory\expandedgroup" ("element") COMPRESS DUPBYTE;

select 
 C3_BE20040601.a_o, C3_BE20040601.t_o, 
 C3_BE20040601.c_o, C3_BE20040601.r_o , 
 C3_BE20040601.a_d, C3_BE20040601.t_d, 
 C3_BE20040601.c_d, C3_BE20040601.r_d, 
 C3_BE20040601.quarter, 
 C3_BE20040601.shipment_date,C3_BE20040601.weight,C3_BE20040601.shipments
into "memory\temp1"
from C3_BE20040601
where  ((C3_BE20040601.quarter in  ('1'))  
 or (C3_BE20040601.shipment_date in ('2004-07-01','2004-06-01'))); 


/* with MEMORY expandedgroup table */

select 
 data.a_o as "ORIG_AP", data.t_o as "ORIG_CITY", 
 data.c_o as "ORIG_CTR", data.r_o as "ORIG_RGN", 
 data.a_d as "FDES_AP", data.t_d as "FDES_CITY", 
 data.c_d as "FDES_CTR", data.r_d as "FDES_RGN", 
 eg0.group_el as "FDES_GUSNA", eg1.group_el as "ORIG_test", 
 eg2.group_el as "STDT_q10001", data.quarter as "STDT_QUAR" , 
 min(data.weight) as "WGHT_MIN", sum(data.weight) as "WGHT_SUM", 
 min(data.shipments) as "NOUL_MIN", sum(data.shipments) as "NOUL_SUM"
into "memory\result1" 
from "memory\temp1" data 
 left outer join  "memory\expandedgroup" eg0 on
data.c_d=eg0.element and eg0.group_code='GUSNA'   
 left outer join  "memory\expandedgroup" eg1 on
data.a_o=eg1.element and eg1.group_code='test'   
 left outer join  "memory\expandedgroup" eg2 on
data.shipment_date=eg2.element and eg2.group_code='q10001'  
group by data.a_o, data.t_o, data.c_o, data.r_o, 
 data.a_d, data.t_d, data.c_d, data.r_d , data.quarter,
 eg0.group_el,eg1.group_el,eg2.group_el;



/* with DISK-BASED expandedgroup table */
/*
select 
 data.a_o as "ORIG_AP", data.t_o as "ORIG_CITY", 
 data.c_o as "ORIG_CTR", data.r_o as "ORIG_RGN", 
 data.a_d as "FDES_AP", data.t_d as "FDES_CITY", 
 data.c_d as "FDES_CTR", data.r_d as "FDES_RGN", 
 eg0.group_el as "FDES_GUSNA", eg1.group_el as "ORIG_test", 
 eg2.group_el as "STDT_q10001", data.quarter as "STDT_QUAR" , 
 min(data.weight) as "WGHT_MIN", sum(data.weight) as "WGHT_SUM", 
 min(data.shipments) as "NOUL_MIN", sum(data.shipments) as "NOUL_SUM"
into "memory\result1" 
from "memory\temp1" data 
 left outer join  "expandedgroup" eg0 on
data.c_d=eg0.element and eg0.group_code='GUSNA'   
 left outer join  "expandedgroup" eg1 on
data.a_o=eg1.element and eg1.group_code='test'   
 left outer join  "expandedgroup" eg2 on
data.shipment_date=eg2.element and eg2.group_code='q10001'  
group by data.a_o, data.t_o, data.c_o, data.r_o, 
 data.a_d, data.t_d, data.c_d, data.r_d , data.quarter,
 eg0.group_el,eg1.group_el,eg2.group_el;
*/

drop table if exists "memory\temp1";

select * from "memory\result1";

---

 Comments and Workarounds

---

The problem wasn't related specifically to in-memory tables, but rather to the fact that query result sets were using a much bigger page size than necessary combined with inefficient bitmap building code on large index pages when performing the joins. The workaround is to create the tables and indexes first and then populate them with an INSERT query instead of using the INTO clause.

---

 Resolution

---

Fixed Problem on 11/4/2004 in version 4.15 build 1