(rehash from firebird-devel)

The following query is very slow and ultimately fails due to lack of
disk space. The temp file grows to more than 2GB.
The relevant metadata is below. TABLE1 has ca. 1000 records, TABLE2 ca.
150000.
I am a bit surprised about this because postgres has no problems with
this query and provides the result in seconds.

Alois


Configuration:
Firebird 1.5 Superserver Version: WI-V1.5.0.4306
OS: Win98

Query Plan:
PLAN SORT (JOIN (TABLE1 NATURAL,TABLE2 NATURAL))

Query:
SELECT "TABLE1"."FIELD1", "TABLE1"."N2", "TABLE2"."FIELD2",
"TABLE2"."FIELD3", SUM( "TABLE2"."FIELD4" ), COUNT( * ) FROM "TABLE1"
"TABLE1", "TABLE2" "TABLE2" GROUP BY "TABLE1"."FIELD1", "TABLE1"."N2",
"TABLE2"."FIELD2", "TABLE2"."FIELD3"

Statement failed, SQLCODE = -902

I/O error for file "C:\WINDOWS\TEMP\fb_sort_admp3k"
-Error while trying to write to file
-Der Drucker hat kein Papier mehr.

-sort error


/*===========================================================================*/
/*Metadata Extract performed 26-Mrz-2004 22:38
*/
/*===========================================================================*/
SET SQL DIALECT 3;

/*===========================================================================*/
/*Create Database
*/
/*===========================================================================*/
create database 'C:\db\Firebird_Daten\db.fdb' user 'NONAME';



/*===========================================================================*/
/*Table Definitions
*/
/*===========================================================================*/

/*Table: TABLE1*/
create table TABLE1(
FIELD1 integer not null,
N1 date,
N2 char(20),
N3 char(20),
N4 char(20),
N5 char(20),
N6 char(20),
N7 integer,
N8 char(20),
N9 char(20),
N10 char(20),
N11 double precision,
N12 double precision,
N13 double precision,
N14 double precision,
N15 integer,
N16 integer);




/*Table: TABLE2*/
create table TABLE2(
FIELD2 char(2),
FIELD3 char(2),
N17 integer,
N18 integer,
N19 integer,
N20 integer,
FIELD4 decimal(4, 2),
N21 integer,
N22 integer,
N23 integer,
N24 integer,
N25 integer,
FIELD1 integer);







/*===========================================================================*/
/*Table Primary Key Definitions
*/
/*===========================================================================*/
alter table TABLE1 add constraint INTEG_2 primary key (FIELD1);
/*===========================================================================*/
/*Table Foreign Key Definitions
*/
/*===========================================================================*/


/*===========================================================================*/
/*Table Index Definitions
*/

__________________________________
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
http://taxes.yahoo.com/filing.html