Hi ,
I have an insert statement which uses append hint. select query provide the data set for the insert. When i'm running my select query alone, it fetches the data in less than sec time. But when i'm using the same query in my insert.. Its taking huge time.
I have a partition on t.day. Please help
truncate table tab1
insert /*+ append */ into tab1
(
trn_id,
afe_id,
act_id,
afe_ziet_id,
acc_zip_id,
tran_zip_id,
mem_zip_id,
veh_class,
rated,
sou,
rateclass,
super_class,
currency,
date,
edate,
amount
)select t.transaction_id,
t.affiliate_id,
t.account_id,
t.affiliate_zipfleet_id,
t.account_zipfleet_id,
t.transaction_zipfleet_id,
t.member_zipfleet_id,
r.vehicle_class,
res.rated_as,
r.source,
rp.rate_plan_class,
rp.super_class,
t.currency_iso,
cast(t.bill_date at time zone 'US/EASTERN' as date) bill_date,
cast(t.end_date at time zone 'US/EASTERN' as date) end_date,
round(t.amount,2) as amount
from t1 t,
r1 r,
r2 res,
r3 rp,
r4 p
where (t.transaction_type = 'usage' or t.code_key = 'waiver_fee')
and t.revenue_p = 't'
and t.reservation_id is not null
and t.bill_date < t.end_date
and trunc( t.bill_date at time zone 'US/EASTERN') < trunc(t.end_date at time zone 'US/EASTERN')
and t.amount <> 0
and t.reservation_id = r.reservation_id
and res.reservation_id = r.reservation_id
and rp.rate_plan_id = r.rate_plan_id
and t.day >= add_months(p.min_day,-12);