"Insert into ... select from ..." takes too long
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: "Insert into ... select from ..." takes too long

Hybrid View

  1. #1
    Join Date
    Jun 2005
    Posts
    5

    Exclamation "Insert into ... select from ..." takes too long

    I'm just trying to do a simple insert, selecting from 2 joined tables.

    The frustrating thing is that when I execute the "select from" by itself, it executes in about 15 seconds. When I slap on the "insert into " in front of it, it takes 2 hours!!! The 2 "select from" tables have 45,000 and 400,000 records. I'm running Oracle 10g on a 3 Ghz Windows XP machine with 1 GB of RAM. (Not that I really think that should make any difference.)

    Here's the query:

    insert into WORKSESSIONS
    select
    from SHIFTS s,
    TASKHISTORY h
    where s.FACILITYID = 'EA-N90-TRACON'
    and h.FAC_IDENT = 'EA-N90-TRACON'
    and s.EMPLOYEEID = h.EMPLOYEE_ID
    and h.START_TIME_LCL between s.SHIFTSTART and s.SHIFTSTOP
    and h.PROJECT_NUM is not null
    and h.TASK_NUM is not null

    I've tried numerous optimizer hints including INDEX (specifying the appropriate indexes), RULE, and others and nothing changes. When I look into the session with OEM it shows the "cost" at more than 4000. There's obviously some kind of optimizer hint that applies but I'm not expert enough to know what to use.

  2. #2
    Join Date
    Jun 2005
    Posts
    5
    Addendum to post:

    For some reason the forum chopped out the text after the "select" word in my query. All it was is this simple list of fields being selected from both tables:

    s.SHIFTNUMBER, h.START_TIME_LCL, h.END_TIME_LCL, 'AT', h.PROJECT_NUM

  3. #3
    Join Date
    Mar 2002
    Posts
    534
    please show the explain plan.


    are there any indexes on WORKSESSIONS?
    have you tried with the /*+ append */ hint?

  4. #4
    Join Date
    Jun 2005
    Posts
    5

    + APPEND did nothing

    First, thank you very much for taking the time to help.

    The problem seems to be that Oracle knows how to do the join when it's just the select by itself. But when you add the insert statement, it goes and does something stupid with the select portion. I've inserted one of the versions of the query along with its explain plan below it.

    insert /*+ APPEND */ into LDRWORKED
    select /*+ INDEX(s SHIFTSFAC h TASKHISTORY_PK) */ s.SHIFTNUMBER, h.START_TIME, h.PROJECT_NUM, h.TASK_NUM
    from SHIFTS s,
    TASKHISTORY h
    where s.FACILITYID = 'EA-N90-TRACON'
    and h.FAC_IDENT = 'EA-N90-TRACON'
    and s.EMPLOYEEID = h.EMPLOYEE_ID
    and h.START_TIME between s.SHIFTSTART and s.SHIFTSTOP
    and h.PROJECT_NUM is not null
    and h.TASK_NUM is not null


    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    INSERT STATEMENT Optimizer Mode=ALL_ROWS 7 K 5387
    LOAD AS SELECT
    HASH JOIN 7 K 729 K 5387
    TABLE ACCESS BY INDEX ROWID SWARM.SHIFTS 30 K 1 M 517
    INDEX RANGE SCAN SWARM.SHIFTSFAC 30 K 164
    TABLE ACCESS FULL MSDT.DUTY_TBLTASK_HISTORY 45 K 2 M 4643

  5. #5
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131
    Quote Originally Posted by dougclind
    First, thank you very much for taking the time to help.

    The problem seems to be that Oracle knows how to do the join when it's just the select by itself. But when you add the insert statement, it goes and does something stupid with the select portion. I've inserted one of the versions of the query along with its explain plan below it.

    insert /*+ APPEND */ into LDRWORKED
    select /*+ INDEX(s SHIFTSFAC h TASKHISTORY_PK) */ s.SHIFTNUMBER, h.START_TIME, h.PROJECT_NUM, h.TASK_NUM
    from SHIFTS s,
    TASKHISTORY h
    where s.FACILITYID = 'EA-N90-TRACON'
    and h.FAC_IDENT = 'EA-N90-TRACON'
    and s.EMPLOYEEID = h.EMPLOYEE_ID
    and h.START_TIME between s.SHIFTSTART and s.SHIFTSTOP
    and h.PROJECT_NUM is not null
    and h.TASK_NUM is not null


    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    INSERT STATEMENT Optimizer Mode=ALL_ROWS 7 K 5387
    LOAD AS SELECT
    HASH JOIN 7 K 729 K 5387
    TABLE ACCESS BY INDEX ROWID SWARM.SHIFTS 30 K 1 M 517
    INDEX RANGE SCAN SWARM.SHIFTSFAC 30 K 164
    TABLE ACCESS FULL MSDT.DUTY_TBLTASK_HISTORY 45 K 2 M 4643
    Are shifts and taskhistory views or synonyms. It ignored your hint on taskhistory.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The 15 seconds -- is that to get the first row back or to get al of them?

    I'm wondering whether your system is configured to use FIRST_ROWS, but then in an insert statement Oracle would want to use ALL_ROWS -- this would change the execution plan. what's the explain plan for the select that does perform well?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Jun 2005
    Posts
    5

    Good question, but ...

    the answer is that it was all rows returned. The entire select took the 15 seconds and returned 5599 rows:

    insert /*+ APPEND */ into LDRWORKED
    select /*+ INDEX(s SHIFTSFAC h DUTY_TBLTASK_HISTORY_PK) */ s.SHIFTNUMBER, h.START_TIME_LCL, h.END_TIME_LCL, 'AT', h.PROJECT_NUM, h.TASK_NUM, h.DATETIMESTAMP
    from SHIFTS s,
    msdt.duty_tblTASK_HISTORY h
    where s.FACILITYID = 'EA-N90-TRACON'
    and h.FAC_IDENT = 'EA-N90-TRACON'
    and s.EMPLOYEEID = h.EMPLOYEE_ID
    and h.START_TIME_LCL between s.SHIFTSTART and s.SHIFTSTOP
    and h.PROJECT_NUM is not null
    and h.TASK_NUM is not null

    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    SELECT STATEMENT Optimizer Mode=ALL_ROWS 7 K 5387
    HASH JOIN 7 K 729 K 5387
    TABLE ACCESS BY INDEX ROWID SWARM.SHIFTS 30 K 1 M 517
    INDEX RANGE SCAN SWARM.SHIFTSFAC 30 K 164
    TABLE ACCESS FULL MSDT.DUTY_TBLTASK_HISTORY 45 K 2 M 4643

  8. #8
    Join Date
    Jun 2005
    Posts
    5

    for dbtoo...

    All tables are tables, not views. To be honest I have no idea what synonyms are. The task history table has about 400,000 records in it.

    Another question I didn't answer, the destination table (which I originally referred to as WORKSESSIONS but is actually named LDRWORKED) has no indexes. It only has a PK constraint and it only contains a few thousand records.

    If anyone is confused as to why the names in the queries seemed to change between posts, I tried to keep things simple in the first two. In the last one I just gave up and left the query with the original names.

  9. #9
    Join Date
    Dec 2001
    Location
    Edmonton, Canada
    Posts
    50
    Make sure you have indexes on EmployeeID . Index should be on the larger table. This is the only way Optimizer can use Nested Loop for your query and will avoid the extra has join.
    An elephant is a mouse with an operating system.

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    do you have triggers on worksession?

    also how about post your query explain plan and the insert explain plan WITHOUT your hints?

Posting Permissions

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


Click Here to Expand Forum to Full Width