Rajesh.Rao_at_jpmchase.com 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
>
- 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.
- 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: sfaroult_at_oriole.com
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: ListGuru_at_fatcity.com (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).
Received on Thu Jun 20 2002 - 14:53:45 CDT
