Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Posts
    5

    Unanswered: Need Performance Improvement Suggestion - Update Statement

    Dear All,
    I have been trying to update a partitioned table - a few records in Single partition. The partition is based on Source System Name. For this, I followed below steps:-

    1. Create temp table - this records are required to be updated.
    CREATE TABLE temp_1
    AS
    SELECT
    a.source_system_key,
    a.novation_dt,
    a.last_upd_dt,
    a.source_system_name
    FROM temp@csdra a
    WHERE a.source_system_name = 'ABC'
    AND a.prodinst_subtype IN ('NTN', 'BIN', 'FOI', 'NWS', 'PORT')
    AND (a.novation_dt IS NOT NULL OR a.last_upd_dt IS NOT NULL);

    2. Created index :-
    CREATE INDEX idx_temp_1 ON temp_1(source_system_key);

    3. Update records:-
    UPDATE temp_table b
    SET ( b.novation_dt, b.last_upd_dt)
    = ( SELECT a.novation_dt, a.last_upd_dt
    FROM temp_1 a
    WHERE b.source_system_key = a.source_system_key
    )
    WHERE b.source_system_name ='ABC' AND b.deleted_flg =0;

    Here, the table temp_1 created in step 1 contains 4 millions. Table temp_table is original table which contains 10 million records. I am trying to update temp_table in step3 using temp_1 table.

    However, when i try this, it is taking huge amount of time e.g 4-5 hours and still not completed. Also, as per the expain plain, this update should take aroung 1hour 20 mins.

    Please suggest and help to improve the performance of this update statement.

    Regards, Anand.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    do as below so we can know complete Oracle version & OS name.

    Post via COPY & PASTE complete results of
    SELECT * from v$version;

    Are statistics current for all table & indexes involved?

    post formatted EXPLAIN PLAN like below

    Code:
    SQL> set autotrace on explain
    SQL> select sysdate from dual;
    
    SYSDATE
    -------------------
    2011-09-05 07:18:48
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1388734953
    
    -----------------------------------------------------------------
    | Id  | Operation	 | Name | Rows	| Cost (%CPU)| Time	|
    -----------------------------------------------------------------
    |   0 | SELECT STATEMENT |	|     1 |     2   (0)| 00:00:01 |
    |   1 |  FAST DUAL	 |	|     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    More important, does the table temp_table have an index on source_system_key. For such small tables, it should return within a few seconds. also does deleted_flg have an index, if it does you might want to disable it by using the following select. A function on an indexed column (unless using a function index) will disable the index

    UPDATE temp_table b
    SET ( b.novation_dt, b.last_upd_dt)
    = ( SELECT a.novation_dt, a.last_upd_dt
    FROM temp_1 a
    WHERE b.source_system_key = a.source_system_key
    )
    WHERE b.source_system_name ='ABC' AND floor(b.deleted_flg) =0;
    Last edited by beilstwh; 09-06-11 at 15:04.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Oct 2010
    Posts
    5

    Need Performance Improvement Suggestion - Update Statement

    Hi,
    Thanks for the reply and suggestion.

    SELECT * from v$version;

    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE 10.2.0.4.0 Production
    TNS for HPUX: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production

    Explain plan from TEST BOX:
    =========================
    PLAN_TABLE_OUTPUT

    Plan hash value: 603503536

    ----------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------------------------
    | 0 | UPDATE STATEMENT | | 12M| 264M| 42182 (2)| 00:23:53 | | |
    | 1 | UPDATE | TEMP_TABLE | | | | | | |
    | 2 | PARTITION LIST SINGLE | | 12M| 264M| 42182 (2)| 00:23:53 | KEY | KEY |
    |* 3 | TABLE ACCESS FULL | TEMP_TABLE | 12M| 264M| 42182 (2)| 00:23:53 | 53 | 53 |
    | 4 | TABLE ACCESS BY INDEX ROWID| TEMP_1 | 1 | 25 | 4 (0)| 00:00:01 | | |
    |* 5 | INDEX RANGE SCAN | IDX_TEMP_1 | 1 | | 3 (0)| 00:00:01 | | |
    ----------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    3 - filter("B"."DELETED_FLG"=0)
    5 - access("A"."SOURCE_SYSTEM_KEY"=:B1)

    There is no index on TEMP_TABLE.

    Regards, Anand.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    without an index on temp_table the procedure will have to do a full table scan for every row. Put on the index and it should run in about a minute.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •