Hi guys.

I've read whatever I could find about this problem. Suggestion is to create a proper indices and that should speed up the query but query optimizer do not take my indices into account and generates natural plan. I do not know what should I do?

Here are my two tables:

CREATE TABLE CLIENT
(
ID_CLIENT integer NOT NULL,
RECORD_ID integer,
RECORD_TIME timestamp DEFAULT CURRENT_TIMESTAMP,
RECORD_ACTIVE integer DEFAULT 0,
RECORD_ADD_USER integer,
RECORD_EDIT_USER integer,
SHORTNAME varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
NAME varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
CITY varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
STREET varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
ADRESS varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
HOMENUMBER varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
APPARTMENTNUMBER varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
POSTCODE varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
POST varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
CORESPCITY varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
CORESPSTREET varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
CORESPADRESS varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
CORESPHOMENUMBER varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
CORESPAPPARTMENTNUMBER varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
CORESPPOSTCODE varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
CORESPPOST varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
CONTACTNAME varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
CONTACTSURNAME varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
PHONE1 varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
PHONE2 varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
NOTICE varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
ISACTIVE integer DEFAULT 1,
EXPORTED integer DEFAULT 0,
NIP varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
REGON varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
TASKSBLOCKED integer DEFAULT 0,
ARCHIVEFROMDATE date,
SYMFONIANUMBER varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
TRASHNUMBER varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
TASKSBLOCKEDREASON varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
PAYTYPE integer,
CLIENTCONFIRMATION integer,
IDCLIENTGROUP integer,
PURENIP varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
NOTICEPRINT integer,
COUNTRYCODE varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
PESEL varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
KRS varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
CONSTRAINT PK_CLIENT PRIMARY KEY (ID_CLIENT)
);
CREATE INDEX CLIENT_ID_CLIENT_A ON CLIENT (ID_CLIENT);
CREATE DESCENDING INDEX CLIENT_ID_CLIENT_D ON CLIENT (ID_CLIENT);
CREATE INDEX CLIENT_SYMFONIANUMBER_A ON CLIENT (SYMFONIANUMBER);
CREATE INDEX IDX_CLIENT ON CLIENT (RECORD_ACTIVE);


CREATE GLOBAL TEMPORARY TABLE IMPORT_TEMP_LOCATION
(
OBJNUM integer NOT NULL,
CITY varchar(255),
IDCLIENT integer,
IDLOCATION integer,
LATITUDE integer,
LONGITUDE integer,
GEOCODED_LEVEL integer,
POSITIONVERIFIED integer,
CLIENT_NAME varchar(255),
CLIENT_OUTID integer,
STREET varchar(255),
POSTCODE varchar(6),
HOMENUMBER varchar(12),
CONSTRAINT PK_IMPORT_TEMP_LOCATION_0 PRIMARY KEY (OBJNUM)
)
ON COMMIT PRESERVE ROWS;
CREATE INDEX IDX_IMPORT_TEMP_LOCATION1 ON IMPORT_TEMP_LOCATION (CITY,POSTCODE,STREET,HOMENUMBER,CLIENT_NAME);
CREATE INDEX IDX_IMPORT_TEMP_LOCATION2 ON IMPORT_TEMP_LOCATION (CITY,CLIENT_OUTID,STREET,POSTCODE,HOMENUMBER);
CREATE INDEX IDX_IMPORT_TEMP_LOCATION3 ON IMPORT_TEMP_LOCATION (CLIENT_OUTID);


In CLIENT table I have 167502 rows and in IMPORT_TEMP_LOCATION I have 203 rows and it takes 1:30 minutes to execute my query.

SELECT C.ID_CLIENT FROM IMPORT_TEMP_LOCATION T
LEFT OUTER JOIN CLIENT C ON (T.CLIENT_OUTID = C.SYMFONIANUMBER)


I've recomputed indices statistics but it does not help.

Here is the info about my query:

Executing statement...
Statement executed (elapsed time: 0.000s).
72911748 fetches, 0 marks, 2450838 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 34006763 seq.
Delta memory: 17956 bytes.
Total execution time: 0:01:36 (hh:mm:ss)
Script execution finished.
Rolling back the transaction...
Transaction rolled back (elapsed time: 0.001s).
Starting transaction...
Preparing statement: SELECT C.ID_CLIENT FROM IMPORT_TEMP_LOCATION T
LEFT OUTER JOIN CLIENT C ON (T.CLIENT_OUTID = C.SYMFONIANUMBER)
Statement prepared (elapsed time: 0.001s).
Field #01: CLIENT.ID_CLIENT Alias:ID_CLIENT Type:INTEGER
PLAN JOIN (T NATURAL, C NATURAL)


Executing statement...
Statement executed (elapsed time: 0.000s).
72911748 fetches, 0 marks, 2450840 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 34006763 seq.
Delta memory: 18072 bytes.
Total execution time: 0:01:36 (hh:mm:ss)
Script execution finished.


Maybe the problem is that I use GLOBAL TEMPORARY TABLE ?

Thanks for any help