Path: news.easynews.com!easynews!cyclone.swbell.net!cyclone-sf.pbi.net!64.245.249.51!sfo2-feed1.news.algx.net!allegiance!logbridge.uoregon.edu!newsfeed.stanford.edu!sn-xit-01!sn-post-01!supernews.com!corp.supernews.com!not-for-mail From: Ron Reidy Newsgroups: comp.databases.oracle.server Subject: Re: Which "INSERT INTO" is fastest ? Date: Thu, 06 Jun 2002 05:00:59 -0600 Organization: Posted via Supernews, http://www.supernews.com Message-ID: <3CFF40EB.D29A165A@indra.com> X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.4.2-2 i686) X-Accept-Language: en MIME-Version: 1.0 References: <4e07f6b1.0206051815.52caf7f@posting.google.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: newsabuse@supernews.com Lines: 83 Xref: easynews comp.databases.oracle.server:149729 X-Received-Date: Thu, 06 Jun 2002 04:59:51 MST (news.easynews.com) 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" wrote in message > news:4e07f6b1.0206051815.52caf7f@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