Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Vivek.

Asked: June 08, 2000 - 8:47 am UTC

Last updated: August 26, 2022 - 5:27 am UTC

Version: 7.3

Viewed 50K+ times! This question is

You Asked

Hi Tom
1. I have an Update statement which is taking too long
It has a co-related subquery. How can I optimize it ?
2. This SQL also has a WHERE EXISTS clause, if I remove
this will the columns that fail the join be set to null ?

thanks
Vivek

and Tom said...

It sounds like you are trying to update a table T1 with data from T2 and you are using a query like:

update T1
set c1 = ( select c2 from T2 where T2.key = T1.key )
where exists ( select c2 from T2 where T2.key = T1.key )
/

correct?

If my assumption is correct, then the answer to #2 above is "Yes, if you remove the where exists -- the rows in T1 that have no mate in T2 will be set to NULL".

The way to optimize this, if T2 is relatively small and T1 is large will be to update the join. The reason (probably) this is taking long is because the update above is forced to FULL scan T1 and for every row, run the where exists query and then do it again if it succeeds and then do the update. If the table T1 is large, the full scan will take a while -- as well performing the where exists query for each row.

What you can try is something along these lines:



scott@ORA734.WORLD> create table t1
2 ( x int constraint t1_pk primary key,
3 y int );

scott@ORA734.WORLD> create table t2
2 ( x int constraint t2_pk primary key,
3 y int );

scott@ORA734.WORLD> insert into t1 values ( 1, 1 );
scott@ORA734.WORLD> insert into t1 values ( 2, 1 );
scott@ORA734.WORLD> insert into t1 values ( 3, 1 );

scott@ORA734.WORLD> insert into t2 values ( 2, 2 );
scott@ORA734.WORLD> insert into t2 values ( 3, 2 );

scott@ORA734.WORLD> set autotrace on explain
scott@ORA734.WORLD> update
2 ( select /*+ USE_NL(t1) INDEX( t1 t1_pk ) */
3 t1.y t1_y, t2.y t2_y
4 from t1, t2
5 where t1.x = t2.x )
6 set t1_y = t2_y
7 /

Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=83
1 0 NESTED LOOPS (Cost=83 Card=68 Bytes=3536)
2 1 TABLE ACCESS (FULL) OF 'T2'
3 1 INDEX (UNIQUE SCAN) OF 'T1_PK' (UNIQUE)

scott@ORA734.WORLD> set autotrace off
scott@ORA734.WORLD> select * from t1
2 /

X Y
---------- ----------
1 1
2 2
3 2

I used hints to help the optimizer along -- they may not be necessary if you've alot of rows and have analyzed the tables.

The USE_NL hint says to "nested loops join to T1", and the index hint helps it pick the correct index for doing the join.

To see that the hints might not be necessary:



scott@ORA734.WORLD> insert into t1 select rownum+5, 1
2 from all_objects
3 /

1128 rows created.

scott@ORA734.WORLD> analyze table t1 compute statistics
2 /

Table analyzed.

scott@ORA734.WORLD> analyze table t2 compute statistics
2 /

Table analyzed.

scott@ORA734.WORLD> set autotrace on explain
scott@ORA734.WORLD> update
2 ( select t1.y t1_y, t2.y t2_y
3 from t1, t2
4 where t1.x = t2.x )
5 set t1_y = t2_y
6 /

2 rows updated.


Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS (Cost=3 Card=2 Bytes=38)
2 1 TABLE ACCESS (FULL) OF 'T2'
3 1 INDEX (UNIQUE SCAN) OF 'T1_PK' (UNIQUE)

scott@ORA734.WORLD> set autotrace off

So, by putting a significant number of rows into T1 and letting the optimizer know about them -- it picked the correct plan from the get go.




It should be NOTED that in order for the update of a join to work, T2 in the above example must have a primary or unique key on it. If T2 does not, you'll not be able to update the join. The optimizer needs to know that T1 will be joined to AT MOST 1 row in T2 and the way it does this is by seeing that a primary key or unique constraint exists on the join conditions

Rating

  (129 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very Interesting syntax !!

Tom (another one), August 07, 2001 - 7:44 am UTC

The UPDATE (SELECT... FROM... WHERE...) SET... syntax seems to be very powerful !! I'm sure I can use it instead of nested cursors in my PL/SQL blocks, I'll have to try this.


Update t with values from t1

A reader, October 26, 2001 - 11:54 am UTC

update t
set pbcur_code=
(select distinct a.pbcur_cd
from t1 a, t b
where a.dpi_jq_id in (Select distinct dpi_jq_id
from t
where inty_cd='KLM_AA_SWA'
)
and a.pbcur_cd is not null
and a.dpi_jq_id=b.dpi_jq_id
and t.inty_cd='KLM_AA_SWA')


DESC T
PQ_CODE
dpi_jq_id
MONTH_YEAR
inty_cd

DESC T1
PQ_CODE
dpi_jq_id


t CONTAINS the column called Pbcur_CODE, but we need to populate it from t1 only for those records with inty_cd='KLM_AA_SWA'.

So I select all the dpi_jq_id's with a inty_cd='KLM_AA_SWA', and select the distinct of pbcur_cd for these selected dpi_jq_id's from t1 , and then go update the t table with
Pbcur_CODE WHERE a.dpi_jq_id=b.dpi_jq_id i.e. t1.dpi_jq_id=t.dpi_jq_id

Iam getting the following error

[1]: (Error): ORA-01427: single-row subquery returns more than one row

How should I update table t with pbcur_code, getting the pbcur_code's from t1
where t1.dpi_jq_id=t.dpi_jq_id and t.inty_cd='KLM_AA_SWA'

Thank you




Tom Kyte
October 26, 2001 - 3:08 pm UTC

well, you need to make sure your subquery returns a SINGLE record!

update t
set pbcur_code = ( select pbcur_cd
from t1
where t1.dpi_jq_id = t.dpi_jq_id )
where inty_cd = 'KLM_AA_SWA'
and a.pbcur_id is not null
and exists ( select pbcur_cd
from t1
where t1.dpi_jq_id = t.dpi_jq_id )



Error

Adrian, May 07, 2002 - 1:22 pm UTC

look at this
  1      UPDATE (SELECT a.vdc_saldo_ope saldo,
  2                     c.vec_mon_usa
  3                FROM admnnii.vd_operacioncomex a,
  4                     (SELECT max(vdcpk_cod_ope) oper,
  5                             mecpk_cod_prod,
  6                             vecpk_cod_cli
  7                        FROM admnnii.vd_operacioncomex
  8                      GROUP BY mecpk_cod_prod, vecpk_cod_cli) b,
  9                      admnnii.ve_lin_pro c
 10                WHERE b.vecpk_cod_cli = a.vecpk_cod_cli
 11                  AND b.mecpk_cod_prod = a.mecpk_cod_prod
 12                  AND b.oper = a.vdcpk_cod_ope
 13                  AND c.vecpk_cod_cli = a.vecpk_cod_cli
 14                  AND c.mecpk_cod_prod = a.mecpk_cod_prod
 15              GROUP BY a.vecpk_cod_cli, a.mecpk_cod_prod, c.vec_mon_usa)
 16*        SET vec_mon_usa = saldo
SQL>/
    UPDATE (SELECT a.vdc_saldo_ope saldo,
           *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
seems like materialized view but i don't have any 

Tom Kyte
May 07, 2002 - 2:09 pm UTC

No, it is just saying "you cannot update this VIEW" -- the inline view - -the select is a view.

It has a group by. You cannot update a group by view (an aggregate view).

Optimizing update

Vikas Sharma, March 01, 2003 - 3:18 pm UTC

Hello Tom,

There is any way to optimize the following statment

update T1
set c1 = ( select c2 from T2 where T2.key = T1.key )
where exists ( select c2 from T2 where T2.key = T1.key )

by using hints. Suppose i have a restriction for adding primary keys to tables. But i know my subquery will return one row at a time.

Regards,

Vikas Sharma

Tom Kyte
March 01, 2003 - 3:29 pm UTC

If t2(c2) has a primary key or unique constraint -- sure.

just update the join.

If not, you are limited in what you can do -- but you might find using "IN" to be faster than exists (it'll join -- but you'll still be running that correlated subquery for the set clause).


Also,

create global temporary table temp
( key PRIMARY KEY, c2 );


and then

insert into temp
select t2.key, t2.c2;

update ( select t1.c1 t1_c1, t2.c2 t2_c2
from t1, t2
where t1.key = t2.key )
set t1_c1 = t2_c2;


will work

Or if you have 9i -- look at MERGE, you could use that as well.


Optimizng

Vikas Sharma, March 01, 2003 - 3:48 pm UTC

Hi,

Thanks for lighting reply.

I will test it under my condns. and implement accordingly.
because i have the restriction of creating primary key or unique key.

1. temporary table
2. using IN

Regards,

Vikas Sharma

update with big tables

Sri, March 04, 2003 - 1:20 am UTC

Tom,
I went through your first reply. After reading it I have a question.
I have a table 'A' containing 12 million rows and Table 'B' containing 9 million rows. MY sql update is
update B
Set b_column=some_value
where EXISTS (SELECT 'x' from A where A.Match_id=B.Match_id).

Here Match_id is pk on table B and FK on table A.
Pl. let me know use of EXISTS is better than IN or not.
Also, pl.explain why.
thanks


Tom Kyte
March 04, 2003 - 6:57 am UTC

you will run

where EXISTS (SELECT 'x' from A where A.Match_id=B.Match_id)

9,000,000 times. Now, consider that maybe that subquery takes 0.001 seconds to execute. That is 9000 seconds just to run the subquery over and over and over.

Now, the database -- using the CBO can and will turn that into a semi-join, doing a much more efficient "mass operation" that would be equival to an IN.

the real answer comes from explain plan and the actual performance.

The ORDERED hint processes the tables left to right in the from clause and not right to left

Frank Zhou, March 07, 2003 - 2:05 pm UTC

Hi Tom,

Thanks for sharing your valuable knowledge with all the Oracle developers and DBA. Your answers have solved many people's headache. I am looking forward to read your new book.

Your previous comments about ORDERED Hint is not correct. (***The ORDERED hint processes the tables right to left in the from clause,**** the USE_NL hint says to "nested loops join to T1", and the index hint helps it pick the correct index for doing the join.)

I think the ORDERED hint processes the tables left to right in the from clause and not right to left

Thanks again for your excellent answers!!

Frank


Tom Kyte
March 07, 2003 - 2:55 pm UTC

you are correct, i've corrected that.

Update doubt

Peter, August 05, 2003 - 10:07 am UTC

Hi tom,

1.) One of my senior(he is a database guy but not in oracle) is saying that Update is exactly like delete and insert?. is it true?. Please let me know how oracle is handling. is it must that before updateing the data I should check the record is exist or not?. Because If that row not exist I am simply ignoring one update statement(because according to my senior's statement update is toooo costly). At the same time what are the reverse statements will be created in the undo segment for each update. In sql server If exists(select * from table name) then... is available. This will check the record is exist or not. Is there any better way to do this other than finding count(*) and putting into one variable or using sql%found.

2). In my sp I have written like this
update the record....
if sql%found then return end if;
insert the record....
is it advicable to have different sp for insert and update.

My question may look like very simple. but please reply to me

Thanks in advance


Tom Kyte
August 05, 2003 - 1:11 pm UTC

1) huh? not sure what you are asking. It would seem "funny" to have to ask "are there any rows for me to delete" before deleting would it not?

I mean -- you would just issue "delete" if you wanted to delete some. If it deleted zero rows -- so what? that is not an "error" or anything.

the funny thing is between the time you look to see if there is a row to delete and the time you delete it -- the answer could have changed

so that check seems to be "something designed to eat additional resources for no benefit"

2) if you want to "merge", then you did it right. update, if no records updated insert.

In 9i, you can actually use "merge" instead.

Update doubt

peter, August 06, 2003 - 10:08 am UTC

Hi Tom

Ok Tom. Sorry for the confusion.

Actually I didn't ask for delete statement.
I had asked that before updating row in the table I should check that record is exist in the table or not?.
In my procedure I wrote like this
..
begin
update table_name set x=.....
end;

But my senior is saying that before using update statement you should check the particular record is exist in the table or not (using select statement). He mean to say that if that row does not exist in that table you can avoid that "UPDATE" statement. According to his statement UPDATE statement is too costly (even if it does not update any record). SO
He is asking me to write like this
begin
select count(*) into v_Variable from table_name wherer...
if v_Variable > 0 then
Update table_name set x......
end if;
end;
which way will be the efficient one?.
--------------------------------------------------------
Could you explain how update statement works internally in oracle?
Thanks


Tom Kyte
August 06, 2003 - 10:53 am UTC

AGGG -- he needs to prove that.

It is one of the worst practices out there in general.  ok, lets simulate.  A table T with 1 row identified by primary key (simple update).

Two procedures, one that just updates, the other that "looks" and then updates:

ops$tkyte@ORA920> create table t ( x int primary key, y char(20) );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t values ( 1, 'xxx' );

1 row created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace procedure do_update( p_x in number, p_times in number )
  2  as
  3  begin
  4      for i in 1 .. p_times
  5      loop
  6          update t set y = 'aaa' where x = p_x;
  7          commit;
  8      end loop;
  9  end;
 10  /
Procedure created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace procedure do_select_update( p_x in number, p_times in number )
  2  as
  3      l_cnt number;
  4  begin
  5      for i in 1 .. p_times
  6      loop
  7          select count(*) into l_cnt from t where x = p_x;
  8          if ( l_cnt > 0 )
  9          then
 10              update t set y = 'aaa' where x = p_x;
 11              commit;
 12          end if;
 13      end loop;
 14  end;
 15  /
Procedure created.

ops$tkyte@ORA920>


<b>First, we simulate what happens when about 1/2 of the updates can be "avoided"</b>

ops$tkyte@ORA920> exec runStats_pkg.rs_start

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec do_update( 0, 10000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec do_update( 1, 10000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runStats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec do_select_update( 0, 10000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec do_select_update( 1, 10000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runStats_pkg.rs_stop(1000);
Run1 ran in 1864 hsecs
Run2 ran in 2089 hsecs
run 1 ran in 89.23% of the time

Name                                  Run1        Run2        Diff
STAT...redo size                 5,562,668   5,561,032      -1,636
LATCH.lgwr LWN SCN                   5,846       7,525       1,679
LATCH.mostly latch-free SCN          5,846       7,525       1,679
LATCH.Consistent RBA                 5,845       7,526       1,681
STAT...messages sent                 5,843       7,524       1,681
LATCH.redo allocation               32,244      35,574       3,330
LATCH.redo writing                  17,563      22,604       5,041
LATCH.messages                      29,087      37,471       8,384
LATCH.cache buffers chains         144,056     153,835       9,779
STAT...session logical reads        50,823      60,739       9,916
STAT...consistent gets              20,037      30,013       9,976
STAT...index fetch by key           20,000      30,000      10,000
STAT...consistent gets - exami      20,003      30,008      10,005
LATCH.session idle bit              20,027      10,019     -10,008
STAT...enqueue requests             30,029      20,005     -10,024
STAT...enqueue releases             30,030      20,005     -10,025
STAT...recursive calls              60,075      50,012     -10,063
LATCH.enqueues                      20,132      10,039     -10,093
LATCH.library cache                100,315      90,206     -10,109
LATCH.dml lock allocation           40,002      20,003     -19,999
LATCH.session allocation            40,018      20,002     -20,016
LATCH.enqueue hash chains           60,150      40,045     -20,105
STAT...session pga memory                0    -131,072    -131,072

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
674,042     614,743     -59,299    109.65%

PL/SQL procedure successfully completed.

<b>so the select/update took longer AND it latched more.  A lose lose situation.  Now, what if ALL of the updates can be skipped?</b>

ops$tkyte@ORA920> exec runStats_pkg.rs_start

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec do_update( 0, 10000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runStats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec do_select_update( 0, 10000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runStats_pkg.rs_stop(1000);
Run1 ran in 614 hsecs
Run2 ran in 165 hsecs
run 1 ran in 372.12% of the time

Name                                  Run1        Run2        Diff
STAT...calls to get snapshot s      20,003      10,003     -10,000
LATCH.session idle bit              10,013          12     -10,001
STAT...execute count                20,006      10,005     -10,001
STAT...enqueue requests             10,003           1     -10,002
STAT...enqueue releases             10,004           0     -10,004
LATCH.shared pool                   20,065      10,058     -10,007
LATCH.enqueues                      10,011           0     -10,011
LATCH.dml lock allocation           20,002           0     -20,002
LATCH.session allocation            20,002           0     -20,002
STAT...recursive calls              30,009      10,001     -20,008
LATCH.library cache pin             40,060      20,050     -20,010
LATCH.enqueue hash chains           20,014           0     -20,014
LATCH.library cache                 50,083      20,069     -30,014

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
204,014      63,482    -140,532    321.37%

PL/SQL procedure successfully completed.


<b>Maybe a point to be made there -- but -- tell me, do you believe you'll skip them all really?  What about when NONE of the updates are skipped?</b>


ops$tkyte@ORA920> exec runStats_pkg.rs_start

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec do_update( 1, 10000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runStats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec do_select_update( 1, 10000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runStats_pkg.rs_stop(1000);
Run1 ran in 1497 hsecs
Run2 ran in 2298 hsecs
run 1 ran in 65.14% of the time

Name                                  Run1        Run2        Diff
LATCH.redo allocation               32,420      34,436       2,016
LATCH.redo writing                  17,876      20,865       2,989
STAT...redo size                 5,560,236   5,563,972       3,736
LATCH.messages                      29,538      34,541       5,003
STAT...index fetch by key           10,000      20,000      10,000
STAT...consistent gets - exami      10,004      20,005      10,001
STAT...execute count                20,007      30,016      10,009
STAT...calls to get snapshot s      30,006      40,035      10,029
STAT...consistent gets              10,012      20,050      10,038
LATCH.shared pool                   20,074      30,123      10,049
STAT...recursive calls              30,017      40,089      10,072
STAT...session logical reads        40,740      50,856      10,116
LATCH.cache buffers chains         133,794     144,062      10,268
LATCH.library cache pin             40,076      60,158      20,082
LATCH.library cache                 50,103      70,221      20,118

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
473,547     547,689      74,142     86.46%

PL/SQL procedure successfully completed.

<b>big lose lose there.  So, ask yourself "will I update it more then 1/2 of the times?  if so....</b>



Now, my question to you is -- why are you updating ROWS you didn't select in the first place?  could you be suffering from lost update issues and not even know it?  You might have data integrity issues in the first place....
 

Update takes long time

Tony, August 07, 2003 - 6:28 am UTC

There are two big tables t1 and t2 having 30 million rows each in my pro*c appliction. As I open two cursors (one for t1 and one for t2), bulk fetch and process data, it takes long time to update all rows. To speed up the update, I've joind the two tables to update t1 as below:

update( select t1.y t1_y, t2.y t2_y
from t1, t2
where t1.x = t2.x )
set t1_y = t2_y

This approach is very very slower than the two cursor pro*c approach. Tables are analyzed. I tried bulk fetch and bulk update approach in PL/SQL also. Why is the single update and pl/sql bulk update are slower than pro*c update?
Can you suggest some alternative please?



Tom Kyte
August 09, 2003 - 4:33 pm UTC

not that I don't believe you -- but -- i don't believe you.

show us the TKPROF for the pro*c and the TKPROF for the update. Including all query plans in the tkprof report.

Need to see all of the relevent sql for the pro*c program (select + update)

To Tony

A reader, August 07, 2003 - 2:42 pm UTC

hi Tony
(Tom is on out till weekend)

When you say bulk fetch - do you mean bulk collect?
If so, you should use the "limit" clause to limit
the number of records fetched to 100 or so and go in a
loop..

see </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5918938803188, <code>

Regards
Menon:)

A better, faster non-cursor update?

AJ, November 11, 2003 - 6:00 pm UTC

Tom,

My update is also taking too long on Oracle 9i. I have two tables, table1 and table2. Table1 has 300 records
with an item_id. Table 2 has 1.2 million records with an item_id. The item_id's in table1 are shorter than
the item_id's in table2 but some of the item_id's in table1 may match the first part of the item_id's in table2.
I need to search through table2 for each occurrence of an item_id that exists in table1. If I find a
match then I need to update table2's item_id to the shortened version in table1. Right now I am issueing
300 update statements with PL/SQL code, is there a way to get this into a single update statement and make it
go faster? Currently it takes about 2 hours to complete.

Table1 may have item_id's like 'XYZ123EFG'
Table2 may have item_id's like 'XYZ123EFG789QRS'

If I find an item_id in table2 whose substr matches the entire item_id in table1, then I need to update
the table2 item_id to the shorter table1 item_id. The length of the item_id in table1 may vary from table1 record
to record.

---------------------------------------------

declare

--local variables
l_item_id varchar2(25);
l_sql_str varchar2(200);
l_item_len number;


--cursor for 300 row table
cursor cur1 is
select item_id
from table1;


begin

open cur1;

loop
fetch cur1 into l_item_id;

exit when(cur1%notfound);

l_item_len := length(l_item_id);

l_item_id := trim(l_item_id);

--update for 500K row table
update table2
set item_id = l_item_id
where substr(item_id, 1, l_item_len) = l_item_id;

dbms_output.put_line( sql%rowcount || ' rows updated' );

end loop;

close cur1;

end;
/
---------------------------------------------


Tom Kyte
November 12, 2003 - 6:52 am UTC

so, what happens when table1 has:


Table1 may have item_id's like 'XYZ123'
Table1 may have item_id's like 'XYZ123EF'
Table1 may have item_id's like 'XYZ123EFG'
Table2 may have item_id's like 'XYZ123EFG789QRS'

is that ever "possible"? can a row in table2 have more then one match in table1??

absolutely right

A reader, November 12, 2003 - 11:26 am UTC

Tom,

The "possible" case you mentioned is right on the money. You are really smart! There is more than one instance in table1 where there is data like:

XXXEE888BB2
XXXEE888BB2LA
XXXEE888BB2LAV

Depending on the order of which item_id in table one is used first to update table2, the current code would update some of the table2 item_id's incorrectly. If I created my table1 cursor of item_id's as sorted desc:

XXXEE888BB2LAV
XXXEE888BB2LA
XXXEE888BB2

that would update the table2 item_id's correctly for the first pass thru table2, but then subsequent passes with the similar but shortened item_id's would screw up the records that have already been updated. What can I do? Add a temporary "updated" column in table2 and add it to the where clause in the update?

Thanks in Advance



Tom Kyte
November 12, 2003 - 4:34 pm UTC

why would you not make the data in the table you are updating from UNAMBIGOUS????

agreed

AJ, November 12, 2003 - 4:54 pm UTC

The table1 data is being cleaned up (thanks to you). Would it be possible to now accomplish this update without a cursor, say, using an update with an inline view?

went ahead and tried it

A reader, November 12, 2003 - 7:38 pm UTC

Tom,

It looks like the select that I would use in my in-line Update is not very efficient. Neither item_id column has an index on it.  It is taking about 2 hours to run the trace on the select where table1 has 300 rows and table2 has about 1.2 million.  The 129K rows is the right number of rows in table2 to be updated.

SQL> ed
Wrote file afiedt.buf

  1  select a.item_id c1, b.item_id c2
  2      from table1 a,
  3           table2 b
  4*    where a.item_id = substr(b.item_id,1,length(a.item_id))
SQL> /

129823 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=245252 Card=3755343
          Bytes=123926319)

   1    0   NESTED LOOPS (Cost=245252 Card=3755343 Bytes=123926319)
   2    1     TABLE ACCESS (FULL) OF 'TABLE1' (Cost=2 Card=32
          7 Bytes=3924)

   3    1     TABLE ACCESS (FULL) OF 'TABLE2' (Cost=750 Ca
          rd=11484 Bytes=241164)





Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
    6285342  consistent gets
    6230403  physical reads
          0  redo size
    5229940  bytes sent via SQL*Net to client
    1134177  bytes received via SQL*Net from client
       8656  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     129823  rows processed

SQL>  

Tom Kyte
November 13, 2003 - 6:29 am UTC

do you have hash_join_enabled set to false or something? that should not be nested looping.

hash join enabled...

AJ, November 13, 2003 - 2:58 pm UTC

Tom,

There is no setting in the init.ora for hash_area_size on this instance. Also, I tried alter session set hash_join_enabled = true and the performance was exactly the same as before. The optimizer is not choosing to use a hash join. Is this because there is no hash_area_size parameter in init.ora?

Thanks in advance...

Tom Kyte
November 13, 2003 - 9:17 pm UTC

ahh, i see now, its like a cartesian product -- every row in t1 must be compared to every row in t2

1 select a.item_id c1, b.item_id c2
2 from table1 a,
3 table2 b
4* where a.item_id = substr(b.item_id,1,length(a.item_id))
^^ ^^

no hash join there.

I guess I might drop down to a procedure here


for x in ( select * from small_table )
loop
update big_table set item_id = x.item_id where item_id like x.item_id||'%';
end loop;



Thanks Tom

AJ, November 14, 2003 - 12:23 am UTC

I appreciate your time Tom. I just placed my order on Amazon for Effective Oracle by Design.

Thanks again!

Update using dblinks for million records

Karthik, February 23, 2004 - 1:53 pm UTC

Tom
Thanks very much for the above techique. It is very nice and useful. Using your techique, I am trying to do update using dblink.

My remote table emp_hist have about 6.5M records. Update is going to happen for 3M records. I am using 8.1.7 in Unix.

Following is my sql, which is taking ages to finish job.
Could you please suggest me, how to tune this for better.

UPDATE emp_hist@remotedb t2
SET (yearly_percent,weekly_percent ) =
(SELECT t1.yearly_percent,
t1.weekly_percent
FROM
( select week_no,
fiscal_year,
weekly.emp_no empno,
DECODE(f_52weeks.ordered_dollars, 0, 0,ROUNDf_52weeks.shipped_dollars/f_52weeks.ordered_dollars,3))*100 yearly_percent,
DECODE(weekly.ordered_dollars, 0, 0,ROUND(weekly.shipped_dollars/weekly.ordered_dollars, 3))*100 weekly_percent
from
( SELECT wdes.emp_no,
wdes.week_date,
fnc.week_no week_no,
fnc.fiscal_year fiscal_year,
SUM(wdes.ordered_dollars) ordered_dollars,
SUM(wdes.shipped_dollars) shipped_dollars
FROM weekly_dept_emp_sal wdes,
my_calendar@remotedb fnc
WHERE wdes.week_date = fnc.week_date
AND fnc.fiscal_year IN (2003,2004)
GROUP BY wdes.emp_no,
wdes.week_date,
fnc.week_no,
fnc.fiscal_year
) weekly,
( SELECT wdes.emp_no,
wdes.week_date,
fnc.week_no week_no,
fnc.fiscal_year fiscal_year,
SUM(wdes.ordered_dollars) ordered_dollars,
SUM(wdes.shipped_dollars) shipped_dollars
FROM weekly_dept_emp_sal wdes,
my_calendar@remotedb fnc
WHERE wdes.week_date BETWEEN ( fnc.week_date - 363 ) AND fnc.week_date
AND fnc.fiscal_year IN (2003,2004)
AND wdes.week_date = fnc.week_date
GROUP BY wdes.emp_no,
wdes.week_date,
fnc.week_no,
fnc.fiscal_year
) f_52weeks
WHERE weekly.week_date = f_52weeks.week_date
AND weekly.emp_no = f_52weeks.emp_no
AND fnc.fiscal_year IN (2003,2004)
) t1
WHERE t2.fiscal_year = t1.fiscal_year
AND t2.week_no = t1.week_no
AND t2.emp_no = t1.emp_no
AND t2.fiscal_year in (2003)
)

Thanks very much.

Tom Kyte
February 23, 2004 - 4:54 pm UTC

that query doesn't fly

ops$tkyte@ORA9IR2> create table t1 ( a int, b int, c int );
Table created.
 
ops$tkyte@ORA9IR2> create table t2 ( a int, b int, c int );
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update t2
  2     set ( a,b ) = ( select a,b from t1 ) t1
  3   where t2.c = t1.c
  4  /
   set ( a,b ) = ( select a,b from t1 ) t1
                                        *
ERROR at line 2:
ORA-00933: SQL command not properly ended
 

you cannot join to a subquery like that.

But, assuming that

a) you want to update the remote table
b) row by row running a correlated subquery

You might consider:

a) creating a result set that has the primary key (fiscal_yes, week_no, emp_no)
b) put that into a real table (materialize that result set) on remote
c) on remote, update the join

