Thanks for the reply, Stephen. When I had a look at the "Batch job performance problems", I vented it out the same way you did. The naive business folks were told "The database is slow. Tune it."
I created a seperate schema with the culprit tables, updated the column values to trim them, removed the function calls in the query, and executed the job within 15 minutes (from 3 hours). "Are you sure this is the same database on the same server?". And now the cannon ball's not in my court. Thanks. Raj "Only Robinson Crusoe had all his work done by Friday" Stephane Faroult To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> <sfaroult@ori cc: ole.com> Subject: Re: Query Tuning Sent by: root@fatcity. com June 20, 2002 03:53 PM Please respond to ORACLE-L [EMAIL PROTECTED] wrote: > > Hello Folks, > > Given an Oracle 7.3.4 database, how would you tune a query as under, other > than suggesting a migration to a higher version. This query is currently > performing a lot of I/O, obviously doing a full tablescan on CAMPMAIN. > > SELECT CAMPNAME,ASGNMTTYPE,CAMPRTGNUM, LTRIM(RTRIM(CAMPTYPE)) > FROM CAMPMAIN > WHERE LTRIM(RTRIM(CAMPNAME)) = :b1 > AND (LOAD_FAILED_FLG = 'N' OR LOAD_FAILED_FLG = '' OR LOAD_FAILED_FLG IS > NULL ); > > This query runs in a PL/SQL loop. For now, my suggestion was to create a > temporary table with all the fields and a fully trimmed CAMPNAME field > outside the loop, create an index on this table, and then use this > temporary table inside the loop. Any better suggestions? > > Regards > Raj > 1) What the $%ща#? is this LTRIM(RTRIM()) doing? CAMPNAME and CAMPTYPE are CHAR instead of VARCHAR2 ? You are right to want to clean-up your data. 2) Queries inside loop are rarely useful. Try to rethink in terms of INSERT ... SELECT if you are inserting, or UPDATE SET (..., ... ) = (SELECT ....) if updating. In-line views can help too. Ah, and if you are using your SELECT to use the result into another SELECT, there's something named a join. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).