Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Which "INSERT INTO" is fastest ?
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:
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
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 ...
So the work is passed to a dedicated function. But 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). Wonder if this really helps.
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:10:35 CDT