Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Which "INSERT INTO" is fastest ?

Re: Which "INSERT INTO" is fastest ?

From: Richard Spee <rhpspee_at_wxs.nl>
Date: Thu, 6 Jun 2002 12:00:02 +0200
Message-ID: <adnc8c$570$1@reader12.wxs.nl>


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
Received on Thu Jun 06 2002 - 05:00:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US