Path: news.easynews.com!easynews!priapus.visi.com!news-out.visi.com!hermes.visi.com!newsfeed.esat.net!news.heanet.ie!news.indigo.ie!not-for-mail Reply-To: "Telemachus" From: "Telemachus" Newsgroups: comp.databases.oracle.server References: <4e07f6b1.0206051815.52caf7f@posting.google.com> Subject: Re: Which "INSERT INTO" is fastest ? Lines: 76 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Message-ID: Date: Thu, 6 Jun 2002 09:55:12 +0100 NNTP-Posting-Host: 194.125.187.82 X-Complaints-To: news@indigo.ie X-Trace: news.indigo.ie 1023353712 194.125.187.82 (Thu, 06 Jun 2002 09:55:12 BST) NNTP-Posting-Date: Thu, 06 Jun 2002 09:55:12 BST Organization: Indigo Xref: easynews comp.databases.oracle.server:149713 X-Received-Date: Thu, 06 Jun 2002 03:53:25 MST (news.easynews.com) Views (the normal, non-materialized ones) are just extra SQL. So the (full) SQL will be evaluated anyway. I would only use a function for a relatively deterministic result, so it could be indexed. doesn't look like that below. The main solution for you is to do the explain/trace/tkprof cycle for (1) below. Optimizing that to run as fast as possible looks like the best solution. 4 would be slower (depends on how much time you're willing to wait though) . On a client site 2 weeks ago we got a 3 week runtime down to 10 mins by moving from a (4) to (1) - much the same as your queries. "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