Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002

    Unanswered: Query takes a long time

    I am trying to update update a table with the following cursor, this table has around 2.3 million records. This PLSQL code has been running for around 16 hours now. I used the same PL/SQL code for other tables that had around 1.4 million records. It took only about an hour to update those. I was wandering why this particular table gets so long to update?


    cursor episodes is
    select v9_drg_cd, fk_ptnt_epsd_dalhs from st_NY_epsd_&1 for update of fk_ptnt_epsd_dalhs nowait;


    for episode_rec in episodes loop

    if episode_rec.v9_drg_cd in ('001','002','004','005','007','008','530','531',' 730','737','738','739','792') then

    update st_NY_epsd_&1
    set fk_ptnt_epsd_dalhs='01'
    where current of episodes;

    /* NEUROLOGY */
    elsIF episode_rec.v9_drg_cd in('009','012','013','014','015','016','017','018' ,'019','020','021',
    '022','023','024','025','034','035','532','533','7 61','762',
    '763','764','765','766','767','768','769') then

    update st_NY_epsd_&1
    set fk_ptnt_epsd_dalhs='02'
    where current of episodes;


  2. #2
    Join Date
    Jul 2003
    I think you are making this harder than it needs to be.
    Also, it looks like you added some spaces to the values in your IN clauses unless that was intentional (ie: ' 730').

    Also, is v9_drg_cd a numeric column or varchar?

    put an index on v9_drg_cd (possibly a bitmap index) then try
    some straight update statements.

    PHP Code:
    UPDATE st_ny_epsd_&1
       SET fk_ptnt_epsd_dalhs 
    WHERE v9_drg_cd IN ('001''002''004''005',
    ' 730''737''738''739''792');

    /* NEUROLOGY */
    update st_NY_epsd_&
    set fk_ptnt_epsd_dalhs 
    where v9_drg_cd in ('009','012','013','014',
    '035','532','533','7 61',
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Mar 2002
    Reading, UK
    OK you can speed this up by doing the following

    1) convert into a single update statement i.e.

    update st_NY_epsd_&1 set fk_ptnt_epsd_dalhs=
    (case when v9_drg_cd in(x,y,...) then '01' when ... end)

    2) if the number of different values for episode_rec.v9_drg_cd is into the tens or more then setup a lookup table with columns (episode_rec.v9_drg_cd,fk_ptnt_epsd_dalhs) and make it an index organized table in the keep pool

    and do the following update

    update st_NY_epsd_&1 x set fk_ptnt_epsd_dalhs=(select fk_ptnt_epsd_dalhs from lookup_table y where y.v9_drg_cd = x.v9_drg_cd)

    3. Try updating a subset of records in st_NY_epsd_&1 at a time to reduce undo usage.

    4. watchout for chained rows by doing an analyze before and after.

    Last edited by AlanP; 08-31-04 at 11:31.

  4. #4
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    The previous suggestions may or may not decrease the runtime.
    Without having any facts upon which to base (proposed) changes, you are just shooting in the dark & hoping to get lucky.
    Why not enable SQL_TRACE to actually see where the time is being spent after running results thru TKPROF?
    Are the updated columns part of an index; which then too must be updated?
    Is there an UPDATE trigger on the table?
    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.

Posting Permissions

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