Quoting jankowalsky825 <[email protected]>: I remember having to put where clause conditions on the join fields to get Firebird to use the indices, for example:
where T.CLIENT_OUTID > 0 and C.SYMFONIANUMBER > 0 Assuming those conditions are always true, this might get the optimizer to use the indices. Louis Kleiman > 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 > > > ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.
