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
