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 ...
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