--- In [email protected], "jankowalsky825" wrote: > > 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 > ( ... > SYMFONIANUMBER varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK, ... > ); ... > CREATE INDEX CLIENT_SYMFONIANUMBER_A ON CLIENT (SYMFONIANUMBER); ... > CREATE GLOBAL TEMPORARY TABLE IMPORT_TEMP_LOCATION > ( ... > CLIENT_OUTID integer, ... > ) > ON COMMIT PRESERVE ROWS; ... > 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)
You are joining INTEGER with VARCHAR. It couldn't use index for such condition. You can : - change type of CLIENT_OUTID to VARCHAR, or - change type of SYMFONIANUMBER to INTEGER, or - CAST(CLIENT_OUTID AS VARCHAR) in join condition (if it makes sence for your data) Regards, Vlad
