"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.

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

please show the explain plan.


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

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

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?

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.

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

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.

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.

do you have triggers on worksession?

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

Is nologging hint appropriate here? Its been a while since I did this stuff. I recall append only uses empty unused blocks.

Did you resolve the issue? if so, how did you do that. I have similar issue now.

Please share if you find any solution to this issue.

you are trying to get answers for 6+ years old question ... trace your session and see what it is doing.

Regards,