Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009

    Unanswered: Reasons for update statement is slow for huge records....

    Hi all,

    Am new to dbforums. First I will explain my problems as below:

    1) I have a table of 350 columns for which i have two indexes. One is for primary key's id

    and the other is the composite id (combination of two functional ids)
    2) Through my application, the user can calculate some functional conditions and the result

    is updated in the same table.
    3) The table consists of all input, intermediate and output columns.
    4) The only way of calculation is done through update statements. The problem is, for one

    complete process, the total number of update statement hits the db is around 1000.
    5) From the two index, one indexed column is mandatory in all update where clause. So one

    will come at any case but the other is optional.
    6) Updating the table is taking a long time if the row count exceeds 1lakh.
    7) I will now explain the scenario:
    a. Say there is 5lakh 100 records in the table in which mandatory indexed column id 1 has

    100 records and id 2 has 5 lakhs record.
    b. If I process id 1, it is very fast and executed within 10seconds. But if I process id 2,

    then it is taking more than 4 minutes to update.

    Is there any way to increase the speed of the update statement. Am using oracle 10g.
    Please help me in this, Since I am a developer and dont have much knowledge in oracle.

    Thanks in advance.


  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    You have table & DDL to create it. We don't.
    You have data & DML to create it. We don't.
    You have SQL which is or OK or not. We don't.
    You have EXPLAIN PLAN for good & bad SQL. We don't.
    You know Operating System name & version. We don't.
    You know Oracle version (to 4 decimal places). We don't.

    Why do you expect that we can rub our crystal ball & provide you answers?

    Are statistics current on both tables & indexes?
    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
    Liverpool, NY USA
    Do you have indexes on the table? More information is needed.
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jan 2009
    Dhaka, Bangladesh
    create index on those columns which are foreign key.

    if your table contains hug data try table partition
    Mohammad Hasan Shaharear

Posting Permissions

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