rather then a correlated subquery 3million times over a dblink.
 

can I do this in single SQL?

A reader, February 26, 2004 - 12:33 pm UTC

Hi

I have this procedure

CURSOR C1 IS
select product, sum(qty) summy
from proc_ff
where flag_act is null
group by product;
CURSOR C2 IS
select sales, sumz
from proc_gg
where product = v_product;

FOR i IN C1 LOOP
v_product := c1.product;
for x in C2 loop
UPDATE proc_gg
set percent = decode(C1.suma, 0, 0,
C2.sumz/C1.summy)
where product = C1.product
and sales = C2.sales; commit;
end loop;
END LOOP;


is it possible to do in single SQL?

Tom Kyte
February 26, 2004 - 2:37 pm UTC

either of these:

merge into t2
using ( select t2.product, t2.sales,
decode( sum(t1.qty), 0, 0, t2.sumz/sum(t1.qty)) pct
from t1, t2
where t1.product = t2.product
and t1.flag_act is null
group by t2.product, t2.sales, t2.sumz ) t
on (t.product = t2.product and t.sales = t2.sales )
when matched then update set percent = t.pct
when not matched then insert (product) values(null);


update t2
set percent = ( select decode( sum(qty), 0, 0, t2.sumz/sum(qty) )
from t1
where t1.product = t2.product
and t1.flag_act is null )
where product in ( select product from t1 )
/


(t1 = proc_ff, t2 = proc_gg)

Never never never commit in a for loop like that!! your transaction is what? the successful update of proc_gg. Not each row of proc_gg, the entire thing.

You commit when and only when YOU ARE DONE.

Updating a table with a join to itself

CJ, March 18, 2004 - 8:51 am UTC

I have a table with a a key that includes "real" keys plus a unique sequence number. Records in this table may get adjustments, so that a new record appears with the same real keys, but a new sequence number. If I wanted to update this new record with some value from the original record, is there any easy way to do it? I can determine the values with a select as follows (rec_type=3 means adjustment):

select t1.key_1,t1.key_2,t1.key_3,t1.seqno,t2.upd_field
from t1, t1 t2
where t1.key_1=t2.key_1
and t1.key_2=t2.key_2
and t1.key_3=t2.key_3
and t2.seqno < t1.seqno
and t2.seqno = (select min(seqno) from t1 t3
where t2.key_1=t3.key_1
and t2.key_2=t3.key_2
and t2.key_3=t3.key_3)
and t1.rec_type='3'
/
Thanks in advance.

Tom Kyte
March 18, 2004 - 9:39 am UTC

same "real keys" ;)  real keys would not be duplicated, but anyway - i understand what you are saying...


ops$tkyte@ORA9IR2> select * from t;
 
        ID         K1         K2  UPD_FIELD   REC_TYPE UFLAG
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          0          0
         2          1          1         -1          3
         3          1          1          0          0
         4          1          1          0          0
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> merge into t t1
  2  using (select *
  3           from (select t.*,
  4                        row_number() over(partition by k1,k2 order by id) rn,
  5                        max(decode(rec_type,3,3)) over (partition by k1,k2) max_rec_type
  6                   from t)
  7          where rn = 1
  8            and max_rec_type = 3) t2
  9     on (t1.k1 = t2.k1 and t1.k2 = t2.k2 and t1.rec_type = 3)
 10   when matched then update set t1.upd_field = t2.upd_field, t1.uflag = '<<==='
 11   when not matched /* never happens */ then insert (id) values(null)
 12  /
 
1 row merged.
 
ops$tkyte@ORA9IR2> select * from t;
 
        ID         K1         K2  UPD_FIELD   REC_TYPE UFLAG
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          0          0
         2          1          1          0          3 <<===
         3          1          1          0          0
         4          1          1          0          0
 
ops$tkyte@ORA9IR2>
 

Re: Updating a table with a join to itself

CJ, March 18, 2004 - 12:23 pm UTC

Yeah, funny about those "real" keys. :-)

Excellent answer, but I'm curious about:
max(decode(rec_type,3,3)) over (partition by k1,k2) max_rec_type

Why the decode?

Thanks


Tom Kyte
March 18, 2004 - 3:09 pm UTC

I wanted to make sure there was a rec_type = 3 in the k1,k2 set of values.

the decode returns NULL for everything but rec_type 3

the max() over (partition by k1,k2) returns the max of the set of decodes for that k1,k2 pair

that way:


3 from (select t.*,
4 row_number() over(partition by k1,k2 order by id) rn,
5 max(decode(rec_type,3,3)) over (partition by k1,k2) max_rec_type
6 from t)
7 where rn = 1
8 and max_rec_type = 3) t2

only returns k1,k2 pairs when there is at least one rec_type = 3 so I could make the following claim:

..
10 when matched then update set t1.upd_field = t2.upd_field, t1.uflag =
'<<==='
11 when not matched /* never happens */ then insert (id) values(null)
12 /



update random (Oracle Rel 9.2.0.3.0)

A reader, April 07, 2004 - 1:36 pm UTC

Hi Tom,
We have a situation where we want to update a column with unique random numbers against an already populated key column. Lets say.....

TABLE A
key random
------ ------
000001 198749
000002 293733
000003 387386
000004 498398

Just wanted to know if there's a way to achieve this in a single update? as, currently we do a series of steps to achive this.

Thanks

Tom Kyte
April 08, 2004 - 9:12 am UTC

you have an oxymoron there.

"unique random"

such a thing doesn't exist.

you can have unique.
you can have random.

it is quite simply "not possible" to have a unique random by defintion.


but -- what you seem to want is non-predicable primary keys -- sort of like the numbers above in the URL (my displayid). for that I use:

to_number(
to_char(wwv_seq.nextval) ||
lpad( substr( abs(wwv_flow_random.rand), 1, 5 ),5, '0' ) ||
ltrim(to_char(mod(abs(hsecs),1000000),'000000')))


so, a sequence plus a fixed width random number plus hsecs (gotten from v$timer or dbms_utility.get_time)





update an entire column against already populated column values

A reader, April 08, 2004 - 12:24 pm UTC

Hi Tom,
Thanks very much for your reply. It indeed helped!

Just another thing I wanted to know was how to update the entire random column in TABLE A against the already populated KEY value(now that I have random numbers generated). I mean what reference could be used (e.g. could rownum be used) to update the entire random column.

Hope I make it clear? Let me know otherwise and I explain with an example.

Thanks again!

Tom Kyte
April 08, 2004 - 3:35 pm UTC

ops$tkyte@ORA9IR2> create sequence s;
 
Sequence created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update t set y =
  2  to_number(
  3        to_char(s.nextval) ||
  4                  to_char( abs(trunc(dbms_random.value(0,99999))), 'fm00000' ) ||
  5                                ltrim(to_char(mod(abs(dbms_utility.get_time),1000000),'000000')))
  6  /
 
49 rows updated.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;
 
                 X                  Y
------------------ ------------------
                 0       195179109742
                 5       234251109742
                11       343400109742
                19       463293109742
.....

would be the easiest way to do it -- not sure what you mean by "i have random numbers generated", if you generated and inserted them somewhere -- why didn't you just generate and update row by row in the first place if you were going through all of that work)
 

Re: Updating a table with a join to itself (again)

CJ, April 21, 2004 - 8:08 am UTC

Regarding updating the adjustments above, is there an easy way to code this in a trigger without getting the mutating error? That is, is there a way to update the fields on the adjustments as they are inserted rather than after the fact?

Thanks,
CJ

RE: Re: Updating a table with a join to itself (again)

CJ, April 21, 2004 - 9:41 am UTC

I've coded the trigger, and it is working except for adjustments which are for rows inserted in the same batch as the adjustments. Is there a way to fix this?

Thanks,
CJ

never mind

CJ, April 21, 2004 - 10:34 am UTC

I've got it working.

CJ

what do you think of this query.....any thoughts

lou, June 28, 2004 - 7:33 pm UTC

INSERT INTO metadata.error_log
SELECT 64249175, SYSDATE,
'RDSC2-20050-SURQTR, PGMNR, TCODE DO NOT EXIST IN PAY, PRROGAM_DETAIL OR QUARTERLY_DETAIL',
NULL, 'RDSC2_PGMDTL_QTRDTL_PAY_CHECK', a.row_id, 'PROGRAM_DETAIL'
FROM program_detail a
WHERE status IN ('E', 'D')
AND NOT EXISTS (
SELECT 'X'
FROM dreuser.quarterly_detail b
WHERE a.surqtr = b.surqtr
AND a.pgmnr = b.pgmnr
AND a.ttlcode = b.ttlcde)
OR NOT EXISTS (
SELECT 'X'
FROM dreuser.pay c
WHERE a.surqtr = c.surqtr
AND a.pgmnr = c.distprognbr
AND a.ttlcode = c.ttlcde)






Tom Kyte
June 28, 2004 - 7:54 pm UTC

i'm always scared of AND's and OR's living together without ()'s

I *never* remember if

x and y or z

is

(x and y) or z

or

x and (y or z)

myself....

as long as you are using the CBO, the not exists will be rewritten to NOT IN if that is better -- so it is OK.

if you are using the RBO, the not exists will be run as an index probe for sure, assuming an index on the columns, so just hope the outer table is not "big"

Thanks for your comments!

A reader, June 29, 2004 - 9:34 am UTC


getting errors when run query

Ryan, August 02, 2004 - 10:30 am UTC

The table being updated is a child table, so has more than one row for every row in
the parent table. Anyway to do this?

*
update child_table
set fk_value = (select parent.fk_value
from parent
where parent.key = child_table.key)
where exists (select parent.fk_value
from parent
where parent.key = child_table.key
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row


Tom Kyte
August 02, 2004 - 10:59 am UTC

that update doesn't make sense.

the fk_value in child is what you use to join to parent. if it needs to be updated, it must be "wrong" -- meaning it cannot be used to join to parent.

what are you really trying to do here? this as written "does not compute" you do not join a child table to a parent table by the childs primary key as you show.

that and "where parent.key = <a value>" would always return a single row -- no? I mean, it is after all the parents primary key right?


so, give me a real "for example" -- create tables, inserts, and a demonstration of the error.

Different plan for your example

Reader, August 04, 2004 - 5:26 am UTC

Hi Tom,

I used your example to understand the discussion but I am getting a different plan to the same query that you used. In your case the plan shows nested loop access to table T1 via the unique index only and not subsequent access by 'TABLE BY ROWID'.

I tried this on 8.1.7 and 9.2 database and still the same result.

So,why are the plans different ?

SQL> drop table t1;

Table dropped.

SQL> create table t1(x int constraint t1_pk primary key, y int);

Table created.

SQL> create table t2(x int constraint t2_pk primary key, y int);        

Table created.

SQL> insert into t1 values(1,1);

1 row created.

SQL> insert into t1 values(2,1);

1 row created.

SQL> insert into t1 values(3,1);

1 row created.

SQL> insert into t2 values(2,2);

1 row created.

SQL> insert into t2 values(3,2);

1 row created.

SQL> set autotrace on explain
SQL> update 
  2  ( select /*+ USE_NL(t1) INDEX(t1 t1_pk) */
  3    t1.y t1_y,t2.y t2_y
  4    from t1,t2
  5    where t1.x = t2.x
  6  )
  7  set t1_y = t2_y;

2 rows updated.


Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=83 Card=67 Bytes=348
          4)

   1    0   UPDATE OF 'T1'
   2    1     NESTED LOOPS (Cost=83 Card=67 Bytes=3484)
   3    2       TABLE ACCESS (FULL) OF 'T2' (Cost=1 Card=82 Bytes=2132)
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1 Card=82  Bytes=2132)  <<=======================
   5    4         INDEX (UNIQUE SCAN) OF 'T1_PK' (UNIQUE)

SQL> set autotrace off
SQL> select * from t1;

         X          Y
---------- ----------
         1          1
         2          2
         3          2 

Tom Kyte
August 04, 2004 - 10:32 am UTC

it would have been an issue with the 7.3 explain plan. that table needed to be accessed in order to do the update of that row.

there was an implied "go to table" in the "update of 'T1'" step that 7.3 did not expose in the explain plan.

How to do this in efficient way?

A reader, August 07, 2004 - 3:52 am UTC

Hi Tom,
Could you please advice for doing this update in best possible way:
Update eva for each client in state and having rtype as below
eva = ((tamnt for a client in state for a rtype/avg(for all clients in a state and for a rtype))*aeva for each client in a state and for a rtype)*pcount for each client and rtype
The script as as below:
CREATE TABLE "TARUN"."TEST"
( "SNAPSHOT_KEY" NUMBER(6,0) NOT NULL ENABLE,
"CLIENT" NUMBER(10,0) NOT NULL ENABLE,
"STATE" VARCHAR2(2) NOT NULL ENABLE,
"RTYPE" VARCHAR2(4) NOT NULL ENABLE,
"YOR" NUMBER(2,0) NOT NULL ENABLE,
"PCOUNT" NUMBER(2,0) NOT NULL ENABLE,
"PAMNT" NUMBER(15,2) NOT NULL ENABLE,
"AEVA" NUMBER(15,4),
"TIMESTAMP_RECORD_CREATED" TIMESTAMP (6) NOT NULL ENABLE,
"EVA" NUMBER(15,4)
)
;
insert into test
values ('200407',1,'02','CR',2,2,500,100,sysdate,0)
/
insert into test
values ('200407',1,'04','CR',2,3,1200,200,sysdate,0)
/
insert into test
values ('200407',1,'05','CO',2,1,300,500,sysdate,0)
/
insert into test
values ('200407',2,'02','CR',2,1,400,200,sysdate,0)
/
insert into test
values ('200407',2,'02','CO',2,1,100,300,sysdate,0)
/
insert into test
values ('200407',3,'06','BL',2,2,300,500,sysdate,0)
/
insert into test
values ('200407',3,'07','CO',2,1,100,400,sysdate,0)
/
COMMIT;
Fo example eva for client 1 state 02 and rtype CR should be
(500/450)*100*2 = 400
and for client 2 with state 02 and rtype CR should be
(400/450)200*1
etc..
The table containing about 20Million rows.
Regards


Tom Kyte
August 07, 2004 - 10:27 am UTC

((tamnt for a client in state for a rtype/avg(for all clients in a state
and for a rtype))*aeva for each client in a state and for a rtype)*pcount for
each client and rtype

didn't really follow that.

tamnt for a client in state for a rtype -- really means "tamnt for current row" i presume (eg: in state for a rtype isn't meaningful really?).

avg(for all clients in a state/rtype) -- really means avg(tamnt) over (partition by state, rtype) -- the avg tamnt (guessing) by state, rtype


aeva for each client in a state and for a rtype -- is like tamnt, really means current row

and same with pcount.


but unfortunately, i don't see tamnt in there anywhere? if tamnt doesn't exist to begin with, getting the avg(tamnt) isn't possible.

Sorry for the mistakes in above question

A reader, August 07, 2004 - 7:00 am UTC

Hi Tom,
tamnt should be pamnt
and
(500/450)*100*2
(pamnt for client 1 state 02, rtype CR/average of pamnts for all clients in state 02, rtype CR i.e., 500 (client 1)+400 (client 2 same state and rtype) /2=450*aeva for client 1 *pcount for client 1 in state 02 and rtype CR
--->222.2222
Regards

Tom Kyte
August 07, 2004 - 10:31 am UTC

ops$tkyte@ORA9IR2> select client, state, rtype, pamnt, aeva, pcount,
  2         pamnt/(avg(pamnt) over (partition by state, rtype))*aeva*pcount new_val
  3    from test;
 
    CLIENT ST RTYP      PAMNT       AEVA     PCOUNT    NEW_VAL
---------- -- ---- ---------- ---------- ---------- ----------
         2 02 CO          100        300          1        300
         1 02 CR          500        100          2 222.222222
         2 02 CR          400        200          1 177.777778
         1 04 CR         1200        200          3        600
         1 05 CO          300        500          1        500
         3 06 BL          300        500          2       1000
         3 07 CO          100        400          1        400
 
7 rows selected.
 


why the motivation "to update this data that immediately becomes wrong after the very first insert/delete or update"?


why not compute it upon retrieval so it is correct? 

Thanks a lot!

A reader, August 08, 2004 - 12:28 am UTC

Hi Tom,
Thanks a lot for your help. Actually, after posting a question, I got idea from your first book (Chapter Analytic fuctions). Your this on-line help and both books are really great. I always try to do things in best possible way, and when I am stuck your timely help gives me lots of hopes.
With Regards,
btw: You are right, I can do this while inserting data. I was just avoiding becuase the insert itself is very complex and is based upon complex business rules , which area handled dynamically to create the aeva. Just out of curiosity, Can I do through update?

Tom Kyte
August 08, 2004 - 9:47 am UTC

the update would either be:

a) a merge to do the update
b) a two step -- insert into a global temporary table and then update the join of the gtt with the real data.

merge into test
using (
select rowid rid,
pamnt/(avg(pamnt) over (partition by state, rtype))*aeva*pcount new_val
from test ) t2
on (test.rowid = t2.rid)
when matched then update set whatever = new_val
when not matched then insert (pamnt) values (null) <<=== never happens tho, all
rows in t2 are in test

Steps of update

Saheli, September 08, 2004 - 3:38 am UTC

I would really appreciate if you can describe the actual steps and what happens in the background (internally) whenever an update or delete is issued.

Tom Kyte
September 08, 2004 - 9:43 am UTC

do you have access to my book "Expert One on One Oracle" -- i covered that topic in large detail -- stating what happens with redo, rollback, crashes, buffer caches, etc.

Can you please help me with this update

A reader, September 14, 2004 - 5:17 pm UTC

UPDATE member_pseudonym a
SET psdocae = b.CAE_NR
FROM caematch_cae b,
stg_match_cae c
WHERE c.src_ind = 'V'
AND c.MBR_SOC_CDE <> '010'
AND b.CAE_GRP_NR = c.cae_grp_nr
AND a.icode = c.mbr_grp_icode
AND c.MBR_GRP_ICODE < 50
AND b.name_type = 'PA'
AND c.MBR_GRP_ICODE = a.icode
AND a.ID = 1

Tom Kyte
September 14, 2004 - 7:10 pm UTC

lets see -- seeing as how this page shows

a) how to do that via correlated subqueries
b) how to do that via updating a join

hmmm....? what needs be done past that? i mean, it sort of appears that the technique you need to UNDERSTAND (since you will be maintaining this code, enhancing this code) is right here.

(guess I'm saying, I'd feel better if you tried first -- it will be your code to own after all, your code to debug, best if you understand..)

last_ddl_time

A reader, September 15, 2004 - 2:41 pm UTC

Tom,

does an update statement on a table change its last_ddl_time?

Tom Kyte
September 15, 2004 - 2:44 pm UTC

update is DML.

no, it won't.

for update in a cursor

Thiru, September 15, 2004 - 6:37 pm UTC

Will the following in effect lock the table during the processing of the transaction?

create procedure p1
as
cursor my_cur is select * from emp for update;
d_mycur my_cur%rowtype;
begin
open my_cur;
update emp set amt=amt+commn where empid between 100 and 200;
close my_cur;
end;
/
Is there a better way of locking the rows?

Tom Kyte
September 15, 2004 - 9:08 pm UTC


why would you select * from emp for update -- to update only at most 100 records?

this procedure would be nothing more than:


begin
update emp set amt = amt+comm where empid between 100 and 200;
end;


i don't understand, given this amount of context, why you would select * from emp for update.

A reader, September 16, 2004 - 10:40 am UTC

The context in which I was asking is this:
I will be either inserting a new record or updating the record if it already exists.
I do not want a new insert generated while a similar insert is going on. The second insert should be an update. This could happen when there are simultaneous inserts coming in with the same conditions.

case:
Field values:

'U','09-SEP-01','R-1',1000

So my proc will check if the data exists with the info from the first 3 columns. If yes then update else insert.

create procedure p1
as
LD_CNT NUMBER;
begin
select count(*) INTO LD_CNT from my_tbl where c1='U' AND C2='09-SEP-01' AND C3='R-1';


