Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    1

    Question Unanswered: Large Update/Join and Performance

    Hi All -

    I have a procedure that creates dynamic sql to perform updates on several tables with parent/child relations, many of which have several children. I am merging data from several databases and need to update keys based on those relations.

    Some of the tables run upward of several hundred thousand records each, and must be joined then updated. The updates are extremely slow and I'm wondering if I could get some suggestions on the update statement.

    Here is an example that must update child table foreign keys based on records retrieved from a join. The inner "WHERE" clause that joins TEMP_FIELD to PARENT_ID is needed to identify new records brought into the merged database. The records that will be updated are those that are populated (ie TEMP_FIELD IS NOT NULL) and match the aforementioned join.

    The last where clause (ie WHERE a.PK = q.childPK) is used to keep the query from returning multiple rows for update (otherwise produces sql error "ORA-01427: single-row subquery returns more than one row").

    The logic works as tested on the data. The problem is that the performance is horrible-- looking for any insight.



    UPDATE child a
    SET a.childFK =
    (
    SELECT parentGID FROM
    (SELECT b.PK parentPK, b.GLOBALID parentGID, c.PK childPK
    FROM parent b, child c
    WHERE c.TEMP_FIELD = b.PARENT_ID
    AND c.TEMP_FIELD IS NOT NULL) q
    WHERE a.PK = q.childPK)



    Please ask away for more clarification as it's kind of convoluted.

    Thank you for your replies!

    Chris

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    OS name & version?
    Oracle version to 4 decimal places

    >I have a procedure that creates dynamic sql
    Almost guarentees non-scalable s/w.

    TEMP_FIELD?????
    Sounds like kludge or s/w ported to Oracle from other RDBMS
    Please elaborate how, what & why this field ( name).

    WHERE c.TEMP_FIELD = b.PARENT_ID
    AND c.TEMP_FIELD IS NOT NULL) q
    WHERE a.PK = q.childPK)

    do indexes exist on every column in WHERE clause
    & are statistics current on all tables & indexes.

    Post FORMATTED! EXPLAIN PLAN

    Where does SQL_TRACE show time is being spent?
    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
    Feb 2009
    Posts
    62
    If you're confident that your scripts will preserve referential integrity, you can save some time by disabling all the FK constraints, and then re-enabling them at the end.

  4. #4
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    Quote Originally Posted by opspatial
    Hi All -

    "ORA-01427: single-row subquery returns more than one row").



    UPDATE child a
    SET a.childFK =
    (
    SELECT parentGID FROM
    (SELECT b.PK parentPK, b.GLOBALID parentGID, c.PK childPK
    FROM parent b, child c
    WHERE c.TEMP_FIELD = b.PARENT_ID
    AND c.TEMP_FIELD IS NOT NULL) q
    WHERE a.PK = q.childPK)



    Please ask away for more clarification as it's kind of convoluted.

    Thank you for your replies!

    Chris

    ORA-01427: means the sub query give more than one row (result) from updating the main table. re write the sub query so that it return only one row.

    SELECT parentGID FROM
    (SELECT b.PK parentPK, b.GLOBALID parentGID, c.PK childPK
    FROM parent b, child c
    WHERE c.TEMP_FIELD = b.PARENT_ID
    AND c.TEMP_FIELD IS NOT NULL) q
    WHERE a.PK = q.childPK
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

Posting Permissions

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