If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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

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

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?

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.

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.