IF LD_CNT=0
THEN
INSERT INTO MY_TBL VALUES('U','09-SEP-04','R-1',1000);
ELSE
UPDATE MY_TBL SET AMT=AMT+1000 WHERE c1='U' AND C2='09-SEP-01' AND C3='R-1';
END IF;
END;
/

So when this record is being inserted, then a second record that's come in with similar condition should not insert but update. So I thought of using the "for update" clause through a cursor. Is there a better way of doing this?

I tried to do this "for update" in the body but gives an error "not enough values".

create procedure p1
as
LD_CNT NUMBER;
LD_LOCK MY_TBL%ROWTYPE;
begin

select * into ld_lock from my_tbl for update;

select count(*) INTO LD_CNT from my_tbl where c1='U' AND C2='09-SEP-01' AND C3='R-1';

IF LD_CNT=0
THEN
INSERT INTO MY_TBL VALUES('U','09-SEP-04','R-1',1000);
ELSE
UPDATE MY_TBL SET AMT=AMT+1000 WHERE c1='U' AND C2='09-SEP-01' AND C3='R-1';
END IF;
END;
/




Tom Kyte
September 16, 2004 - 10:45 am UTC

do you have proper primary keys/unique constraints in place?

if so, SKIP the select count(*)

just insert, on error, update.

A reader, September 16, 2004 - 11:08 am UTC

Thanks. That should do what I expected.



Rare Plans

A reader, November 01, 2004 - 1:36 pm UTC

Tom, do you see any reason of the behaivor under 8.1.7 with your same escenario, but gathering statistics with DBMS_STATS instead ? .. The optimizer is HASH JOINING both tables in FULL ACCESS, in my humble case T2 has all the entries T1 might have plus more (but not by much -- indeed, I think the FULL ACCESS approach is the right one, but I dont understand the timing). If I hint the update with /*+ rule */ it takes the NESTED LOOPS approach and uses the index on T2, that takes about half of the time (15 seconds with NESTED LOOPS between 33 seconds or so with HASH JOIN). Also, t1 key in my case is a foreign key (not primary -- but ... would that even matter?). Can you please ellaborate about WHY is this behaivor ?

Thank you!

Tom Kyte
November 02, 2004 - 6:15 am UTC

give me an example to work with.

Here are they..

A reader, November 02, 2004 - 9:50 am UTC

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE    8.1.7.0.0       Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

SQL> create table t1 as select 'A' name, trunc( 1 + dbms_random.value * 150000 ) t2_id from pieces;

Table created.

SQL> create table t2 as select rownum id, 'B' name from pieces where rownum <= 150000;

Table created.

SQL> select count(*) from t1;

  COUNT(*)
----------
    267318

SQL> alter table t2 add constraint t2_pk primary key ( id );

Table altered.

SQL> alter table t1 add constraint t1_fk foreign key ( t2_id ) references t2;

Table altered.

SQL> exec dbms_stats.gather_table_stats('xxx','T1',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('xxx','T2',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> set timing on autotrace traceonly explain
SQL> update ( select t1.name t1_name, t2.name t2_name
  2             from t1, t2
  3            where t1.t2_id = t2.id )
  4     set t1_name = t2_name
  5  /

267318 rows updated.

Elapsed: 00:00:10.02

Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=225 Card=267318 Bytes=3207816)
   1    0   UPDATE OF 'T1'
   2    1     HASH JOIN (Cost=225 Card=267318 Bytes=3207816)
   3    2       TABLE ACCESS (FULL) OF 'T2' (Cost=16 Card=150000 Bytes=900000)
   4    2       TABLE ACCESS (FULL) OF 'T1' (Cost=27 Card=267318 Bytes=1603908)

SQL> update ( select /*+ rule */ t1.name t1_name, t2.name t2_name
  2             from t1, t2
  3            where t1.t2_id = t2.id )
  4     set t1_name = t2_name
  5  /

267318 rows updated.

Elapsed: 00:00:08.08

Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=HINT: RULE
   1    0   UPDATE OF 'T1'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'T1'
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'T2'
   5    4         INDEX (UNIQUE SCAN) OF 'T2_PK' (UNIQUE) 

Tom Kyte
November 02, 2004 - 10:41 am UTC

ops$tkyte@ORA9IR2> /*
DOC>drop table t1;
DOC>drop table t2;
DOC>create table t1
DOC>as
DOC>select 'A' name, trunc( 1 + dbms_random.value * 150000 ) t2_id
DOC>from big_table.big_table where rownum <= 267318;
DOC>
DOC>create table t2 as select rownum id, 'B' name from big_table.big_table where rownum <= 150000;
DOC>
DOC>alter table t2 add constraint t2_pk primary key ( id );
DOC>alter table t1 add constraint t1_fk foreign key ( t2_id ) references t2;
DOC>exec dbms_stats.gather_table_stats(user,'T1',cascade=>TRUE);
DOC>exec dbms_stats.gather_table_stats(user,'T2',cascade=>TRUE);
DOC>*/
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set timing on autotrace traceonly
ops$tkyte@ORA9IR2> update ( select /*+ rule */ t1.name t1_name, t2.name t2_name
  2             from t1, t2
  3            where t1.t2_id = t2.id )
  4     set t1_name = t2_name
  5  /
 
267318 rows updated.
 
Elapsed: 00:00:07.58
 
Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=HINT: RULE
   1    0   UPDATE OF 'T1'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'T1'
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'T2'
   5    4         INDEX (UNIQUE SCAN) OF 'T2_PK' (UNIQUE)
 
 
 
 
Statistics
----------------------------------------------------------
        449  recursive calls
     273568  db block gets
     535186  consistent gets
       1013  physical reads
   65431292  redo size
        781  bytes sent via SQL*Net to client
        912  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
     267318  rows processed
 
ops$tkyte@ORA9IR2> update ( select t1.name t1_name, t2.name t2_name
  2             from t1, t2
  3            where t1.t2_id = t2.id )
  4     set t1_name = t2_name
  5  /
 
267318 rows updated.
 
Elapsed: 00:00:05.37
 
Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=223 Card=267318 Bytes=3207816)
   1    0   UPDATE OF 'T1'
   2    1     HASH JOIN (Cost=223 Card=267318 Bytes=3207816)
   3    2       TABLE ACCESS (FULL) OF 'T2' (Cost=39 Card=150000 Bytes=900000)
   4    2       TABLE ACCESS (FULL) OF 'T1' (Cost=69 Card=267318 Bytes=1603908)
 
 
 
 
Statistics
----------------------------------------------------------
        128  recursive calls
     273564  db block gets
        725  consistent gets
          3  physical reads
   65413192  redo size
        787  bytes sent via SQL*Net to client
        900  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
     267318  rows processed
 
ops$tkyte@ORA9IR2> set autotrace off


<b>I see the opposite.  suggestion -- run multiple times, use 10046 level 12 tracing, see what the waits are.  could have been "check point not complete", log file sync, one did more IO than the other -- etc

and frankly, a wall clock difference of less than 2 seconds?  not significant.  could be your network, could be a billion things.</b>

 

Not the same version..

A reader, November 02, 2004 - 11:38 am UTC

Sorry, but I see you used 9iR2 to reproduce, the version I used was 8i (thus why I posted the banner). Could you try it on there ? .. It is not the first time I note this kind of things on 8i, where a plan seems to be the "adecuate" but the timings does not compute very well.

Tom Kyte
November 03, 2004 - 5:32 am UTC

no, you do it with tkprof.

in 9i -- one time I run it, hash better than nl, next time nl better than hash.

we are talking 2 seconds here -- 2 seconds on the wall clock

with the amount of work being done, the fact that disks make us wait sometimes longer than others, the fact that the hash join buffered the data for the nl join, etc etc etc


I see no "spectacular difference" here -- nothing "wrong", nothing actually surprising.

did you see that MY plans were the same as YOUR plans?

Funky dilemma with Update Statement

denni50, December 02, 2004 - 10:29 am UTC

Hi Tom

I have an update statement that runs correctly when run
independently but doesn't work when it is called via
sqlplus with a @filename.sql using decode function.

In my test run I took 3 prospects for testing.
Two with a salutcode='000' that updates greeting with
First and Last name if First is not null. If First
is null then update greeting with 'Friend'.

It works if I run the update statement directly
but doesn't work if I use the @greetings.sql

I'm kind of stumped??...if you can shed some light
on this that would be great.....thanks!

****Update Statement run from within @greetings.sql*************************/
update prospect
set greeting=decode(first,null,'Friend',first||' '||last)
where recordtype='I' and salutcode='000' and idnumber in(41490,60828,5164416);
/
commit;

2 rows updated.


Commit complete.


SQL> select salutcode,greeting,first,last from prospect
  2  where idnumber in(41490,60828,5164416);

SAL GREETING             FIRST           LAST
--- -------------------- --------------- --------------------
MM  Mr. & Mrs. Reese     James           Reese
000 Calott                               Calott
000 Uigaese              Emmanuel        Uigaese


*****Update Statement run outside .sql file**********************************

SQL> update prospect
  2  set greeting=decode(first,null,'Friend',first||' '||last)
  3  where recordtype='I' and salutcode='000' and idnumber in(41490,60828,5164416);

2 rows updated.

SQL> commit;

Commit complete.

SQL>  select salutcode,greeting,first,last from prospect
  2   where idnumber in(41490,60828,5164416);

SAL GREETING             FIRST           LAST
--- -------------------- --------------- --------------------
MM  Mr. & Mrs. Reese     James           Reese
000 Friend                               Calott
000 Emmanuel Uigaese     Emmanuel        Uigaese

 

Never mind Tom!

denni50, December 02, 2004 - 11:15 am UTC

sorry....found the culprit...I running another
statement right after that one with the decode that
overwrote the decode update statement....geeeezzzz!!!



How about merging a self joining table?

Scott, December 24, 2004 - 3:50 pm UTC

On a 10g database there is a table containing a parent/child self joining relationship:

create table t
(record_id numeric not null
,description varchar2(100) not null
,parent_id numeric
,constraint t_pk
primary key (record_id)
,constraint t_uk
unique (description)
,constraint t_fk
foreign key (parent_id)
references t(record_id)
)

create sequence record_id_seq;

we need to do a merge from a table containing child and parent descriptions (sourced from a log file supplied from another system)...

create table s
(description varchar2(100) not null
,parent_description varchar2(100))

insert into s (description) values ('record1');
insert into s values ('record2', 'record1');
insert into s values ('record3', 'record1');
insert into s values ('record4', 'record2');
insert into s (description) values ('record5');
insert into s values ('record6', 'record4');
insert into s values ('record7', 'record3');

Which essentially represents a structure resembling the following:
* record 1
* * record 2
* * * record 4
* * * * record 6
* * record 3
* * * record 7
* record 5

What would be the best/most efficient method to insert the contents of table s in to table t where:
- t.record_id is populated from the sequence record_is_seq
- t.description is populated from s.description
- t.parent_id is the record_id from the record in t matching the s.parent_description

Table s may contain mappings that differ to the existing contents of t, so we want to update those existing records that are different and insert those new records. There is no defined limit to the depth of the hierarchy. It also may contain potentially millions of rows.

I'm thinking a merge should be able to do this, but am having trouble getting my head around the recursive nature of the requirement.

Tom Kyte
December 24, 2004 - 4:06 pm UTC

I'd add to S the record_id column....

update S set record_id = that_sequence.nextval


