Re: Which "INSERT INTO" is fastest ?

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" <contrapositive_at_hotmail.com> wrote in message news:4e07f6b1.0206051815.52caf7f_at_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 Thu Jun 06 2002 - 03:55:12 CDT