Skip to Main Content

My insert doesn't work well

908418Jun 12 2014 — edited Jun 17 2014

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);

Comments
Post Details
Added on Jun 12 2014
6 comments
150 views