then a simple insert into will do it (self join of S to get the child record id and the parent record id.

You need to assign the records ids before you could look them up -- chicken and egg more than a recursive problem.

Not so much...

Scott, December 24, 2004 - 4:30 pm UTC

The table s is actually an external table based on a feed from an external system (that doesn't have id values in it unfortunately).

Our table t contains quite a few records (millions) that already have record_id's assigned, and parent/child relationships established.

Each day we get a new file from the source system (so a new s) that we need to apply to t. Some of t will stay the same, some of t's parent/child relationships will change, and some new records from s will get inserted in to t.

Are you suggest that I map s against the existing t to find the record_id's where they match first, and then do an update where they do followed by an update where they don't?



Tom Kyte
December 24, 2004 - 9:46 pm UTC

in order to get the chicken egg problem solved, you'll need to load that S table into a table, update the record id with the sequence.

It is quite impossible for you to "lookup" the parents record id before it is assigned.

load the external table, give every record an ID -- that ID might be a pre-existing one or a new one, but they need an ID first, then you can load the relations by ID.

clarification...

Scott, December 24, 2004 - 4:32 pm UTC

> Are you suggest that I map s against the existing t to
> find the record_id's where they match first, and then do
> an update where they do followed by an update where they > don't?

That should read an update where the do match followed by an INSERT where they don't.

Restricted privilegs (SELECT only) on the second table

Ralf, December 28, 2004 - 6:19 am UTC

Hi Tom,

today I ran into the same problem Vivek had and tried your idea to update the join. But as an additional restriction my table T2 is read only.
When I run the UPDATE with the join as an inline view i get an ORA-01031 "insufficient privilegs".
I update only columns of T1.
But when I first create a view containing the join and then update this view, the update succeeds.
Is it possible to use the first statement with the inline view, perhaps with some additional keywords or hints?
Or do i have to create a view?


Tom Kyte
December 28, 2004 - 10:40 am UTC

you should be able to use the correlated subquery approach without the view.


ops$tkyte@ORA9IR2> create table t1 ( x int primary key, y int );
 
Table created.
 
ops$tkyte@ORA9IR2> create table t2 ( x int primary key, y int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> grant all on t1 to a;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2> grant select on t2 to a;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect a/a
ops$tkyte@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2>
a@ORA9IR2> update ( select t1.y t1_y, t2.y t2_y
  2             from ops$tkyte.t1 t1, ops$tkyte.t2 t2
  3                    where t1.x = t2.x )
  4    set t1_y = t2_y;
           from ops$tkyte.t1 t1, ops$tkyte.t2 t2
                                           *
ERROR at line 2:
ORA-01031: insufficient privileges
 
 
a@ORA9IR2>
a@ORA9IR2>
a@ORA9IR2> update ops$tkyte.t1
  2     set y = (select y from ops$tkyte.t2 where t2.x = t1.x)
  3   where exists (select y from ops$tkyte.t2 where t2.x = t1.x)
  4  /
 
0 rows updated.
 

SQL tuning

sudhir, January 18, 2005 - 5:02 am UTC

reading this!! no clear cu response!! i too am facing the same issue.. this is wht i am facing
can anyone help??

UPDATE mtl_material_transactions mtl
SET attribute10='20040729051542'
WHERE attribute10 is null
And exists (select inv_org_id from sminv_organization_mapping som where oper_unit=106 And som.inv_org_id=mtl.organization_id);

Tom Kyte
January 18, 2005 - 10:02 am UTC

umm, facing WHAT precisely. "same issue as WHAT"


for you see, this page was about updating a table with values in another table. You are not doing that.


if this is about performance

a) make sure you are using the CBO.
b) consider using "IN" if you are not using the CBO.

update view

A reader, January 21, 2005 - 7:31 am UTC

hi

you have this update

update ( select t1.c1 t1_c1, t2.c2 t2_c2
from t1, t2
where t1.key = t2.key )
set t1_c1 = t2_c2;

I have never seen this before even in the docs, how does this work? In this case how does Oracle know which table do you want to update? It can be either t1 or t2 no?


Tom Kyte
January 21, 2005 - 8:46 am UTC

sure you have.. (seen it in the docs)

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state27a.htm#2067717 <code>

update DML_Table_expression_clause

where DML_table_expression_clause is a table/view/partition/SUBQUERY/table() expression.....



the table you want to update is somewhat self evident -- t1_c1 = t2_c2. t1_c1 is being set, it is in table t1.

yes, it could be either t1 or t2, but in this case -- it is only t1.

view´s base table

A reader, January 22, 2005 - 3:41 am UTC

In the documentation it suggests that when we run update DML_CLAUSE then only the base table is updated, if the view is an inline view which table would be the base table?

You suggested that the update above can update either t1 or t2 but since t1 appears first in where clause in the update so we are updating t1. Is it possible to update t1 and t2 at same time? (not sure if it makes sense but curious)

Tom Kyte
January 22, 2005 - 9:57 am UTC

No, it is all about what you reference in the SET clause -- that is what makes it obvious which table is being updated.  the order of things in the select portion bears no relationship to what is being updated or what is updateable.

You can only update one of the tables.


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t1 ( x int primary key, y int, z int );
 
Table created.
 
ops$tkyte@ORA9IR2> create table t2 ( a int primary key, b int, c int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update ( select *
  2             from t1, t2
  3                    where t1.x = t2.a )
  4    set y = b;
 
0 rows updated.

<b>update t1 from t2...</b>

 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update ( select *
  2             from t1, t2
  3                    where t1.x = t2.a )
  4    set b = y;
 
0 rows updated.

<b>update t2 from t1....</b>
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update ( select *
  2             from t1, t2
  3                    where t1.x = t2.a )
  4    set b = y,
  5        z = c;
      z = c
      *
ERROR at line 5:
ORA-01776: cannot modify more than one base table through a join view
 
<b>but not both...</b>
 

can i use this update (select ) in my case?

A reader, January 26, 2005 - 8:12 am UTC

Hi I have these two tables

SQL>select * from anunc;

ID_CONTRACT MOVEMENT_DATE       ID_ANUNC TEXT
----------- ----------------- ---------- ----------
          1 20050121 00:00:00          2 PEPA

SQL>select * from tso_anunc;

ID_CONTRACT MOVEMENT_DATE       ID_ANUNC TEXT
----------- ----------------- ---------- ----------
          1 20050121 00:00:00          2 PEPE
          2 20050121 00:00:00          3 PEPE

I need to update tso_anunc with values from anunc

SQL>update tso_anunc
  2  set text = (select text from anunc where id_contract = 1)
  3  where id_anunc = (select id_anunc from anunc where id_contract = 1);

1 row updated.

SQL>select * from tso_anunc;

ID_CONTRACT MOVEMENT_DATE       ID_ANUNC TEXT
----------- ----------------- ---------- ----------
          1 20050121 00:00:00          2 PEPA
          2 20050121 00:00:00          3 PEPE

The problem is I need to run two queries in anunc for this update, is there any way to avoid that?

THX 

Tom Kyte
January 26, 2005 - 9:08 am UTC

did you read the above original answer?

cannot modify a column which maps to a non key-preserved table

Thiru, February 17, 2005 - 10:03 am UTC

Hi Tom,

I get this error ORA-01779: cannot modify a column which maps to a non key-preserved table
even when I did a global temp table with a group by clause instead of using it in the update
statement directly. shall appreciate your input.

drop table t1;
drop table gt;
create table t1(id number, c1 number,dt date);
insert into t1 values(1,100,null);
insert into t1 values(2,200,null);
insert into t1 values(1,500,null);
insert into t1 values(2,300,null);
insert into t1 values(3,100,null);
insert into t1 values(3,400,null);

create global temporary table gt on commit preserve rows
as select max(rowid) rid from t1 group by id;

update
(select a.c1 c1,a.dt dt from t1 a,t1 b
where a.id=b.id and a.rowid in(select rid from gt))
set dt=sysdate;

ORA-01779: cannot modify a column which maps to a non key-preserved table



Tom Kyte
February 17, 2005 - 11:18 am UTC

do not you mean:

update (
select a.dt
from t1 a
where id in ( select id from t2 )
and rowid in (select rid from gt)
)
set dt = sydate

the problem is the JOIN, not the global temporary table here (i assumed you really did not mean to join t1 to itself by id to id....)

Thiru, February 17, 2005 - 1:56 pm UTC

Actually I did mean to join the same table.there was a typo.


update
(select a.c1 c1,b.dt dt from t1 a,t1 b
where a.id=b.id and a.rowid in(select rid from gt))
set dt=sysdate;

btw, when I used the BYPASS_UJVC (searched your site for this), the error was not there. Is the approach right as the rowid would uniquely identify the row to be updated?

Also when i did:

update
(select a.c1 c1,a.dt dt from t1 a
where a.rowid in(select rid from gt))
set dt=sysdate;

the optimizer used a cartesian product while the first update with the join between the same tables did a hash join.


Thanks for the suggestions.


Tom Kyte
February 17, 2005 - 2:32 pm UTC

is ID unique in T1?

if not, how would you expect this to work????

if so, what is the point of the join again? you would be joining a row to itself? just use "id is not null"

the optmizer probably does not understand the size of the gtt, dynamic sampling, dbms-stats.set_table_stats -- they would help with that


</code> https://www.oracle.com/technetwork/issue-archive/2014/14-sep/o54asktom-2279184.html <code>
see query plans with temporary tables.

Update non Oracle Table via dblink

denni50, March 02, 2005 - 10:57 am UTC

Hi Tom

I'm attempting to update a column in an Access table
from an Oracle Table via a dblink.

I created a view of the table@dblink and then tried
to update the phone column with data from the Phone
table in Oracle...tried a workaround using MERGE and
can't get that to work either.

thanks for any tips/help.

SQL> create or replace view odbc_test
  2  as
  3  select idnumber,phone from odbc_upd_test@myacss;

View created.



SQL> select * from user_updatable_columns where table_name='ODBC_TEST';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME                    UPD INS DEL
------------------------------ --- --- ---
USER                           ODBC_TEST
IDNUMBER                       YES YES YES

USER                           ODBC_TEST
PHONE                          YES YES YES


SQL> update odbc_test od  
  2  set od.phone= (select p.areacode||' '||p.phone from phone p
  3  where p.idnumber= od.idnumber
  4  and p.preference='Y');
set od.phone= (select p.areacode||' '||p.phone from phone p
               *
ERROR at line 2:
ORA-02070: database MYACSS does not support subqueries in this context



SQL> MERGE INTO odbc_test o
  2  USING (select idnumber,phone from phone) e
  3  ON (o.idnumber = e.idnumber)
  4  WHEN MATCHED THEN UPDATE 
  5      SET o.phone = e.phone
  6  WHEN NOT MATCHED THEN 
  7  INSERT (idnumber,phone)
  8  values (null,null);
USING (select idnumber,phone from phone) e
      *
ERROR at line 2:
ORA-02022: remote statement has unoptimized view with remote object











 

Tom Kyte
March 02, 2005 - 11:16 am UTC

you might need to do this in plsql, for x in ( select ..) update access;

Update For..Loop not working either

denni50, March 02, 2005 - 2:44 pm UTC

Tom

I used your tip for updating a column in Access via
dblink in a for..loop,..tried doing it two ways.

SQLPlus just hangs and if I go into OEM it shows the ora-02025 error below.
(maybe my statement is incorrect)

begin
for x in ( select o.idnumber, p.phone from phone p, odbc_test o where p.idnumber=o.idnumber and p.preference='Y')
loop
update odbc_test set phone = x.phone;
end loop;
end;


begin
for x in ( select idnumber, phone from phone where preference='Y')
loop
update odbc_test set phone = x.phone where idnumber = x.idnumber;
end loop;
end;

keep getting error that is displayed in OEM:
ORA-02025: all tables in the SQL statement must be at the remote database
Cause: A SQL statement referenced tables from multiple databases, and one or
more of the remote databases are not Oracle Servers.
Action: Remote updates can be performed only if all databases in the SQL
statement are Oracle Servers. Update the earlier version databases in a separate
statement.


in the Oracle9i Heterogeneous Connectivity Administrator's Guide
Release 2 (9.2)...it states:

"Data manipulation language statements that update objects on the remote non-Oracle system
should not reference any objects on the local Oracle database.
For example, a statement such as the following will cause an error to be raised:
INSERT INTO remote_table@link as SELECT * FROM local_table;"


it then goes on further...
"Note:
Starting with Oracle9i, Release 2, there is another means of sharing information between databases.
Called Streams, this functionality includes the replication of information between Oracle and non-Oracle databases."

Do I need to use Streams or InterMedia?

what do you recommend for sharing data between oracle and non-oracle tables?

thanks





Tom Kyte
March 02, 2005 - 5:07 pm UTC

I'd contact support on this one for that update does not reference multiple tables.

can you do a standalone update?

SQL> update odbc_test set phone = xxx where idnumber = yyyy;

? 

ODBC Update Re-Test

denni50, March 03, 2005 - 9:31 am UTC

Tom

I scaled down the test tables(odbc_new) to 5,000 records
and was able to update remote table as you suggested.
I also created PK on idnumber in Access Table.

SQL> select table_name from all_tables@myacss;

TABLE_NAME
------------------------------
ODBC_New
ODBC_Test

2 rows selected.

SQL> update odbc_new@myacss
  2  set phone='555-5555'
  3  where idnumber=3337;

1 row updated.

SQL> commit;

Commit complete.


the 9i HS Admin Guide recommended using a cursor like
below...tried that and when I went into OEM to review
Sql Analyzer/Trace..etc the Long Ops showed 19004 blocks
being read, taking 6 hours and SQL showed error:

ora-02019 database error...connection description for
remote database not found.

hs 9i admin guide example:

SQL> DECLARE
  2    CURSOR c1 IS SELECT idnumber,phone FROM phone p
  3                 WHERE  p.preference='Y' and p.idnumber < 1788638;
  4  
  5  BEGIN
  6    FOR REC IN c1 LOOP
  7      UPDATE odbc_new@myacss SET phone = rec.phone
  8      WHERE idnumber = rec.idnumber; 
  9    END loop;
 10  END;

I ended up killing the session...performance definitely
an issue. I read about analyzing the non-Oracle tables
for optimization.

Do you think...given we are going to be working with large
tables, Oracle and non-Oracle we should use OO4O instead?

I'm going to try analyzing the Access Table and see what
gives...any feedback greatly appreciated.






 

Tom Kyte
March 03, 2005 - 10:00 am UTC

sorry, i just don't have access to access to play with and have never tried this.

if this were a one time mass update, I'd be tempted to just create a sql script even to run in access (spool the updates out or whatever). If this is ongoing -- working with support to discover what is wrong would be my approach.

thanks Tom...

denni50, March 03, 2005 - 10:11 am UTC

mgt is pushing for this as we receive continous third party
files from our web based and radio mass marketing campaigns
and they can be fairly large week to week. It would save
an enormous amount of time to the user who is responsible
for downloading these files.

When I get this figured out I'll post results here for
you and others to use.

I like challenges anyway.... :~)

Tom...(others) here's the results working with non-Oracle tables

denni50, March 03, 2005 - 11:22 am UTC

you definitely need to use views.
you need to set up pk(index) on data source file for
optimization.

My situation was the oracle-table phone has 3+ million
records...I only wanted the phone table records
that were needed to update the non-oracle table phone
column(5,000+ records) so I did that in a view.

SQL> create or replace view odbc_upd
  2  as
  3  select p.phone,p.idnumber from phone p, odbc_new@myacss o
  4  where p.idnumber=o.idnumber and p.preference='Y';

View created.

SQL> select * from odbc_upd;

>>>>> end of select

PHONE          IDNUMBER
------------ ----------
278-0000        639906
488-0000        837029
524-0000        805135
594-0000         35069
862-0000        500025

5846 rows selected.

then I was able to update the odbc_new@myacss phone column
utilizing the view in a pl/sql block:
SQL> set timing on
SQL> begin
  2  for x in (select idnumber, phone from odbc_upd)
  3    loop
  4       update odbc_new@myacss set phone = x.phone where idnumber=x.idnumber;
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:49.01
SQL> 

the phone column in the access table was updated correctly.

here is this link for 9i Generic Connectivity...extremely useful.

http://www.cs.umb.edu/cs634/ora9idocs/server.920/a96544/trouble.htm
Tom Kyte
March 03, 2005 - 11:31 am UTC

Thanks so much.

Record has been updated by another user

Jamil, April 13, 2005 - 3:47 am UTC

Dear Friends

I have this trigger in my block level
Pre-insert trigger

DECLARE
LOC_DIS NUMBER(12,3);
LOC_BALANCE NUMBER(12,3);
LOC_F_DIS_QTY NUMBER(6);
LOC_B_DIS_QTY NUMBER(6);
---
LO_COUNT NUMBER(4);
LO_ITEM VARCHAR2(30);
BEGIN
:GLOBAL.DEL_STATUS := 0;
IF NVL(:ORDER_DISRUPTION.D_DIST_QTY,0) !=0 THEN

SELECT (NVL(OD_ORDERED_PRODUCE,0) + NVL(:ORDER_DISRUPTION.D_DIST_QTY,0)),
(NVL(OD_ORDERED_QUANTITY,0)-(NVL(OD_BALANCE,0)+(NVL(:ORDER_DISRUPTION.D_DIST_QTY,0))))
INTO LOC_DIS,LOC_BALANCE
FROM PROD_ORDER_DETAILS
WHERE OD_ORD_CODE = :INTER_PROD.IN_ORD_CODE
AND OD_ITEM_CODE = :ORDER_DISRUPTION.D_ITEM_CODE;


--- UPDATE PROD_ORDER_DETAILS
:PROD_ORDER_DETAILS.OD_ORDERED_PRODUCE := LOC_DIS;
:PROD_ORDER_DETAILS.OD_BALANCE := LOC_BALANCE;
UPDATE PROD_ORDER_DETAILS
SET OD_ORDERED_PRODUCE = LOC_DIS,OD_BALANCE=LOC_BALANCE
WHERE OD_ORD_CODE = :INTER_PROD.IN_ORD_CODE
AND OD_ITEM_CODE = :ORDER_DISRUPTION.D_ITEM_CODE;



END IF;
End ;

This record is used to distribute the item in the production Screen
For example if my item_code 1 have the order_quantity 20 and
If I insert the first record as
item_code = 1 and order_quantity = 10 it is insert in the current
block table and update the UPDATE PROD_ORDER_DETAILS table but
if I insert the second record as item_code = 1 and order_quantity =10 I got this message
FRM-40654
Record has been updated by another user .re-query to see change .
Best regards
jamil




Tom Kyte
April 13, 2005 - 9:08 am UTC

as I recall (going back over a decade now), you have changed the record without forms really knowing it, it has the old cached copy and thinks some other user did it.

so you need to use one of the builtins to set the record status. You'd be best off using the forums on otn.oracle.com as I haven't done forms in too long a time.

how to update with a sequence in it

Ryan, May 04, 2005 - 4:43 pm UTC

anyway to make this work without pl/sql?

1 update mytable
2* set mycolumn = (select sequence.nextval from dual)

ERROR at line 2:
ORA-02287: sequence number not allowed here


Tom Kyte
May 04, 2005 - 6:10 pm UTC

what are you trying to do? Not sure what outcome you are wanting

if there are two rows in there, do you want them to have the same or different values?



Update the results of a SELECT statement

A reader, June 22, 2005 - 3:22 pm UTC

Hi Tom, please

How could I get the original value in a update from a select statement.

For example:
If I do a correlated update I can say:
Update ... set COLUMN=COLUMN+1
I can call the original value of the column COLUMN

how can I do the same in an update
UPDATE (...)
SELECT VALUE+nVariable ....

Thank you

Tom Kyte
June 23, 2005 - 1:45 pm UTC

sorry, didn't follow you on this one.

A reader, June 23, 2005 - 10:07 am UTC

I found the example, I was ommiting the =

UPDATE test a
SET (object_name) =
( SELECT b.object_name||a.object_name from dba_objects b
where a.object_Id= b.object_id)


SPACE

space, June 29, 2005 - 4:04 pm UTC

Hi TOM,

There were some wrong values and I wanted to update them with proper values hence I wrote the statement below

Update AA
set acc = decode(to_char(to_number(acc),'FM0000'), null, '0000',to_char(to_number(acc),'FM0000'))
where id > 10

In table AA acc is varchar2(5), The aim was to
if column acc = '0' make it acc = '0000'
if column acc = '019' make it acc = '0019'
if column acc = '970' make it acc = '0970'

With above update statement values are updated as '####'

I made a mistake I guess I should have written as follows

Update AA
set acc = decode(acc, null, '0000',to_char(to_number(acc),'FM0000'))
where id > 10.

I am just curious what is this ####?


-Thankyou very much

Tom Kyte
June 29, 2005 - 8:09 pm UTC

"prove" it?

if the number in acc didn't fit in fm0000, sure. but 019 'fits'. so it wouldn't have done that.



tkyte@ORA9IR2W> select to_char(to_number(x),'fm0000'), x from t;

TO_CH X
----- -----
0019 019
9999 9999
##### 99999

and just today, I finished the first draft of a chapter on datatypes and how bad bad bad it is to use strings to store numbers :(

Its and earlier application where I was asked to fix somethings

space, June 30, 2005 - 11:37 am UTC

Tom,
I cannot change the existing data structures and datatype of tables, for new I do take care and do the appropriate :-)

I dont know why that happened, but I rectified my mistake in the query.

Thankyou very much Tom.

updatable column not updating

denni50, July 13, 2005 - 9:28 am UTC

Tom

am having a dandy of a time trying to figure out why a column in a table will not update. I'm logged in as owner
of the schema objects so I have all rights and permissions.
see below(I included only relevant columns as the table has about 50 columns):


SQL> desc payment_acknowledgements;

 PUSERCODE1                                         VARCHAR2(4)
 PUSERCODE2                                         VARCHAR2(4)
 PUSERCODE3                                         VARCHAR2(4)
 PUSERCODE4                                         VARCHAR2(4)
 


SQL> select column_name,UPDATABLE,
  2           insertable,deletable
  3      from user_updatable_columns
  4     where table_name = 'PAYMENT_ACKNOWLEDGEMENTS'
  5     and column_name like 'P%';

COLUMN_NAME                    UPD INS DEL
------------------------------ --- --- ---
PUSERCODE1                     YES YES YES
PUSERCODE2                     YES YES YES
PUSERCODE3                     YES YES YES
PUSERCODE4                     YES YES YES

7 rows selected.


SQL> select remark,pusercode4 from payment_acknowledgements;

REMARK               PUSE
-------------------- ----
NCV05BA020           V
NCK05BA210           K
NCN05BA060           H
NCH05BA190           H

SQL> update payment_acknowledgements
  2  set pusercode4='N'
  3  where remark='NCN05BA060';

1 row updated.

SQL> commit;

Commit complete.

SQL> select remark,pusercode4 from payment_acknowledgements;

REMARK               PUSE
-------------------- ----
NCV05BA020           V
NCK05BA210           K
NCN05BA060           H
NCH05BA190           H

*************CAN UPDATE PUSERCODE2**************************

SQL> select pusercode2,remark from payment_acknowledgements;

PUSE REMARK
---- --------------------
H    NCV05BA020
H    NCK05BA210
H    NCN05BA060
H    NCH05BA190

SQL> update payment_acknowledgements
  2  set pusercode2='N'
  3  where remark='NCN05BA060';

1 row updated.

SQL> commit;

Commit complete.

SQL> select pusercode2,remark from payment_acknowledgements;

PUSE REMARK
---- --------------------
H    NCV05BA020
H    NCK05BA210
N    NCN05BA060
H    NCH05BA190

SQL> 

???????can't figure out why pusercode4 will not update.

thanks for any ideas of where else I need to look.



 

Tom Kyte
July 13, 2005 - 12:53 pm UTC

look for the trigger that is setting the value back to what it was :)

DUH!

denni50, July 14, 2005 - 8:22 am UTC

thanks Tom...
never thought about the trigger that fires of the package
on that table..after insert,update or delete.

In fact I was the one that added the new procedure to
the package that updates that column with a value from
another table.....duh!

I laughed when I saw your answer....gotta go find my
dunce cap!

"simple update"

Jon Roberts, August 18, 2005 - 10:58 am UTC

This seems to be an easy update but my table is so large that every bit of tweaking I can do helps.

create table my_test
(session_id varchar2(30) not null,
user_name varchar2(30) null,
action_time date not null)
partition by range (action_time)
(partition part_my_test_200501 values less than (to_date('2005-02-01', 'YYYY-MM-DD')),
partition part_my_test_200502 values less than (to_date('2005-03-01', 'YYYY-MM-DD')));

create index my_text_idx on my_test (session_id, user_name);

--is has many more partitions at one per month.
--other columns showing detail information have been removed for the example.
--each partition is about 10 gig in size.

I get daily files to load which I use sqlldr to put into the table. My user_name column can be null for some session_id records but not null for other records with the same session_id.

insert into my_test (session_id, user_name, action_time) values ('123', 'JON', date '2005-02-01');
insert into my_test (session_id, user_name, action_time) values ('123', null, date '2005-02-01');
insert into my_test (session_id, user_name, action_time) values ('123', null, date '2005-02-01');

I need to "fix" the null user_name records by changing the null user_name records with the same session_id to a non-null record if it can be found.

I thought of doing this in the ETL:
insert into my_test (session_id, user_name, action_time)
select session_id, max(user_name) over (partition by session_id), action_time from my_test_stage;

But I can't because I load daily and a session can span days or even partitions. So I'm forced to do an update.

update my_test t
set t.user_name = (select s.user_name
from my_test s
where t.session_id = s.session_id
and s.user_name is not null
and rownum < 2)
where t.user_name is null
and exists (select null
from my_test s2
where s2.session_id = t.session_id
and s2.user_name is not null);

--I could also create a cursor, bulk collect through it and update row by row but I am betting a single update statement would be faster.

Look like a good plan? I looked through your site and you had some good discussions of in/exists with updates but that was with a big table and a small table. I only want to update records that should be updated so that is why I have the exists statement.



Tom Kyte
August 18, 2005 - 4:29 pm UTC

how many null records do you anticipate and how big is this table?

Jon Roberts, August 18, 2005 - 11:28 pm UTC

>how many null records do you anticipate

After the insert, 96% of the user_name records are null. After the update, about 94% of the records have a null user_name.

>and how big is this table?

Each partition is about 10 gig in size and it will have about 12 months of data. 120 gig total. 32K block size too. I left the pctfree at the default 10 because of the updates.

Tom Kyte
August 18, 2005 - 11:40 pm UTC

96% of the inserted record, or 96% of ALL records?

would it be easier and more effective to retrieve the user_name upon RETRIEVAL rather than update it after load???

Indexes & Update statement

Deepak, August 19, 2005 - 12:07 am UTC

Hi Tom,

I have a table like the following...


my_table
--------
a number(2)
b char(10)
c varchar2(20)
d number(10)

I have the following indexes on the table.

primary key (a,b)
index (c)
index (c,d)

And I have an Update statement like this...

update my_table set c='TEST'
where a=? and b=?;

Now When I generate the execution plan for the above statement I only found that only the primary key index is being accessed by oracle. No mention about the other indexes in the execution plan.

As we know indexes on column c have also to be updated/affected by the above update statement.

My question is why the execution plan does not show the other indexes as well.

Please help me in understanding the concept.

Tom Kyte
August 20, 2005 - 3:26 pm UTC

that second index isn't necessarily necessary -- the index on (c,d) can be used as an index on (c) could be.

The explain plan shows INDEXES USED TO ACCESS THE DATA. The maintenance of the index structures takes place transparently in the background, it is implied.

That the indexes on C must be updated isn't part of the execution plan. the execution plan shows you how Oracle is going to *find* the data, once it finds it, it knows what to do to maintain everything it needs to maintain.

Jon Roberts, August 19, 2005 - 9:27 am UTC

> 96% of the inserted record, or 96% of ALL records?

Sorry about that. 96% of the inserted records are null. After I do the update, it drops to about 94%.

> would it be easier and more effective to retrieve the
> user_name upon RETRIEVAL rather than update it after
> load???

Ideally, yes. Basically, I have a log file that has a bunch of junk in it. Inside one column, there is data that is in name/value pairs (example: username=jon). There can be multiple identifiers because multiple applications log to this file. Plus, the delimiters between each name/value pair is not consistent. It could be %, &, or +.

So this junk column looks like this after I load it with sqlldr.
username=jon+source_name=app1+state=ca
user_name=bill;ComputerName=nn3009;MenuID=99

And it goes on and on. Lots of different combinations without a pattern.

I wrote a function to handle the parsing. I do something like this in the function:
create or replace function fn_example (p_search_string varchar2, p_source_string varchar2) return varchar2 is

type lookup_value_data is record (lookup_value varchar2(30));
type lookup_value_array is table of lookup_value_data index by binary_integer;
v_lookup_value lookup_value_array;
v_del lookup_value_array;

begin
if p_search_string = 'USERNAME' then
v_lookup_value(1).lookup_value := 'USERNAME=';
v_lookup_value(2).lookup_value := 'USER_NAME=';
....
--I used to have a cursor and bulk fetch to get the values
--into the array but hard coding makes the function run
--a ton faster. That is another question though. :)

elsif p_search_string = 'MACHINE' then
...

I parse a total of 3 columns out of the junk column.

The problem is this: a record with a valid session_id may or may not include the user_name in this junk column. If I ever get a valid user_name, I need for all of the records with the same session_id to have the valid user_name. Daily loads and sessions can cross days so I have been updating.

Oh yeah, I didn't design this. :) Plus it will go away after all of the applications migrate to a newer, better designed and more structured logging database.

thanks!


Tom Kyte
August 20, 2005 - 4:04 pm UTC

so, upon retrieval, why not

...., nvl( user_name, (select s.user_name
from my_test s
where t.session_id = s.session_id
and s.user_name is not null
and rownum < 2) ) user_name


?

Jon Roberts, August 22, 2005 - 10:36 am UTC

First off, thank you so much for this site and your responses. It has been very helpful.

Let's say this is the first day I insert records. I insert this value:

SESSION_ID USER_NAME ACTION_TIME
321 NULL '1-JAN-2005 23:59:00'

The next day, I insert some more records:

SESSION_ID USER_NAME ACTION_TIME
321 NULL '2-JAN-2005 00:01:00'
321 'BUBBA' '2-JAN-2005 00:01:50'

So now I finally got a valid user_name for session 321. I need for all three records in this example to have 'BUBBA' for this session_id.

If I got this value only upon retrieval, I would never update the first record I inserted yesterday for action_time = '1-JAN-2005 23:59:00'.


Tom Kyte
August 23, 2005 - 4:03 am UTC

upon retrieval of the first inserted record, you certainly would get bubba. why wouldn't you?

after putting in the 3rd record, anyone that retrieved the first with the nvl() stated above would get bubba.

replacing one column value between two rows..help??

ramis, August 28, 2005 - 6:58 pm UTC

HI,

I have table T

it has five columns, all are in number data type
Col1 and col2 are jointly primary key i.e. they cannot be repeated combined

when i run this query..

SQL> Select * from t 
it shows me the data like this

Col1        Col2              Col3       Col4           Col5
1             1               78          58            12.76
2             1               128         446           32.10
3             1               468         566           52.10
4             1               878         58            52.05
5             1               987         348           22.02

... so on. 

my requirement is that i want to update this table by replacing the col1 value between any two rows

suppose this is the first row

Col1        Col2          Col3        Col4         Col5
1             1             78          58        12.76

and this is any 2nd row

Col1        Col2          Col3        Col4        Col5
5             1            987         348       22.02


now i want that for first row the value of col1 replaces the value of col1 of 2nd row
and for 2nd row the value of col1 replaces the value of col1 of 1st row

i.e.

Col1        Col2          Col3        Col4         Col5
5             1            78           58         12.76

Col1        Col2          Col3        Col4         Col5
1             1            987        348          22.02

please tell how to achieve this in single query/procedure

regards,
Ramis 

Tom Kyte
August 29, 2005 - 1:11 am UTC

if you have the pairs:

(1,1)
(5,1)

you can

variable col1_1
variable col1_2
variable col2

exec :col1_1 := 1; :col1_2 := 5; :col2 := 1;

update t set col1 = decode( col1, :col1_1, :col1_2, :col1_1)
where (col1 = :col1_1 and col1 = :col2 )
or (col1 = :col1_2 and col1 = :col2 )
/



Another more self documenting approach

Bob B, August 29, 2005 - 7:50 am UTC

An identical way to do this with slightly different syntax is to use a tuple operator (unofficial name):

update t set col1 = decode( col1, :col1_1, :col1_2, :col1_1)
where (col1, col2) IN (
(:col1_1, :col2),
(:col1_2, :col2)
)



Tom Kyte
August 29, 2005 - 1:58 pm UTC

thanks :)

Alex, September 01, 2005 - 10:28 am UTC

Tom,

I have a question about bind variables in an update within a stored procedure.  If you look at a procedure like this:

SQL> CREATE OR REPLACE PROCEDURE update_bind (paddress IN VARCHAR, pphone IN NUMBER)
  2  AS
  3  BEGIN
  4     UPDATE t
  5        SET address = paddress,
  6            phone = pphone
  7      WHERE address = 'LANE';
  8  END update_bind;
  9  /

Procedure created.

Is this the correct way to write this or do I need a 'USING' clause in there?  I couldn't find syntax anywhere on this so I'm being lead to believe this is one of those cases where pl/sql is binding for us but I want to be sure.
Thanks. 

Tom Kyte
September 01, 2005 - 3:46 pm UTC

that binds, plsql variables ARE binds

it is virtually impossible to bind incorrectly using static sql in plsql

A reader, March 22, 2006 - 3:54 am UTC


Brad, April 04, 2006 - 4:17 pm UTC

Tom,
I need your expert advice in a performance issue i am having...

table inv_dtl has 15 million rows and table ship_dtl has 14 million rows


create table inv_dtl
(
inv_number number,
item_number number,
inv_qty number,
inv_cost number,
inv_rtl number,
inv_cnt number,
ship_qty number,
ship_rtl number,
ship_cost number,
ret_qty number,
ret_rtl number,
ret_cost number,
ret_cnt number
)
;

create unique index idx1 on inv_dtl
(inv_number,item_number);

( note:inv_number has a list of item_numbers associated with it..)

create table ship_dtl
(
inv_number number,
item_number number,
ship_type varchar2(1),
qty number,
cst number,
rtl number
)
;
create index idx2 on ship_dtl (inv_number);
create index idx3 on ship_dtl (item_number);


The below update statement runs for 30 minutes,.
The plans looks fine to me

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

UPDATE STATEMENT Optimizer Mode=CHOOSE 15 M 6642
UPDATE SCH.INV_DTL
TABLE ACCESS FULL SCH.INV_DTL 15 M 343 M 6642
SORT GROUP BY NOSORT 1 24 4
TABLE ACCESS BY INDEX ROWID SCH.SHIP_DTL 1 24 4
INDEX RANGE SCAN SCH.IDX2 21 3



is there a better way to re-write the below statement?

update inv_dtl a
set
(inv_qty,
inv_cost,
inv_rtl,
inv_cnt,
ret_qty,
ret_rtl,
ret_cost,
ret_cnt
) =
(
select
nvl(sum(qty),0),
nvl(sum(cst),0),
nvl(sum(rtl),0),
count(*),
sum(decode(ship_type,'R',qty,0)),
sum(decode(ship_type,'R',rtl,0)),
sum(decode(ship_type,'R',cst,0)),
count(decode(ship_type,'R',cst))
from
ship_dtl b
where
b.inv_number =a.inv_number and
b.item_number =a.item_number
group by
b.inv_number,
b.item_number
)


thanks


Tom Kyte
April 04, 2006 - 7:41 pm UTC

use merge.

merge into inv_dtl a
using (select sum(...), sum(...), .... from ship_dtl gropu by inv_number, item_number )
on (a.inv_number = b.inv_number and a.item_number = b.item_number)
when matched then set .....;



Brad, April 04, 2006 - 9:42 pm UTC

Tom,
Thanks for the reply..The problem with merge approach is that there could be some (inv_number,item_number) combinations in ship_dtl that may not be in inv_dtl and i dont want them inserted into inv_Dtl.my oracle version is 9.2.any suggestions
Thanks

Tom Kyte
April 05, 2006 - 5:36 pm UTC

merge into inv_dtl a
using (select sum(...), sum(...), .... from ship_dtl
where (inv_number,item_number) in (select inv_number,item_number from inv_dtl)
group by inv_number,
item_number )
on (a.inv_number = b.inv_number and a.item_number = b.item_number)
when matched then set .....
when not matched then insert (item_number) values (null); <<=== never happens because of the IN (subquery)

A reader, May 07, 2006 - 9:36 pm UTC


Update statement hangs in Toad

A reader, June 28, 2006 - 9:15 am UTC

Hi,


UPDATE TEST SET test_id=SQ_TEST_ID.NEXTVAL,name='Test Naming Convention';

The above statement is taking a long time to execute in Toad
Can you let me know the reason.The number of rows present in the table is only 2

Update statement hangs in Toad

A reader, June 28, 2006 - 9:47 am UTC

An update regarding the above issue. When I update it is giving me the following problem

