"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
from SHIFTS s,
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.