Remote index not used with INSERT in local table over dblink
644807Feb 15 2010 — edited Feb 17 2010Hi all
I am not sure if anyone has encountered this issue before, but for some reason the remote index remains unused ONLY* during an insert operation on the local database. Let me explain this with psuedo-code
insert into LOCAL_TABLE
select /*+ index_combine(alias_remote_tab IDX_LOG_DATE) */
trunc(log_datetime),
count(*)
from REMOTE_TABLE@DBLINK alias_remote_tab
where trunc(log_datetime)=trunc(sysdate-1)
group by trunc(log_datetime);
where:
REMOTE_TABLE is a partitioned table on log_datetime (monthly)
IDX_LOG_DATE is a valid function-based bitmap index on log_datetime created as trunc(log_datetime)
local database: 10gR2
remote database: 11gR1
os: windows (both)
Funny thing is when I run just the select query independently on both local as well as remote database the index is used. I verified this by printing out the explain plan for the select query. But when I prefix the query with the insert all hell breaks lose and local database acts ignorant about the index. The explain plan for the insert query has no mention of the index even when I explicitly place the index hint in the select part of the query.
Shouldn't this be simple enough for ORACLE? Am I missing something here?