Record is locked by another user.

How can I eliminate the above . Is there any way by which I can prevent the Locking of the row. Should I modify the update statement which I mentioned in the above issue

Tom Kyte
June 28, 2006 - 9:57 am UTC

eh?

in order to "eliminate the above", you'll have to hunt down the person locking the record and ask them to "stop doing that"

v$lock

A reader, January 02, 2007 - 1:50 pm UTC

Two questions on update statement, it comes from your Google thread of "write consistency"

One of the update goes

"update accounts
set amount = amount + pending, pending = 0, cleared = sysdate
where cleared is null; "

That depends on the order of the update on the SET operator is from the Left to Right, does Oracle gaurantee that?


2)

Is the job of the Where clause part of the Update/Delete (or insert into select ..) statements only as far as getting the ROWID of the row.

That is each column value is actually got in the CURRENT mode for update and each of its values has nothing to do with what was got in CONSISITENT mode to get the rowid (where clause part)

Thanks

Ravi




Tom Kyte
January 04, 2007 - 11:00 am UTC

it does not depend on the order.

update t
set a = c, b = a, c = b;


the left hand sides - read consistent, before the update takes place - the order of assignment is not relevant.


the row is retrieved in CURRENT mode to process the update after being found in CONSISTENT READ mode.

Best way to do mass update

Mobra, February 15, 2007 - 2:14 pm UTC


Let's say I have a "fact" table that is populated with some codes (foreign keys), and I would subsequently like (for the sake of denormalization) to update the fact table with the text/description that corresponds to the code.

create table fact_table
(
  code1 varchar2(3),
  code1_descr varchar2(50),
  code2 varchar2(3),
  code2_descr varchar2(50),
  code3 varchar2(3),
  code3_descr varchar2(50),
  code4 varchar2(3),
  code4_descr varchar2(50),
  code5 varchar2(3),
  code5_descr varchar2(50)

);

create table lookup_table1
(
  code_key varchar2(3),
  descr varchar2(50)
);

create table lookup_table2
(
  code_key varchar2(3),
  descr varchar2(50)
);

create table lookup_table3
(
  code_key varchar2(3),
  descr varchar2(50)
);

create table lookup_table4
(
  code_key varchar2(3),
  descr varchar2(50)
);

create table lookup_table5
(
  code_key varchar2(3),
  descr varchar2(50)
);


insert into fact_table values ('ABC', null, 'XYZ', null, '123', null, 'DEF', null, 'XXX', null);
insert into fact_table values ('ABC', null, 'XYZ', null, '123', null, 'DEF', null, 'XXX', null);
insert into fact_table values ('ABC', null, 'XYZ', null, '123', null, 'DEF', null, 'XXX', null);
insert into fact_table values ('ABC', null, 'XYZ', null, '123', null, 'DEF', null, 'XXX', null);

insert into lookup_table1 values ('ABC', 'some text for ABC');
insert into lookup_table1 values ('CBA', 'some text for CBA');
insert into lookup_table2 values ('XYZ', 'some text for XYZ');
insert into lookup_table2 values ('ZYX', 'some text for ZYX');
insert into lookup_table3 values ('123', 'some text for 123');
insert into lookup_table3 values ('321', 'some text for 321');
insert into lookup_table4 values ('DEF', 'some text for DEF');
insert into lookup_table4 values ('FED', 'some text for FED');
insert into lookup_table5 values ('XXX', 'some text for XXX');
insert into lookup_table5 values ('AAA', 'some text for AAA');



I will have to update this description after the table has been filled. Currently, I'm doing it like this:

update fact_table
  set code1_descr = (select descr from lookup_table1 where code1 = code_key),
    code2_descr = (select descr from lookup_table2 where code2 = code_key),
    code3_descr = (select descr from lookup_table3 where code3 = code_key),
    code4_descr = (select descr from lookup_table4 where code4 = code_key),
    code5_descr = (select descr from lookup_table5 where code5 = code_key)
;


This approach takes several minutes to finish for around 200,000 rows (the lookup tables have 3-5 rows each).

Is there a better way to do this update?

Tom Kyte
February 16, 2007 - 1:26 pm UTC

once you add the primary keys to your lookups, you can do this (outer joining if any of code1..code5 are nullable)

ops$tkyte%ORA9IR2> select * from fact_table;

COD CODE1_DESCR       COD CODE2_DESCR       COD CODE3_DESCR       COD CODE4_DESCR       COD CODE5_DESCR
--- ----------------- --- ----------------- --- ----------------- --- ----------------- --- -----------------
ABC                   XYZ                   123                   DEF                   XXX
ABC                   XYZ                   123                   DEF                   XXX
ABC                   XYZ                   123                   DEF                   XXX
ABC                   XYZ                   123                   DEF                   XXX

ops$tkyte%ORA9IR2> update ( select ft.code1_descr, ft.code2_descr, ft.code3_descr, ft.code4_descr, ft.code5_descr,
  2                  l1.descr l1d, l2.descr l2d, l3.descr l3d, l4.descr l4d, l5.descr l5d
  3             from fact_table ft,
  4                  lookup_table1 l1,
  5                  lookup_table2 l2,
  6                  lookup_table3 l3,
  7                  lookup_table4 l4,
  8                  lookup_table5 l5
  9            where ft.code1 = l1.code_key
 10              and ft.code2 = l2.code_key
 11              and ft.code3 = l3.code_key
 12              and ft.code4 = l4.code_key
 13              and ft.code5 = l5.code_key
 14         )
 15   set code1_descr = l1d,
 16       code2_descr = l2d,
 17       code3_descr = l3d,
 18       code4_descr = l4d,
 19       code5_descr = l5d
 20  /

4 rows updated.

ops$tkyte%ORA9IR2> select * from fact_table;

COD CODE1_DESCR       COD CODE2_DESCR       COD CODE3_DESCR       COD CODE4_DESCR       COD CODE5_DESCR
--- ----------------- --- ----------------- --- ----------------- --- ----------------- --- -----------------
ABC some text for ABC XYZ some text for XYZ 123 some text for 123 DEF some text for DEF XXX some text for XXX
ABC some text for ABC XYZ some text for XYZ 123 some text for 123 DEF some text for DEF XXX some text for XXX
ABC some text for ABC XYZ some text for XYZ 123 some text for 123 DEF some text for DEF XXX some text for XXX
ABC some text for ABC XYZ some text for XYZ 123 some text for 123 DEF some text for DEF XXX some text for XXX

Please help

Ramesh, February 27, 2007 - 8:32 am UTC

Hi Tom,
I have an update requirement like this.
I need to update a table which holds 5 months of data at any time.

for prd_cursor_rec in prd_cursor loop
update prd_fact
set adj_value = adj_value * prd_cursor_rec.price_diff
where country = 'UK' and ( dly_date >= prd_cursor_rec.file_load_date and .... ) --
end loop;

dly_date is a date column and file_load_date is the first day of current
month.FOR THE CURRENT MONTH and immediate last 13 days of the LAST month I want to update all the rows with that set clause.

Could you please help me here?
Thanks in Advance.

Tom Kyte
February 27, 2007 - 10:57 am UTC


dly_date >= file_load_date-13

isn't that it? you just want 13 days prior to the beginning of the current month as far as I can tell.

OK

Siva, March 02, 2007 - 7:06 am UTC

Hi Tom,
Can we not use like this?

SQL> declare
2 r_updated number;
3 begin
4 update a set rnum = rnum+1
5 returning sql%bulk_rowcount into r_updated;
6 dbms_output.put_line('Rows updated are:'||to_char(r_updated));
7 end;
8 /
returning sql%bulk_rowcount into r_updated;
*
ERROR at line 5:
ORA-06550: line 5, column 14:
PL/SQL: ORA-00911: invalid character
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored


SQL> declare
2 r_updated number;
3 begin
4 update a set rnum = rnum+1
5 returning sql%rowcount into r_updated;
6 dbms_output.put_line('Rows updated are:'||to_char(r_updated));
7 end;
8 /
returning sql%rowcount into r_updated;
*
ERROR at line 5:
ORA-06550: line 5, column 14:
PL/SQL: ORA-00911: invalid character
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored
Tom Kyte
March 04, 2007 - 5:43 pm UTC

no, you cannot.

Please help

Kumar, March 21, 2007 - 5:11 am UTC

Hi Tom,
In an update statement Cannot an inline query be used?
Please see the below update statement..

update qdbsales.gooddata_fact gf,(select gf.country_code,gf.transaction_date,gf.pfc_code,gf.supplier_code,gf.sales_units,
gf.trade_value as old_trade_value,(gf.trade_value/gf.sales_units) as old_unit_price,
ppd.pack_price,gf.sales_units*ppd.pack_price as new_trade_value,
ppd.pack_price_prev
from
qdbsales.gooddata_fact gf,
qdbsales.product_pack_dim ppd,
qdbsales.product_atc_dim pad
where ppd.country_code = 'IRLQ'
and gf.country_code = ppd.country_code
and gf.pfc_code = ppd.prod_pack_code
and pad.atc4_code = ppd.atc4_code
and pad.country_code = ppd.country_code
and ppd.pack_price_prev <> 0
and gf.sales_units <> 0
and ppd.pack_price <> ppd.pack_price_prev
and gf.transaction_date >= price_eff_date
and price_eff_date = (select max(price_eff_date)
from qdbsales.product_pack_dim where country_code = 'IRLQ')
and (gf.trade_value/gf.sales_units) <> pack_price ) ilq
set (gf.trade_value) = ilq.new_trade_value
where gf.country_code = ilq.country_code
and gf.transaction_date = ilq.transaction_date
and gf.supplier_code = ilq.supplier_code
and gf.pfc_code = ilq.pfc_code
and gf.trade_value = ilq.old_trade_value

update qdbsales.gooddata_fact gf,(select gf.country_code,gf.transaction_date,gf.pfc_code,gf.supplier
*
ERROR at line 1:
ORA-00971: missing SET keyword

Your help to correct this update is expected.
Thanks
Tom Kyte
March 21, 2007 - 6:55 am UTC

if I understood what this update was actually attempting to do - I would have something meaningful to say.

As it is - I have no clue what you are trying to do.

OK

Kumar, March 21, 2007 - 9:11 am UTC

Hi Tom,
Thanks for your reply.

actually the update needs to work in this way

update tabl a
set (a.c1) = (select sum(c1) from ( select c1,a1,b1 from t1,t2,t3 where .. some joins)).

This query need to be aliased so that I can check with the
table to be updated with the key values.

update tabl a
set (a.c1) = (select sum(c1) from ( select c1,a1,b1 from t1,t2,t3 where .. some joins))qry
where a.x = qry.x
and a.y = qry.y

Is this possible?

I tried this way.

update tabl a,(select ...) qry
set a.x = qry.x,a.y = qry.y.

Please let me know if you know some other way to make this
update work.
Thanks for your time.




Tom Kyte
March 21, 2007 - 11:21 am UTC

update tabl
set c1 = (select sum(c1)
from t1, t2, t3
where <joins>
and x = tabl.x
and y = tabl.y );


OK

Siva, March 30, 2007 - 5:17 am UTC

Hi Tom,

Mar 01 data must be subtracted from Feb16 data
and Mar 02 data must be subtracted from Feb17 data and so on.

I did an update like this for the below data

update mdata set val = val - (select val from fdata where
mdata.dt >= fdata.dt-13 and fdata.dt <= mdata.dt and rownum=1)

But it takes the first row from fdata and use that value to
delete from mdata for all the rows not the remaining rows
from fadat.
How to achieve this kind of update?



SQL> create table fdata(dt date,val number)
2 /

Table created.

SQL> create table mdata(dt date,val number)
2 /

Table created.

SQL> insert into mdata
2 select trunc(sysdate,'MM')+(ROWNUM-1),rownum*3 from obj where rownum <= 15
3 /

15 rows created.

SQL> insert into fdata
2 select trunc(sysdate,'MM')-13+(rownum-1),rownum*2 from obj where rownum <= 13
3 /

13 rows created.

SQL> commit
2 /

Commit complete.

OK

A reader, March 31, 2007 - 8:52 am UTC

Hi Tom,
Any idea about the above update?
Tom Kyte
March 31, 2007 - 12:58 pm UTC

i did not follow the explanation.

swap the fields value between columns in a single table using sql.

shyam, April 05, 2007 - 2:08 am UTC

hi,

is it possible to swap the fields value between two columns in a single table using sql?

example :

i have table with two column.
Table T:
c1 c2
------ -----
1 2
2 3
4 5

now is there any possible that using a single query, can i swap the values of column in my table T?

output will be after executing query will be

sql> select c1,c2 from T;

c1 c2
------ ------
2 1
3 2
5 4

can any one help me out?

Thanks
Shyam~



Tom Kyte
April 05, 2007 - 10:49 am UTC

update t set c1 = c2, c2 = c1;


How about WITH clause for updates?

Tony, April 11, 2007 - 10:19 am UTC


Tom,

I would suggest the following enhancements for updates:

1. WITH tmp_tab AS (query )
UPDATE tab1
SET tab1.col1 = ( SELECT col2 FROM tmp_tab
WHERE tmp_tab.col2 = tab1.col2 )


2. UPDATE tab2 a , (SELECT col3, col4 FROM tab3 ) b
SET a.col3 = b.col3
WHERE a.col4 = b.col4

The above enhancements will be very helpful to solve many performace issues due to updates. -:)



Tom Kyte
April 11, 2007 - 11:32 am UTC

1) we can do that with merge now.

merge into tab1
using (query) A
on (tab1.col1 = a.col2)
when matched then update set col2 = a.col2;



2) we've been able to do that for a long time - from the original 7.3 example above:

scott@ORA734.WORLD> update
  2    ( select t1.y t1_y, t2.y t2_y
  3            from t1, t2
  4           where t1.x = t2.x )
  5     set t1_y = t2_y
  6  /

How about WITH clause for updates?

Tony, April 11, 2007 - 11:57 am UTC

Tom,

1. First update locks the rows being updated. So, we can not use it in OLTP systems. Am I right?

2. The second update requires PK or UK on the table. Am I right? How to avoid this constraint?


Tom Kyte
April 11, 2007 - 11:59 am UTC

1) huh? no idea what you mean there

2) on the SOURCE TABLE, yes. You can use MERGE

merge into t1
using t2
on (join condition)
when matched .....;


without a unique constraint (merge has the logic to detect you updated the row two times and will FAIL at runtime if you do that)

How about WITH clause for updates?

Tony, April 11, 2007 - 12:09 pm UTC

Tom,

I meant that merge will lock the table and others can't update it.

Thanks
Tom Kyte
April 11, 2007 - 1:12 pm UTC

that would not be accurate.

merge will only lock the affected rows - just like update.

ops$tkyte%ORA10GR2> create table t ( x int, y int );

Table created.

ops$tkyte%ORA10GR2> insert into t values (1,null);

1 row created.

ops$tkyte%ORA10GR2> insert into t values (2,null);

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> merge into t
  2  using (select 1 x, 2 y from dual) d
  3  on (t.x = d.x)
  4  when matched then update set y = d.y;

1 row merged.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare<b>
  2          pragma autonomous_transaction;</b>
  3  begin
  4          insert into t values ( 3, 1 );
  5          update t set y = 0 where x = 2;
  6          commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.


ops$tkyte%ORA10GR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          update t set y = 0 where x = 1;
  5          commit;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4


ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> select * from t;

         X          Y
---------- ----------
         1          2
         2          0
         3          1



the autonomous transaction is not blocked - the table is NOT locked.

that the second plsql block failed demonstrates the row only is locked.

MERGE doesn't work without WHEN NOT MATCHED THEN clause.

Tony, April 11, 2007 - 1:50 pm UTC

Thanks Tom,

MERGE doesn't work without WHEN NOT MATCHED THEN clause. I need only WHEN MATCHED THEN clause. Is there any work around? I am using Oracle 9.2

Tom Kyte
April 11, 2007 - 5:48 pm UTC

10g sure permits it (and I always assume current software unless and until someone says otherwise!)

