Does the substring function on a1.FI_INSTRUMENT cause a table scan?

ALso, possibly create this with union statements, one for each of the
values in the IN clause. Sometimes an IN can cause a scan rather than
using and index.

Keith




"sumeet"
<db2udbdba-ezmlms
hield-x20898676.x To
[Email Address Removed] "LazyDBA Discussion"
A.com> <[Email address protected]
cc
01/27/2005 03:54
PM Subject
RE: SQL SUBOPTIMAL help please...











Hey Folks,

Since we have upgraded from Db7.2 to DB2 8.2 to DB2 8.2 fix pack 7a we
are getting the This SQL Error. We run DB2 on AIX 5.1 Unix Server.

insert into PS_HBC_CUSTR_LKUP_wk select * from PS_HBC_CUSTR_LKUP a where
substr(a.FI_INSTRUMENT_ID,1,3) IN ('XIM','XAM','XST','XCL') and
a.fi_instrument_id = ( select b.HBC_INSTRUMENT_FR from PS_HBC_INSTRUMENT b
WHERE b.HBC_INSTRUMENT_FR = a.fi_instrument_id)
SQL0437W Performance of this complex query may be sub-optimal. Reason
code:
"6". SQLSTATE=01602

Other than this message We also out ETL Processes which load data into
Tables from Flat Files have been running 4 to 5itmes faster.

Can someone please help us in this issue?

Thank You,

Sumeet Agarwal
Technology & Solutions
Ph: 312-293-4976
sumeet.[Email address protected]
Fax: 312-461-7333





---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html





This electronic message and any attachments hereto (this "e-mail") may contain information that is privileged, confidential or otherwise protected from disclosure. The information is intended for the use of the addressee(s) only. If the reader of this email is not the intended recipient, any disclosure, copy, distribution or use of the contents of or attachments to this email is prohibited. If you have received this e-mail in error, please notify us immediately by reply e-mail, then delete this e-mail and destroy any printed copies. It is the sole responsibility of the recipient to ensure that this e-mail is free from virus or other defect which may adversely affect the recipient's business operations. We disclaim any liability for any damages, including special and incidental or consequential damages, and any related costs or expenses arising in any way whatsoever from the receipt or use of this e-mail.
This e-mail is not an offer to create a contract and its terms do not form a legally binding contract. Thank you.

DB2 & UDB email list listserv db2-l LazyDBA home page