Re: Query Tuning

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 <ORACLE-L_at_fatcity.com>        
                    <sfaroult_at_ori        cc:                                                                        
                    ole.com>             Subject:     Re: Query Tuning                                              
                    Sent by:                                                                                        
                    root_at_fatcity.                                                                                   
                    com                                                                                             
                                                                                                                    
                                                                                                                    
                    June 20, 2002                                                                                   
                    03:53 PM                                                                                        
                    Please                                                                                          
                    respond to                                                                                      
                    ORACLE-L                                                                                        
                                                                                                                    
                                                                                                                    

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
>

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




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Rajesh.Rao_at_jpmchase.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).