merge into t1
using (select * from t2 where (key,columns) in (select key,columns from t1) t2
on (join-condition)
when matched then update ...
when not matched /* never happens!!!! */ then insert (col) values ( null);


Mass update, follow-up to your answer on Feb 15th

Mobra, April 12, 2007 - 7:56 am UTC

Hi,

you said "once you add the primary keys to your lookups, you can do this (outer joining if any of code1..code5 are nullable) ".

This does indeed work when the lookup tables are separate tables with PKs, but my case is a bit different. The lookup "tables" are actually views based on a single lookup table which has a composite primary key.

When I try to use this approach I get an "ORA-01779: cannot modify a column which maps to a non key-preserved table".

An updated test case follows:

create table fact_table
(
  code1 varchar2(3),
  code1_descr varchar2(50),
  code2 varchar2(3),
  code2_descr varchar2(50),
  code3 varchar2(3),
  code3_descr varchar2(50),
  code4 varchar2(3),
  code4_descr varchar2(50),
  code5 varchar2(3),
  code5_descr varchar2(50)

);


insert into fact_table values ('ABC', null, 'XYZ', null, '123', null, 'DEF', null, 'XXX', null);
insert into fact_table values ('ABC', null, 'XYZ', null, '123', null, 'DEF', null, 'XXX', null);
insert into fact_table values ('ABC', null, 'XYZ', null, '123', null, 'DEF', null, 'XXX', null);
insert into fact_table values ('ABC', null, 'XYZ', null, '123', null, 'DEF', null, 'XXX', null);


create table lookup_master
  (code_group varchar2(3) not null,
   code_key   varchar2(3) not null,
   descr      varchar2(50) not null);


ALTER TABLE lookup_master  ADD  ( CONSTRAINT XPK_lookup_master PRIMARY KEY (code_group, code_key) ) ;   


insert into lookup_master values ('1', 'ABC', 'some text for ABC');
insert into lookup_master values ('1', 'CBA', 'some text for CBA');
insert into lookup_master values ('2', 'XYZ', 'some text for XYZ');
insert into lookup_master values ('2', 'ZYX', 'some text for ZYX');
insert into lookup_master values ('3', '123', 'some text for 123');
insert into lookup_master values ('3', '321', 'some text for 321');
insert into lookup_master values ('4', 'DEF', 'some text for DEF');
insert into lookup_master values ('4', 'FED', 'some text for FED');
insert into lookup_master values ('5', 'XXX', 'some text for XXX');
insert into lookup_master values ('5', 'AAA', 'some text for AAA');

create or replace view lookup_table1 as select code_key, descr from lookup_master where code_group = '1';
create or replace view lookup_table2 as select code_key, descr from lookup_master where code_group = '2';
create or replace view lookup_table3 as select code_key, descr from lookup_master where code_group = '3';
create or replace view lookup_table4 as select code_key, descr from lookup_master where code_group = '4';
create or replace view lookup_table5 as select code_key, descr from lookup_master where code_group = '5';


update ( select ft.code1_descr, ft.code2_descr, ft.code3_descr, ft.code4_descr, 
ft.code5_descr,
                  l1.descr l1d, l2.descr l2d, l3.descr l3d, l4.descr l4d, l5.descr l5d
             from fact_table ft,
                  lookup_table1 l1,
                  lookup_table2 l2,
                  lookup_table3 l3,
                  lookup_table4 l4,
                  lookup_table5 l5
            where ft.code1 = l1.code_key
              and ft.code2 = l2.code_key
              and ft.code3 = l3.code_key
              and ft.code4 = l4.code_key
              and ft.code5 = l5.code_key
         )
   set code1_descr = l1d,
       code2_descr = l2d,
       code3_descr = l3d,
       code4_descr = l4d,
       code5_descr = l5d
;
  



The update gives "ORA-01779: cannot modify a column which maps to a non key-preserved table". Why? How can this be avoided (and still use the view approach? or perhaps the base table directly?).


Tom Kyte
April 13, 2007 - 12:01 pm UTC

yet another reason, amongst the dozens of others, not to use a single lookup table.

you can use merge instead.

WIth Clause

A reader, May 08, 2007 - 6:54 pm UTC

Can you give a practical (update) query where "WITH" clause is used.. and "WITH" query alias is referenced twice in the update stmt.



Tom Kyte
May 11, 2007 - 9:17 am UTC

this is one of those "you'll know it when you see it"

I'm always somewhat amused at a request like this. Things are just "tools", techniques.

with subquery factoring is "practical" in any SELECT statement that makes it practical.

If you find a practical select statement that would benefit from it, then it is practical in whatever other statement uses it.

Help!

Kumar, November 06, 2007 - 9:25 am UTC

Hi Tom,
I have a subquery which returns more than one row and
I am doing an update and it fails because it gets more than one row.
For example

update t
set x = ( select x from a where desc = 'product')
where ..

Any generalized update is there which can overcome this
kind of problem?

Tom Kyte
November 06, 2007 - 3:02 pm UTC

umm, think about it.

if that returns more than a single row, please tell me, which one should be used?

once you do that, well, then, we'll be able to write the subquery properly to return just that row!

I hate randomness, so - tell us precisely how to identify the correct row, and please do not say "any row will do", because then I just say, set the column x to null because "you do not KNOW what x should be"

OK

A reader, November 13, 2007 - 7:30 am UTC

Hi Tom,
Below update fails with the oracle error 01427.
How to correct this?

product_restruct is a table having the old_prod_id and new_prod_id.
product_details is a transaction table date wise 

SQL> update product_details pd
  2  set prod_id = ( select new_prod_id from product_restruct pr where country = 'UK'
  3                    and pd.country = pr.country and
  4                    pd.prod_code = TO_NUMBER(SUBSTR(TO_CHAR(prod_pack_code),1,LENGTH(prod_pack_code)-3)))
  5  where exists
  6  ( select 1 from product_restruct where country = pd.country and old_prod_id = pd.prod_id
  7*   and TO_NUMBER(SUBSTR(TO_CHAR(prod_pack_code),1,LENGTH(prod_pack_code)-3)) = pd.prod_code)
SQL> set time on
15:43:03 SQL> /
set prod_id = ( select new_prod_id from product_restruct pr where country = 'UK'
                  *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

Tom Kyte
November 16, 2007 - 2:18 pm UTC

how to correct it?

make the subquery return a single row.

think about this please. if procedure_restruct returns more than one row given your query - to what value would prod_id be set to????????


please, think through the logic here - if "set prod_id = (select more than one new_prod_id from something )" happened - what value would prod_id be left with.


Then, look at your SQL and figure out (based on your model, your knowledge of the data) how to precisely find THAT ROW with your where clause.

Self updating a table

Rajesh, April 21, 2008 - 2:32 pm UTC

I have a table with records like this

lnno crit1 crit2 Val Status
1 1 1 10
2 1 2 5
3 1 1 3
4 2 2 4
5 1 2 15

I need to update the same table with resultant records like this

lnno crit1 crit2 Val Status
1 1 1 13 VALID
2 1 2 20 VALID
3 1 1 3
4 2 2 4 VALID
5 1 2 8


Basically, I need to update the same table with the sum of values that has matching criterias and mark them as valid. Could you please let me know how to achieve this? I suppose we should be using analytic function row_number. But I couldn't figure out an sql as we are updating the same table.

Here's the table scripts

Create table tab1(lnno number, crit1 number, crit2 number, val number, status varchar2(10))


insert into tab1(lnno,crit1,crit2,val) values(1,1,1,10);
insert into tab1(lnno,crit1,crit2,val) values(2,1,2,5);
insert into tab1(lnno,crit1,crit2,val) values(3,1,1,3);
insert into tab1(lnno,crit1,crit2,val) values(4,2,2,4);
insert into tab1(lnno,crit1,crit2,val) values(5,1,2,15);
Tom Kyte
April 23, 2008 - 5:18 pm UTC

you explained in ZERO lines of text how to achieve your logic.


..I need to update the same table with the sum of values that has matching criterias and mark them as valid....

is so vague as to be "not useful"

update

A reader, April 27, 2008 - 4:21 pm UTC


That was a brilliant solution to speed up UPDATE!

Sachin S, May 05, 2008 - 1:32 pm UTC

Hey Tom

That was a brilliant solution to speed up UPDATE!

Thanks alot!

My query was taking more than 1 hour to update the 7000 records after applying the suggested solution the query takes just 1-2 seconds to update the records.


A Reader, June 11, 2008 - 5:32 pm UTC

create table test
(dt_key number(8)
,s_id varchar2(20)
,a_id number(10)
,comp_id number(8)
,b_s_id number(5)
,b_u_id varchar2(10)
,flag varchar2(1)
,status varchar2(1) default 'A' not null);

insert into test
values
(20080603,'ABC US test',31075000,107000,1000,'ABC0001000','N','A');

insert into test
values
(20080603,'ABC US test',31075001,107000,1000,'ABC0001000','U','A');


insert into test
values
(20080603,'DEF US test2',31076000,108000,2000,'ABC0001000','N','A');

insert into test
values
(20080603,'DEF US test',31076001,108000,2000,'ABC0001000','U','A');

insert into test
values
(20080603,'DEF US test',31076002,108000,2000,'ABC0001000','N','A');

commit;

select * from test order by a_id;

I need update record 1's status to I, since I have a new record with new a_id
I need to update record 3 and 4's status to I, since I have a record with new a_id (5th record)
These records will have same dt_key, S_id, Comp_id,b_s_id, b_u_id. Can you tell me how to do this?

Tom Kyte
June 11, 2008 - 9:11 pm UTC

sorry, this logic was not parseable by me.

I'll presume record 1 is the first record returned after sorting by a_id.

You write:
... I need update record 1's status to I, since I have a new record with new a_id. I need tou pdate record 3 and 4's status. ....

well, if you ask me, you need to update record 1, 2, 3 and 4 - why isn't record 2 included???

Reader, June 12, 2008 - 10:40 am UTC

Sorry for not being clear on my question:

create table test
(dt_key number(8)
,s_id varchar2(20)
,a_id number(10)
,comp_id number(8)
,b_s_id number(5)
,b_u_id varchar2(10)
,flag varchar2(1)
,status varchar2(1) default 'A' not null);

insert into test
values
(20080603,'ABC US test',31075000,107000,1000,'ABC0001000','N','A');

insert into test
values
(20080603,'ABC US test',31075001,107000,1000,'ABC0001000','U','A');


insert into test
values
(20080603,'DEF US test2',31076000,108000,2000,'ABC0001001','N','A');

insert into test
values
(20080603,'DEF US test2',31076001,108000,2000,'ABC0001001','U','A');

insert into test
values
(20080603,'DEF US test2',31076002,108000,2000,'ABC0001001','N','A');

commit;

select * from test order by a_id;

Here are the records in test table after ordering by a_id.

DT_KEY S_ID A_ID COMP_ID B_S_ID B_U_ID F S
---------- ------------ ---------- ---------- ---------- ---------- - -
20080603 ABC US test 31075000 107000 1000 ABC0001000 N A
20080603 ABC US test 31075001 107000 1000 ABC0001000 U A
20080603 DEF US test2 31076000 108000 2000 ABC0001001 N A
20080603 DEF US test2 31076001 108000 2000 ABC0001001 U A
20080603 DEF US test2 31076002 108000 2000 ABC0001001 N A


update status of record 1 (a_id = 31075000) to I, since I have a new record with a_id = 31075001 (2nd record)
update status of record 3 (a_id = 31076000) and recod 4 (a_id = 31076001) to I, since I have a new record with a_id = 31076002 (5th record)


Records 1 and 2 has same dt_key, S_id, Comp_id,b_s_id, b_u_id.
Records 3,4 and 5 has same dt_key, S_id, Comp_id,b_s_id, b_u_id

Can you tell me how to do this?
Tom Kyte
June 12, 2008 - 11:00 am UTC

ops$tkyte%ORA10GR2> select dt_key, s_id, comp_id, b_s_id, b_u_id, a_id, status,
  2         row_number() over (partition by dt_key, s_id, comp_id, b_s_id, b_u_id order by a_id DESC nulls last) rn
  3    from test
  4   order by dt_key, s_id, comp_id, b_s_id, b_u_id, a_id desc nulls last
  5  /

    DT_KEY S_ID                    COMP_ID     B_S_ID B_U_ID           A_ID S         RN
---------- -------------------- ---------- ---------- ---------- ---------- - ----------
  20080603 ABC     US test          107000       1000 ABC0001000   31075001 A          1
  20080603 ABC     US test          107000       1000 ABC0001000   31075000 A          2
  20080603 DEF     US test2         108000       2000 ABC0001001   31076002 A          1
  20080603 DEF     US test2         108000       2000 ABC0001001   31076001 A          2
  20080603 DEF     US test2         108000       2000 ABC0001001   31076000 A          3

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> update test
  2     set status = 'I'
  3   where rowid in
  4  (
  5  select rid
  6    from (
  7  select rowid rid,
  8         row_number() over (partition by dt_key, s_id, comp_id, b_s_id, b_u_id order by a_id DESC nulls last) rn
  9    from test
 10         )
 11   where rn <> 1
 12  )
 13  /

3 rows updated.

ops$tkyte%ORA10GR2> select dt_key, s_id, comp_id, b_s_id, b_u_id, a_id, status
  2    from test
  3   order by dt_key, s_id, comp_id, b_s_id, b_u_id, a_id
  4  /

    DT_KEY S_ID                    COMP_ID     B_S_ID B_U_ID           A_ID S
---------- -------------------- ---------- ---------- ---------- ---------- -
  20080603 ABC     US test          107000       1000 ABC0001000   31075000 I
  20080603 ABC     US test          107000       1000 ABC0001000   31075001 A
  20080603 DEF     US test2         108000       2000 ABC0001001   31076000 I
  20080603 DEF     US test2         108000       2000 ABC0001001   31076001 I
  20080603 DEF     US test2         108000       2000 ABC0001001   31076002 A

ops$tkyte%ORA10GR2>

Reader, June 12, 2008 - 12:32 pm UTC

Thank you very much. This works. I appreciate your help.

Reader, June 12, 2008 - 5:04 pm UTC

create table test
(dt_key number(8)
,s_id varchar2(20)
,a_id number(10)
,comp_id number(8)
,b_s_id number(5)
,b_u_id varchar2(10)
,flag varchar2(1)
,status varchar2(1) default 'A' not null);

insert into test
values
(20080603,'PQR US test3',31075004,105000,0,NULL,'N','A');

insert into test
values
(20080603,'PQR US test3',31075005,105000,0,NULL,'U','A');


insert into test
values
(20080603,'DEF US test2',31076006,109000,0,NULL,'N','A');

insert into test
values
(20080603,'DEF US test2',31076007,109000,0,NULL,'U','A');

insert into test
values
(20080603,'DEF US test2',31076008,109000,0,NULL,'N','A');

insert into test
values
(20080603,'KLM US test2',31076009,106000,0,'XYZ200','U','A');

insert into test
values
(20080603,'KLM US test2',31076010,106000,0,'XYZ200','N','A');

commit;

select dt_key, s_id, comp_id, b_s_id, b_u_id, a_id, status,
row_number() over (partition by dt_key, s_id, comp_id, b_s_id, b_u_id order by a_id DESC nulls last) rn
from test
order by dt_key, s_id, comp_id, b_s_id, b_u_id, a_id desc nulls last;

DT_KEY S_ID COMP_ID B_S_ID B_U_ID A_ID S RN
---------- -------------------- ---------- ---------- ---------- ---------- - ----------
20080603 DEF US test2 109000 0 31076008 A 1
20080603 DEF US test2 109000 0 31076007 A 2
20080603 DEF US test2 109000 0 31076006 A 3
20080603 KLM US test2 106000 0 XYZ200 31076010 A 1
20080603 KLM US test2 106000 0 XYZ200 31076009 A 2
20080603 PQR US test3 105000 0 31075005 A 1
20080603 PQR US test3 105000 0 31075004 A 2

7 rows selected.


There might be nulls in b_u_id. In that case, b_s_id will be zero. Still the rank function is giving the correct rn. Can you please tell me how row_number() treats nulls?
Tom Kyte
June 12, 2008 - 7:29 pm UTC

we are partitioning by b_u_id, all NULLS will be considered "the same", all null b_u_id values will be consider "the same" as far as the partition goes.

Updating a table with a join to itself

Maverick, July 31, 2008 - 10:29 am UTC

Hi Tom, I have a problem similar to a reviewer CJ from NY above, with subject "Updating a table with a join to itself". Eventhough it's somewhat similar to his problem ,I still need your help:-)

Here is my scenario:

table a:
id key1 key2 key3 stepid
----- ------- ------- -------- --------
1 7 1 23
2 7 1 23
3 7 1 23
4 7 1 23
5 7 1 23
6 8 1 24
7 8 1 24
8 8 1 24


Now I need to update stepid in this table to

id key1 key2 key3 stepid
----- ------- ------- -------- --------
1 7 1 23 1
2 7 1 23 2
3 7 1 23 3
4 7 1 23 4
5 7 1 23 5
6 8 1 24 1 <= new set
7 8 1 24 2
8 8 1 24 3


I could not use single Update as it's failing on non-key preserved columns error.
So, I took Merge approach:

Merge into a
using (select key1,
key2,
key3,
row_number() over (partition by key1,key2,key3 order by key1,key2,key3) r
from a
) b
on (a.key1=b.key1
and a.key2=b.key2
and a.key3=b.key3)
when matched then
update set step_id=b.r;

I keep getting this error "ORA-30926: unable to get a stable set of rows in the source tables" ..How do I do this?

I'm using Oracle 10g R2.

Thanks for your help

Tom Kyte
August 03, 2008 - 1:28 pm UTC

your update is non-deterministic.

think about it - you order by key1, key2, key3 after partitioning by key1, key2, key3.

so, which one is "first"? all of them are, none of them are. any of them are.

that is, if you run the merge twice using your logic, you could end up with different answers each time.


and you are joining by key1, key2, key3 - that is NOT unique!! You are updating key1,key2,key3 = (7,1,23) with 5 different rows, like a cartesian join.



ops$tkyte%ORA10GR2> merge into t
  2  using
  3  (select rowid rid, row_number() over (partition by key1, key2, key3 order by id) rn
  4     from t) d
  5  on
  6  ( t.rowid = d.rid )
  7  when matched then update set stepid = d.rn
  8  /

8 rows merged.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t order by id;

        ID       KEY1       KEY2       KEY3     STEPID
---------- ---------- ---------- ---------- ----------
         1          7          1         23          1
         2          7          1         23          2
         3          7          1         23          3
         4          7          1         23          4
         5          7          1         23          5
         6          8          1         24          1
         7          8          1         24          2
         8          8          1         24          3

8 rows selected.




Updating a table with a join to itself

Maverick, August 01, 2008 - 5:38 pm UTC

I have fixed this problem by adding ID column as well. But If you can provide any other better solution, I would really appreciate it.

Thanks,
Tom Kyte
August 03, 2008 - 2:01 pm UTC

see above.

Update for bridge table

daily reader, December 05, 2008 - 2:17 pm UTC

Tom,

In my data mart I need to update bridge table with new surrogate key values from both parent tables however when I
do an update as below I get "ORA-01427: single-row subquery returns more than one row" is there a way to achieve this?

create table bridge (t1_key number, t2_key number, t1_id number, t2_id number)

The T1.id is natural key.
update bridge
set t1_key = (select key from t1 where t1.id = bridge.id)
where exists (select key from t1 where t1.id = bridge.id)

The T2.id is natural key.
update bridge
set t2_key = (select key from t2 where t2.id = bridge.id)
where exists (select key from t2 where t2.id = bridge.id)
Tom Kyte
December 09, 2008 - 12:15 pm UTC

I don't know what a "bridge" table is - is this like an association table used in a many to many relationship?

If so, I don't understand yours.

typically it would be:

table t1 ( x int primary key, <other columns> );
table t2 ( y int primary key, <other columns> );

table t1_t2 ( x int, y int, primary key(x,y) );


Update for bridge table

daily reader, December 09, 2008 - 1:36 pm UTC

You understood me correctly bridge/map table is the one that joins many-to-many T1 and T2 tables. That table has 2 natural keys that come from transactional system in data mart however I need to assign new surrogate keys, doesn't it?

So I extracted changed data from previous day and I need to generate new set of data mart keys. First I generate new keys for table T1 using sequence then for T2 as a last step I need to update mapping table with those keys.

How then can I update table t1_t2 mapping table with new keys from both parents?
Tom Kyte
December 09, 2008 - 2:43 pm UTC

you'll need to expand on this data model more.

I don't know why you would generate new keys???

I don't get the two keys - no.

update with its own record

just reader, December 11, 2008 - 4:12 pm UTC

Tom,

I have case when I need update row in dim table with value from new arriving row for the same id from another table.

I need to update t_dim.row_expire_date with t_stg.row_effective_date. The ID in both tables is natural key.

I get ORA-01779 for the following update but I don't know way around it. Can you please help?

UPDATE ( select dim.row_expire_date dim_expire_date,
stg.row_effective_date stg_effective_date
from t_stg stg, t_dim dim
where stg.id = dim.id
and dim.row_expire_date = to_date('12/31/9999','mm/dd/yyyy')
)
SET dim_expire_date = stg_effective_date

Set up:

create table t_dim (id integer, row_effective_date date, row_expire_date date);

create table t_stg (id integer, row_effective_date date);

insert into t_dim values (1, sysdate-5, sysdate-4);

insert into t_dim values (1, sysdate-4, to_date('12/31/9999','mm/dd/yyyy' ));

insert into t_dim values (2, sysdate-3, sysdate-2);

insert into t_dim values (2, sysdate-2, to_date('12/31/9999','mm/dd/yyyy' ));

insert into t_stg values (1, sysdate);
insert into t_stg values (2, sysdate);




Tom Kyte
December 11, 2008 - 9:15 pm UTC

alter table t_stg add constraint id_must_be_unique unique(id);


ops$tkyte%ORA10GR2> alter table t_stg add constraint id_must_be_unique unique(id);

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>  UPDATE ( select dim.row_expire_date dim_expire_date,
  2                           stg.row_effective_date stg_effective_date
  3                      from t_stg stg, t_dim dim
  4                     where stg.id = dim.id
  5                       and dim.row_expire_date = to_date('12/31/9999','mm/dd/yyyy')
  6                   )
  7              SET dim_expire_date = stg_effective_date
  8  /

2 rows updated.

update with its own record

just reader, December 12, 2008 - 12:51 pm UTC

Simply great! However I have followup question.

Why if I run following query it works without unique constraint but achieves the same result? This will help me understand semantic difference between two updates.

update t_dim
set row_expire_date = (select row_effective_date
from t_stg
where t_dim.id = id
and row_expire_date = to_date('12/31/9999','mm/dd/yyyy'))
where exists (select row_effective_date
from t_stg
where t_dim.id = id
and row_expire_date = to_date('12/31/9999','mm/dd/yyyy'));






Tom Kyte
December 12, 2008 - 4:11 pm UTC

they are semantically equivalent ASSUMING ID IS UNIQUE IN STG.

but this one will fail if id is duplicated in t_stg - the other one prevents id from being duplicated.

update statement

Aru, January 27, 2010 - 1:45 pm UTC

Hi Tom,
Nice thread this.
I began reading this thread and have come to the conclusion that I really need to understand the internals of an Update statement first. Is it fair to say that 'an update against a column in a table (without indexes) is essentially an delete followed by an insert', or am I totally wrong in my assumption?
Regards,
Aru.
Tom Kyte
January 29, 2010 - 3:19 pm UTC

totally wrong.

It would be an update of the existing data, in place - in general.


exception: partitioned table whereby you update the partition key and cause a row to move from partition 1 to partition 2. We do under the covers delete and reinsert the row.

exception: update an IOT and update the primary key, We do under the covers delete and reinsert the row.

Update taking time

GM, March 10, 2010 - 11:07 pm UTC

Hi Tom,

This query updates 300k rec or sometimes 500k rec in the below table, we use J2EE application which has a limit of 900 seconds as timeout, every time the process fails and we will resubmit the process sometime it passes through. Any suggestion, how we go about this.

araccountinginformation has composite index with ardetail_id and accountinginformation_id.

UPDATE araccountinginformation
SET araccountinginformation.ardetail_id = 5787
WHERE araccountinginformation_id IN (SELECT /*+ FIRST_ROWS ORDERED */ arai.araccountinginformation_id
FROM araccountinginformation arai
,accountinginformation ai
WHERE 1=1
AND ai.accountinginformationtype_id = 8 -- WP
AND ai.partnercode||'' = '00013559000'
AND arai.accountinginformation_id = ai.accountinginformation_id
AND arai.ardetail_id = -1
AND ai.trxndate+0 <= sysdate);

Thanx for your Answer...
Tom Kyte
March 11, 2010 - 8:27 am UTC

lose the hints would be first and foremost. first rows in an update??

secondly, ask them "what the heck is up with AND ai.partnercode||'' = '00013559000' , why are you using a trick from 1990 in code written in 2010??? Same with AND ai.trxndate+0 <= sysdate. Did you read a really old book on the rule based optimizer?"


thirdly, ask them "do you really think it is reasonable to do a obviously BATCH oriented thing in an interactive application?" (the answer to that is "ummm, no, of course not, you cannot do something that is going to take more than a few seconds interactively, you need to approach this entirely differently - obviously")


They should background a batch process. No one wants to sit for 900 plus seconds. But, after they fix the query by taking out the hints and by removing the extra work ||'' and +0 - they might find it runs much faster.

Update taking time

GM, March 11, 2010 - 11:55 am UTC

Hi Tom,

first one - I accept with you, first_rows & ordered hint has to be removed, find the modified query below which has been in proposal.

second one - The age old code technique is used and they have the reason why they added, it has FK type index on those columns and to avoid FK they added

third one - we also suggested this putting these update as BATCH, putting this as a BATCH will have a split on the module level


UPDATE araccountinginformation
SET araccountinginformation.ardetail_id = 5787
WHERE araccountinginformation_id IN (SELECT /*+ USE_NL(arai,ai) */ arai.araccountinginformation_id
FROM araccountinginformation arai
,accountinginformation ai
WHERE 1=1
AND ai.accountinginformationtype_id = 8 -- WP
AND ai.partnercode||'' = '00013559000'
AND arai.accountinginformation_id = ai.accountinginformation_id
AND arai.ardetail_id = -1
AND ai.trxndate+0 <= sysdate);

After these changes it has improvement but not much gained, any other way to make the stmt faster, ... thanx again.
Tom Kyte
March 12, 2010 - 2:28 pm UTC

how do you 'avoid' a foreign key??

If you agreed with me on the hints, you should agree with me on this 'technique' which is a "hinting technique used with the rule based optimizer". Don't do it.



I'll not look at this further until you get the query the way it should be. get rid of the +0, remote the ||''.

Update taking time

A reader, March 12, 2010 - 3:14 pm UTC

Hi Tom,

Here is the Explain plan, where the cost varies btwn the old technique and the without the || & +0. There are few more queries written in the same way and more interesting thing is cost varies btwn the 2 queries and also execution time is faster with || & +0...

UPDATE araccountinginformation
SET araccountinginformation.ardetail_id = 5787
WHERE araccountinginformation_id IN (SELECT /*+ USE_NL(arai,ai) */ arai.araccountinginformation_id
FROM araccountinginformation arai
,accountinginformation ai
WHERE 1=1
AND ai.accountinginformationtype_id = 8 -- WP
AND ai.partnercode||'' = '00013559000'
AND arai.accountinginformation_id = ai.accountinginformation_id
AND arai.ardetail_id = -1
AND ai.trxndate+0 <= sysdate);
------------------------------------------------------------

Statement Id=10 Type=INDEX
Cost=1 TimeStamp=12-03-10::12::52:50

(1) UPDATE STATEMENT CHOOSE
Est. Rows: 559 Cost: 7,424
UPDATE R2PA02.ARACCOUNTINGINFORMATION
(10) NESTED LOOPS
Est. Rows: 559 Cost: 7,424
(8) VIEW VIEW SYS.VW_NSO_1
Est. Rows: 559 Cost: 7,198
(7) SORT UNIQUE
Est. Rows: 559
(6) NESTED LOOPS
Est. Rows: 559 Cost: 7,198
(3) TABLE TABLE ACCESS BY INDEX ROWID R2PA02.ARACCOUNTINGINFORMATION [Analyzed]
(3) Blocks: 32,104 Est. Rows: 17,817 of 21,861,242 Cost: 27
Tablespace: DATA
(2) INDEX INDEX RANGE SCAN R2PA02.ARACCINFO_ARDETAIL_ID_IDX [Analyzed]
Est. Rows: 17,817 Cost: 4
(5) TABLE TABLE ACCESS BY GLOBAL INDEX ROWID R2PA02.ACCOUNTINGINFORMATION [Analyzed]
Blocks: 1,484,055 Est. Rows: 1 of 167,593,129 Cost: 1
(4) INDEX (UNIQUE) INDEX UNIQUE SCAN R2PA02.ACCOUNTINGINFORMATION_PK [Analyzed]
Est. Rows: 1 Cost: 1
(9) INDEX (UNIQUE) INDEX UNIQUE SCAN R2PA02.ARACCOUNTINGINFORMATION_PK [Analyzed]
Est. Rows: 1 Cost: 1



UPDATE araccountinginformation
SET araccountinginformation.ardetail_id = 5787
WHERE araccountinginformation_id IN (SELECT /*+ USE_NL(arai,ai) */ arai.araccountinginformation_id
FROM araccountinginformation arai
,accountinginformation ai
WHERE 1=1
AND ai.accountinginformationtype_id = 8 -- WP
AND ai.partnercode = '00013559000'
AND arai.accountinginformation_id = ai.accountinginformation_id
AND arai.ardetail_id = -1
AND ai.trxndate <= sysdate);
------------------------------------------------------------

Statement Id=10 Type=INDEX
Cost=1 TimeStamp=12-03-10::12::53:13

(1) UPDATE STATEMENT CHOOSE
Est. Rows: 11,187 Cost: 11,703
UPDATE R2PA02.ARACCOUNTINGINFORMATION
(10) NESTED LOOPS
Est. Rows: 11,187 Cost: 11,703
(8) VIEW VIEW SYS.VW_NSO_1
Est. Rows: 11,187 Cost: 7,198
(7) SORT UNIQUE
Est. Rows: 11,187
(6) NESTED LOOPS
Est. Rows: 11,187 Cost: 7,198
(3) TABLE TABLE ACCESS BY INDEX ROWID R2PA02.ARACCOUNTINGINFORMATION [Analyzed]
(3) Blocks: 32,104 Est. Rows: 17,817 of 21,861,242 Cost: 27
Tablespace: DATA
(2) INDEX INDEX RANGE SCAN R2PA02.ARACCINFO_ARDETAIL_ID_IDX [Analyzed]
Est. Rows: 17,817 Cost: 4
(5) TABLE TABLE ACCESS BY GLOBAL INDEX ROWID R2PA02.ACCOUNTINGINFORMATION [Analyzed]
Blocks: 1,484,055 Est. Rows: 1 of 167,593,129 Cost: 1
(4) INDEX (UNIQUE) INDEX UNIQUE SCAN R2PA02.ACCOUNTINGINFORMATION_PK [Analyzed]
Est. Rows: 1 Cost: 1
(9) INDEX (UNIQUE) INDEX UNIQUE SCAN R2PA02.ARACCOUNTINGINFORMATION_PK [Analyzed]
Est. Rows: 1 Cost: 1

Select statement with AR...

SQL Statement from editor:


select * from araccountinginformation ar where araccountinginformation_id in (
SELECT /*+ FIRST_ROWS ORDERED */ arai.araccountinginformation_id
FROM araccountinginformation arai
,accountinginformation ai
WHERE 1=1
AND ai.accountinginformationtype_id+0 = 8 -- WP
AND ai.partnercode||'' = '00013559000'
AND arai.accountinginformation_id = ai.accountinginformation_id
AND arai.ardetail_id = -1
AND ai.trxndate+0 <= to_date('02/12/2010 12:00:00','MM/DD/YYYY H24:MI:SS'))
------------------------------------------------------------

Statement Id=9 Type=TABLE ACCESS
Cost=1 TimeStamp=12-03-10::13::09:18

(1) SELECT STATEMENT CHOOSE
Est. Rows: 42,889 Cost: 227,153
(11) NESTED LOOPS
Est. Rows: 42,889 Cost: 227,153
(8) VIEW VIEW SYS.VW_NSO_1
Est. Rows: 42,889 Cost: 209,788
(7) HASH UNIQUE
Est. Rows: 42,889
(6) NESTED LOOPS
Est. Rows: 42,889 Cost: 209,788
(3) TABLE TABLE ACCESS BY INDEX ROWID R2PP02.ARACCOUNTINGINFORMATION [Analyzed]
(3) Blocks: 47,704 Est. Rows: 520,312 of 26,431,870 Cost: 834
Tablespace: DATA
(2) INDEX INDEX RANGE SCAN R2PP02.ARACCINFO_ARDETAIL_ID_IDX [Analyzed]
Est. Rows: 530,110 Cost: 159
(5) TABLE TABLE ACCESS BY GLOBAL INDEX ROWID R2PP02.ACCOUNTINGINFORMATION [Analyzed]
Blocks: 1,740,998 Est. Rows: 1 of 197,385,540 Cost: 1
(4) INDEX (UNIQUE) INDEX UNIQUE SCAN R2PP02.ACCOUNTINGINFORMATION_PK [Analyzed]
Est. Rows: 1 Cost: 1
(10) TABLE TABLE ACCESS BY INDEX ROWID R2PP02.ARACCOUNTINGINFORMATION [Analyzed]
(10) Blocks: 47,704 Est. Rows: 1 of 26,431,870 Cost: 1
Tablespace: DATA
(9) INDEX (UNIQUE) INDEX UNIQUE SCAN R2PP02.ARACCOUNTINGINFORMATION_PK [Analyzed]
Est. Rows: 1 Cost: 1


SQL Statement from editor:


select * from araccountinginformation ar where araccountinginformation_id in (
SELECT /*+ FIRST_ROWS ORDERED */ arai.araccountinginformation_id
FROM araccountinginformation arai
,accountinginformation ai
WHERE 1=1
AND ai.accountinginformationtype_id+0 = 8 -- WP
AND ai.partnercode = '00013559000'
AND arai.accountinginformation_id = ai.accountinginformation_id
AND arai.ardetail_id = -1
AND ai.trxndate+0 <= to_date('02/12/2010 12:00:00','MM/DD/YYYY H24:MI:SS'))
------------------------------------------------------------

Statement Id=9 Type=TABLE ACCESS
Cost=1 TimeStamp=12-03-10::13::10:21

(1) SELECT STATEMENT CHOOSE
Est. Rows: 42,889 Cost: 101,010
(11) NESTED LOOPS
Est. Rows: 42,889 Cost: 101,010
(8) VIEW VIEW SYS.VW_NSO_1
Est. Rows: 42,889 Cost: 83,645
(7) HASH UNIQUE
Est. Rows: 42,889
(6) HASH JOIN
Est. Rows: 42,889 Cost: 83,645
(3) TABLE TABLE ACCESS BY INDEX ROWID R2PP02.ARACCOUNTINGINFORMATION [Analyzed]
(3) Blocks: 47,704 Est. Rows: 520,312 of 26,431,870 Cost: 834
Tablespace: DATA
(2) INDEX INDEX RANGE SCAN R2PP02.ARACCINFO_ARDETAIL_ID_IDX [Analyzed]
Est. Rows: 530,110 Cost: 159
(5) TABLE TABLE ACCESS BY GLOBAL INDEX ROWID R2PP02.ACCOUNTINGINFORMATION [Analyzed]
Blocks: 1,740,998 Est. Rows: 42,889 of 197,385,540 Cost: 82,122
(4) INDEX INDEX RANGE SCAN R2PP02.ACCTINFO_PTNRCODE_TRXNDATE_IDX [Analyzed]
Est. Rows: 615,473 Cost: 12,112
(10) TABLE TABLE ACCESS BY INDEX ROWID R2PP02.ARACCOUNTINGINFORMATION [Analyzed]
(10) Blocks: 47,704 Est. Rows: 1 of 26,431,870 Cost: 1
Tablespace: DATA
(9) INDEX (UNIQUE) INDEX UNIQUE SCAN R2PP02.ARACCOUNTINGINFORMATION_PK [Analyzed]
Est. Rows: 1 Cost: 1

Please see the difference in Explain plan varies when we use || and when we don't use || for same query and also the execution time varies by 3 times...

--Thanx



Tom Kyte
March 12, 2010 - 4:23 pm UTC

I don't care about plans, I care about "what happens in real life"

Show me a tkprof along with the plans (are the estimated cardinalities way off - if so, we need to fix that)

and watch the word wrapping - try to make things fit so we can read them- thanks.

Update taking time

GM, March 17, 2010 - 3:23 pm UTC

Hi Tom,

I am back, took long time to get the tkprof... here is it

select * from araccountinginformation ar where araccountinginformation_id in (
SELECT /*+ FIRST_ROWS ORDERED */ arai.araccountinginformation_id
FROM araccountinginformation arai
,accountinginformation ai
WHERE 1=1
AND ai.accountinginformationtype_id+0 = 8 -- WP
AND ai.partnercode = '00013559000'
AND arai.accountinginformation_id = ai.accountinginformation_id
AND arai.ardetail_id = -1
AND ai.trxndate+0 <= sysdate)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3258 32.31 186.36 46152 3625775 0 48843
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3260 32.33 186.38 46152 3625775 0 48843

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 37 (XXXXX)

Rows Row Source Operation
------- ---------------------------------------------------
48843 NESTED LOOPS (cr=3625775 pr=46152 pw=0 time=185798995 us)
48843 VIEW VW_NSO_1 (cr=3474589 pr=46044 pw=0 time=184745548 us)
48843 HASH UNIQUE (cr=3474589 pr=46044 pw=0 time=184696701 us)
48843 NESTED LOOPS (cr=3474589 pr=46044 pw=0 time=52104450 us)
790715 TABLE ACCESS BY INDEX ROWID ARACCOUNTINGINFORMATION (cr=33076 pr=4133 pw=0 time=22140102 us)
790715 INDEX RANGE SCAN ARACCINFO_ARDETAIL_ID_IDX (cr=30067 pr=2106 pw=0 time=14232933 us)(object id 86137)
48843 TABLE ACCESS BY GLOBAL INDEX ROWID ACCOUNTINGINFORMATION PARTITION: ROW LOCATION ROW LOCATION (cr=3441513 pr=41911 pw=0 time=163505564 us)
790715 INDEX UNIQUE SCAN ACCOUNTINGINFORMATION_PK (cr=2650797 pr=12261 pw=0 time=44386031 us)(object id 39887)
48843 TABLE ACCESS BY INDEX ROWID ARACCOUNTINGINFORMATION (cr=151186 pr=108 pw=0 time=1435690 us)
48843 INDEX UNIQUE SCAN ARACCOUNTINGINFORMATION_PK (cr=102343 pr=108 pw=0 time=1043431 us)(object id 111989)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: CHOOSE
48843 NESTED LOOPS
48843 VIEW OF 'VW_NSO_1' (VIEW)
48843 HASH (UNIQUE)
48843 NESTED LOOPS
790715 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'ARACCOUNTINGINFORMATION' (TABLE)
790715 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ARACCINFO_ARDETAIL_ID_IDX' (INDEX)
48843 TABLE ACCESS MODE: ANALYZED (BY GLOBAL INDEX ROWID) OF
'ACCOUNTINGINFORMATION' (TABLE) PARTITION:ROW LOCATION
790715 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'ACCOUNTINGINFORMATION_PK' (INDEX (UNIQUE))
48843 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'ARACCOUNTINGINFORMATION' (TABLE)
48843 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'ARACCOUNTINGINFORMATION_PK' (INDEX (UNIQUE))


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3258 0.00 0.00
db file sequential read 46152 0.84 124.48
gc cr grant 2-way 22065 0.26 23.50
gc cr grant congested 234 0.27 3.91
gc current block 2-way 22 0.00 0.01
gc current block 3-way 4 0.00 0.00
gc current grant 2-way 416 0.00 0.25
latch free 14 0.00 0.00
latch: gcs resource hash 7 0.00 0.00
gc current grant congested 3 0.00 0.01
SQL*Net message from client 3258 70.23 73.82
********************************************************************************

select * from araccountinginformation ar where araccountinginformation_id in (
SELECT /*+ FIRST_ROWS ORDERED */ arai.araccountinginformation_id
FROM araccountinginformation arai
,accountinginformation ai
WHERE 1=1
AND ai.accountinginformationtype_id+0 = 8 -- WP
AND ai.partnercode||'' = '00013559000'
AND arai.accountinginformation_id = ai.accountinginformation_id
AND arai.ardetail_id = -1
AND ai.trxndate+0 <= sysdate)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3258 15.78 16.73 1 3318037 0 48843
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3260 15.80 16.75 1 3318037 0 48843

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 37 (XXXXX)

Rows Row Source Operation
------- ---------------------------------------------------
48843 NESTED LOOPS (cr=3318037 pr=1 pw=0 time=16312090 us)
48843 VIEW VW_NSO_1 (cr=3168250 pr=1 pw=0 time=15530530 us)
48843 HASH UNIQUE (cr=3168250 pr=1 pw=0 time=15481684 us)
48843 NESTED LOOPS (cr=3168250 pr=1 pw=0 time=3947663 us)
790715 TABLE ACCESS BY INDEX ROWID ARACCOUNTINGINFORMATION (cr=5390 pr=0 pw=0 time=1581543 us)
790715 INDEX RANGE SCAN ARACCINFO_ARDETAIL_ID_IDX (cr=2381 pr=0 pw=0 time=83 us)(object id 86137)
48843 TABLE ACCESS BY GLOBAL INDEX ROWID ACCOUNTINGINFORMATION PARTITION: ROW LOCATION ROW LOCATION (cr=3162860 pr=1 pw=0 time=13664256 us)
790715 INDEX UNIQUE SCAN ACCOUNTINGINFORMATION_PK (cr=2372145 pr=0 pw=0 time=8403856 us)(object id 39887)
48843 TABLE ACCESS BY INDEX ROWID ARACCOUNTINGINFORMATION (cr=149787 pr=0 pw=0 time=965626 us)
48843 INDEX UNIQUE SCAN ARACCOUNTINGINFORMATION_PK (cr=100944 pr=0 pw=0 time=540527 us)(object id 111989)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: CHOOSE
48843 NESTED LOOPS
48843 VIEW OF 'VW_NSO_1' (VIEW)
48843 HASH (UNIQUE)
48843 NESTED LOOPS
790715 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'ARACCOUNTINGINFORMATION' (TABLE)
790715 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ARACCINFO_ARDETAIL_ID_IDX' (INDEX)
48843 TABLE ACCESS MODE: ANALYZED (BY GLOBAL INDEX ROWID) OF
'ACCOUNTINGINFORMATION' (TABLE) PARTITION:ROW LOCATION
790715 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'ACCOUNTINGINFORMATION_PK' (INDEX (UNIQUE))
48843 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'ARACCOUNTINGINFORMATION' (TABLE)
48843 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'ARACCOUNTINGINFORMATION_PK' (INDEX (UNIQUE))


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3258 0.00 0.01
gc cr grant 2-way 1 0.00 0.00
db file sequential read 1 0.00 0.00
SQL*Net message from client 3258 13.17 16.92
library cache lock 1 0.00 0.00
row cache lock 1 0.00 0.00
********************************************************************************

Let me know what we can do on this, not sure abt formatting this is right or wrong.

Thanx - GM
Tom Kyte
March 18, 2010 - 7:48 am UTC

lose the hints will you - do it again, without the hints. We already talked about the hints, you are forcing things to happen, stop doing that.

Update atatement.

M.Anbuselvam., March 21, 2010 - 2:40 am UTC

Hi Tom,
My Update statement taking to long to run . i tired all the possibilites . i need assistance to tune the following quey.

UPDATE smi_sale_fact sald
SET sald.mat_usd_amt =
(SELECT bmat_usd_amt
FROM (SELECT safd.prod_surro_id
, safd.panel_surro_id
, safd.annl_qtr_pd_id
, safd.annl_mth_pd_id
, safd.curr_surro_id
, safd.ctry_cd
-- , safd.LCURR_AMT
, (SUM (safs.lcurr_amt * safs.usd_exch_rt_fct) OVER (PARTITION BY safd.ctry_cd, safd.curr_surro_id, safd.panel_surro_id, safd.prod_surro_id ORDER BY TO_DATE
( SUBSTR (safd.annl_qtr_pd_id
, 1
, 4
)
|| TO_NUMBER
(SUBSTR (safd.annl_qtr_pd_id
, 5
, 1
))
* 3
, 'YYYYMM') ASC RANGE INTERVAL '9' MONTH PRECEDING)
) bmat_usd_amt
FROM (SELECT DISTINCT prod_surro_id
, panel_surro_id
, curr_surro_id
, ctry_cd
, annl_qtr_pd_id
, annl_mth_pd_id
FROM smi_sale_fact) safd
, (SELECT DISTINCT prod_surro_id
, panel_surro_id
, curr_surro_id
, ctry_cd
, annl_qtr_pd_id
, usd_exch_rt_fct
, SUM (lcurr_amt) OVER (PARTITION BY prod_surro_id, panel_surro_id, curr_surro_id, ctry_cd, annl_qtr_pd_id)
lcurr_amt
FROM smi_s_sale_fact
-- WHERE src_sys_nm = smi_usk_global_parameters.g_gca_429_src_sys_nm
) safs
WHERE safd.prod_surro_id = safs.prod_surro_id
AND safd.panel_surro_id = safs.panel_surro_id
AND safd.annl_qtr_pd_id = safs.annl_qtr_pd_id
AND safd.curr_surro_id = safs.curr_surro_id
AND safd.panel_surro_id = safs.panel_surro_id
AND safd.ctry_cd = safs.ctry_cd) safc
WHERE sald.prod_surro_id = safc.prod_surro_id
AND sald.panel_surro_id = safc.panel_surro_id
AND sald.annl_qtr_pd_id = safc.annl_qtr_pd_id
AND sald.curr_surro_id = safc.curr_surro_id
AND sald.ctry_cd = safc.ctry_cd)
WHERE EXISTS (
SELECT 1
FROM smi_s_sale_fact bc
WHERE sald.prod_surro_id = bc.prod_surro_id
AND sald.panel_surro_id = bc.panel_surro_id
AND sald.annl_qtr_pd_id = bc.annl_qtr_pd_id
AND sald.curr_surro_id = bc.curr_surro_id
AND sald.ctry_cd = bc.ctry_cd
-- AND src_sys_nm = smi_usk_global_parameters.g_gca_429_src_sys_nm
);


Thanks,
M.Anbuselvam.

Update taking more than 30 mins

A reader, September 30, 2011 - 2:42 am UTC


update statement which was running in seconds in the past now taking 30+ mins. please suggest a solution. thanks in advance.

EXPLAIN PLAN for
UPDATE table_1
SET
(
table_1.HIER1_TYPE_ID ,
table_1.HIER1_TYPE_DESC ,
table_1.HIER1_LEVEL1_ID ,
table_1.HIER1_LEVEL1_DESC ,
table_1.HIER1_LEVEL2_ID ,
table_1.HIER1_LEVEL2_DESC ,
table_1.HIER1_LEVEL3_ID ,
table_1.HIER1_LEVEL3_DESC ,
table_1.HIER1_LEVEL4_ID ,
table_1.HIER1_LEVEL4_DESC ,
table_1.HIER1_LEVEL5_ID ,
table_1.HIER1_LEVEL5_DESC ,
table_1.HIER1_LEVEL6_ID ,
table_1.HIER1_LEVEL6_DESC ,
table_1.HIER1_LEVEL7_ID ,
table_1.HIER1_LEVEL7_DESC ,
table_1.HIER1_LOWESTLEVEL_ID ,
table_1.HIER1_LOWESTLEVEL_DESC ,
table_1.RF_INSALIAS_ID
)
=
(SELECT HIER.HIER1_TYPE_ID ,
HIER.HIER1_TYPE_DESC ,
NVL(HIER.HIER1_LEVEL1_ID,' ') ,
HIER.HIER1_LEVEL1_DESC ,
NVL(HIER.HIER1_LEVEL2_ID ,' '),
HIER.HIER1_LEVEL2_DESC ,
NVL(HIER.HIER1_LEVEL3_ID ,'0'),
HIER.HIER1_LEVEL3_DESC ,
NVL(HIER.HIER1_LEVEL4_ID ,'0'),
HIER.HIER1_LEVEL4_DESC ,
NVL(HIER.HIER1_LEVEL5_ID ,'0'),
HIER.HIER1_LEVEL5_DESC ,
NVL(HIER.HIER1_LEVEL6_ID ,'0'),
HIER.HIER1_LEVEL6_DESC ,
NVL(HIER.HIER1_LEVEL7_ID ,'0'),
HIER.HIER1_LEVEL7_DESC ,
HIER.HIER1_LOWESTLEVEL_ID ,
HIER.HIER1_LOWESTLEVEL_DESC ,
HIER.REPAIRFACILITY_ID
FROM XTERN_table_2 ALIAS,
XTERN_Table_3 HIER
WHERE ALIAS.RF_INSALIAS_ID=HIER.REPAIRFACILITY_ID
AND table_1.PERSON_ID =ALIAS.RF_REPAIRFACILITY_ID
AND RTRIM(table_1.COMPANY_CODE) = RTRIM(SUBSTR(HIER.HIER1_LEVEL1_ID,1,6))
)
WHERE table_1.COMPANY_CODE IN
(SELECT RTRIM(table_1.COMPANY_CODE)
FROM XTERN_table_3 HIER,
XTERN_table_2 ALIAS
WHERE RTRIM(table_1.COMPANY_CODE)= RTRIM(SUBSTR(HIER.HIER1_LEVEL1_ID,1,6))
AND ALIAS.RF_INSALIAS_ID =HIER.REPAIRFACILITY_ID
AND table_1.PERSON_ID =ALIAS.RF_REPAIRFACILITY_ID
);



explain plan:
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 103 | 4284K (8)| 21:25:20 |
| 1 | UPDATE | table_1 | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | table_1 | 164K| 16M| 509 (5)| 00:00:10 |
|* 4 | FILTER | | | | | |
| 5 | NESTED LOOPS | | 1 | 28 | 27 (8)| 00:00:01 |
|* 6 | EXTERNAL TABLE ACCESS FULL| XTERN_table_2 | 1 | 16 | 7 (0)| 00:00:01 |
|* 7 | EXTERNAL TABLE ACCESS FULL| XTERN_table_3 | 1 | 12 | 20 (10)| 00:00:01 |
|* 8 | HASH JOIN | | 1 | 174 | 27 (8)| 00:00:01 |
|* 9 | EXTERNAL TABLE ACCESS FULL | XTERN_table_2 | 1 | 16 | 7 (0)| 00:00:01 |
|* 10 | EXTERNAL TABLE ACCESS FULL | XTERN_table_3 | 68 | 10744 | 19 (6)| 00:00:01 |

Tom Kyte
September 30, 2011 - 6:26 pm UTC

what changed?

do you have AWR/ASH information - it would be illuminating.

Something has changed - somebody might be blocking you (you would see that in ASH), the volume of data might have changed (ASH again), the plan might be radically different (ASH...)

Unless you know what it was doing when it was "good", it is pretty neigh impossible to say what changed, what caused the performance regression.

So - please say you have some historical data to look at?


Do the external tables have representative stats on them?

Update restriction

Ashish, May 09, 2012 - 3:46 am UTC

Hello,

Is there any way to get mail notification if user will update any of its schema table?

I dont want to set audit and also dont want to create trigger on every table cause i have 300 tables in that schema.

Regards,
Ashish


Tom Kyte
May 10, 2012 - 3:13 am UTC

I dont want to set audit and also dont want to create trigger on every table
cause i have 300 tables in that schema.


why not? why wouldn't you want to do something that would be necessary to accomplish what you are wanting to do?

I want to be skinny, but I don't want to exercise or stop eating a lot. Can you help? :)


I would suggest

a) audit
b) have a job query the audit trail every now and then and send you an alert

