with tmp as (select * from a where xxxx=xxx)
select a.*
from tmp a
left join
(
select * from b
where inserttime >= trunc(sysdate)+0/24
and inserttime < trunc(sysdate)+4/24
) b on
where b.id is null
union all
select a.*
from tmp a
left join
(
select * from b
where inserttime >= trunc(sysdate)+4/24
and inserttime < trunc(sysdate)+8/24
) b on
where b.id is null
SELECT STATEMENT, GOAL = ALL_ROWS Cost=90 CPU cost=252284361 IO cost=61 Cardinality=7716 Bytes=100308
TEMP TABLE TRANSFORMATION
LOAD AS SELECT
TABLE ACCESS FULL Object name=ETL_RULE Cost=37 CPU cost=207190251 IO cost=13 Cardinality=1286 Bytes=136316
FILTER
HASH GROUP BY Cost=53 CPU cost=45094111 IO cost=48 Cardinality=7716 Bytes=100308
VIEW Cost=52 CPU cost=32037059 IO cost=48 Cardinality=7716 Bytes=100308
UNION-ALL
FILTER
HASH JOIN RIGHT OUTER Cost=9 CPU cost=5339510 IO cost=8 Cardinality=1286 Bytes=111882
TABLE ACCESS FULL Object name=OP_MAIL_VISIT_17_1104 Cost=2 CPU cost=7121 IO cost=2 Cardinality=1 Bytes=48
VIEW Cost=6 CPU cost=919767 IO cost=6 Cardinality=1286 Bytes=50154
TABLE ACCESS FULL Object name=SYS_TEMP_0FD9D660B_D73396C9 Cost=6 CPU cost=919767 IO cost=6 Cardinality=1286 Bytes=136316
FILTER
HASH JOIN RIGHT OUTER Cost=9 CPU cost=5339510 IO cost=8 Cardinality=1286 Bytes=111882
TABLE ACCESS FULL Object name=OP_MAIL_VISIT_17_1104 Cost=2 CPU cost=7121 IO cost=2 Cardinality=1 Bytes=48
VIEW Cost=6 CPU cost=919767 IO cost=6 Cardinality=1286 Bytes=50154
TABLE ACCESS FULL Object name=SYS_TEMP_0FD9D660B_D73396C9 Cost=6 CPU cost=919767 IO cost=6 Cardinality=1286 Bytes=136316
FILTER
HASH JOIN RIGHT OUTER Cost=9 CPU cost=5339510 IO cost=8 Cardinality=1286 Bytes=111882
TABLE ACCESS FULL Object name=OP_MAIL_VISIT_17_1104 Cost=2 CPU cost=7121 IO cost=2 Cardinality=1 Bytes=48
VIEW Cost=6 CPU cost=919767 IO cost=6 Cardinality=1286 Bytes=50154
TABLE ACCESS FULL Object name=SYS_TEMP_0FD9D660B_D73396C9 Cost=6 CPU cost=919767 IO cost=6 Cardinality=1286 Bytes=136316
FILTER
HASH JOIN RIGHT OUTER Cost=9 CPU cost=5339510 IO cost=8 Cardinality=1286 Bytes=111882
TABLE ACCESS FULL Object name=OP_MAIL_VISIT_17_1104 Cost=2 CPU cost=7121 IO cost=2 Cardinality=1 Bytes=48
VIEW Cost=6 CPU cost=919767 IO cost=6 Cardinality=1286 Bytes=50154
TABLE ACCESS FULL Object name=SYS_TEMP_0FD9D660B_D73396C9 Cost=6 CPU cost=919767 IO cost=6 Cardinality=1286 Bytes=136316
FILTER
HASH JOIN RIGHT OUTER Cost=9 CPU cost=5339510 IO cost=8 Cardinality=1286 Bytes=111882
TABLE ACCESS FULL Object name=OP_MAIL_VISIT_17_1104 Cost=2 CPU cost=7121 IO cost=2 Cardinality=1 Bytes=48
VIEW Cost=6 CPU cost=919767 IO cost=6 Cardinality=1286 Bytes=50154
TABLE ACCESS FULL Object name=SYS_TEMP_0FD9D660B_D73396C9 Cost=6 CPU cost=919767 IO cost=6 Cardinality=1286 Bytes=136316
FILTER
HASH JOIN RIGHT OUTER Cost=9 CPU cost=5339510 IO cost=8 Cardinality=1286 Bytes=111882
TABLE ACCESS FULL Object name=OP_MAIL_VISIT_17_1104 Cost=2 CPU cost=7121 IO cost=2 Cardinality=1 Bytes=48
VIEW Cost=6 CPU cost=919767 IO cost=6 Cardinality=1286 Bytes=50154
TABLE ACCESS FULL Object name=SYS_TEMP_0FD9D660B_D73396C9 Cost=6 CPU cost=919767 IO cost=6 Cardinality=1286 Bytes=136316
select *
from tmp_actionid_lst a
where not exists
(
select 1
from lars01.OP_MAIL_VISIT_03_1105@oss139.localdomain b
where a.action_id=b.actionid and a.opcontent_id=b.opcontentid
and b.inserttime >=trunc(to_date('20110510000000','yyyymmddhh24miss'))
and b.inserttime <trunc(to_date('20110510000000','yyyymmddhh24miss'))+1
)
