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).