twice or once?

A reader, May 29, 2012 - 2:52 am UTC

Hi tom,

Per your first reply as below:
'The reason (probably) this is taking long is because the update above is forced to
FULL scan T1 and for every row, run the where exists query and then do it again if it
succeeds and then do the update'

I noticed the word 'probably', but still tried to test whether it will happen.
But got no lucky, below is my test script and could you please help on those question
embedded in below script?

create table test1(id int, name varchar2(10));
insert into test1 select rownum, 'a'||rownum from dual connect by level<=5;
create table test2 as select id, upper(name) name from test1 where rownum<=4;
create or replace function fun1 return number as
begin
dbms_output.put_line('hello');
return 1;
end;
/

update test1
set name = ( select name from test2 where test2.id = test1.id )
where exists ( select name from test2 where test2.id = test1.id and fun1=1 );
-- update 4 rows, print out 1 'hello'
-- it means fun1 was called once, it is due to query rewrite it to 'in'?

update test1
set name = ( select name from test2 where test2.id = test1.id and fun1=1)
where exists ( select name from test2 where test2.id = test1.id and fun1=1 );
-- update 4 rows, print out 5 'hello'
-- i guess 1 is for 'where' and the other 4 are for 'set', am i right?

insert into test2 select rownum+4, 'a'||rownum from dual connect by level<=90000;
update test1
set name = ( select name from test2 where test2.id = test1.id and fun1=1)
where exists ( select name from test2 where test2.id = test1.id and fun1=1 );
-- update 5 rows, print out 6 'hello'
-- seems 1 is for 'where' and the other 5 are for 'set', still rewrite it to 'in'?
-- per my knowledge, when outer>>inner it will use 'in', when inner>>outer it will use 'exist', how comes above result?

then what is the best way to demonstrate the same query in 'set' and 'where exists' will be executed twice?
Tom Kyte
May 30, 2012 - 12:42 am UTC

I noticed the word 'probably', but still tried to test whether it will happen.


all you would need is a tkprof with the row source operation report. And remember, when this was originally written - the version used was...

7.3 (pre-1995 software!)

In the meantime - since then - many things have changed - scalar subquery caching for example:

http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html


so, using a larger example:

ops$tkyte%ORA11GR2> create table t1
  2  as
  3  select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t2
  2  as
  3  select * from all_users;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table t2 add constraint t2_pk primary key(username);

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> @trace
ops$tkyte%ORA11GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA11GR2> update t1
  2     set object_id = (select user_id from t2 where t2.username = t1.owner)
  3   where exists (select user_id from t2 where t2.username = t1.owner)
  4  /

44689 rows updated.



update t1
   set object_id = (select user_id from t2 where t2.username = t1.owner)
 where exists (select user_id from t2 where t2.username = t1.owner)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          4          0           0
Execute      1      0.44       0.78        676       1121      45779       44689
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.45       0.79        676       1125      45779       44689

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 840
Number of plan statistics captured: 1

