Which "INSERT INTO" is fastest ?

From: contrapositive <contrapositive_at_hotmail.com>
Date: 5 Jun 2002 19:15:38 -0700
Message-ID: <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 Received on Wed Jun 05 2002 - 21:15:38 CDT