update taking too long on 174,000+ records

From: Saira Somani <saira_somani_at_yahoo.com>
Date: Mon, 05 May 2003 09:54:36 -0800
Message-ID: <F001.0058FCDF.20030505095436@fatcity.com>

List Gurus,

SQL> desc TEST65D.PDA_ORDER_HEADER;

 Name                                      Null?    Type
 ----------------------------------------- -------- ---------
 ID                                                 NUMBER(10)
 USER_CODE                                          CHAR(30)
 CUST_NUM                                           CHAR(10)
 SORT_NAME                                          CHAR(20)
 DATE_STAMP                                         DATE
 STAT                                               CHAR(1)

I wanted to update the STAT field to ' ' in PDA_ORDER_HEADER table under the TEST65D schema so records are available for testing.

Usually it doesn't take this long. Previously, I had changed the STAT from 'Y' to 'N' in less than 5 minutes with an update and commit statement for 174,000+ records. Now the statement runs for over 15 minutes and I haven't yet had success.

update pda_order_header set stat=' ' where stat='N'

Then I tried to filter it by customer and date but no success - again the query ran for way too long:

update pda_order_header set stat=' ' where cust_num='2' and date_stamp between '02-JAN-03' and '31-JAN-03'

This is also the case when I try to update one record using a unique ID in the where clause like this:

update pda_order_header set stat=' ' where id=37267

V$SESSION shows that the session is active but even after 15 minutes, there is no result.

I have since killed the sessions from SQLPLUS using:

alter system kill session 'pid,serial'

What is going on?
Thanks for your help.

Saira

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Saira Somani
  INET: saira_somani_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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 Mon May 05 2003 - 12:54:36 CDT