Row Source Operation
---------------------------------------------------

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  -------------------------
         0          0          0  UPDATE  T1 (cr=1133 pr=676 pw=0 time=786412 us
     44689      44689      44689   HASH JOIN RIGHT SEMI (cr=1043 pr=676 pw=0 tim
        50         50         50    INDEX FULL SCAN T2_PK (cr=1 pr=0 pw=0 time=6
     72803      72803      72803    TABLE ACCESS FULL T1 (cr=1042 pr=676 pw=0 ti
        37         37         37   TABLE ACCESS BY INDEX ROWID T2 (cr=74 pr=0 p
        37         37         37    INDEX UNIQUE SCAN T2_PK (cr=37 pr=0 pw=0 t




that shows it turned the one where exists into a semi join (it updated the join - something 7.3 wasn't able to transform a query into) but then ran the scalar subquery to get the value just a few times

update slow performance

Eric, June 29, 2012 - 7:12 pm UTC

Hi Tom,

We have an update statement that looks similar to the below.

update tab1 t1 set col1='TEST',col2=(select t3.col1 from tab2 t2,tab3 t3 where t3.col='CHECK' and t3.col4=t2.col4 and t2.col3=t1.col3) where t1.col5=4 and t1.col6='TS';

tab1 & tab2 has around 12M rows. tab3 has 100K records.

Problem is for every row returned by t1 it's doing a nested loop of the subquery and running very long(around 2hours). It's updating around 4M rows.

Could you please provide some suggestions on how to rewrite this query? DB version is 11gR2
Tom Kyte
July 02, 2012 - 6:59 am UTC

if you are updating 4million rows out of 12million, it is HIGHLY likely (almost 100% probable here) that you don't want to update.

Think about it - you are going to hit pretty much every single block in the table you are updating. You will have to read that block into the SGA, generate undo and redo for that block (meaning your cache is filling up fast - table blocks, undo blocks), you will have to write that all back to disk...


Why not

a) alter table old read only
b) create table as select (new data), something like:

create table new_t1
as
select case when t1.col5=4 and t1.col6='TS' then 'TEST' else t1.col1 end col1, 
       case when t1.col5=4 and t1.col6='T2' then X.t3_col1 else t1.col2 end col2,
       t1.col3, t1.col4, t1.col5, ....
  from t1, (select t3.col1 t3_col1, t2.col3 t2_col3
              from t2, t3 
             where t3.col = 'CHECK'
               and t3.col4 = t2.col4 ) X
 where t1.col3 = X.t2_col3(+)
/


c) index/constraint/grant/etc
d) drop old
e) rename new to old


parallel in update

Rahul, September 05, 2012 - 9:05 am UTC

I am using update (select col1,col2 from T1 ,t2) set
col1=col2;

Initially I thought it will be 50 K but now its coming like 5 Million

So my question is can I go for 5 Million records
and how can I put parallel on this.
Tom Kyte
September 10, 2012 - 7:46 pm UTC

I would use ddl

create table new_table as
select col2 col1, col2, col3, .... from old_table;

drop old_table;
index, constraints, grants

skip redo, skip undo, do it in parallel, bypass the buffer cache - direct path all of the way.

update statement

amit, November 12, 2012 - 8:16 pm UTC

Tom
please can you help ,

I need a SQL update statement powerful one which does following things.

fetch column with which has criteria like '______-1%-%' or like '______-2%-%'
and replace it with if existing column value contains
GIGEAC-29-CHCGILCLW60 to be converted to OCXACC-B29-CHCGILCLW60

example 2:- GIGEAC-30-CHCGILCLW60 should be updated or replaced with string OCXACC-B30-CHCGILCLW60 in DB .

it should check first 6 character and replace it with accoring 6character and "-B" ... is this possible in single update query .
Tom Kyte
November 14, 2012 - 8:11 pm UTC

no create
no inserts
no look

your explanation is horrible. example 2 shows the number "3" being relevant. what the heck?? You said clearly "1" and "2"

but think about this, this is trivial. Have you thought about it?

substr( col, 1, 7 ) || 'B' || substr( col, 8 )


you want to insert a B after 7 characters if it matches some pattern.


update all tables

SorryForSQL, November 15, 2012 - 5:00 am UTC

Hello, Tom.
I have a table:
ID NAME OPTION
1 John yes
2 Sam yes
1 Peter yes
1 Pam no
Its possible to make script that take Name with ID=2,then search for data with ID=1 and with the same option and change Name in this rows on Name from row with ID=2 and in other table do the same(change John and Peter on Sam)?


Tom Kyte
November 19, 2012 - 9:23 am UTC

update t set name = (select name from t where id=2) where id=1;


update taking a long time

Nathan, February 19, 2013 - 12:22 am UTC

Hi Tom,

i have the dept,emp table. i added ename coumn in dept table and trying to update ename based on max(rowid) of emp table.
i tried like

update dept d set ename=
(select ename
from emp e
where e.deptno=d.deptno
and e.rowid in
(select max(rowid) from emp group by deptno
)
where exists
(select null from emp e where e.deptno=d.deptno
);

i tried on your way update by view,merge,by using in operator,by using cursor
everything is failing.in realtime the dept table is having 40000 and emp table 400000 records.
and its taking very long time.please suggest me how to reduce the updation time.

regards,
Nathan
Tom Kyte
February 25, 2013 - 8:12 am UTC

if you had an index on e.deptno, even on a slow system - this should only take a few seconds.


I wouldn't use your approach, it would be more like:


ops$tkyte%ORA11GR2> alter table dept rename to old_dept;

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table dept
  2  as
  3  select d.*, e.ename
  4    from old_dept d,
  5         (select deptno, ename
  6            from (select deptno, ename, row_number() over (partition by deptno order by rowid DESC) rn
  7                    from emp
  8                 )
  9          where rn = 1 ) e
 10   where d.deptno = e.deptno(+)
 11  /

Table created.

ops$tkyte%ORA11GR2> select * from dept;

    DEPTNO DNAME          LOC           ENAME
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK      MILLER
        20 RESEARCH       DALLAS        FORD
        30 SALES          CHICAGO       JAMES
        40 OPERATIONS     BOSTON





if you are going to update every row in a table, just create it - no undo, you can skip redo, big bulk operation, direct path, no row migrations, and so on.


if you really truly want to do it the wrong way, the inefficient way, they way that should not be done - you can use merge easily with bulk operations


ops$tkyte%ORA11GR2> alter table dept add ename varchar2(30);

Table altered.

ops$tkyte%ORA11GR2> lock table dept in exclusive mode;

Table(s) Locked.


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> merge into dept d
  2  using
  3         (select deptno, ename
  4            from (select deptno, ename, row_number() over (partition by deptno order by rowid DESC) rn
  5                    from emp
  6                 )
  7          where rn = 1 ) e
  8  on (d.deptno = e.deptno)
  9  when matched then update set ename = e.ename;

3 rows merged.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from dept;

    DEPTNO DNAME          LOC           ENAME
---------- -------------- ------------- ------------------------------
        10 ACCOUNTING     NEW YORK      MILLER
        20 RESEARCH       DALLAS        FORD
        30 SALES          CHICAGO       JAMES
        40 OPERATIONS     BOSTON


Very informative!!!!

A reader, July 04, 2013 - 5:18 am UTC

Tom,

I would like to know your thinking process on your thoughts on this process of "integration".

requirements:
to insert or update in bulk not row-by-row
the records from let's say table student into
table called integration

this is a case here: (but it can be 100 tables in this sets and would like to
do it all in one step!)


table_name column datatype primarykey fkeys
----------------------------------------------------------------------
student column1 varchar2 pk
student column2 varchar2 fk
student column3 number
student column4 date
principal column1 varchar2 pk
principal column2 varchar2 fk
principal column3 number
school column4 date
school column1 varchar2 pk
school column2 varchar2 fk
school column3 number
school column4 date



this is how I am doing it now:
-------------------------------

insert into integration values ( first_name, last_name, district_number, date_registered)
select column1, column2, column3, column4
from table_name = 'student';


Thanks for your help before hands!!


/Elena

TKPROF of simple Update statement along with waits

John, March 01, 2022 - 5:57 am UTC

Thanks for your time and help for the oracle community.

This is RAC database and is running on Oracle 19.11.0.0 with compatibility of 12.2.0.0. After upgrade to 19.11 from 11.2.0.4, application reports that the batch process run time has considerably increased. Application uses RAC service to connect to database and service is configured to run on both Instance 1 and 2. I traced the session with 10046 trace and TKPROF is below- and some of the update statements are posted below- Each update is single row update.

TKPROF shows lot GC waits like
gc current grant read-mostly invalidation
gc current grant busy
gc current block busy
etc..

Can you please share your thoughts on these waits and How to reduce /resolve these waits to make update complete faster.


SQL ID: b5v4yghfxhmqc Plan Hash: 569602257

update BKS.TREF set ACCRUAL_FIRST_D=:1 , ADDED_BY_C=:2 , ADDED_Z=:3 , 
  ADJUST_Z=:4 , AVG_LIFE_Q=:5 , AVG_LIFE=:6 , CALL_ACT_EXP_C=:7 , CALL_TYPE_C=
  :8 , CHECK_DIGIT_C=:9 , CLASSIFICATION_Z=:10 , CNTRB_ISSUE_OTSD_EFF_D=:11 , 
  CNTRB_ISSUE_OTSD_M=:12 , COMPLETE_CALL_I=:13 , COMPOUND_YIELD_P=:14 , 
  CONV_MOD_Q=:15 , COUPON_INITIAL_P=:16 , COUPON_NEW_P=:17 , COUPON_RATE_P=
  :18 , COUPON_TYPE_C=:19 , CUSIP_DERVN_I=:20 , CUSIP_PRC_OVRIDE_I=:21 , 
  DATED_D=:22 , DEFAULT_STATUS_I=:23 , DEFEASED_C=:24 , DENOM_INIT_I=:25 , 
  DISCLOSURE_IND_C=:26 , DTC_ELGBY_I=:27 , DURN_MOD_Q=:28 , ECON_DEFEASED_C=
  :29 , EVAL_CALC_C=:30 , EVAL_PRICE_P=:31 , EVAL_YIELD_P=:32 , EVALUATION_Z=
  :33 , FACTOR_FLAG_C=:34 , FDIC_C=:35 , FEATURE_PRICE_P=:36 , GUARANTOR_I=
  :37 , HYBRID_TYPE_C=:38 , INCREMENTS_1_M=:39 , INCREMENTS_2_M=:40 , 
  INST_CATEGORY_C=:41 , INST_SUB_ID_C=:42 , INSURANCE_I=:43 , INTEREST_TYPE_C=
  :44 , ISSUE_DEAD_D=:45 , ISSUE_DENOM_1_L=:46 , ISSUE_DENOM_2_L=:47 , 
  ISSUE_DENOM_3_L=:48 , ISSUE_DENOM_4_L=:49 , ISSUE_ID_C=:50 , ISSUE_ORGL_1_Q=
  :51 , ISSUE_ORGL_2_Q=:52 , ISSUE_ORGL_L=:53 , ISSUE_OTSD_EFF_D=:54 , 
  ISSUE_OTSD_L=:55 , ISSUE_REOFFER_I=:56 , ISSUE_STATUS_C=:57 , 
  ISSUE_STATUS_REASON_C=:58 , ISSUE_OTSD_I=:59 , ISSUING_ID_C=:60 , 
  LEGAL_DEFEASED_C=:61 , LOC_I=:62 , MARKET_MAKER_1_C=:63 , MARKET_MAKER_2_C=
  :64 , MARKET_MAKER_3_C=:65 , MARKET_MAKER_4_C=:66 , MARKET_SECTOR_ID_C=:67 ,
   MATURITY_D=:68 , MATURITY_TYPE_C=:69 , MIDW_ELGBY_I=:70 , MIN_UNITS_1_Q=
  :71 , MIN_UNITS_2_Q=:72 , MINIU_ELGBY_I=:73 , MKT_OF_ISSUANCE=:74 , 
  MODIFIED_BY_C=:75 , MODIFIED_Z=:76 , NOM_RATE_P=:77 , OFFERING_ID_C=:78 , 
  OFFERING_PRICE_P=:79 , OFFERING_YIELD_P=:80 , OID_I=:81 , 
  OPT_CALL_DEFEASED_C=:82 , OTE_FLAG_I=:83 , PAC_BOND_C=:84 , PARTIAL_CALL_I=
  :85 , PAYMENT_LAST_ROLL_D=:86 , PAYMENT_NEXT_D=:87 , PAYMENT_PREV_D=:88 , 
  PHILDEP_ELGBY_I=:89 , PREREF_TYPE_C=:90 , PRICE_CALC_C=:91 , PRICE_TO_D=:92 
  , PRICEABLE_C=:93 , PRICING_ERROR_C=:94 , PRIN_INIT_I=:95 , PROJECT_NAME_C=
  :96 , PTC_OFFERING_PRICE_P=:97 , PTC_OFFERING_YIELD_P=:98 , PURP_CLASS_C=
  :99 , PURP_SUB_CLASS_C=:100 , PURP_TYPE_C=:101 , PUT_TYPE_C=:102 , 
  QUALITY_RATG_C=:103 , RATE_ADJUST_D=:104 , RESEARCH_PENDING_I=:105 , 
  SECURITY_TYPE_C=:106 , SHORT_TERM_I=:107 , SINKING_FUND_C=:108 , SPECIAL_I=
  :109 , SUPER_SINKER_I=:110 , SUPPLEMENTAL_ISSUE_TYPE_C=:111 , TENDER_I=:112 
  , TICKER_SYMBOL_C=:113 , TIME_TO_MSRB_Q=:114 , TRADING_GROUP_ID_C=:115 , 
  VARIABLE_TYPE_C=:116 , WHEN_ISSUE_I=:117 , ZERO_TYPE_C=:118  
where
 ISSUE_ALPHA_C=:119  and ISSUER_ID_C=:120 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    31378      0.33       0.41          0          0          0           0
Execute  31378     50.15      81.84       2151     104634     409451       31378
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    62756     50.48      82.26       2151     104634     409451       31378

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 135  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  TREF (cr=3 pr=0 pw=0 time=679 us starts=1)
         1          1          1   INDEX UNIQUE SCAN TREF_P1 (cr=3 pr=0 pw=0 time=7 us starts=1 cost=2 size=268 card=1)(object id 371019)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  gc current grant read-mostly invalidation
                                                413        0.00          0.09
  Disk file operations I/O                        1        0.00          0.00
  gc current block busy                       19524        0.03         16.16
  SQL*Net message to client                   31378        0.00          0.01
  SQL*Net message from client                 31378        0.05         18.90
  gc current grant busy                        4659        0.00          0.56
  gc buffer busy acquire                        592        0.02          1.60
  db file scattered read                        256        0.01          0.19
  gc cr block 2-way                            4959        0.00          0.51
  buffer busy waits                             306        0.00          0.08
  db file sequential read                        99        0.00          0.05
  gc buffer busy release                        395        0.04          1.46
  gc cr block busy                               57        0.02          0.07
  enq: TX - index contention                    757        0.04          0.20
  transaction                                    51        0.00          0.00
  gc current grant 2-way                        424        0.00          0.04
  gc current split                               19        0.00          0.01
  latch: cache buffers chains                     6        0.00          0.00
  gc current retry                                2        0.00          0.00
  read by other session                           3        0.00          0.00
  latch: ges resource hash list                   6        0.00          0.00
  log file sync                                   1        0.00          0.00
  enq: HW - contention                            1        0.00          0.00
  row cache lock                                  2        0.00          0.00
  DLM cross inst call completion                  4        0.00          0.00
  KJC: Wait for msg sends to complete             1        0.00          0.00
  db file parallel read                           1        0.00          0.00
  enq: FB - contention                            3        0.00          0.00
  log file switch completion                      1        0.00          0.00
  gc current block 2-way                          1        0.00          0.00
********************************************************************************

SQL ID: cr1kqu5c77fdv Plan Hash: 3104700247

update BKS.TISSREF set A144C=:1 , A_3A3_COMM_PAPER_C=:2 , ACCRUAL_CODE_C=
  :3 , ACCRUAL_COUNT_C=:4 , ADDED_BY_C=:5 , ADDED_Z=:6 , ADDL_STATE_CODE_1=:7 
  , ADDL_STATE_CODE_2=:8 , AMT_TAX_I=:9 , ASSET_CLAIM_C=:10 , 
  BANK_QUALIFIED_I=:11 , BOND_CNSL_SUB_ID_C=:12 , BOND_INST_CAT_C=:13 , 
  BUILD_AMERICA_BONDS_C=:14 , BUS_DAY_ADJUST_1_C=:15 , BUS_DAY_ADJUST_2_C=:16 
  , COVERED_SECURITIES_C=:17 , CUR_DENOM_ID_C=:18 , CUR_ID_C=:19 , CUR_INT_D=
  :20 , CUR_INT_I=:21 , CUR_ORGL_ID_C=:22 , DELIVERY_METHOD_C=:23 , 
  DENOM_INIT_I=:24 , FINCL_EXEMPT_I=:25 , FINCL_EXPECT_D=:26 , FINCL_GRACE_Q=
  :27 , FINCL_YR_END_D=:28 , FISCL_INST_CAT_C=:29 , FISCL_SUB_ID_C=:30 , 
  FREQ_1_Q=:31 , FREQ_2_Q=:32 , FREQ_DAY_1_C=:33 , FREQ_DAY_2_C=:34 , 
  FREQ_PER_OCCUR_1_Q=:35 , FREQ_PER_OCCUR_2_Q=:36 , FREQ_PERIOD_1_C=:37 , 
  FREQ_PERIOD_2_C=:38 , INCOME_TAX_I=:39 , INFO_NOT_AVAIL=:40 , 
  INITIAL_TRADE_D=:41 , INST_CATEGORY_C=:42 , INST_SUB_ID_C=:43 , 
  INT_PAY_TYPE_C=:44 , ISSUING_CUSIP_T=:45 , MODIFIED_BY_C=:46 , MODIFIED_Z=
  :47 , MUNI_TAX_I=:48 , OFFERING_DENOM_L=:49 , OFFERING_ID_C=:50 , 
  OFFERING_MARKET_TYPE_C=:51 , OFFERING_MRKT_C=:52 , OFFERING_ORGL_L=:53 , 
  OFFERING_OTSD_L=:54 , OFFERING_TYPE_C=:55 , OS_REPOS_D=:56 , OS_REPOS_ID_C=
  :57 , OTHER_STATE_TAX_I=:58 , PAYMENT_FINAL_D=:59 , PAYMENT_FIRST_D=:60 , 
  PAYMENT_FORM_C=:61 , PAYMENT_OBLGN_C=:62 , PAYMENT_SECOND_D=:63 , 
  PRELIM_INFO_C=:64 , PRIN_INIT_I=:65 , RECORD_DATE_ADJ_C=:66 , RECORD_DATE_C=
  :67 , RECORD_DATE_Q=:68 , REDENOMINATION_I=:69 , REFUNDING_C=:70 , REG_D_C=
  :71 , REG_S_C=:72 , REGISTRTION_TYPE_C=:73 , RFUND_RSRCH_STATUS_D=:74 , 
  SALE_NOTICE_D=:75 , SALE_NOTICE_Z=:76 , SALE_STATUS_C=:77 , SALE_TIME_C=:78 
  , SECURITY_CLASS_C=:79 , SERIES_GROUP_C=:80 , SETTLMNT_D=:81 , 
  SETTLMNT_RCV_D=:82 , SETTLMNT_STATUS_C=:83 , STATE_TAX_I=:84 , SUBMISSION_D=
  :85 , SUBMISSION_TIME_C=:86 , SUBMISSION_TIME_Z=:87 , SUPRA_NATIONALS_C=:88 
  , SVNG_DIR_SECURITIES_C=:89 , TAX_CREDIT_FREQ_C=:90 , TAX_CREDIT_RATE=:91 , 
  TAX_PROGRAM_C=:92 , UNDWRTR_INST_CAT_C=:93 , UNDWRTR_SUB_ID_C=:94 , 
  UNRESTRICTED_I=:95  
where
 ISSUING_ID_C=:96 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    30917      0.26       0.37          0          0          0           0
Execute  30917     41.27      60.15       2376      98308     500866       30917
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    61834     41.53      60.53       2376      98308     500866       30917

Misses in library cache during parse: 0
Misses in library cache during execute: 2
Optimizer mode: ALL_ROWS
Parsing user id: 135  
Number of plan statistics captured: 143

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  TISSREF (cr=3 pr=0 pw=0 time=576 us starts=1)
         1          1          1   INDEX UNIQUE SCAN TISSREF_P1 (cr=3 pr=0 pw=0 time=5 us starts=1 cost=2 size=306 card=1)(object id 371289)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        2        0.00          0.00
  gc current block busy                       27006        0.02          8.25
  IPC group service call                          2        0.00          0.00
  SQL*Net message to client                   30917        0.00          0.01
  SQL*Net message from client                 30917        0.14         24.86
  gc current grant read-mostly invalidation
                                                666        0.00          0.13
  gc current grant busy                         770        0.00          0.13
  gc buffer busy acquire                        137        0.02          0.36
  db file scattered read                        253        0.01          0.18
  gc cr block 2-way                             579        0.00          0.06
  db file sequential read                       352        0.00          0.18
  enq: TX - index contention                    502        0.00          0.06
  gc current block 2-way                         79        0.00          0.00
  buffer busy waits                              29        0.00          0.00
  gc current grant 2-way                        730        0.00          0.07
  gc current split                                3        0.00          0.00
  transaction                                    30        0.00          0.00
  gc cr block busy                                5        0.00          0.00
  gc buffer busy release                         11        0.01          0.03
  latch: gc element                               2        0.00          0.00
********************************************************************************

Chris Saxon
March 01, 2022 - 2:13 pm UTC

Each update is single row update.

Really the solution here is to change the process so you update many rows with each execution.

update statement with TKPROF waits

John, March 01, 2022 - 8:34 pm UTC

>>Each update is single row update.
Really the solution here is to change the process so you update many rows with each execution.

Yes this was suggested. But this is legacy app. No immediate plan from application side to make this change. App is comparing 11.2.0.4 with 19.11. Its running OK in 11.2.0.4 (the current production). This is slow after 19.11 upgrade.
Can you provide some insights on the waits and any recommendations to reduce those waits ?

I will still push them to optimize the code with batch update after 19.11 upgrade is complete.
Connor McDonald
March 02, 2022 - 2:50 am UTC

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  gc current block busy                       27006        0.02          8.25
  SQL*Net message from client                 30917        0.14         24.86


Almost all of your time was lost here.

SQL*Net means we were waiting om the client to do something - not much we can do about that. Perhaps there has been a network change for the new version

'gc current block busy' is when we're waiting for an instance that owns the block to pass it to the requesting instance. High CPU load or poor redo I/O performance can be related to this.

update statement with ROWID

A reader, August 25, 2022 - 5:04 am UTC

Greetings!

Is there any way better way to rewrite this update statement ?


SQL_ID fp6v9fq101yd1
--------------------
UPDATE MH.FMP SET REC_DT_END = :1     WHERE
ROWID IN (         SELECT FP.ROWID FROM MH.FMP FMP
           INNER JOIN MH.F_STG SMP ON FP.PRC_DT =
SMP.PRC_DT AND FP.SOURCE_ID = SMP.SOURCE_ID             AND
FP.IDENTIFIER_TYPE = SMP.IDENTIFIER_TYPE AND FP.IDENTIFIER =
SMP.IDENTIFIER WHERE FP.LOADED_BY = 'FM02 File Loader' AND
SMP.SRC_COMMT = :2  AND FP.REC_DT_END =
TO_TIMESTAMP('9999-12-31 05:00:00','YYYY-MM-DD HH24:MI:SS')             )

Plan hash value: 2794395325

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                 |                   |       |       |    37M(100)|          |       |       |
|   1 |  UPDATE                          | FMP               |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                   |                   |     1 |    91 |    37M  (1)| 00:24:22 |       |       |
|   3 |    VIEW                          | VW_NSO_1          |     1 |    12 |    37M  (1)| 00:24:22 |       |       |
|   4 |     SORT UNIQUE                  |                   |     1 |   153 |            |          |       |       |
|   5 |      HASH JOIN                   |                   |     1 |   153 |    37M  (1)| 00:24:22 |       |       |
|   6 |       PART JOIN FILTER CREATE    | :BF0000           |     1 |    74 |  1297   (1)| 00:00:01 |       |       |
|   7 |        TABLE ACCESS STORAGE FULL | F_STG             |     1 |    74 |  1297   (1)| 00:00:01 |       |       |
|   8 |       PARTITION RANGE JOIN-FILTER|                   |   764M|    56G|    37M  (1)| 00:24:22 |:BF0000|:BF0000|
|   9 |        PARTITION HASH ALL        |                   |   764M|    56G|    37M  (1)| 00:24:22 |     1 |    64 |
|  10 |         TABLE ACCESS STORAGE FULL| FMP               |   764M|    56G|    37M  (1)| 00:24:22 |     1 |1048575|
|  11 |    TABLE ACCESS BY USER ROWID    | FMP               |     1 |    79 |     1   (0)| 00:00:01 | ROWID | ROWID |
----------------------------------------------------------------------------------------------------------------------

Connor McDonald
August 26, 2022 - 5:27 am UTC

Um....where is FP ?

In any event, maybe a merge, along the lines of:

merge into MH.FMP fp 
using ( select * from mh.f_stg  smp
where fp.loaded_by = 'FM02 File Loader'
)
      on ( fp.prc_dt = smp.prc_dt
         and fp.source_id = smp.source_id
         and fp.identifier_type = smp.identifier_type
         and fp.identifier = smp.identifier
       and smp.src_commt = :2
       and fp.rec_dt_end = to_timestamp('9999-12-31 05:00:00','YYYY-MM-DD HH24:MI:SS')    
)
where matched then
  update SET REC_DT_END = :1   

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.