Bravo
Mark, November 16, 2001 - 7:52 pm UTC
short, sweet and elegant. very helpful.
Temporary table vs single query
Anne C Murphy, November 19, 2001 - 12:20 pm UTC
This is exactly what I needed and saves hours of work, and wasted time from going down the wrong path.
Thank you so much.
absolute
bala, March 30, 2002 - 12:03 am UTC
crisp,great.........
when to use temp table
Steve, April 23, 2004 - 10:21 am UTC
Hi Tom
In the following case,
update T1
set c1= v1,
c2= v2
where (pk1, pk2) in ( select pk1,pk2
from
( select some cols from T3,
some cols from T4,
some cols from T5,
...
from T3,T4,T5,T6
where t3.c1 = t4.c1
and t3.c2 = t5.c2
and other join condition
)
)
update T2
set c = ( select col1
from (
select some cols from T3,
some cols from T4,
some cols from T5,
...
from T3,T4,T5,T6
where t3.c1 = t4.c1
and t3.c2 = t5.c2
and other join condition
)
)
Should I create temp table and
insert into temp_table
select some cols from T3,
some cols from T4,
some cols from T5
....
from T3,T4,T5,T6
where t3.c1 = t4.c1
and t3.c2 = t5.c2
and other join conditions ......
and then replace the query with the temp table in above update statements. If the query is big, should I use temp table instead of single query from the performance wise and maintainable wise?
Thanks!
Steve
April 23, 2004 - 1:44 pm UTC
hard to say -- as a literal reading of the example would have me believe that the query that is repeated returns but a single row -- so it probably goes really fast.
Sure, if the query takes "a really long time (tm)" to execute, resolving it into a temporary table could have some benefit -- it would have to take some significant amount of time however to offset the extra work you are doing.
Otherwise, no.
temp table to single query
Steve, December 06, 2005 - 5:04 pm UTC
Hi Tom,
For the following temp table usage pattern,
I converted the statements 1,2,3 into 4,
the statement 4 is not efficient. What do you think if I change them into 2 update statements by removing union operator?
1. trunc table work
2.
insert into work (id, new_value)
(
select s.id, b.new_value
from S, B
where s.id = b.id
union
select s.id, c.new_value
from S, C
where s.id = c.id
)
3.
update
(
select s.id,
s.old_value s_old_value,
w.new_value w_new_value
from S, work w
where s.id = w.id
)
set s.old_value = w.new_value
4.
update
(
select s.id,
s.old_value s_old_value,
w.new_value w_new_value
from S,
(select s.id, b.new_value
from S, B
where s.id = b.id
Union
select s.id, c.new_value
from S, C
where s.id = c.id
) W
where s.id = w.id
)
set s.old_value = w.new_value
Thanks!
December 07, 2005 - 1:37 am UTC
are you sure you meant union or did you mean union all.
statement 4 is not "inefficient", it is not executable. What statement am I really comparing to here?
ops$tkyte@ORA10GR2> l
1 update
2 (
3 select s.id,
4 s.old_value s_old_value,
5 w.new_value w_new_value
6 from S,
7 (select s.id, b.new_value
8 from S, B
9 where s.id = b.id
10 Union
11 select s.id, c.new_value
12 from S, C
13 where s.id = c.id
14 ) W
15 where s.id = w.id
16 )
17* set s_old_value = w_new_value
ops$tkyte@ORA10GR2> /
set s_old_value = w_new_value
*
ERROR at line 17:
ORA-01779: cannot modify a column which maps to a non key-preserved table
continue my followup
Steve, December 07, 2005 - 12:15 pm UTC
Sorry Tom!
So I can't convert statment 1,2, and 3
into a single sql. what I can do is to replace
above 1,2,3 into 2 updates
update (
select s.id,
s.old_value s_old_value,
b.new_value b_new_value
from S, B
where s.id = b.id
)
set s_old_value = b_new_value
update (
select s.id,
s.old_value s_old_value,
c.new_value c_new_value
from S, C
where s.id = c.id
)
set s_old_value = c_new_value
I am wondering if you can convert this temp table
usage oattern into single query. If not, what way do you think better? temp table way and 2 Update statements way.
Thanks!
December 08, 2005 - 1:31 am UTC
In 10g, with merge and just an update clause - yes, else not really
you better make darn sure B and C never have the same ID - seems B and C should be just one table in the first place.