Re: Which "INSERT INTO" is fastest ?

Richard Spee wrote:
>
> If you are loading large quantities of data, then consider using direct path load in combination
> with nologging.
> There are some restrictions so read about it before you use it.
> The effect is that no undo and redo data is generated and rows are inserted direct above the high
> water mark.
> All you have to do:
>
> Alter table aTable nologging;
> Insert/*APPEND*/ into aTable select .... from ....
> Commit;
> Until backup this table cannot be recovered from a media failure.
>
> (Of course the "select ... from..." has to be optimized too)
> Please share your results with us.
>
> "contrapositive" <contrapositive_at_hotmail.com> wrote in message
> news:4e07f6b1.0206051815.52caf7f_at_posting.google.com...
> > Hi again... When doing an INSERT INTO t SELECT * FROM t..., which is
> > fastest? My SELECT potentially needs to link to a mess of tables, but
> > I'd like to do it as efficiently as possible. I'm working with an
> > order_details table and a mess of pricing tables (very poor model, but
> > it's what we're stuck with). Here are what I think my options are:
> >
> > 1. Integrate the pricing tables into the query:
> >
> > INSERT INTO order_details
> > SELECT decode(x.indicator, 1, y.value, 0), ...
> > FROM order_details d,
> > pricing_table_a a,
> > pricing_table_b b,
> > pricing_table_c c, ...
> > WHERE d.item_id = a.item_id
> > AND d.cust_id = a.cust_id
> > AND a... = b...
> > AND (b... = c... OR b... = x...) ...
> >
> > This runs slow and there are other tables involved not shown.
> >
> > 2. Build a view for the pricing tables:
> >
> > INSERT INTO order_details
> > SELECT decode(d.get_price_ind, 1, p.price, 0), ...
> > FROM order_details d,
> > vw_pricing p, ...
> > WHERE d.cust_id = p.cust_id
> > AND d.item_id = p.item_id ...
> >
> > So the pricing mess is in a view. Seems like the view is a lot of
> > overhead; the query that builds the view would need to bring back all
> > item/customer combinations before the query above links to it (or
> > would it?; not real sure how views work in Oracle).
> >
> > 3. Build a function:
> >
> > SELECT decode(d.get_price_ind, 1, get_price(d.item_id, d.cust_id), 0),
> > ...
> > FROM order_details d ...
> > WHERE ...
> >
> > Is the function evaluated for each row or does it consider the decode
> > to see if it even needs to evaluate it? Also, if many rows have the
> > same function parameter values, is the function still evaluated for
> > each? In the function, I just query the pricing tables, but I may be
> > able to optimize things a bit since I have some "breathing room" (i.e.
> > I'm not trying to crunch everything into the query above).
> >
> > 4. Just insert order details one at a time
> >
> > By far the easiest approach, but seems like it would be MUCH slower...
> >
> >
> >
> > Thanks for any help at all... !
> >
> > -jk

Your hint has a syntax error. It should read "INSERT /*+ append */ ...". The way it is written, the hint will be ignored and people will be scratching their heads wondering why it didn't work.

-- 
Ron Reidy
Oracle DBA