Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, sean.

Asked: August 17, 2001 - 2:37 pm UTC

Last updated: December 08, 2011 - 12:55 pm UTC

Version: 8.0.6

Viewed 10K+ times! This question is

You Asked

Tom,
By refer to note 10696.1

In order to avoid the FTS on the target table, unobtemp,I want to change the correlated update from

UPDATE unobtemp
SET (tdte, COMMIT) = (SELECT MIN(ctr.trans_date), SUM(ctr.trans_amount)
FROM commit_trans_reg ctr
WHERE ctr.prac_line_no = unobtemp.line_no
AND ctr.prac_no = unobtemp.doc_no
AND ctr.gl_acct_num LIKE '457%'
AND DECODE(length(:xacpd), 6, ctr.acct_period, 8, to_char(ctr.trans_date, 'yyyymmdd')) <= :xacpd)
WHERE EXISTS (SELECT 'X'
FROM commit_trans_reg ctr
WHERE ctr.prac_line_no = unobtemp.line_no
AND ctr.prac_no = unobtemp.doc_no
AND ctr.gl_acct_num LIKE '457%'
AND DECODE(length(:xacpd), 6, ctr.acct_period, 8, to_char(ctr.trans_date, 'yyyymmdd')) <= :xacpd)
/

To

declare
cursor source is
select MIN(trans_date) tr_date
, SUM(trans_amount) tr_amount
, prac_line_no
, prac_no
from
commit_trans_reg
where gl_acct_num LIKE '457%'
AND DECODE(length(200106), 6, acct_period, 8, to_char(trans_date, 'yyyymmdd'))
<= '200106';
begin
for row in source loop
update unobtemp
SET (tdte, COMMIT) = (row.tr_date, row.tr_amount)
where row.prac_line_no = unobtemp.line_no
AND row.prac_no = unobtemp.doc_no;
end loop;
exception
when others then
null;
end;

And got error:

ERROR at line 15:
ORA-06550: line 15, column 29:
PLS-00103: Encountered the symbol "ROW" when expecting one of the following:
( select
The symbol "select" was substituted for "ROW" to continue.
ORA-06550: line 15, column 55:
PLS-00103: Encountered the symbol ")" when expecting one of the following:
. ( , * @ % & - + / mod rem
an exponent (**) as
from ||

How to fix it?

TIA

and Tom said...

I do believe thats the oldest note i've seen ;)

It would be much better to keep this in SQL in my opinion. Just change the EXISTS to IN and it should happen if indexes are in place, consider:

ops$tkyte@ORA817.US.ORACLE.COM> create table unobtemp ( tdte date, commit number, line_no number, doc_no number );

Table created.

ops$tkyte@ORA817.US.ORACLE.COM> create index unobtemp_idx on unobtemp(line_no,doc_no);

Index created.

ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> create table commit_trans_reg ( trans_date date, trans_amount number, prac_line_no number, prac_no number,
2 gl_acct_num varchar2(25), acct_period number );

Table created.

ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> create index commit_trans_reg_idx on commit_trans_reg(prac_line_no,prac_no);

Index created.

ops$tkyte@ORA817.US.ORACLE.COM> create index commit_trans_reg_idx2 on commit_trans_reg(gl_acct_num);

Index created.

ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> variable xacpd varchar2(20)
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> set autotrace on explain
ops$tkyte@ORA817.US.ORACLE.COM> UPDATE unobtemp
2 SET (tdte, COMMIT) = (SELECT MIN(ctr.trans_date), SUM(ctr.trans_amount)
3 FROM commit_trans_reg ctr
4 WHERE ctr.prac_line_no = unobtemp.line_no
5 AND ctr.prac_no = unobtemp.doc_no
6 AND ctr.gl_acct_num LIKE '457%'
7 AND DECODE(length(:xacpd), 6, ctr.acct_period, 8,
8 to_char(ctr.trans_date, 'yyyymmdd')) <= :xacpd)
9 WHERE EXISTS (SELECT 'X'
10 FROM commit_trans_reg ctr
11 WHERE ctr.prac_line_no = unobtemp.line_no
12 AND ctr.prac_no = unobtemp.doc_no
13 AND ctr.gl_acct_num LIKE '457%'
14 AND DECODE(length(:xacpd), 6, ctr.acct_period, 8,
15 to_char(ctr.trans_date, 'yyyymmdd')) <= :xacpd)
16 /

0 rows updated.


Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'UNOBTEMP'
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'UNOBTEMP'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'COMMIT_TRANS_REG'
5 4 INDEX (RANGE SCAN) OF 'COMMIT_TRANS_REG_IDX' (NON-UNIQUE)
6 0 SORT (AGGREGATE)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'COMMIT_TRANS_REG'
8 7 INDEX (RANGE SCAN) OF 'COMMIT_TRANS_REG_IDX' (NON-UNIQUE)



ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> UPDATE unobtemp
2 SET (tdte, COMMIT) = (SELECT MIN(ctr.trans_date), SUM(ctr.trans_amount)
3 FROM commit_trans_reg ctr
4 WHERE ctr.prac_line_no = unobtemp.line_no
5 AND ctr.prac_no = unobtemp.doc_no
6 AND ctr.gl_acct_num LIKE '457%'
7 AND DECODE(length(:xacpd), 6, ctr.acct_period, 8,
8 to_char(ctr.trans_date, 'yyyymmdd')) <= :xacpd)
9 WHERE (line_no,doc_no) IN (SELECT prac_line_no, prac_no
10 FROM commit_trans_reg ctr
11 where ctr.gl_acct_num LIKE '457%'
12 AND DECODE(length(:xacpd), 6, ctr.acct_period, 8,
13 to_char(ctr.trans_date, 'yyyymmdd')) <= :xacpd)
14 /

0 rows updated.


Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'UNOBTEMP'
2 1 NESTED LOOPS
3 2 VIEW OF 'VW_NSO_1'
4 3 SORT (UNIQUE)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'COMMIT_TRANS_REG'
6 5 INDEX (RANGE SCAN) OF 'COMMIT_TRANS_REG_IDX2' (NON-UNIQUE)
7 2 INDEX (RANGE SCAN) OF 'UNOBTEMP_IDX' (NON-UNIQUE)
8 0 SORT (AGGREGATE)
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'COMMIT_TRANS_REG'
10 9 INDEX (RANGE SCAN) OF 'COMMIT_TRANS_REG_IDX' (NON-UNIQUE)

Rating

  (86 ratings)

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

Comments

Thanks Tom!

Sean, August 18, 2001 - 1:27 am UTC


referencing outer table

A reader, October 03, 2002 - 10:45 am UTC

Hi

If I have a correlated update like this

update emp a
set comm = (select avg(sal) from emp b
where a.deptno = b.deptno);

this works

but if I have

update emp b
set comm = (select avg(sal)
from (select * from emp a
where a.deptno = b.deptno));

this doesnt work, any restrictions?

Tom Kyte
October 03, 2002 - 6:05 pm UTC

the correlation name B can only go one level down. the correlated subquery may ONLY reference its parent query.

regarding last post

A reader, October 11, 2002 - 4:16 pm UTC

Hi


I guess the only way for the last post to work is using PL/SQL right?


Tom Kyte
October 11, 2002 - 8:42 pm UTC

no

update emp b
set comm = (select avg(sal) from emp a where a.deptno = b.deptno )
/

is the same as the last post but it works.

Need some help regarding Coorelated Update

Riaz Shahid, October 12, 2002 - 4:52 am UTC

Hello Tom !

I have a table with the following structure:

SQL> desc recovery_info

Name                    Null?    Type
----------------------- -------- ----------------
RECOVERY_NO                      NUMBER(4)
COLLECTORATE_CODE                CHAR(2)
ST_REG_NO                        VARCHAR2(13)
RECOVERD_AMT                     NUMBER(14,2)

and sample data in this table is like :

Rec#      Coll Code   St Reg No     Recovered Amt        
----      ---------   ------------  --------------
           03         0311111111111          10000
           03         0311111111111          23000
           03         0311111111111           5000
           01         0101392301528         100000
           05         0592999972455        5600000

I want to assign unique Recovery no to each record w.r.t columns Collectorate_Code,St_Reg_No. The data (after updation) should look like :

Rec#      Coll Code   St Reg No     Recovered Amt        
----      ---------   ------------  --------------
1          03         0311111111111          10000
2          03         0311111111111          23000
3          03         0311111111111           5000
1          01         0101392301528         100000
1          05         0592999972455        5600000


Please advise ...


Regards

Riaz
 

Tom Kyte
October 12, 2002 - 9:18 am UTC

I would not assign that which is most likely going to change

Also -- I don't see why:

Rec# Coll Code St Reg No Recovered Amt
---- --------- ------------ --------------
1 03 0311111111111 10000
2 03 0311111111111 23000
3 03 0311111111111 5000

rec# is 23000 I would think either of:

Rec# Coll Code St Reg No Recovered Amt
---- --------- ------------ --------------
1 03 0311111111111 5000
2 03 0311111111111 10000
3 03 0311111111111 23000

or

Rec# Coll Code St Reg No Recovered Amt
---- --------- ------------ --------------
1 03 0311111111111 23000
2 03 0311111111111 10000
3 03 0311111111111 5000

would be the ONLY logical way to do it (order by recovered amt desc or asc). I mean, you seem to imply that the physical "order" (which doesn't exist!) has some meaning but it cannot.


When I "needed" this number, I would use the row_number() analytic function to materialize it:


select
row_number() over (partition by coll_code, st_reg_no order by recoverd_amt)
rec#, coll_code, st_reg_no, recovered_amt
from t;


Period. A simple insert/update/delete on this table would render the rec# in there obsolete -- hence this is a classic example of a column that needs to be computed on the fly (sort of like AGE, you would never store AGE in a table, you would store date_of_birth and compute AGE on the fly)


Excellent

Riaz Shahid, October 14, 2002 - 11:25 am UTC


Update error

Martin, July 09, 2003 - 10:21 am UTC

This one is no big deal for you. I wish to update the name in the company table to that in company_temp where the checking account number are the same. What's wrong ?

(idle)> ed
Wrote file afiedt.buf

1 update company c
2 set name=(select t.name
3 from company_tmp t
4* where t.ca=c.ca)
(idle)> /
set name=(select t.name
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

Tom Kyte
July 09, 2003 - 11:37 am UTC

select t.ca, count(*)
from company_tmp t
group by t.ca
having count(*) > 1;

you have dups in your company_tmp table -- which one did you want Oracle to use? that is what it is complaining about.



How to optimize correlated update?

Jeff, August 29, 2003 - 8:15 am UTC

Tom,
Great advice as usual.

I have following query that takes long time to run and never gets more than 3% of Oracle resources for some reason.

I'm wondering if somehow I can rewrite this and avoid the duplicated query in the WHERE clause. Seems like this shouldn't be necessary, if I can get Oracle to correlate the Select query with the update statement.

UPDATE PGP_PERSON NOLOGGING SET DATE_OF_BIRTH =
(SELECT s.date_of_birth
FROM ods.ship_passenger s, pgp_cruise_history c, pgp_person p
WHERE s.voy_num = c.voy_num
AND s.book_num = c.book_num
AND s.seq_num = c.seq_num
AND c.person_num = p.person_num
AND c.matched_ind IS NOT NULL
AND NVL(s.date_of_birth,TO_DATE('01-JAN-03')) <> NVL(p.DATE_OF_BIRTH,TO_DATE('01-JAN-03')))

WHERE person_num IN
(SELECT c.person_num FROM pgp_cruise_history c, pgp_person p, ods.ship_passenger s
WHERE s.voy_num = c.voy_num
AND s.book_num = c.book_num
AND s.seq_num = c.seq_num
AND c.person_num = p.person_num
AND c.matched_ind IS NOT NULL
AND NVL(s.date_of_birth,TO_DATE('01-JAN-03')) <> NVL(p.DATE_OF_BIRTH,TO_DATE('01-JAN-03')))

Thanks for your help Tom.
Jeff


Tom Kyte
August 29, 2003 - 9:38 am UTC

i'm wondering what metric you used to compute "3% of Oracle resources"

but anyway....


that nologging -- hope it "makes you feel better", it has no more effect then:


update pgp_person FEEL_BETTER set date_of_birth = .....


it is just a correlation name :)



But -- are you sure you have that update right at all???? the subquery says:

take all of the rows in s, c, p and join them together.
find the none null c.matched_ind rows and keep them.
Then, find me a row where the s.date_of_birth isn't equal to the p.date_of_birth

But -- I see NO relation to the outer table which is aliased as "nologging". that is -- you have no correlation between the table you are updating and the rows you are updating with!!!!

I believe the entire update is "wrong" -- i cannot see the circumstances where it would make sense.


Can you phrase, in english, the requirements here?

Correlated Update

Jeff, August 29, 2003 - 4:58 pm UTC

Tom,

Environment: Oracle 8.1.7, Unix 2 cpu

When I said 3% oracle resources, I think I meant Unix CPU time. When I run unix top it never shows greater than 3%.

Also, when this runs, it never spawns parallel queries.

I have the 3 tables s, c, & p.
I want to update p.date_of_birth to s.date_of_birth if they are different. I have to go thru c to get to p. That's it! Period. Simple stuff right?

I rewrote as PL/SQL, but would prefer single SQL statement:

DECLARE
counter INTEGER;
BEGIN
counter:= 0;

FOR s IN (SELECT s.date_of_birth, c.person_num
FROM ods.ship_passenger s, pgp_cruise_history c
WHERE s.voy_num = c.voy_num
AND s.book_num = c.book_num
AND s.seq_num = c.seq_num
AND c.matched_ind IS NOT NULL)

LOOP

UPDATE pgp_person

SET date_of_birth = s.date_of_birth, update_by = 'SDM18649', update_date = TRUNC(SYSDATE), load_timestamp = SYSDATE

WHERE
person_num = s.person_num
AND
NVL(s.date_of_birth,TO_DATE('01-JAN-03')) <> NVL(DATE_OF_BIRTH,TO_DATE('01-JAN-03'))
IF SQL%FOUND THEN counter:= counter + 1; END IF;

END LOOP;
dbms_output.put_line ('Total records updated are: "||TO_CHAR(counter));
END;

This PL/SQL never spawns parallel query, but seems to accomplish what I want. However, it has been running for 4 hours.

It seems like I should be able to do something like:

UPDATE PGP_PERSON p SET DATE_OF_BIRTH =
(SELECT s.date_of_birth
FROM ods.ship_passenger s, pgp_cruise_history c
WHERE s.voy_num = c.voy_num
AND s.book_num = c.book_num
AND s.seq_num = c.seq_num
AND c.person_num = p.person_num
AND c.matched_ind IS NOT NULL
AND NVL(s.date_of_birth,TO_DATE('01-JAN-03')) <>
NVL(p.DATE_OF_BIRTH,TO_DATE('01-JAN-03')))

But I'm not sure if I need to put a WHERE clause on the outer query or if the query will be correlated by referencing the p.person num in the subquery, making the WHERE clause unnecessary.

PS - thanks for the NOLOGGING tip, but I swear my updates go faster when I use it.

Thanks again!!!!
Jeff

Tom Kyte
August 30, 2003 - 10:30 am UTC

if you have the proper primary keys to assure uniqueness on S and C, this does it (i have the keys noted in the creates)

ops$tkyte@ORA920> create table pgp_person
  2  ( date_of_birth date,
  3    update_by varchar2(10),
  4    update_date date,
  5    load_timestamp date,
  6    person_num int );
 
Table created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create table ship_passenger
  2  ( date_of_birth date,
  3    person_num int,
  4    voy_num int,
  5    book_num int,
  6    seq_num int,
  7    primary key (person_num) );
 
Table created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create table pgp_cruise_history
  2  ( voy_num int,
  3    book_num int,
  4    seq_num int,
  5    matched_ind int,
  6    primary key(voy_num,book_num,seq_num) );
 
Table created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> update ( select p.date_of_birth dob1, s.date_of_birth dob2,
  2                  p.update_by, p.update_date, p.load_timestamp
  3             from pgp_person p, pgp_cruise_history c, ship_passenger s
  4                    where p.person_num = s.person_num
  5                      and s.voy_num = c.voy_num
  6                          and s.book_num = c.book_num
  7                          and s.seq_num = c.seq_num
  8                          and c.matched_ind is not null
  9                          and s.date_of_birth <> p.date_of_birth )
 10    set dob1 = dob2,
 11        update_by = 'sdm18649',
 12            update_date = trunc(sysdate),
 13            load_timestamp = sysdate;
 
0 rows updated.
 

advice on approach

A reader, September 03, 2003 - 3:36 pm UTC

Hi tom
Thanx for the great site and your generosity as usual!
I have the following situation (with data):
------------
drop table t1;
create table t1 as
select 't1_' || rownum name, 'TYPE' type, 't1_'||sys_guid() id
from all_objects
where rownum <= 10;

drop table t2;
create table t2 as
select 't1_' || rownum name, 'TYPE' type, 't2_'||sys_guid() id
from all_objects
where rownum <= 5;

insert into t2 values ( 't2_1', 'TYPE', 't2_'||sys_guid());

drop table t11;
drop table t12;
drop table t13;
drop table t14;
drop table t15;

create table t11 as
select * from t1
union select * from t2;
create table t12 as
select * from t1
union select * from t2;
create table t13 as
select * from t1
union select * from t2;
create table t14 as
select * from t1
union select * from t2;
create table t15 as
select * from t1
union select * from t2;

/*
Step 1:
now we need to select those records from t2 for which name
and type don't have a match in t1.

*/
select id, name, type
from t2
where (name, type) not in
(
select b.name, b.type
from t1 b
);

/*
Step 2:
for each of these records -we need to update 5 tables
t11, t12, t13, t14, t15 set their id to
the id from t2 selected above concatenated with a string
'NEW' for matching name and type.
*/
update t11
set id = (select 'NEW'||id from t2
where name = :name
and type = :type);

update t12
set id = (select id from t2
where name = :name
and type = :type);

and so on.. where :name and :type are names and types selected in step 1 above. For any of the given tables
there may be more than one records for the same name,type
in which case all the id's of all records need to be
changed as specified ((I realize that the corelated update as shown will not work in such cases.)

My question is : is there a better way to do the above
as compared to the outlined solution. In particular,
can we do the update in step1 directly in statement
instead of in a loop as I am doing?

Thanx!

Tom Kyte
September 04, 2003 - 8:40 am UTC

don't see how the updates work -- they update every row in the table?

but you can just push the subquery straight in there

update t12 set id = ( select id from t2 where (name,type) in ( select YOUR SUBQUERY ) )

thanx Tom!

A reader, September 04, 2003 - 12:46 pm UTC

"don't see how the updates work -- they update every row in the table?"
update t12
set id = (select id from t2
where name = :name
and type = :type);

So in t12, we need to update to set any id for a row
that has the same name and type column values for the rows
that get selected from the corelated subquery.

So, I think the update should look like:

update t12
set id = (select id from t2
where name = :name
and type = :type)
where (t12.name, t12.type) = ( t2.name, t2.type);

Sorry - haven't run this one yet - am testing it as we
speak.

So the question is (with my answers):

1. Should I use dynamic sql cos the similar query will
be written but for different tables.

Answer: NO - static is faster than dynamic and list
of tables is not likely to change . However, the code
would look a little ugly with similar updates
for different tables.
2. It seems that you imply that I should put the corelated
subquery inside each update instead of running it
once and running individual updates for each record,
correct?

Thanx!



Tom Kyte
September 05, 2003 - 1:34 pm UTC

2) correct

thanx!

A reader, September 05, 2003 - 2:14 pm UTC

I am having a little trouble writing this.
Say my loop is:
--
for rec in ( select a, b, c
from source_table
)
loop

update destination_table s
set s.a = rec.a
where s.a =
(
select a
from table1
where b = rec.b
);

end loop;


--

how would you remove the loop and do this in a single
update?

Thank you so much!!

Tom Kyte
September 05, 2003 - 7:04 pm UTC

confusing choices of aliases -- assuming we look at:

for st in ( select a, b, c
from source_table
)
loop

update destination_table dt
set dt.a = st.a
where dt.a =
(
select t1.a
from table1
where t1.b = st.b
);

end loop;

instead (st = source table, dt = dest table, t1 = table1)...

We can derive from this:

a) c is not meaningful.... never used...

b) source_table.a BETTER be a primary key or two runs of this could result in a different result (eg: A better be unique in source_table, else this is *wrong* logic)

c) b in table1 must be unique as well -- else the subquery would return more then 1 row sometimes....

So, we can create:


drop table st;
create table st ( a int primary key, b int );

drop table dt;
create table dt ( a int );

drop table t1;
create table t1 ( a int , b int primary key );



Now, all we need to do is join DT to ST by A. and then ST to T1 by B and DT to T1 by A....


update(
select dt.a dt_a, st.a st_a
from dt, st, t1
where dt.a = st.a
and dt.a = t1.a
and st.b = t1.b )
set dt_a = st_a;



pretty sure that does it....

thanx Tom!!!!

A reader, September 05, 2003 - 9:08 pm UTC

That should work though I did not get a chance to
test it out - I think the conditions that you said
should be true are true - so I should be all set.

Thanx again!!!!

Nice

Siva, January 25, 2004 - 10:46 am UTC

Dear sir,
Please look at the following query

sql>select ename,sal,deptno from emp e where
sal > (select avg(sal) from emp where deptno = e.deptno
group by deptno)
Are there any other ways to put this query?
Bye!



Tom Kyte
January 25, 2004 - 11:05 am UTC

sure


select *
from (
select ename, sal, deptno, avg(sal) over (partition by deptno) avg_sal
from emp
)
where sal > avg_sal;



Nice

Siva, January 26, 2004 - 8:34 am UTC

Dear sir,
Thanks for your response.Sorry for disturbing you again.
Any other ways to put the following query?
1)select hiredate from emp where hiredate like '%DEC%';
2)select hiredate from EMP where instr(hiredate,'DEC') >0;
3)select hiredate from emp where substr(hiredate,4,3) = 'DEC';
I don't want to use to_char(hiredate,...)
Do you some other ways?
Please do reply.
Bye!


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

compare DATES to DATES.

strings to strings

and numbers to numbers


never never compare a date to string (like you are). dates are dates, dates are NOT strings. You want to use an explicit conversion in order to do what you are trying to do.


YOU HAVE TO USE TO_CHAR if you want this to resemble anything near "correctly done".

You are relying on default date formats and the english language being in place. Neither of which is assured.

the way to do this:


select * from emp where to_char(hiredate,'mm') = '12';


compare strings to strings -- never dates to strings. You'll never be sorry.

A reader, January 26, 2004 - 11:50 am UTC

Tom,

I took this code from above and pasted in vi so that i can generate insert statement.

03 0311111111111 10000
03 0311111111111 23000
03 0311111111111 5000
01 0101392301528 100000
05 0592999972455 5600000

Can you please show me the command in vi to put , (comma) after every column

Thanks.

Tom Kyte
January 26, 2004 - 1:00 pm UTC

welcome to MY world.

to make that be an insert I would:

:1,$s/^/insert into t values (/
:1,$s/$/);/

and then just position the cursor on some whitespace in there and hit "r," to turn a space into a comma and then "down arrow + period" to do it on the next line and so on.

A reader, January 26, 2004 - 1:27 pm UTC

Thanks ... I like your world. :-)

A reader, January 26, 2004 - 1:33 pm UTC

Tom,

Instead of using replace and then , with down arrow & period for repitition is there some command that will do that at once.

Thanks.

Tom Kyte
January 26, 2004 - 3:58 pm UTC

:1,$s/.{5,5}/&,/

will add a comma in column 5 (using vim anyway -- might depend on your editor)

I am trying to use this alternate update form

Paul D. Phillips, March 19, 2004 - 1:42 pm UTC

I am reading this with great interest, as I hate the need to go through a table twice to do one update, once to insure that a update value is there for the target row ( the one to be updated ) and then a second time to get the update value to apply to the target.

I am attempting to use this form of inline view, I think that is what it is, but am encountering an ORA error that I do not think I should be getting. I'm running 9.2.0.4.

The standard form of the query is:
UPDATE INSURED_LIVES i
SET i.Age =
( SELECT
m.Observed AGE_M
FROM
MEASURES m
WHERE
i.Policy_Holder_ID = m.Policy_Holder_ID
and i.Suffix_ID = m.Suffix_ID
and m.Measid = 'Age' )
WHERE EXISTS
( SELECT *
FROM
MEASURES m
WHERE
i.Policy_Holder_ID = m.Policy_Holder_ID
and i.Suffix_ID = m.Suffix_ID
and m.Measid = 'Age' );

The new form of the query, is ( I think? ):
UPDATE
( SELECT
i.Age Age_I,
m.Observed AGE_M
FROM
INSURED_LIVES i,
MEASURES m
WHERE
i.Policy_Holder_ID = m.Policy_Holder_ID
and i.Suffix_ID = m.Suffix_ID
and m.Measid = 'Age' )
SET Age_I = Age_M ;

But when I attempt to run this, I get:
ORA-01779 cannot modify a column which maps to a non key-preserved table

What is confusing me is both the tables involved are primary keyed. The Insured_Lives table is PKed by the 2 I. columns and the Measures table is PKed by the 3 M. columns refered to in the WHERE clause.

Any insights into why I'm encountering this will be VERY, very helpful. Doing DSS work through multi million row tables, one less pass represents an enourmous improvement.

And, by the way, your previous help with analytic operators has proven to be a major productivity boost for me, also.

Tom Kyte
March 19, 2004 - 2:16 pm UTC

well, i guess, that since

</code> http://otn.oracle.com/products/oracle9i/ <code>pdf/dss_enviroment_9i.pdf

seems to "officially" talk about it -- so can i.

UPDATE /*+ BYPASS_UJCV */
( SELECT i.Age Age_I, m.Observed AGE_M
FROM INSURED_LIVES i, MEASURES m
WHERE i.Policy_Holder_ID = m.Policy_Holder_ID
and i.Suffix_ID = m.Suffix_ID
and m.Measid = 'Age' )
SET Age_I = Age_M ;


google bypass_ujvc or search on this site for a note or two.



Please see this

Catherine, March 19, 2004 - 11:54 pm UTC

Dear Sir,
 I tried the following queries.
SQL> SHO REL
     release 902000100
SQL> select ename,deptno,sal from emp e where sal > 
  2* (select avg(sal) from emp where deptno = e.deptno group by deptno)
SQL> /

ENAME          DEPTNO        SAL                                                
---------- ---------- ----------                                                
ALLEN              30       1600                                                
JONES              20       2975                                                
BLAKE              30       2850                                                
SCOTT              20       3000                                                
KING               10       5000                                                
FORD               20       3000                                                

6 rows selected.


SQL> with a as (select avg(sal) as avg_sal from emp group by deptno),
  2       b as (select ename,deptno,sal from emp )
  3*     select ename,deptno,sal from b where b.sal > a.avg_sal
SQL> /
    select ename,deptno,sal from b where b.sal > a.avg_sal
                                                 *
ERROR at line 3:
ORA-00904: "A"."AVG_SAL": invalid identifier 

Is not this a valid sql approach?What's wrong over there?
Please do reply.


 

Tom Kyte
March 20, 2004 - 9:58 am UTC

you did not query A in your second example. you only have from b

you'll need to JOIN to A by deptno.

Still Not Working

Catherine, March 20, 2004 - 12:39 pm UTC

Dear Tom,
 Still the query doesn't work.
 
SQL> with a as(select deptno,avg(sal) as avg_sal from emp group by deptno),
  2       b as(select deptno,ename,sal from emp)
  3   select deptno,ename,sal from b where deptno = a.deptno and sal > a.avg_sal;
 select deptno,ename,sal from b where deptno = a.deptno and sal > a.avg_sal
                                                                  *
ERROR at line 3:
ORA-00904: "A"."AVG_SAL": invalid identifier 



 

Catherine Hope this helps

A reader, March 20, 2004 - 10:03 pm UTC

SQL> with a as (select deptno ,avg(sal) as avg_sal from emp group by deptno),
  2           b as (select ename,deptno,sal from emp )
  3        select ename,a.deptno,sal from b ,a where b.sal >  a.avg_sal and a.deptno = b.deptno ;

ENAME          DEPTNO        SAL
---------- ---------- ----------
KING               10       5000
CLARK              10       2450
FORD               20       3000
SCOTT              20       3000
JONES              20       2975
BLAKE              30       2850
ALLEN              30       1600

7 rows selected.

with the With clause you just pre defined the 2 queries.But you forget to join them in the select statement.Thats what Tom the great meant. 

BYPASS_UJVC still does not work

Paul, March 23, 2004 - 12:33 pm UTC

Tom, I hate to seem like I'm beating this to death, here, but I really want to understand why this is not working.

First, the BYPASS_UJVC does not help, it gives the same ORA-01779 message. I copied the code right off the page and pasted it into Worksheet, and both tried to run it and tried to Explain it and it shows the same thing both times.

Second, I still want to understand what KEY-PRESERVED means, that was part of my (implied?) question.

If both tables have Primary Keys, one with 2 columns and one with 3 and the one with the 3 has the 2 from the other table and a single value for the 3rd ( obviously ), how is this not key preserved?

Simply put:

from t1 a,
t2 b
where a.c1 = b.c1
and a.c2 = b.c2
and b.c3 = 'SomeLiteral' ;

If a.c1, a.c2 is the PK of a and b.c1, b.c2, b.c3 is the PK of c, how is this not be key preserved, please?

It should have read: /*+ BYPASS_UJVC */

Ed Sherman, March 23, 2004 - 2:19 pm UTC

Paul,

The code doesn't work if you copy and paste it in because the "C" and the "V" are transposed in /*+ BYPASS_UJCV */

It should have read: /*+ BYPASS_UJVC */

It worked for me like that.

As for the key-preserved tables, I'm still working on understanding what this means too.

I'm guessing that Oracle doesn't like the fact that you are joining on two columns of a three column composite key. When I add a composite UNIQUE constraint to the MEASURES table on the POLICY_HOLDER_ID and SUFFIX_ID columns:

ALTER TABLE measures
ADD CONSTRAINT u_measures UNIQUE(policy_holder_id, suffix_id);

Then the UPDATE works without using the hint.

This is probably not a feasable workaround for you though.

I'm guessing that Oracle doesn't consider the join to be key-preserved since joining on two columns of a three-column primary key won't necessarily produce a unique result. You took care of this by writing "AND m.Measid = 'Age'; in your query but Oracle doesn't seem to "get it". I don't know if this is a bug or if it is intentional.

At any rate I wanted to let you know why the hint didn't work when you copied and pasted from the Followup.

Good Luck!


Tom Kyte
March 24, 2004 - 7:59 am UTC

thanks -- hate it when i transpose :(


you need to join from T1 to T2 by the full key of T2 using columns from T1, eg:

update ( select t1_column, t2_column
from t1, t2
where t1.pk1 = t2.pk1
and t2.pk2 = 'X' )
set t1_column = t2_column

will fail, it wants to see

where t1.pk1 = t2.pk1 AND t1.pk2 = t2.pk2




Wow, what a difference!

Paul, March 24, 2004 - 9:48 am UTC

Ed and Tom, when I fixed the hint, that did work, thank you both very much.

The shocking thing is how much of an improvement this makes in the update. It takes less than half the time and does only 1/4 of the IO and eliminates 7 disk sorts ( tables are parallel and partitioned ), which is where I suspect the big time savings comes in. I'm going to use this in selected cases, I think.

I'm still debating whether to open a TAR, though. I think from Tom's statement this is the intended behaviour, though why a literal is not sufficient, but a join is, is a bit puzzling.

Same results on 10g

Ed Sherman, March 26, 2004 - 9:37 am UTC

Paul,

I get the same behaviour with Oracle Database 10g on Win2K.
I had to try... You never know.

Cheers!

OK

Ben, April 13, 2004 - 3:32 pm UTC

Dear Tom,
Do you have any full example for "bypass_ujvc" hint?How this hint can alter the execution plan of the Update
Statement?Please do write a followup.
Thanks in advance.


Tom Kyte
April 13, 2004 - 6:43 pm UTC

as stated above, google it or search for it on this site, there are examples out there.

it just disables the "table is not key preserved" error (but if the table is in fact NOT key preserved it'll raise a runtime error that it could not get a stable set of rows)

Stable set of rows...

Padders, April 27, 2004 - 9:13 am UTC

Not so sure about that. Have been (ahem) using /*+ BYPASS_UJVC */ for a while now and it doesn't appear to care if the join is not key-preserved, it just updates rows multiple times - presumably the restriction protects us from this non-deterministic behaviour.
 
Also I stumbled on another way to do this which involves creating an unusable unique index on the join column(s). This appears to work on 10g but not 9.2 (don't have any other versions to hand). Presumably this would be considered a bug?  
 
I believe the below example illustrates all of the above.
 
Personal Oracle Database 10g Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> INSERT INTO emp 
  2  SELECT *
  3  FROM   emp;
 
14 rows created.
 
SQL> SELECT COUNT (*), COUNT (DISTINCT empno)
  2  FROM   emp;
 
  COUNT(*) COUNT(DISTINCTEMPNO)
---------- --------------------
        28                   14
 
SQL> UPDATE (
  2    SELECT a.ename, b.ename new_ename
  3    FROM   emp a, emp b
  4    WHERE  b.empno = a.empno)
  5  SET ename = new_ename;
SET ename = new_ename
    *
ERROR at line 5:
ORA-01779: cannot modify a column which maps to a non key-preserved table
 
SQL> UPDATE /*+ BYPASS_UJVC */ (
  2    SELECT a.ename, b.ename new_ename
  3    FROM   emp a, emp b
  4    WHERE  b.empno = a.empno)
  5  SET ename = new_ename;
 
56 rows updated.
 
SQL> CREATE UNIQUE INDEX i_emp ON emp (empno) UNUSABLE;
 
Index created.
 
SQL> UPDATE (
  2    SELECT a.ename, b.ename new_ename
  3    FROM   emp a, emp b
  4    WHERE  b.empno = a.empno)
  5  SET ename = new_ename;
 
56 rows updated.
 
SQL>  

Tom Kyte
April 28, 2004 - 12:33 pm UTC

see, that is the problem with undocumented hints.  we cannot really say *what* its behaviour is.  it is actually non-deterministic:

ops$tkyte@ORA9IR2> create table t2 ( x int, b int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 values ( 1, null );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t2 values ( 1, 55 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t2 values ( 1, 100 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update ( select a, b
  2             from t1, t2
  3                    where t1.x = t2.x )
  4    set a = b;
  set a = b
      *
ERROR at line 4:
ORA-01779: cannot modify a column which maps to a non key-preserved
table
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update /*+ BYPASS_UJVC */
  2         ( select a, b
  3             from t1, t2
  4                    where t1.x = t2.x )
  5    set a = b;
 
2 rows updated.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t1;
 
         X          A
---------- ----------
         1         55
 
ops$tkyte@ORA9IR2> truncate table t2;
 
Table truncated.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t2 values ( 1, 100 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t2 values ( 1, 55 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update /*+ BYPASS_UJVC */
  2         ( select a, b
  3             from t1, t2
  4                    where t1.x = t2.x )
  5    set a = b;
 
2 rows updated.
 
ops$tkyte@ORA9IR2> select * from t1;
 
         X          A
---------- ----------
         1        100
 
ops$tkyte@ORA9IR2>


If the table is in fact NOT KEY PRESERVED, having the same exact inputs can and will lead to different results based on query plans, physical order of rows, the alignment of the sun with pluto and other factors.


Hence, I'm putting this one back into my list of "don't use". 

Stable set of rows a MERGE error

Adrian Billington, April 29, 2004 - 10:19 am UTC

The stable set of rows error is attributed to the MERGE statement, not updateable joins. It occurs when there are many source records to one target record, as the following demonstrates :-

scott@test10g SQL> create table source ( id int, val varchar2(1) );

Table created.

scott@test10g SQL>
scott@test10g SQL> insert into source values ( 1, 'A' );

1 row created.

scott@test10g SQL> insert into source values ( 1, 'B' );

1 row created.

scott@test10g SQL> insert into source values ( 2, 'C' );

1 row created.

scott@test10g SQL>
scott@test10g SQL> create table target ( id int, val varchar2(1), rn int );

Table created.

scott@test10g SQL>
scott@test10g SQL> insert into target values ( 1, null, null );

1 row created.

scott@test10g SQL> insert into target values ( 2, null, null );

1 row created.

scott@test10g SQL>
scott@test10g SQL> update (
  2     select src.val as source_val
  3     ,      tgt.val as target_val
  4     ,      tgt.rn as target_rn
  5     from   source src
  6     ,      target tgt
  7     where  src.id = tgt.id
  8     )
  9  set target_val = source_val
 10  ,   target_rn = rownum;
set target_val = source_val
    *
ERROR at line 9:
ORA-01779: cannot modify a column which maps to a non key-preserved table


scott@test10g SQL>
scott@test10g SQL> update /*+ bypass_ujvc */ (
  2     select src.val as source_val
  3     ,      tgt.val as target_val
  4     ,      tgt.rn as target_rn
  5     from   source src
  6     ,      target tgt
  7     where  src.id = tgt.id
  8     )
  9  set target_val = source_val
 10  ,   target_rn = rownum;

3 rows updated.

scott@test10g SQL>
scott@test10g SQL> select * from target;

        ID V         RN
---------- - ----------
         1 B          2
         2 C          3

scott@test10g SQL>
scott@test10g SQL> merge
  2     into  target tgt
  3     using source src
  4     on  ( src.id = tgt.id )
  5  when matched then
  6     update
  7     set tgt.val = src.val
  8     ,   tgt.rn = rownum;
   using source src
         *
ERROR at line 3:
ORA-30926: unable to get a stable set of rows in the source tables

Regards
Adrian 

Tom Kyte
April 29, 2004 - 10:40 am UTC

correct -- that is what I proved above. That is why BYPASS_UJVC is going right back into my list of EVIL hints, not to be used.

you get different results with the SAME INPUTS using it. not a "good thing (tm)"

Might be able to cheat BYPASS_UJVC

Adrian Billington, April 29, 2004 - 10:41 am UTC

It might be possible to overcome the "randomness" of the BYPASS hint if we have some criteria for which of the source rows to use. For example, following shows us implementing a rule that says we want to take the most recent row per ID from the source data only. In which case it seems like we might be able to cheat a bit...

scott@test10g SQL> create table source ( id int, val varchar2(1), dt date );

Table created.

scott@test10g SQL>
scott@test10g SQL> insert into source values ( 1, 'A', sysdate );

1 row created.

scott@test10g SQL> insert into source values ( 1, 'B', sysdate-365 );

1 row created.

scott@test10g SQL> insert into source values ( 2, 'C', sysdate );

1 row created.

scott@test10g SQL>
scott@test10g SQL> create table target ( id int, val varchar2(1), dt date );

Table created.

scott@test10g SQL>
scott@test10g SQL> insert into target ( id ) values ( 1 );

1 row created.

scott@test10g SQL> insert into target ( id ) values ( 2 );

1 row created.

scott@test10g SQL>
scott@test10g SQL> --
scott@test10g SQL> -- Random...
scott@test10g SQL> --
scott@test10g SQL> update /*+ bypass_ujvc */ (
  2     select src.val as source_val
  3     ,      tgt.val as target_val
  4     ,      src.dt  as source_dt
  5     ,      tgt.dt  as target_dt
  6     from   source src
  7     ,      target tgt
  8     where  src.id = tgt.id
  9     )
 10  set target_val = source_val
 11  ,   target_dt = source_dt;

3 rows updated.

scott@test10g SQL>
scott@test10g SQL> select * from target;

        ID V DT
---------- - ---------
         1 B 30-APR-03
         2 C 29-APR-04

scott@test10g SQL>
scott@test10g SQL> --
scott@test10g SQL> -- Update with the most recent row but still too many updates...
scott@test10g SQL> --
scott@test10g SQL> update /*+ bypass_ujvc */ (
  2     select src.val as source_val
  3     ,      tgt.val as target_val
  4     ,      src.dt  as source_dt
  5     ,      tgt.dt  as target_dt
  6     from   source src
  7     ,      target tgt
  8     where  src.id = tgt.id
  9     order  by
 10            src.id, src.dt
 11     )
 12  set target_val = source_val
 13  ,   target_dt = source_dt;

3 rows updated.

scott@test10g SQL>
scott@test10g SQL> select * from target;

        ID V DT
---------- - ---------
         1 A 29-APR-04
         2 C 29-APR-04

scott@test10g SQL>
scott@test10g SQL> --
scott@test10g SQL> -- Better - get a 1-2-1 relationship between source and target...
scott@test10g SQL> --
scott@test10g SQL> update /*+ bypass_ujvc */ (
  2     select src.val as source_val
  3     ,      tgt.val as target_val
  4     ,      src.dt  as source_dt
  5     ,      tgt.dt  as target_dt
  6     from  (
  7            select src.*
  8            ,      row_number() over ( partition by id order by dt desc ) as rn
  9            from   source src
 10           ) src
 11     ,      target tgt
 12     where  src.rn = 1
 13     and    src.id = tgt.id
 14     )
 15  set target_val = source_val
 16  ,   target_dt = source_dt;

2 rows updated.

scott@test10g SQL>
scott@test10g SQL> select * from target;

        ID V DT
---------- - ---------
         1 A 29-APR-04
         2 C 29-APR-04

Now we have the correct number of updates. BUT, obviously, I would advocate using this technique to ensure a stable set of MERGE source data, not by using naughty hints...

scott@test10g SQL>
scott@test10g SQL> merge
  2     into target tgt
  3     using (
  4             select src.*
  5            ,      row_number() over ( partition by id order by dt desc ) as rn
  6            from   source src
  7           ) src
  8     on ( src.rn = 1 and src.id = tgt.id )
  9  when matched then
 10     update
 11     set tgt.val = src.val
 12     ,   tgt.dt = src.dt;

2 rows merged.

scott@test10g SQL>
scott@test10g SQL> select * from target;

        ID V DT
---------- - ---------
         1 A 29-APR-04
         2 C 29-APR-04

Regards
Adrian 

Tom Kyte
April 29, 2004 - 11:24 am UTC

if you can ASSURE that there is only a single row to join to, it is deterministic.

It is when there are more than one row that the problem raises its head.


I would *not* trust the order by at all. It has no meaning in the processing of an update.

Which is why I used the analytic...

Adrian Billington, April 29, 2004 - 11:32 am UTC

>>I would *not* trust the order by at all. It has no meaning in the processing of
an update.

That's right. I just included it as part of an evolving example that's all...

which is better?

dxl, June 09, 2004 - 8:06 am UTC

Tom

I have to update a table from another table.
I have come up with 2 different ways of doing this,

1) copy rows into a gtt with primary key and then use and updateable join to the table i wish to update.

2) use a correlated update.


I am developing on a dev instance which unfortunately doesn't have anywhere near as much data as i need. Therefore i am trying to use autotrace to decide which method is more optimal ie quicker. I do not completely understand the output i am getting as i've not had a lot of experience with reading explain plans, please can you tell me what you think:

-- First using the gtt method:

12:34:31 OPT1@OP1>INSERT INTO temp_Agg_Backpay
12:34:32 2 SELECT * FROM Agg_Backpay
12:34:32 3 WHERE backpay_run_id = 1
12:34:32 4 and Payment_Org_ID IN ( SELECT NHS_Org_ID
12:34:32 5 FROM Payment_Cycle_Orgs
12:34:32 6 WHERE Cycle_ID = 1002)
12:34:32 7 AND OMP = 'NO';

107 rows created.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=2146)
1 0 NESTED LOOPS (Cost=6 Card=1 Bytes=2146)
2 1 VIEW OF 'VW_NSO_1' (Cost=5 Card=1 Bytes=13)
3 2 SORT (UNIQUE) (Cost=5 Card=1 Bytes=8)
4 3 TABLE ACCESS (FULL) OF 'PAYMENT_CYCLE_ORGS' (Cost=1
Card=2 Bytes=16)

5 1 TABLE ACCESS (BY INDEX ROWID) OF 'AGG_BACKPAY' (Cost=1 C
ard=1 Bytes=2133)

6 5 INDEX (RANGE SCAN) OF 'AGG_BACKPAY_PK' (UNIQUE) (Cost=
2 Card=1)





Statistics
----------------------------------------------------------
18 recursive calls
22 db block gets
20 consistent gets
1 physical reads
1604 redo size
1020 bytes sent via SQL*Net to client
1014 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
107 rows processed

12:34:33 OPT1@OP1>
12:34:33 OPT1@OP1>
12:34:33 OPT1@OP1>
12:34:33 OPT1@OP1>UPDATE (SELECT bp.Payment_Org_ID,
12:34:33 2 bp.Practitioner_Code,
12:34:33 3 -- backpay fields
12:34:33 4 OMP,
12:34:33 5 BP_ST_VALUE,
12:34:33 6 BP_DV_FULL_VALUE,
12:34:33 7 BP_DV_PART_VALUE,
12:34:33 8 BP_TOTAL_VALUE,
12:34:33 9 -- statements fields
12:34:33 10 s.BACKPAY_ST_VALUE AS BACKPAY_ST_VALUE,
12:34:33 11 s.BACKPAY_DV_FULL_VALUE AS BACKPAY_DV_FULL_VALUE,
12:34:33 12 s.BACKPAY_DV_PART_VALUE AS BACKPAY_DV_PART_VALUE,
12:34:33 13 s.BACKPAY_TOTAL_VALUE AS BACKPAY_TOTAL_VALUE
12:34:33 14 FROM (SELECT Payment_Org_ID,
12:34:33 15 Practitioner_Code,
12:34:33 16 OMP,
12:34:33 17 Status,
12:34:33 18 BP_ST_VALUE,
12:34:33 19 BP_DV_FULL_VALUE,
12:34:33 20 BP_DV_PART_VALUE,
12:34:33 21 BP_TOTAL_VALUE
12:34:33 22 FROM temp_Agg_Backpay ag
12:34:33 23 -- WHERE Payment_Org_ID IN ( SELECT NHS_Org_ID
12:34:33 24 -- FROM Payment_Cycle_Orgs
12:34:33 25 -- WHERE Cycle_ID = 1002)
12:34:33 26 -- maybe add in payment cycle to check for unfinalised
12:34:33 27 -- AND OMP = 'NO'
12:34:33 28 ) bp,
12:34:33 29 Statements s
12:34:33 30 WHERE bp.Payment_Org_ID = s.Payment_Org_ID
12:34:33 31 AND bp.Practitioner_Code = s.Practitioner_Code
12:34:34 32 )
12:34:34 33 SET
12:34:34 34 BACKPAY_ST_VALUE = BP_ST_VALUE,
12:34:34 35 BACKPAY_DV_FULL_VALUE = BP_DV_FULL_VALUE,
12:34:34 36 BACKPAY_DV_PART_VALUE = BP_DV_PART_VALUE,
12:34:34 37 BACKPAY_TOTAL_VALUE = BP_TOTAL_VALUE;

107 rows updated.

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=30 Card=77 Bytes=746
9)

1 0 UPDATE OF 'STATEMENTS'
2 1 HASH JOIN (Cost=30 Card=77 Bytes=7469)
3 2 TABLE ACCESS (FULL) OF 'STATEMENTS' (Cost=13 Card=94 B
ytes=2350)

4 2 TABLE ACCESS (FULL) OF 'TEMP_AGG_BACKPAY' (Cost=16 Car
d=8168 Bytes=588096)





Statistics
----------------------------------------------------------
0 recursive calls
129 db block gets
85 consistent gets
0 physical reads
30628 redo size
1020 bytes sent via SQL*Net to client
1860 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
107 rows processed

12:34:35 OPT1@OP1>
12:34:35 OPT1@OP1>
12:35:13 OPT1@OP1>
12:35:14 OPT1@OP1>
12:35:14 OPT1@OP1>
12:35:14 OPT1@OP1>
12:35:14 OPT1@OP1>

-- now using the correlated update:



12:35:14 OPT1@OP1>
12:35:14 OPT1@OP1>
12:35:14 OPT1@OP1>
12:35:14 OPT1@OP1>UPDATE Statements
12:35:22 2 SET (BACKPAY_ST_VALUE,
12:35:22 3 BACKPAY_DV_FULL_VALUE,
12:35:22 4 BACKPAY_DV_PART_VALUE,
12:35:22 5 BACKPAY_TOTAL_VALUE) = ( SELECT BP_ST_VALUE,
12:35:22 6 BP_DV_FULL_VALUE,
12:35:22 7 BP_DV_PART_VALUE,
12:35:22 8 BP_TOTAL_VALUE
12:35:22 9 FROM Agg_Backpay ag
12:35:22 10 WHERE ag.Payment_Org_Id = Statements.Payment_Org_ID
12:35:22 11 AND ag.Practitioner_Code = Statements.Practitioner_Code
12:35:22 12 AND Payment_Org_ID IN ( SELECT NHS_Org_ID
12:35:22 13 FROM Payment_Cycle_Orgs
12:35:22 14 WHERE Cycle_ID = 1002)
12:35:22 15 AND backpay_run_id = 1
12:35:22 16 AND OMP = 'NO'
12:35:22 17 )
12:35:23 18 WHERE (Payment_org_id, practitioner_code) IN ( SELECT Payment_org_id, practitioner_c
ode
12:35:23 19 FROM Agg_Backpay ag
12:35:23 20 WHERE ag.Payment_Org_Id = Statements.Payment_Org_ID
12:35:23 21 AND ag.Practitioner_Code = Statements.Practitioner_Code
12:35:23 22 AND Payment_Org_ID IN ( SELECT NHS_Org_ID
12:35:23 23 FROM Payment_Cycle_Orgs
12:35:23 24 WHERE Cycle_ID = 1002)
12:35:23 25 AND OMP = 'NO'
12:35:23 26 );

107 rows updated.

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=13 Card=5 Bytes=125)
1 0 UPDATE OF 'STATEMENTS'
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'STATEMENTS' (Cost=13 Card=5 By
tes=125)

4 2 FILTER
5 4 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=2030)
6 5 INDEX (RANGE SCAN) OF 'PAYMENT_CYCLE_ORGS_IDX_001'
(NON-UNIQUE) (Cost=1 Card=1 Bytes=8)

7 5 SORT (JOIN) (Cost=1 Card=1 Bytes=2022)
8 7 TABLE ACCESS (FULL) OF 'AGG_BACKPAY' (Cost=1 Car
d=1 Bytes=2022)

9 0 NESTED LOOPS (Cost=6 Card=1 Bytes=2100)
10 9 TABLE ACCESS (BY INDEX ROWID) OF 'AGG_BACKPAY' (Cost=1 C
ard=1 Bytes=2087)

11 10 INDEX (RANGE SCAN) OF 'AGG_BACKPAY_PK' (UNIQUE) (Cost=
1 Card=1)

12 9 VIEW OF 'VW_NSO_1'
13 12 SORT (UNIQUE) (Cost=5 Card=1 Bytes=8)
14 13 INDEX (RANGE SCAN) OF 'PAYMENT_CYCLE_ORGS_IDX_001' (
NON-UNIQUE) (Cost=1 Card=1 Bytes=8)





Statistics
----------------------------------------------------------
0 recursive calls
1921 db block gets
1781 consistent gets
0 physical reads
29652 redo size
1019 bytes sent via SQL*Net to client
1683 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
249 sorts (memory)
0 sorts (disk)
107 rows processed


The STATEMENTS table in the prod instance will have about 4000 rows in it (and probably not increase much), whereas the Agg_Backpay table will definitely be larger at least 20000 rows upwards (possibly growing larger unless we clear it down regularly).

We are on 8.1.7.4

Tom Kyte
June 09, 2004 - 9:29 am UTC

4000 and 20000 rows is tiny, probably won't make much of a difference one way or the other.

You have to ask

"will it be more expensive to run a 4000 correlated subqueries then it would be to run a single query, into a temp table, and join both sets"


for 4000 rows, unless the correlated subquery is really nasty and takes measurable amount of time (more than say a couple thousandths of a second) -- it'll suffice. Otherwise, use the GTT.

please help on this query

reader, August 18, 2004 - 5:29 am UTC

Hi Tom,
Good day to you, I need your help on this query. I have a table with a few million records, sample structure as follows

transactions

transid number
price number
exetended_price number

sample data

transid price extended_price
1 100
1 100
1 100
1 -300

I want to have extended_price as sum(price) excluding negative values grouped on transid,in the above scenario it will be 300

using corelated subquery as below

update transactions t set extended_price = (select sum(price) from transactions t1 where
price >= 0 and t.transid = t1.transid)

the table is having a few million records and this is taking much time to get completed any alternative way to do this using analytical functions or some other way please suggest

Thanks in advance for your help.

Tom Kyte
August 18, 2004 - 8:31 am UTC

why not just compute it on the way OUT (eg: in your query that selects the data).

given that i doubt this table is "static" and you don't want to be updating a few "gazillion records" on a recurring basis?

thanks but still need further help on this

reader ( your fan ), August 18, 2004 - 8:39 am UTC

Hi Tom,
as always greatful to you for all your help but let me give you some more details may be I should have given before itself sorry for this, the table is populated after bulk collect of data selected from a few tables over db link can you please suggest how do i do this by the way you are mentioning it will really help me out.

waiting for you reply.

Thanks as always.

Tom Kyte
August 18, 2004 - 9:46 am UTC

create view v
as
select t.*,
(select sum(price) from transactions t1
where price >= 0 and t.transid = t1.transid) extended_price
from t;


use that to query -- don't do the update.

Please help me with this update

A reader, September 14, 2004 - 5:57 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

?????

A reader, September 14, 2004 - 6:13 pm UTC

ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
even thoug,

SELECT DISTINCT a.psdocae , b.CAE_NR
FROM caematch_cae b,member_pseudonym a,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
AND ROWNUM = 1

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

i don't see any subqueries there.

A reader, September 15, 2004 - 9:30 am UTC

UPDATE MEMBER_PSEUDONYM a
SET psdocae =( SELECT DISTINCT a.psdocae , b.CAE_NR
FROM CAEMATCH_CAE b,MEMBER_PSEUDONYM a,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
AND ROWNUM = 1)

Tom Kyte
September 15, 2004 - 10:09 am UTC

why not just set it to a random value?

"and rownum = 1"???!????




Please help with correlate update

michael, September 16, 2004 - 5:24 pm UTC

knbi_cams@KLROO1> UPDATE KCAMS_SAMPGRID SET (
2 SAMPGRID_NAME,
3 SAMPLE_TYPE_ID,
4 DATE_SAMPGRID_RECEIVED,
5 SAMPGRID_IS_ACTIVE
6 ) =
7 (SELECT PL_NAME,SAMPLE_TYPE_ID,MAX(PDATE) as PDATE ,'Y'
8 from KCAMS_PLATEMAP KPM,
9 KCAMS_SAMPLE_TYPE KST
10 where map_processing_id = 4 and platemap_error_id = 0 and PL_BARCODE = KCAMS_SAMPGRID.SAMPGR
ID_BARCODE
11 and KST.SAMPLE_TYPE_LABEL = KPM.SAMP_TYPE
12 group by PL_NAME,PL_BARCODE,SAMPLE_TYPE_ID,'Y'
13 )
14 WHERE SAMPGRID_BARCODE in (SELECT PL_BARCODE from KCAMS_PLATEMAP where map_processing_id = 4 an
d platemap_error_id = 0);

90 rows updated.

Elapsed: 00:01:17.08

Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=136)
1 0 UPDATE OF 'KCAMS_SAMPGRID'
2 1 NESTED LOOPS (SEMI) (Cost=2 Card=1 Bytes=136)
3 2 TABLE ACCESS (FULL) OF 'KCAMS_SAMPGRID' (Cost=2 Card=1
Bytes=93)

4 2 TABLE ACCESS (BY INDEX ROWID) OF 'KCAMS_PLATEMAP'
5 4 INDEX (RANGE SCAN) OF 'PLATEMAP_ERRORID_X' (NON-UNIQ
UE)

6 1 SORT (GROUP BY) (Cost=4 Card=1 Bytes=100)
7 6 NESTED LOOPS (Cost=2 Card=1 Bytes=100)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'KCAMS_PLATEMAP' (C
ost=1 Card=1 Bytes=86)

9 8 INDEX (RANGE SCAN) OF 'PLATEMAP_ERRORID_X' (NON-UN
IQUE) (Cost=1 Card=1)

10 7 TABLE ACCESS (BY INDEX ROWID) OF 'KCAMS_SAMPLE_TYPE'
(Cost=1 Card=1 Bytes=14)

11 10 INDEX (UNIQUE SCAN) OF 'UQ_KCAMS_SAMPLE_TYPE_NAME'
(UNIQUE)





Statistics
----------------------------------------------------------
0 recursive calls
188 db block gets
21425730 consistent gets
12 physical reads
43508 redo size
619 bytes sent via SQL*Net to client
1052 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
91 sorts (memory)
0 sorts (disk)
90 rows processed

knbi_cams@KLROO1> select count(*) from kcams_platemap
2 where map_processing_id = 4 and platemap_error_id = 0;

COUNT(*)
----------
34056

Elapsed: 00:00:00.00
knbi_cams@KLROO1> select count(*) from kcams_sample_type;

COUNT(*)
----------
12

Elapsed: 00:00:00.00
knbi_cams@KLROO1> select count(*) from kcams_sampgrid;

COUNT(*)
----------
329

Elapsed: 00:00:00.00
knbi_cams@KLROO1>

Is there a better method to perform the update since the logical i/o is huge?

Thank you for your time.



Tom Kyte
September 16, 2004 - 7:58 pm UTC

do it with sql_trace=true and tell us if the estimated cardinalities are even close to reality. are the stats up to date?

Thanks

Sean Drysdale, November 20, 2004 - 12:47 pm UTC

Usefull and solid information - thank you.

OK

James, February 07, 2005 - 10:05 am UTC

Hello Tom,
I am not able to do the update as follows.

SQL> create table t(x int)
  2  /

Table created.

SQL> insert into t select rownum from user_objects where rownum 

<= 5
  2  /

5 rows created.

SQL> /

5 rows created.

SQL> commit;

Commit complete.

SQL> select * from t
  2  /

         X                                                        

              
----------                                                        

              
         1                                                        

              
         2                                                        

              
         3                                                        

              
         4                                                        

              
         5                                                        

              
         1                                                        

              
         2                                                        

              
         3                                                        

              
         4                                                        

              
         5                                                        

              

10 rows selected.

SQL> update (select x from t where rownum between 6 and 10)
  2  set x = x+5
  3  /
update (select x from t where rownum between 6 and 10)
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view 


SQL> update ( select x from (select rownum rn,t.* from t)
  2           where rn between 6 and 10)
  3* set x = x+5
SQL> /
set x = x+5
    *
ERROR at line 3:
ORA-01733: virtual column not allowed here

Any correlated update possible here??

 

Tom Kyte
February 07, 2005 - 10:59 am UTC

you cannot use rownum like that -- that statement would be totally "non-deterministic" -- rows 6 through 10, what are those? there is no "concept" of rows 6 - 10.

if it worked, it could update a totally different set of rows each time it was executed.


however....

update t
set x = x+5
where rowid in ( select rid
from (select a.*, rownum r
from (select rowid rid from t order by x) a
where rownum <= 10 )
where r >= 6);

that is, get the rowids you are interested in.


update on table

SR, February 10, 2005 - 1:50 pm UTC

Hi Tom! I have a correlated update question. Hope you get chance to take a look at it and suggest some way..

create table emp(id number, sal number);
create table sales1(id number, dttm date default sysdate, amount number);
create table sales2(id number, dttm date default sysdate, amount number);

insert into emp values (1,100000);
insert into emp values (2,100000);
insert into emp values (3,100000);
insert into emp values (4,100000);
insert into sales1 values (1,sysdate,5000);
insert into sales1 values (4,sysdate,8000);
insert into sales2 values (1,sysdate,2000);
insert into sales2 values (4,sysdate,1000);

  1  select e.id, sum(s1.amount) sum1, sum(s2.amount) sum2
  2  from emp e, sales1 s1, sales2 s2
  3  where
  4  e.id=s1.id and
  5  e.id=s2.id
  6* group by e.id
SQL> /

        ID       SUM1       SUM2
---------- ---------- ----------
         1       5000       2000
         4       8000       1000

What I want to do is (broken down)

update emp set sal = sal + 5000 + 2000 where id = 1;
update emp set sal = sal + 8000 + 1000 where id = 4;

Can above select + update be run as one statement?

Thank you

SR

PS: Since sales1/sales2 are on a (same) remote database (and are huge), pl/sql approach seems like killing performance with arraysize of 1. Thus trying to get one statement do it all seems like better. Thanks 

Tom Kyte
February 11, 2005 - 7:27 pm UTC

is emp "local" then? or all on remote?

AND -- are you sure your query is "correct" -- is ID a primary key in both sales1 and sales2 and is there an implicit foreign key from sales1/sales2 to EMP? (eg: is emp preserved in a join to sales1 and sales2?)

and what is huge to you?

Tom, can you please check my question above?

SR, February 11, 2005 - 6:36 am UTC

TIA

SR

Tom Kyte
February 11, 2005 - 8:20 pm UTC

sorry -- i travel, i work, i never have promised to just be sitting here waiting....


I was on a plane for 9 hours -- no internet at 36,000 feet (not on the airline i was on...)

Sorry Tom!

SR, February 12, 2005 - 10:13 pm UTC

I did not mean to be impatient. I fully understand your busy schedule. If you get chance can you please provide some help in my question above. Its a problem in production I am trying to address. TIA

Tom for your followup

SR, February 12, 2005 - 10:25 pm UTC

I am sorry, I did not see your questions in follow up.


"Followup:

is emp "local" then? or all on remote?

AND -- are you sure your query is "correct" -- is ID a primary key in both
sales1 and sales2 and is there an implicit foreign key from sales1/sales2 to
EMP? (eg: is emp preserved in a join to sales1 and sales2?)

and what is huge to you?
"

Yes emp is local. This is a made up example to get idea of possible approach. emp has lets say about 200k records. sales1/sales2 has about 8million each. When I traced both local and remote database it seemed like using remote site as driving site helps, however I am not getting any clear feel for how to avoid row-by-row nested loop?

In theory it seems like it should do
1) send all emp id's to remote database
2) join sales1, sales2 against those emp id's
3) ship the result set back to local database

sales1/sales2 has fk with emp implemented by program. They open two connection handles (one local, one remote) and using emp id, insert values into sales1/2.

--> is emp preserved in a join to sales1 and sales2?
If you mean let say emp id 100 is same employee in sales1 and sales2 - then yes.

Any help will be appreciated.

Thanks

Tom Kyte
February 13, 2005 - 9:13 am UTC

by preserved, the query below:

  1  select e.id, sum(s1.amount) sum1, sum(s2.amount) sum2
  2  from emp e, sales1 s1, sales2 s2
  3  where
  4  e.id=s1.id and
  5  e.id=s2.id
  6* group by e.id
SQL> /

would have to have:

a) a row in sales1 for EVERY e.id
b) a row in sales2 for EVERY e.id
c) at MOST one row in sales1
d) at MOST one row in sales2

otherwise you will

a) lose id's in the join
b) due to cartesian products "multiply" the numbers out.


so, do you satisfy that?  

And -- for sales1, sales2 -- are there alot of E.ID values that are in those tables that are NOT in E 

Thanks for feedback

SR, February 14, 2005 - 10:45 am UTC

Tom,

the sales1, sales2 will have more than one record per e.id (potentially). So your comment on "multiply" are very applicable. Thanks for catching that. I did not think about it. Until I added more than one row per e.id then I saw they are getting "multiplied".

I am interested in only rows from sales1 and sales2 if both of them have at least one row for same e.id

insert into sales1 values (1,sysdate,500);
insert into sales2 values (1,sysdate,500);
commit;

my original sql is giving incorrect result:
SQL> l
  1    select e.id, sum(s1.amount) sum1, sum(s2.amount) sum2
  2    from emp e, sales1 s1, sales2 s2
  3    where
  4    e.id=s1.id and
  5    e.id=s2.id
  6* group by e.id

        ID       SUM1       SUM2
---------- ---------- ----------
         1      11000       5000
         4       8000       1000


SQL> select e.id, sum(s1.amount), sum(s2.amount)
  2  from emp e, (select id, sum(amount) amount from sales1 group by id) s1,
  3  (select id, sum(amount) amount from sales2 group by id) s2
  4  where e.id=s1.id and e.id=s2.id
  5  group by e.id;

        ID SUM(S1.AMOUNT) SUM(S2.AMOUNT)
---------- -------------- --------------
         1           5500           2500
         4           8000           1000


Your questions ..

"
would have to have:

a) a row in sales1 for EVERY e.id

its not every id.

b) a row in sales2 for EVERY e.id

its not every id.

c) at MOST one row in sales1

no, can be more than one

d) at MOST one row in sales2

no, can be more than one

otherwise you will

a) lose id's in the join

Thats the criteria if sales1, sales2 has atleast one row for same e.id then they should be considered. Thus if there is row in sales1, but for that id no row in sales2 - ignore that e.id alltogether

b) due to cartesian products "multiply" the numbers out.

--> Group by on sales1 and sales2 prior to joining them (I have listed above) can remove the multiply effect. Thanks for catching that. Actually, I don't think I have seen this situation before my self. Learned something new.

so, do you satisfy that?  

And -- for sales1, sales2 -- are there alot of E.ID values that are in those 
tables that are NOT in E 

--> all sales1/sales2 id's should be from e.id. Was this your question?

Thanks again.
 

Tom Kyte
February 14, 2005 - 1:45 pm UTC

select e.id, sum(s1.amount), sum(s2.amount)
from emp e, (select id, sum(amount) amount from sales1 group by id) s1,
(select id, sum(amount) amount from sales2 group by id) s2
where e.id=s1.id and e.id=s2.id
group by e.id;


would be correct then and *probably* should be optimized on the remote site (it would be best if you could create that as a VIEW on the remote site)




Thanks!

SR, February 14, 2005 - 3:58 pm UTC

I will try some test with that.

Thanks again.

ORA-01733 error

vll, February 16, 2005 - 5:00 pm UTC

8.1.7

set feedback off
set define off
create table test_table
(
A_KEY NUMBER not null,
RECORD_NUMBER NUMBER not null,
PRODUCT VARCHAR2(15)
)
;
alter table test_table
add constraint PK_TES primary key (A_KEY, RECORD_NUMBER);
insert into test_table (A_KEY, RECORD_NUMBER, PRODUCT)
values (1, 1, 'XXX');
insert into test_table (A_KEY, RECORD_NUMBER, PRODUCT)
values (1, 2, 'YYY');
insert into test_table (A_KEY, RECORD_NUMBER, PRODUCT)
values (1, 3, 'ZZZ');
insert into test_table (A_KEY, RECORD_NUMBER, PRODUCT)
values (2, 2, 'YYY');
insert into test_table (A_KEY, RECORD_NUMBER, PRODUCT)
values (2, 3, 'ZZZ');
insert into test_table (A_KEY, RECORD_NUMBER, PRODUCT)
values (3, 1, 'XXX');
insert into test_table (A_KEY, RECORD_NUMBER, PRODUCT)
values (3, 3, 'ZZZ');
commit;
set feedback on
set define on

In "perfect" world of the application, that is using this data, record_number field always starts with 1 and is incremented by 1 for every next record for specific A_KEY. But nothing is "perfect", so sometimes we need to "restore" this record_number when application drops records somewhere in the middle of this sequence and "forgets" to reorder the whole list.
Query below alows to find these records:

select * from
(select a_key, product, record_number,
row_number() over (partition by a_key order by record_number) real_record_number from test_table)
where record_number<>real_record_number;
A_KEY PRODUCT RECORD_NUMBER REAL_RECORD_NUMBER
---------- --------------- ------------- ------------------
2 YYY 2 1
2 ZZZ 3 2
3 ZZZ 3 2
But the update query
update
(select * from
(select a_key, product, record_number,
row_number() over (partition by a_key order by record_number) real_record_number from test_table)
where record_number<>real_record_number)
set record_number=real_record_number;

gives ORA-01733: virtual column not allowed here. What can be solution to do this update?

Thank you very much!!!

Tom Kyte
February 16, 2005 - 5:35 pm UTC

in the perfect world we recognize that sequences have gaps and just need to be increasing!!

so you have the perfect world analogy backwards. In the worst of cases, an application would for some reason mandate that the numbers start at one and be sequential! :)


create global temporary table gtt ( rid rowid primary key, record_number );

do that once.. then:

insert into gtt
select rid, record_number
from
(select rowid rid, record_number,
row_number() over (partition by a_key order by record_number) real_record_number
from test_table)
where record_number<>real_record_number;

and then update the join of test_table to gtt by rowid.

what if there were committed transactions between 1 and 2?

vll, February 17, 2005 - 4:50 pm UTC

What if there were committed transactions over this table between filling temp table and update of the join? In other words - what if original table changes since this snapshot of rowid is taken. Can this be a problem?

Tom Kyte
February 17, 2005 - 7:35 pm UTC

you can either

a) run serializable

or

b) lock table and then proceed

you would have a similar issue in a single statement as well -- read consistency would not see uncommitted pending changes either


reason #5415133 why this "gap free, sequential sequence" is a bad idea, they get stale straight away

correlated query fails

Kumar Balagopal, April 13, 2005 - 1:51 pm UTC

Hi Tom,

Is it possible to rewrite following correlated update so it does not fail (ORA-00904: "t2"."pkey": invalid identifier)?


create table T1
(
RECPT NUMBER(11),
FKEY NUMBER(11),
);
create table T2
(
PKEY NUMBER(11),
RECPT NUMBER(11),
SOME_ATTR CHAR(1),
PKEY_171 NUMBER(11)
);
create table T3
(
RECPT NUMBER(11),
FKEY NUMBER(11),
);


update T1
set fkey =
( select T2.pkey
from T2 t2, T3
where t2.recpt = T1.recpt
and t2.recpt = T3.recpt
and t2.pkey = T3.fkey
and exists
(
select 'x'
from T2 t22,
(
select substr(history,2,instr(history,'/',1,2)-2) parent,
substr(history, 2,instr(history,'/',-1,1)+1) child
from (
select level depth,
SYS_CONNECT_BY_PATH( pkey,'/' ) history
from T2
start with pkey = t2.pkey
connect by prior pkey = pkey_171
)
where depth > 1
) s0
where to_number( s0.child ) = t22.pkey
and to_number( s0.parent ) = t2.pkey
and t22.some_attr = '6'
)
)
where exists (
...
);

Tom Kyte
April 13, 2005 - 2:18 pm UTC

sorry, with the multiple t2's in there, Not really sure who is what.

but a correlation variable can only go one level down. so, you would have to remove a level which looks doable.

but i need at least two levels

Kumar, April 13, 2005 - 3:29 pm UTC

The inner most subquery gives me:

     DEPTH HISTORY
---------- --------------------------------------------------------------------------------
         1 /12345678987
         2 /12345678987/23456789876
         3 /12345678987/23456789876/34567898765
         4 /12345678987/23456789876/34567898765/45678987654

so I need additional level to get history of given pkey:

123456787       23456789876
123456787       34567898765
123456787       45678987654

using:

column parent format a15
column child format a80
select substr( history, 2, instr( history,'/',1,2 )-2 ) parent,
       substr( history, instr( history,'/',-1,1 )+1 ) child
   from (
         select level depth, 
                SYS_CONNECT_BY_PATH( pkey,'/' ) history 
         from   T2  
         start with pkey = &pkey
         connect by prior pkey = pkey_171
         )
    where depth > 1;

and then join this subquery again with T2 to check for some_attr in that table.
So it seems this particular start-connect by statement "start with pkey = ..." cannot be moved to upper level?!

Here is test data I used:

create table BKT1
(
  RECPT                 NUMBER(11),
  FKEY                  NUMBER(11)
);
create table BKT2
(
  PKEY                  NUMBER(11),
  RECPT                 NUMBER(11),
  SOME_ATTR             CHAR(1),
  PKEY_171              NUMBER(11)
);
create table BKT3
(
  RECPT                 NUMBER(11),
  FKEY                  NUMBER(11)
);

insert into bkt1 (recpt, fkey) values (111111, null);
insert into bkt1 (recpt, fkey) values (222222, null);
insert into bkt1 (recpt, fkey) values (333333, null);
insert into bkt1 (recpt, fkey) values (444444, null);
insert into bkt1 (recpt, fkey) values (555555, null);
insert into bkt1 (recpt, fkey) values (666666, null);

insert into bkt2 (pkey, recpt, some_attr, pkey_171) values (12345678987, 111111, '7', null);
insert into bkt2 (pkey, recpt, some_attr, pkey_171) values (23456789876, 111111, '2', 12345678987);
insert into bkt2 (pkey, recpt, some_attr, pkey_171) values (34567898765, 111111, '3', 23456789876);
insert into bkt2 (pkey, recpt, some_attr, pkey_171) values (45678987654, 111111, '6', 34567898765);
insert into bkt2 (pkey, recpt, some_attr, pkey_171) values (56789876543, 222222, '1', null);
insert into bkt2 (pkey, recpt, some_attr, pkey_171) values (67898765432, 333333, '7', null);
insert into bkt2 (pkey, recpt, some_attr, pkey_171) values (78987654321, 333333, '6', 67898765432);
insert into bkt2 (pkey, recpt, some_attr, pkey_171) values (89876543212, 444444, '1', null);
insert into bkt2 (pkey, recpt, some_attr, pkey_171) values (987654321, 555555, '5', null);
insert into bkt2 (pkey, recpt, some_attr, pkey_171) values (87654321, 666666, '7', null);
insert into bkt2 (pkey, recpt, some_attr, pkey_171) values (7654321, 66666666, '4', 87654321);
insert into bkt2 (pkey, recpt, some_attr, pkey_171) values (654321, 66666666, '3', 7654321);
insert into bkt2 (pkey, recpt, some_attr, pkey_171) values (54321, 66666666, '2', 654321);
insert into bkt2 (pkey, recpt, some_attr, pkey_171) values (4321, 66666666, '6', 54321);
insert into bkt2 (pkey, recpt, some_attr, pkey_171) values (321, 66666666, '6', 4321);

insert into bkt3 (recpt, fkey) values (111111, 12345678987);
insert into bkt3 (recpt, fkey) values (333333, 67898765432);
insert into bkt3 (recpt, fkey) values (666666, 87654321);

SQL> select * from bkt1;

       RECPT         FKEY
------------ ------------
      111111 
      222222 
      333333 
      444444 
      555555 
      666666 

6 rows selected

SQL> select * from bkt2;

        PKEY        RECPT SOME_ATTR     PKEY_171
------------ ------------ --------- ------------
 12345678987       111111 7         
 23456789876       111111 2          12345678987
 34567898765       111111 3          23456789876
 45678987654       111111 6          34567898765
 56789876543       222222 1         
 67898765432       333333 7         
 78987654321       333333 6          67898765432
 89876543212       444444 1         
   987654321       555555 5         
    87654321       666666 7         
     7654321     66666666 4             87654321
      654321     66666666 3              7654321
       54321     66666666 2               654321
        4321     66666666 6                54321
         321     66666666 6                 4321

15 rows selected

SQL> select * from bkt3;

       RECPT         FKEY
------------ ------------
      111111  12345678987
      333333  67898765432
      666666     87654321

update bkt1
set fkey = 
 ( select bkt2.pkey 
   from   bkt2 t2, bkt3 
   where  t2.recpt = bkt1.recpt
   and    t2.recpt = bkt3.recpt
   and    t2.pkey  = bkt3.fkey
   and exists 
   (
   select 'x'
   from bkt2 t22,
       ( 
   select substr(history,2,instr(history,'/',1,2)-2) parent,
          substr(history, instr(history,'/',-1,1)+1) child
   from (
         select level depth, 
                SYS_CONNECT_BY_PATH( pkey,'/' ) history 
         from   bkt2  
         start with pkey = t2.pkey
         connect by prior pkey = pkey_171
         )
    where depth > 1
        ) s0
   where  to_number( s0.child ) = t22.pkey
   and    to_number( s0.parent ) = t2.pkey
   and    t22.some_attr = '6'
     )
  )
where exists ( 
  select bkt2.pkey 
   from   bkt2 t2, bkt3 
   where  t2.recpt = bkt1.recpt
   and    t2.recpt = bkt3.recpt
   and    t2.pkey  = bkt3.fkey
   and exists 
   (
   select 'x'
   from bkt2 t22,
       ( 
   select substr(history,2,instr(history,'/',1,2)-2) parent,
          substr(history, instr(history,'/',-1,1)+1) child
   from (
         select level depth, 
                SYS_CONNECT_BY_PATH( pkey,'/' ) history 
         from   bkt2  
         start with pkey = t2.pkey
         connect by prior pkey = pkey_171
         )
    where depth > 1
        ) s0
   where  to_number( s0.child ) = t22.pkey
   and    to_number( s0.parent ) = t2.pkey
   and    t22.some_attr = '6'
     )
); 

ORA-00904: "BKT2"."PKEY": invalid identifier 

Tom Kyte
April 13, 2005 - 4:20 pm UTC

but they only go one level and I cannot change that fact.

do the substr one layer down, you don't need the inline view of the inline view to substr history, you can do that in one layer and you can apply the depth >1 later

I did that and got error:

Kumar, April 13, 2005 - 5:13 pm UTC

update bkt1
set fkey =
( select t2.pkey
from bkt2 t2, bkt3 t3
where t2.recpt = bkt1.recpt
and t2.recpt = t3.recpt
and t2.pkey = t3.fkey
and exists
(
select
substr( SYS_CONNECT_BY_PATH( bkt2.pkey,'/' ),2,instr(SYS_CONNECT_BY_PATH( bkt2.pkey,'/' ),'/',1,2)-2 ) prnt,
substr( SYS_CONNECT_BY_PATH( bkt2.pkey,'/' ), instr(SYS_CONNECT_BY_PATH( bkt2.pkey,'/' ),'/',-1,1)+1 ) chld
from bkt2, bkt2 same
where level > 1
and to_number(chld) = same.pkey
and to_number(prnt) = t2.pkey
and same.some_attr = '6'
start with bkt2.pkey = t2.pkey
connect by prior bkt2.pkey = bkt2.pkey_171
)
)
where exists (
select t2.pkey
from bkt2 t2, bkt3 t3
where t2.recpt = bkt1.recpt
and t2.recpt = t3.recpt
and t2.pkey = t3.fkey
and exists
(
select
substr( SYS_CONNECT_BY_PATH( bkt2.pkey,'/' ),2,instr(SYS_CONNECT_BY_PATH( bkt2.pkey,'/' ),'/',1,2)-2 ) prnt,
substr( SYS_CONNECT_BY_PATH( bkt2.pkey,'/' ), instr(SYS_CONNECT_BY_PATH( bkt2.pkey,'/' ),'/',-1,1)+1 ) chld
from bkt2, bkt2 same
where level > 1
and to_number(chld) = same.pkey
and to_number(prnt) = t2.pkey
and same.some_attr = '6'
start with bkt2.pkey = t2.pkey
connect by prior bkt2.pkey = bkt2.pkey_171
)
)

ORA-00904: "PRNT": invalid identifier


Tom Kyte
April 13, 2005 - 5:20 pm UTC

select
substr( SYS_CONNECT_BY_PATH( bkt2.pkey,'/'
),2,instr(SYS_CONNECT_BY_PATH( bkt2.pkey,'/' ),'/',1,2)-2 ) prnt,
substr( SYS_CONNECT_BY_PATH( bkt2.pkey,'/' ),
instr(SYS_CONNECT_BY_PATH( bkt2.pkey,'/' ),'/',-1,1)+1 ) chld
from bkt2, bkt2 same
where level > 1
and to_number(chld) = same.pkey
and to_number(prnt) = t2.pkey

prnt doesn't exist yet, so you cannot to number it yet. so to_number the substr(...) that is prnt.

But even this does not work:

Kumar, April 13, 2005 - 5:34 pm UTC

select
to_number(substr( SYS_CONNECT_BY_PATH( bkt2.pkey,'/' ),2,instr(SYS_CONNECT_BY_PATH( bkt2.pkey,'/' ),'/',1,2)-2 )) prnt,
to_number(substr( SYS_CONNECT_BY_PATH( bkt2.pkey,'/' ), instr(SYS_CONNECT_BY_PATH( bkt2.pkey,'/' ),'/',-1,1)+1 )) chld
from bkt2, bkt2 same
where level > 1
and chld = same.pkey
and prnt = t2.pkey
and same.some_attr = '6'
start with bkt2.pkey = t2.pkey
connect by prior bkt2.pkey = bkt2.pkey_171

Now I to_number() after substr() (i.e. after it came to exist)!?

Tom Kyte
April 13, 2005 - 7:25 pm UTC

what does "this does not work" mean.

remember, I haven't your tables or anything here, if you simplify it down and give me a small, concise test case I can work with that.

I removed START WITH to avoid original problem

Kumar, April 13, 2005 - 6:14 pm UTC

but got another error:

update bkt1
set fkey =
( select t2.pkey
from bkt2 t2, bkt3 t3
where t2.recpt = bkt1.recpt
and t2.recpt = t3.recpt
and t2.pkey = t3.fkey
and exists
(
select 'x'
from bkt2 t22,
(
select to_number(substr(history,2,instr(history,'/',1,2)-2)) parent,
to_number(substr(history, instr(history,'/',-1,1)+1)) child
from (
select level depth,
SYS_CONNECT_BY_PATH( pkey,'/' ) history
from bkt2
connect by prior pkey = pkey_171
)
where depth > 1
) s0
where child = t22.pkey
and parent = t2.pkey
and t22.some_attr = '6'
)
)
where exists (
select t2.pkey
from bkt2 t2, bkt3 t3
where t2.recpt = bkt1.recpt
and t2.recpt = t3.recpt
and t2.pkey = t3.fkey
and exists
(
select 'x'
from bkt2 t22,
(
select to_number(substr(history,2,instr(history,'/',1,2)-2)) parent,
to_number(substr(history, instr(history,'/',-1,1)+1)) child
from (
select level depth,
SYS_CONNECT_BY_PATH( pkey,'/' ) history
from bkt2
connect by prior pkey = pkey_171
)
where depth > 1
) s0
where child = t22.pkey
and parent = t2.pkey
and t22.some_attr = '6'
)
)

ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value.

I tried different separators, but every time I get same 30004 error. Can this be resolved at all and how?

thanks,

ok, here is very simple test

Kumar, April 14, 2005 - 10:31 am UTC

create table BKT1
(
RECPT NUMBER(11),
FKEY NUMBER(11)
);
create table BKT2
(
PKEY NUMBER(11),
RECPT NUMBER(11),
SOME_ATTR CHAR(1),
PKEY_171 NUMBER(11)
);
create table BKT3
(
RECPT NUMBER(11),
FKEY NUMBER(11)
);

insert into bkt1 (recpt, fkey) values (111111, null);
insert into bkt1 (recpt, fkey) values (222222, null);
insert into bkt1 (recpt, fkey) values (333333, null);
insert into bkt1 (recpt, fkey) values (444444, null);
insert into bkt1 (recpt, fkey) values (555555, null);
insert into bkt1 (recpt, fkey) values (666666, null);

insert into bkt2 (pkey, recpt, some_attr, pkey_171)
values (12345678987, 111111, '7', null);
insert into bkt2 (pkey, recpt, some_attr, pkey_171)
values (23456789876, 111111, '2', 12345678987);
insert into bkt2 (pkey, recpt, some_attr, pkey_171)
values (34567898765, 111111, '3', 23456789876);
insert into bkt2 (pkey, recpt, some_attr, pkey_171)
values (45678987654, 111111, '6', 34567898765);

insert into bkt3 (recpt, fkey) values (111111, 12345678987);


update bkt1
set fkey =
( select bkt2.pkey
from bkt2 t2, bkt3
where t2.recpt = bkt1.recpt
and t2.recpt = bkt3.recpt
and t2.pkey = bkt3.fkey
and exists
(
select 'x'
from bkt2 t22,
(
select substr(history,2,instr(history,'/',1,2)-2) parent,
substr(history, instr(history,'/',-1,1)+1) child
from (
select level depth,
SYS_CONNECT_BY_PATH( pkey,'/' ) history
from bkt2
start with pkey = t2.pkey
connect by prior pkey = pkey_171
)
where depth > 1
) s0
where to_number( s0.child ) = t22.pkey
and to_number( s0.parent ) = t2.pkey
and t22.some_attr = '6'
)
)
where exists (
select bkt2.pkey
from bkt2 t2, bkt3
where t2.recpt = bkt1.recpt
and t2.recpt = bkt3.recpt
and t2.pkey = bkt3.fkey
and exists
(
select 'x'
from bkt2 t22,
(
select substr(history,2,instr(history,'/',1,2)-2) parent,
substr(history, instr(history,'/',-1,1)+1) child
from (
select level depth,
SYS_CONNECT_BY_PATH( pkey,'/' ) history
from bkt2
start with pkey = t2.pkey
connect by prior pkey = pkey_171
)
where depth > 1
) s0
where to_number( s0.child ) = t22.pkey
and to_number( s0.parent ) = t2.pkey
and t22.some_attr = '6'
)
);

This statement is supposed to fill in the null in bkt1 table where pkey is 111111, nothing else.

If I remove "start with .." statement, everything will work, because there is no propagation problem. But for efficiency reasons it would help to find solution with "start with .." as it cuts down on the number of rows retrieved.
It seems that there must be wrapping inline view around inline view containing substr(...sys_connect..) as it seems it can only materialize when the outer view refers to it!?


Tom Kyte
April 14, 2005 - 10:51 am UTC

sorry, as i keep saying -- you can only push the correlation value down ONE level, one. it cannot, it will not go two, three or more.

too big for me to digest and rewrite here, but unless you can remove levels, it won't happen as coded.

You'll want to look for a way to rewrite:

select 'x'
from bkt2 t22,
(
select substr(history,2,instr(history,'/',1,2)-2) parent,
substr(history, instr(history,'/',-1,1)+1) child
from (
select level depth,
SYS_CONNECT_BY_PATH( pkey,'/' ) history
from bkt2
start with pkey = t2.pkey
connect by prior pkey = pkey_171
)
where depth > 1
) s0
where to_number( s0.child ) = t22.pkey
and to_number( s0.parent ) = t2.pkey
and t22.some_attr = '6'
)

into maybe:


select null
from bkt2 t22
where t22.some_attr = '6'
and substr( ( select max( to_char(level,'fm00000') || pkey )
from bkt2
start with pkey = t22.pkey
connect by prior pkey = pkey_171 ) , 6 ) = t2.pkey


for example. take the some_attr = '6' values, feed their pkey (the child) into the connect by, get the parent (taking the max( level ) recode from that) and seeing if that is t2.pkey

turn it inside out (not sure if I have the connect by correct there, but you get the gist)

Alex, September 15, 2005 - 2:52 pm UTC

Hi Tom,

I'm looking to consolidate some code and move a query into an update statement similar to one you used above:

ops$tkyte@ORA920> update ( select p.date_of_birth dob1, s.date_of_birth dob2,
2 p.update_by, p.update_date, p.load_timestamp
3 from pgp_person p, pgp_cruise_history c, ship_passenger s
4 where p.person_num = s.person_num
5 and s.voy_num = c.voy_num
6 and s.book_num = c.book_num
7 and s.seq_num = c.seq_num
8 and c.matched_ind is not null
9 and s.date_of_birth <> p.date_of_birth )
10 set dob1 = dob2,
11 update_by = 'sdm18649',
12 update_date = trunc(sysdate),
13 load_timestamp = sysdate;

I'm just unsure how updates like this work. Is this saying, "select the rows we are going to update...." or does it say "select the rows we are going to update with..."?

I looked at the documentation, didn't see much explanation However, I saw something that looked incorrect:

</code> http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_10007.htm#SQLRF01708 <code>

UPDATE TABLE(SELECT projs
FROM dept d WHERE d.dno = 123) p
SET p.budgets = p.budgets + 1
WHERE p.pno IN (123, 456);

Shouldn't "TABLE" be removed from this to be correct? Thanks.

Tom Kyte
September 15, 2005 - 3:00 pm UTC

that'll take each of your rows from the query in lines 1..9 - and then update them (since the update I had doesn't filter anything else out)

update ( TABLE )
set columns = values
where <any other conditions>


Just pretend the query is a "table" - it is in effect.


yes, that table doesn't look like in the last example

Peter, December 01, 2005 - 10:53 am UTC

Hello Tom,

thank you for great join update explanation,
we have:

CREATE TABLE cs_acct (
acct_id INT,
tot_fees NUMBER
);

INSERT INTO cs_acct(acct_id) VALUES (1);
INSERT INTO cs_acct(acct_id) VALUES (2);

CREATE TABLE cs_fees (
fees_id INT,
acct_id INT,
volume NUMBER
);

INSERT INTO cs_fees VALUES( 1, 1, 10);
INSERT INTO cs_fees VALUES( 2, 1, 1);
INSERT INTO cs_fees VALUES( 3, 2, 1);
INSERT INTO cs_fees VALUES( 4, 2, 5);

so far we used "where exists" type of update
to allocate fees at the end of month from detail table
to master acct table.

UPDATE cs_acct a
SET tot_fees=(SELECT SUM(volume) FROM cs_fees WHERE acct_id=a.acct_id)
WHERE EXISTS (SELECT 1 FROM cs_fees WHERE acct_id=a.acct_id)

now i tried to make join update with a bypass_ujvc hint.

UPDATE (
SELECT /*+bypass_ujvc */ a.tot_fees, f.volume FROM (
SELECT acct_id, SUM(volume) volume
FROM cs_fees GROUP BY acct_id) f,
cs_acct a
WHERE a.acct_id=f.acct_id
) SET tot_fees=volume;

with few millions of accounts and feesm, our time went down from 4h to 3min!

Now i'm wondering if this speed up is for free:-) you said that /*+bypass_ujvc */ is undocumented and should be avoided. My inner select return always just one value for acct_id (group by acct_id) is there any potential risk with new update?

thank you


Tom Kyte
December 01, 2005 - 12:51 pm UTC

be careful, as long as the join columns in the source table are unique (they are, the group by does that), this is "sort of safe", you might find the hint disappears in the future (i'd say unlikely)

in 10g, we'd use merge

merge into cs_acct
using ( select from cs_fees group by ) x
on ( join_condition )
when matched then update ...;



More vi stuff

Shannon St. Dennis, December 01, 2005 - 3:39 pm UTC

I was raised using vi.. still use it every day.

Just a quick vi addition to:
January 26, 2004
Reviewer: A reader

Tom,

Instead of using replace and then , with down arrow & period for repitition is
there some command that will do that at once.

Thanks.


Followup:
:1,$s/.{5,5}/&,/

will add a comma in column 5 (using vim anyway -- might depend on your editor)
===========

you can also use (if they are spaces in between the columns):
:1,$s/ [ ]*/,/

this will change any pattern of space followed by any number of spaces to comma.


Correlated update with constant

putchi, January 02, 2006 - 10:09 am UTC

Hi Tom!

In a corelated update I need a unique constraint on the source table in order to get a deterministic outcome of the update. Is there any way that I can convince Oracle that the outcome is deterministic when I provide a constant value for one of the primary key columns in the source table?

SQL> drop table a;

Tabell õr borttagen.

SQL>
SQL> create table a (code varchar2(10)
  2                 ,cst  varchar2(10)
  3                 ,col  varchar2(10)
  4  ,constraints a_pk primary key (code,cst));

Tabellen õr skapad.

SQL>
SQL> drop table b;

Tabell õr borttagen.

SQL>
SQL> create table b (code varchar2(10)
  2                 ,col  varchar2(10));

Tabellen õr skapad.

SQL>
SQL> update (select a.col a_col
  2                ,b.col b_col
  3          from a,b
  4          where a.code = b.code
  5            and a.cst  = 1)
  6  set b_col = a_col;
set b_col = a_col
    *
Fel pÕ rad 6:
ORA-01779: cannot modify a column which maps to a non key-preserved table 

Tom Kyte
January 02, 2006 - 11:14 am UTC

ops$tkyte@ORA10GR1> select * from a;

CODE       CST        COL
---------- ---------- ----------
1          1          100
1          2          500
3          1          100

ops$tkyte@ORA10GR1> select * from b;

CODE       COL
---------- ----------
1
2

ops$tkyte@ORA10GR1> merge into b
  2  using ( select * from a where cst = 1) a
  3  on (a.code = b.code)
  4  when matched then update set b.col = a.col;

1 row merged.

ops$tkyte@ORA10GR1> select * from b;

CODE       COL
---------- ----------
1          100
2

 

Still Oracle9i

putchi, January 02, 2006 - 11:29 am UTC

OK, but I'm currently using Oracle9i, so merge without insert is not an option at the moment.

Tom Kyte
January 02, 2006 - 1:11 pm UTC

well, if you don't say - I don't ask and assume current software :)


You can use merge with an insert component.  Just make sure nothing "new" appears, like this:


ops$tkyte@ORA10GR1> merge into b
  2  using ( select * from a where cst = 1 and code in (select code from b) ) a
  3  on (a.code = b.code)
  4  when matched then update set b.col = a.col
     when not matched then insert (col) values ( null); <<<== this'll never 
                                                              happen



1 row merged. 

Great!

putchi, January 03, 2006 - 3:06 am UTC

Thanks Tom!

Alex, January 05, 2006 - 11:58 am UTC

Hello Tom,

I'm working on consolidating some code in procedures we have where they have a select procedure to select some rows, then out them to the application, then the application will call an update procedure. Obviously not efficient. The thing is though I'm terrible at writing update statements using queries so If you could just help me get it this simple example it will give me the formula for what I'm doing.

If I have 2 updates like so:

update roster
set name = p_name
where player_id = p_plyr_id

update roster
set position = p_position
where team = p_team
and salary = p_salary

Can we make this one update?

Tom Kyte
January 05, 2006 - 1:17 pm UTC

why would you want to ? they are complete different looking transactions to me - doesn't seem likely you would update an individual player name AND the position of a set of people in some team having a given salary.

could we? sure.
should we? I seriously doubt it.

Alex

A reader, January 05, 2006 - 1:45 pm UTC

Ok I think we are on the same page. My thought was they were probably ok separate but I've seen you do a lot of cool complex updates I just didn't know if there where ways to enforce multiple conditions against the same table in an update.

Did you mean that we COULD combine them like this:

update roster
set position = p_position,
name = p_name
where team = p_team
and salary = p_salary
and player_id = p_plyr_id;

But that would effect completely different rows so I wouldn't want to?

Tom Kyte
January 05, 2006 - 2:03 pm UTC

No, logically:

update roster
set name = p_name
where player_id = p_plyr_id

update roster
set position = p_position
where team = p_team
and salary = p_salary


could be:

update roster
set name = (case when player_id = p_plyr_id
then p_name
else name
end),
position = (case when team = p_team and salary = p_salary
then p_position
else position
end)
where player_id = p_plyr_id
or (team = p_team and salary = p_salary);



Your update is very very different.

My update is just inefficient :) (it updates name and position on rows where they do not need to be)

Alex, January 05, 2006 - 3:23 pm UTC

Thanks I see your point. That's what I meant by your "cool complex updates" but if it's more efficient to do two separate updates i'll stick with that.

There's one other thing I'm wondering if we can do. Can we set more than one column using a select? I can find examples everywhere using one column, but not more than that. For example

update roster
set name, salary = (select a, b from t where b = 'Joe')
where salary > '10000';

Something like that. I know that doesn't work I was just wondering if there is a way to do that.

Tom Kyte
January 05, 2006 - 3:33 pm UTC

update roster
set (name,salary) = (select a,b from t where c = 'Joe' )
where salary > 100000;


hopefully, salary is a number, not a string in real life.

Updating question

TS, March 29, 2006 - 11:03 am UTC

Tom,

I've a table a:-

empno
1
2
3
4

table b:-
empno empcode
1 abcd
1 efgh
2 xxxx
2 yyyy
now I add a new column to table a, called empcode
and I want the empcode in table a to be updated with
a value like this:-

empno empcode
1 abcd,efgh
2 xxxx,yyyy



Tom Kyte
March 29, 2006 - 11:16 am UTC

why? a comma delimited list, that would be horrendous.

I'll gladly show you how to retrieve that from the two tables above which model exactly what you should be doing.

I won't show how to muck the data up and make it useless though.

Updating question

TS, March 29, 2006 - 11:53 am UTC

ok. Could you show me how to retrieve the rows
from the table?

Tom Kyte
March 29, 2006 - 12:38 pm UTC

search site for stragg.

updating same column multiple times

A reader, August 10, 2006 - 9:14 pm UTC

tom,
Is it possible to update a same column in the table multiple times in single update ?
for example , if i have table called
gen_order(id number primary key, order_name varchar2(30) )and I have 100 records in it. I want to update order_name in slices with different values. ( eg: 50 records will have value = 'first' and other 50 records will have value = 'second')
I am not sure whether we can do it in one single update statment. I appreciate your help.

thanks.


Tom Kyte
August 10, 2006 - 11:31 pm UTC

i'm confused, how is updating the first 50 with something, the next 50 with something else "updating the same column multiple times???"

sounds like you are updating it once top me?

yes, we can update "50 with one thing" and "50 others with something else"

but that is not updating the same column multiple times.

correlated udpate

A reader, August 10, 2006 - 11:51 pm UTC

Tom,
Thanks for the quick response. Sorry for the confusing description
I have table called gen_order(id number primary key, order_name varchar2(30);
I wanted to know if it is possible to do it one update statement to update order_name in slices as in the following psedo code.

update gen_order
set order_name = 'received first' where id < 1000,
set order_name = 'received second' where id > 1000 and id <2000
set order_name = 'received third' where id > 2000 ..etc.




Tom Kyte
August 11, 2006 - 12:07 am UTC

set order_name = case when id < 1000 then 'received first'
when id >= 1000 and id < 2000 then '...'
when ....
else ....
end


you can also use some sort of function

set order_name = 'received ' || trunc(id/1000)

if applicable.

A reader, August 11, 2006 - 12:28 am UTC

Many thanks. That was exactly I was looking for.

OK

Rajkumar, March 01, 2007 - 6:18 am UTC

Hi Tom,
I need to update three tables in a CURSOR for loop.

for cursor_rec in test_cursor loop

update table 1;
update table 2;
update table 3;

end loop;

I do an iterative processing i.e.row level processing.
I tried this one:

for cursor_rec in test_cursor loop

begin -- for first table
loop
update a set x = cursor_rec.x where...
exit when test_cursor%notfound;
end loop;

begin -- for second table
loop
update b set x = cursor_rec.x where...
exit when test_cursor%notfound;
end loop;

begin -- for third table
loop
update c set x = cursor_rec.x where...
exit when test_cursor%notfound;
end loop;

exit when test_cursor%notfound;
end loop;
end;

Unexpectedly it's not working.

I need your help in the following cases:

1)Is this update statement wrong in the context?I believe that I am not able to share
the same cursor values for all the three individual table updates.

2) Can I be able to get a final rowcount of rows updated for all the 3 tables?
since this is an iterative level of update I am not able to get that.

Coudl you please help me here to correct the problem?
Thanks Tom.
Tom Kyte
March 02, 2007 - 11:28 am UTC


Unexpectedly it's not working.


that is a good one ;)


anyway - your logic is a bit strange. You have:

for cursor_rec in query
LOOP
    loop  -- an INFINITE loop!!!!!
        update ...
        exit when test_cursor%notfound; <<<=== well, if one record was found
                                               this will never be NOT FOUND
                                               and in fact, does not make 
                                               sense in an implicit cursor for 
                                               loop
    end loop;
....


perhaps you mean:


for x in (select .... )
loop
    update t1 set ...;
    update t2 set ...;
    update t3 set ...;
end loop;



period.

Acutally, what you really mean to code is:

update (select .... from t1, some_table where join)
  set ..;
update (select .... from t2, some_table where join)
  set ...;
update (select .... from t3, some_table where join )
  set ...;


that is, no procedural code!!!!!

Please do write a followup

Raj, March 02, 2007 - 4:05 am UTC

Hi Tom,
Please do write a followup in your free time.
I expect your reply.
Tom Kyte
March 04, 2007 - 12:52 pm UTC

would you like this "followup" to be about anything in particular???!?!?

OK

Prathip, March 22, 2007 - 6:24 am UTC

Hi Tom,
Is this a good update statement?
It runs for a long time though it's a correlated update.

SQL> update a a1
set a1.sales_value = (select (a2.sales_units * curr_price)
from a a2,b,c
where a2.country = b.country
and a2.country = a1.country -- Can this be avoided?
and a2.pack = c.pack
and a2.trans_date >= c.price_date
and a2.trans_date = a1.trans_date
and a2.sales_value = a1.sales_value)

Can this be made more simpler and effective?
your help would be appreciated.
Thanks

Tom Kyte
March 22, 2007 - 9:41 am UTC

hard to say without understanding the relationships and keys of your tables.

seems funny to query A and update A like that, but - who knows, we don't know the metadata about the tables....

Peculiar Behavior Of Correlated Delete

VKOUL, April 23, 2007 - 9:26 pm UTC

Pls. go thru the following ...

********************************************
Scenario 1
********************************************

SQL> drop table test1;

Table dropped.

SQL>
SQL> drop table test2;

Table dropped.

SQL>
SQL> create table test1 (x number, z number);

Table created.

SQL>
SQL> create table test2 (y number);

Table created.

SQL>
SQL> insert into test1(x) select rownum from all_objects where rownum <= 10;

10 rows created.

SQL>
SQL> insert into test2 select x from test1 where mod(x,2) = 0;

5 rows created.

SQL>
SQL> update --+ BYPASS_UJVC
2 (select z, y
3 from test2
4 inner
5 join test1
6 on y = x
7 )
8 set z = y;

5 rows updated.

SQL>
SQL> select * from test1;

X Z
---------- ----------
1
2 2
3
4 4
5
6 6
7
8 8
9
10 10

10 rows selected.

SQL>
SQL> select * from test2;

Y
----------
2
4
6
8
10

SQL>
SQL> delete --+ BYPASS_UJVC
2 (select NULL
3 from test1
4 inner
5 join test2
6 on y = x
7 );

5 rows deleted.

SQL>
SQL> select * from test1;

X Z
---------- ----------
1
2 2
3
4 4
5
6 6
7
8 8
9
10 10

10 rows selected.

SQL>
SQL> select * from test2;

no rows selected

SQL>
********************************************
Scenario 2
********************************************

SQL> drop table test1;

Table dropped.

SQL>
SQL> drop table test2;

Table dropped.

SQL>
SQL> create table test1 (x number, z number);

Table created.

SQL>
SQL> create table test2 (y number);

Table created.

SQL>
SQL> insert into test1(x) select rownum from all_objects where rownum <= 10;

10 rows created.

SQL>
SQL> insert into test2 select x from test1 where mod(x,2) = 0;

5 rows created.

SQL>
SQL> update --+ BYPASS_UJVC
2 (select z, y
3 from test2
4 inner
5 join test1
6 on y = x
7 )
8 set z = y;

5 rows updated.

SQL>
SQL> select * from test1;

X Z
---------- ----------
1
2 2
3
4 4
5
6 6
7
8 8
9
10 10

10 rows selected.

SQL>
SQL> select * from test2;

Y
----------
2
4
6
8
10

SQL>
SQL> delete --+ BYPASS_UJVC
2 (select NULL
3 from test2
4 inner
5 join test1
6 on y = x
7 );

5 rows deleted.

SQL>
SQL> select * from test1;

X Z
---------- ----------
1
3
5
7
9

SQL>
SQL> select * from test2;

Y
----------
2
4
6
8
10

SQL>
****************************************************

In scenario 1 it deleted the records from the test2, which seems to me like whatever table is listed in the end in the inline view gets records deleted.

I have not tried with tables with PKs though.

Thanks

Tom Kyte
April 24, 2007 - 10:38 am UTC

stop using undocumented hints - I won't look at anything like this.

these are not correlated deletes, if they were, the table to delete from would not be ambigous - it would be in the form of

delete from T where exists/not exists/in/not in (correlated subquery)

this is a join you are deleting from...

The delete operates on only one table. It would normally do the key preserved table, you bypass that processing however. But the delete will only work against one of the tables and when there are more than one that it could delete from - well - you would likely want to avoid that situation and rephrase the SQL to avoid the ambiguity.

VKOUL, April 24, 2007 - 7:07 pm UTC

The tables I am using here suppose have PKs on the columns that are joined and in that case suppose I do not use UNDOCUMENTED HINT, is it a right supposition that Oracle'll delete the records from the last table in the inline view, it does not do that (the behavior) if I form an view and delete from that.

https://metalink.oracle.com/metalink/plsql/f?p=130:15:4757865570396426977::::p15_database_id,p15_docid,p15_show_header,p15_show_help,p15_black_frame,p15_font:BUG,3113065,1,1,1,helvetica

You are right, it is not a correlated DELETE and I realised that after I posted my question.


Why is this correlated subquery valid?

Paul, September 05, 2007 - 1:48 pm UTC

Hi Tom,

Long time reader, first time poster!

I'm confused why the correlated subquery doesn't give an error, in light of your earlier statement that you can only go down 1 level. I was trying to re-write it using row_number(), but I get the error
ORA-00904: "SFSO"."SF_DATE": invalid identifier

when I re-wrote it.

Here is the non-error version:

UPDATE Sf_Sc_Output sfso
set (currency_conversion_factor1, uom_conversion_factor1, uom_conversion_factor2) =
(select
(
select exchange_rate
from currency_conversions
where effective_date = (select max(effective_date)
from currency_conversions
where sfso.sf_date >= effective_date
and currency_id = 'ID000000001')
and currency_id = 'ID000000001'
),
nvl(ucf.uom_conversion_factor1, 0), nvl(ucf.uom_conversion_factor2,0)
from
(
select distinct sfd.sf_id, sfd.sales_product_id, uf.uom_conversion_factor1, uf.uom_conversion_factor2
from Sf_Detail sfd,
(
SELECT attr.sprod_attribute_1, attr.sprod_attribute_2, attr.sprod_attribute_3, attr.sprod_attribute_4,
attr.sprod_attribute_5,
sum(soro.sales_quantity*soro.uom_conversion_factor1)/sum(soro.sales_quantity) uom_conversion_factor1,
sum(soro.sales_quantity*soro.uom_conversion_factor2)/sum(soro.sales_quantity) uom_conversion_factor2
from Sales_Order_Rpt_Output soro, Sales_Product_Master spm,
(
SELECT distinct sprod_attribute_1, sprod_attribute_2, sprod_attribute_3, sprod_attribute_4,
sprod_attribute_5
from Sf_Detail
where sf_id = 'ID000001467'
) ATTR
where soro.sc_id = 'ID000000023'
and soro.sales_product_id = spm.sales_product_id
and spm.attribute_1 = attr.sprod_attribute_1
and spm.attribute_2 = attr.sprod_attribute_2
and spm.attribute_3 = attr.sprod_attribute_3
and spm.attribute_4 = attr.sprod_attribute_4
and spm.attribute_5 = attr.sprod_attribute_5
group by attr.sprod_attribute_1, attr.sprod_attribute_2, attr.sprod_attribute_3, attr.sprod_attribute_4,
attr.sprod_attribute_5
) uf
where sfd.sf_id = 'ID000001467'
and uf.sprod_attribute_1 = sfd.sprod_attribute_1
and uf.sprod_attribute_2 = sfd.sprod_attribute_2
and uf.sprod_attribute_3 = sfd.sprod_attribute_3
and uf.sprod_attribute_4 = sfd.sprod_attribute_4
and uf.sprod_attribute_5 = sfd.sprod_attribute_5
) ucf
where sfso.sales_product_id = ucf.sales_product_id
)
where sfso.sf_id = 'ID000001467'
and sfso.sc_id = 'ID000000023';

Thanks much!


Tom Kyte
September 05, 2007 - 5:40 pm UTC

make it smaller and provide create tables and I'll look at it, as it is, lots of moving pieces and I'm not sure what bit you really want me to even comment on :)

Followup to the delete operation by Reviewer: VKOUL from WA, USA.

Milo Tan, September 06, 2007 - 5:18 pm UTC

I did not know that Oracle allows such type of delete operation, until I saw example of reviewer: VKOUL from WA, USA.

This type of delete command can best be described as a delete operation on the rows of one of the table in an inline view.  I would not really recommend using this type of Delete command as it isn't clear on which table Oracle would execute the delete on.  Plus, a correlated delete works just the same and is a lot clearer on which table the delete operation will be on.

I also didn't use the undocumented hint that reviewer VKOUL, utilized in his examples.

The following are what I found:
- At least one table has to have a unique constraint.  Otherwise, you will get the error: ORA-01752: cannot delete from view without exactly one key-preserved table
- If both tables have a unique constraint, the table that comes after the FROM clause, will have its record deleted.  I tried both Oracle SQL and ANSI SQL, and got the same results.
- If only one of the tables has a unique constraint, then the table without the unique constraint will get its rows deleted.  Regardless of which table comes first after the FROM clause.  I tried this on both Oracle SQL and ANSI SQL, and got the same results.

The ANSI sql delete commands I used:
Delete (Select a.*
          From test1 a
           Inner Join test2 b
          On a.x = b.y);
          
Delete (Select b.*
          From test2 b
         Inner Join test1 a
         On a.x = b.y);


SQL*Plus: Release 10.2.0.3.0 - Production on Thu Sep 6 12:14:40 2007
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table test1 (x number);
Table created.

SQL> create table test2 (y number);
Table created.

SQL> insert into test1(x) select rownum from dual connect by rownum <= 10;
10 rows created.
 
SQL> insert into test2 select x from test1 where mod(x,2) = 0;
5 rows created.

SQL> Commit;
Commit complete.

SQL> select * from test1;
         X
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
10 rows selected.

SQL> select * from test2;
         Y
----------
         2
         4
         6
         8
        10

-- Scenario I: No unique constraint on both tables for the joined columns
SQL> set autotrace on explain;
SQL> Delete (Select a.*
  2            From test1 a,
  3                 test2 b
  4           Where a.x = b.y);
Delete (Select a.*
       *
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table

-- Create the unique indices on both tables
SQL> Create Unique Index test1_idx On test1(x);
Index created.

SQL> Create Unique Index test2_idx On test2(y);         
Index created.

-- ** Test1 comes first after FROM clause
SQL> Delete (Select a.*
  2            From test1 a,
  3                 test2 b
  4           Where a.x = b.y);
5 rows deleted.

SQL> select * from test1;
         X
----------
         1
         3
         5
         7
         9

SQL> select * from test2;
         Y
----------
         2
         4
         6
         8
        10

SQL> rollback;
Rollback complete.

-- ** Test2 comes first after the FROM Clause
SQL> Delete (Select b.*
  2            From test2 b,
  3                 test1 a
  4           Where a.x = b.y);
5 rows deleted.

SQL> Select * from test1;
         X
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
10 rows selected.

SQL> select * from test2;
no rows selected

SQL> rollback;
Rollback complete.

SQL> select * from test2;
         Y
----------
         2
         4
         6
         8
        10
        
-- ** drop the unqiue index on Test1
SQL> Drop Index test1_idx;
Index dropped.

-- ** Test1 comes first after the FROM clause
SQL> Delete (Select a.*
  2            From test1 a,
  3                 test2 b
  4           Where a.x = b.y);
5 rows deleted.

SQL> select * from test1;
         X
----------
         1
         3
         5
         7
         9

SQL> select * from test2;
         Y
----------
         2
         4
         6
         8
        10

SQL> rollback;
Rollback complete.

SQL> Select * from test1;
         X
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
10 rows selected.

-- ** Test2 comes first after the FROM clause
SQL> Delete (Select b.*
  2            From test2 b,
  3                 test1 a
  4           Where a.x = b.y);
5 rows deleted.
        
SQL> select * from test1;
         X
----------
         1
         3
         5
         7
         9

SQL> select * from test2;
         Y
----------
         2
         4
         6
         8
        10

SQL> rollback;
Rollback complete.

SQL> select * from test1;
         X
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
10 rows selected.      

Another correlated subquery...

Drew, April 11, 2008 - 12:05 pm UTC

Hi Tom (and readers),

I'm having trouble re-writing this (pseudo-coded for clarity):

INSERT INTO stored_results
SELECT phn.*, dat.* FROM (SELECT * FROM data_entry MINUS
SELECT * FROM stored_results) dat,
(SELECT * FROM phone_rec MINUS
SELECT * FROM stored_results) phn
WHERE dat.employee_id = phn.employee_login
AND dat.ph_num = phn.ph_num
AND dat.day_created = phn.day_created
AND phn.datetime.created <= dat.datetime_created
AND phn.datetime.created = (SELECT MAX(phnx.datetime_created)
FROM (SELECT * FROM phone_rec MINUS
SELECT * FROM stored_results) phnx
WHERE dat.employee_id = phn.employee_login
AND dat.ph_num = phn.ph_num
AND dat.day_created = phn.day_created
AND phn.datetime.created <= dat.datetime_created);

This is to match records for a call center. DATA_ENTRY is where the employee enters data. PHONE_REC is where phone call records are stored. There is no key to match these records on. This code matches one record from DATA_ENTRY to one or more records from PHONE_REC. The sub-query selects the most recent record from all the matches. All matches are written to the STORED_RESULTS table. This query is re-run (with the STORED_RESULTS "MINUS'd" from the data) until there are no more matches.

I'm having trouble with the data set being a join of two "MINUS" sets - can't seem to get my head around this.

With 30,000 DATA_ENTRY recs and 3,000,000 PHONE_REC recs, this is taking several hours.
Tom Kyte
April 11, 2008 - 12:35 pm UTC

  AND phn.datetime.created = (SELECT MAX(phnx.datetime_created)
                              FROM (SELECT * FROM phone_rec MINUS
                                    SELECT * FROM stored_results) phnx
                              WHERE dat.employee_id = phn.employee_login
                                AND dat.ph_num = phn.ph_num
                                AND dat.day_created = phn.day_created
                                AND phn.datetime.created <= 
dat.datetime_created);


didn't get that part at all...

there seems to be NO predicate on phnx?

you don't mention anything useful about stored_results?

Drew, April 11, 2008 - 1:14 pm UTC

Predicate for PHNX ...

Sorry - my aliases were incorrect:

AND phn.datetime.created = (SELECT MAX(phnx.datetime_created)
FROM (SELECT * FROM phone_rec MINUS
SELECT * FROM stored_results) phnx
WHERE dat.employee_id = phnx.employee_login
AND dat.ph_num = phnx.ph_num
AND dat.day_created = phnx.day_created
AND phnx.datetime.created <=
dat.datetime_created);

STORED_RESULTS contains the results of the query - the structure is all columns from PHN.* and DAT.* . The query is repeated with STORED_RESULTS excluded from the source data set. And repeated until 0 rows returned.
Tom Kyte
April 11, 2008 - 2:09 pm UTC

SELECT phn.*, dat.* FROM (SELECT * FROM data_entry MINUS
SELECT * FROM stored_results) dat,
(SELECT * FROM phone_rec MINUS
SELECT * FROM stored_results) phn
WHERE dat.employee_id = phn.employee_login
AND dat.ph_num = phn.ph_num
AND dat.day_created = phn.day_created
AND phn.datetime.created <= dat.datetime_created
AND phn.datetime.created = (SELECT MAX(phnx.datetime_created)
FROM (SELECT * FROM phone_rec MINUS
SELECT * FROM stored_results) phnx
WHERE dat.employee_id = phn.employee_login
AND dat.ph_num = phn.ph_num
AND dat.day_created = phn.day_created
AND phn.datetime.created <= dat.datetime_created);


could be
select *
from (
SELECT phn.*, dat.*,
max(phn.datetime_created) over (partition by employee_login, ph_num, day_created) max_datetime_created
FROM (SELECT * FROM data_entry MINUS
SELECT * FROM stored_results) dat,
(SELECT * FROM phone_rec MINUS
SELECT * FROM stored_results) phn
WHERE dat.employee_id = phn.employee_login
AND dat.ph_num = phn.ph_num
AND dat.day_created = phn.day_created
AND phn.datetime.created <= dat.datetime_created
)
where datetime_created = max_datetime_created

...
And repeated until 0 rows returned.
....

uh-oh

please back up the bus. Sounds like a 'de-dup' process or something. I'd go WAY BACK TO THE ALGORITHM and start over, with a blank sheet of paper. If you have to do this massive query over and over "until something doesn't return data anymore" - I've a feeling the only way to 'optimize' this will be to remove procedural code.

Go way back up the food chain here - toss the existing code aside for a while, re-look at the original PROBLEM and look for a more efficient method of getting from here to there.

Back to the start

Drew, April 11, 2008 - 2:31 pm UTC

Thanks Tom. Was thinking that myself. I was given SQL to "package", and now am having to backtrack and "optimize". Isn't that how we're *supposed* to do it??

correlated subqueries in an UPDATE statement

Dawar, May 12, 2008 - 11:18 am UTC

SQL> desc class
Name Null? Type
----------------------------------------- -------- ----------------------------
ITEM_NO******************************VARCHAR2(5)
CLASSIFICATION********************VARCHAR2(255)
MIN_RANGE**************************VARCHAR2(14)
MAX_RANGE_CNTRL****************VARCHAR2(14)

insert into class values ('0400','CIS','5000','70000')
/
insert into class values ('0404','CIT','4000','60000')
/
insert into class values ('0406','QA','3000','40000')
/
insert into class values ('0484','CIO','9000','15000')
/
insert into class values ('007','JB','10000','18000')
/
insert into class values ('0500','DOC','8000','90000')

**************************************************************************
SQL> desc items_test
Name Null? Type
----------------------------------------- -------- ----------------------------
ITEM_NO****************************NOT NULL NUMBER(4)
CLASS_TITLE**********************NOT NULL VARCHAR2(50)
RATE_TYPE ***********************NOT NULL VARCHAR2(1)
MAXIMUM_SALARY**************NUMBER(8,2)

insert into items_test values ('1111','CIS','A','78000')
/
insert into items_test values ('2222','CIT','A','60000')
/
insert into items_test values ('3333','QA','A','80000')
/
insert into items_test values ('4444','CIO','B','215000')
/
insert into items_test values ('5555','JB','A','218000')
/
insert into items_test values ('6666','DOC','C','290000')

I am trying to update values of "MAXIMUM_SALARY" in items_test table.
Condition should be where rate_type = 'A' and item_no of items_test table should equal to item_no of class table. Then MAXIMUM_SALARY of items table should be replace by MAX_RANGE_CNTRL of class table.

But I am getting error, 
pl have a look at it.

TEST 1

SQL> update items_test X
2 set MAXIMUM_SALARY =(select to_number(Y.MAX_RANGE_CNTRL)
3 from class Y
4 where X.item_no = Y.item_no)
5 where x.rate_type = 'A';
set MAXIMUM_SALARY =(select to_number(Y.MAX_RANGE_CNTRL)
*
ERROR at line 2:
ORA-01722: invalid number

___________________________________

select item_no,dump(item_no), MAX_RANGE_CNTRL,dump(MAX_RANGE_CNTRL), MIN_RANGE,dump(MIN_RANGE)from classwhere item_no = 1111;

Here what I get

ITEM_NO
1111,
dump(item_no
Typ=1 Len=5: 32,50,53,57,51,
MAX_RANGE_CNTRL
1000000,
dump(MAX_RANGE_CNTRL) 
Typ=1 Len=14: 32,32,32,32,32,32,55,44,54,56,49,46,50,55,
MIN_RANGE
500000,
dump(MIN_RANGE)
Typ=1 Len=14: 32,32,32,32,32,32,54,44,48,49,55,46,55,51 


Please note:
I made table class through MS Acess.
Actually we have a data in MS Excel format,
so I used access to create table with data in Oracle. 

select trim(rtrim(max_range_cntrl,',')) from class

3,020.91
3,742.45
4,046.36
4,509.64


5,727.91
10,505.55
10.83

15,618.51

...
....
........


....

I also tried as below:

Test 2

SQL> update items_test X
2 set MAXIMUM_SALARY =(select to_number(Y.MAX_RANGE_CNTRL, '999G999D99')
3 from class Y
4 where X.item_no = Y.item_no)
5 where x.rate_type = 'A';
set MAXIMUM_ANNUAL_SALARY =(select to_number(Y.MAX_RANGE_CNTRL, '999G999D99')
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row 

__________________________________________

Prior to update

select count (*) from items_test
3945


select count (*) from class
3120

Test 3

SQL> update items_test X
2 set MAXIMUM_ANNUAL_SALARY =(select to_number(Y.MAX_RANGE_CNTRL, '999G999D99')
3 from class Y
4 where X.item_no=Y.item_no and
5 x.class_title = y.classification)
6 where x.rate_type = 'A';

3795 rows updated.

How its updates more rows in items_test table?
Class table has only 3120 rows.

Now I have no values in MAXIMUM_SALARY column of item_test table.

Test 4:

SQL> update items_test X
2 set MAXIMUM_ANNUAL_SALARY =(select to_number(Y.MAX_RANGE_CNTRL, '999G999D99')
3 from class Y
4 where X.item_no=Y.item_no and
5 upper(x.class_title)= upper(y.classification))
6 where x.rate_type = 'A'
7 and
8 exists (select null from class Y
9 where X.item_no=Y.item_no
10 and upper(x.class_title) = upper( y.classification));
set MAXIMUM_ANNUAL_SALARY =(select to_number(Y.MAX_RANGE_CNTRL, '999G999D99')
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row 


Any feed back will be appriciated.

Dawar
LA USA

Tom Kyte
May 12, 2008 - 1:57 pm UTC

if you look in your table, you'll find you have max_range_cntrl values that are not numbers.


you provided no create tables, I cannot run your example.

and your single row subquery problem - that should be self explanatory.


select to_number(Y.MAX_RANGE_CNTRL, '999G999D99')
from class Y
where X.item_no=Y.item_no and
upper(x.class_title)= upper(y.classification))


In your database, that must return more than one row for some item_no/class_title.


Your example does not suffer from these errors, hence your example does not reproduce your problem, hence your example is not useful. It is obvious why they are NOT NUMBERS, your query shows numbers with commas in them!!!!! (please, I don't get what is hard about making a test case?)


You have some rows where that field you to_number is NOT A NUMBER
You have duplidate item_no/upper(classification)


You have no primary keys that I can see.



Corelated Subquery

Sarvan, June 03, 2008 - 3:36 am UTC

i have executed the following query, Dept table doesnt have the Empno attribute, but still it give me result. i couldnt understand how this query get executed..pls clarify

select * from emp where empno in(select empno from dept)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10


correlated subquery in order by

Maverick, July 08, 2009 - 9:26 am UTC

Tom, I was thinking Correlated subquery can only go one level down but disproved so in 10.2.0.1 version of Oracle. In this version it goes down to second level [please see the test case below]. But it fails in all the other versions.Which is very confusing.

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> ;
  1  select * from emp
  2* order by (select dptno from (select deptno dptno from dept where dept.deptno=emp.deptno))
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM      
---------- ---------- --------- ---------- --------- ---------- ----------      
    DEPTNO                                                                      
----------                                                                      
      7934 MILLER     CLERK           7782 23-JAN-82       1300                 
        10                                                                      
                                                                                
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                 
        10                                                                      
                                                                                
      7839 KING       PRESIDENT            17-NOV-81       5000                 
        10                                                                      
                                                                                

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM      
---------- ---------- --------- ---------- --------- ---------- ----------      
    DEPTNO                                                                      
----------                                                                      
      7566 JONES      MANAGER         7839 02-APR-81       2975                 
        20                                                                      
                                                                                                                                           
      7369 SMITH      CLERK           7902 17-DEC-80       2500                 
        20                                                                      
                                                                                

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM      
---------- ---------- --------- ---------- --------- ---------- ----------      
    DEPTNO                                                                      
----------                                                                      
      7902 FORD       ANALYST         7566 03-DEC-81       3000                 
        20                                                                      
                                                                                
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                 
        20                                                                      
                                                                                
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                 
        20                                                                      
                                                                                

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM      
---------- ---------- --------- ---------- --------- ---------- ----------      
    DEPTNO                                                                      
----------                                                                      
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300      
        30                                                                      
                                                                                
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0      
        30                                                                      
                                                                                
      7900 JAMES      CLERK           7698 03-DEC-81        950                 
        30                                                                      
                                                                                

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM      
---------- ---------- --------- ---------- --------- ---------- ----------      
    DEPTNO                                                                      
----------                                                                      
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500      
        30                                                                      
                                                                                
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                 
        30                                                                      
                                                                                
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400      
        30                                                                      
                                                                                

14 rows selected.

SQL> spool off


Now I logged into 10.2.0.4 version and here is the result:

SQL> select banner from v$version;

BANNER                                                                          
----------------------------------------------------------------                
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod                
PL/SQL Release 10.2.0.4.0 - Production                                          
CORE 10.2.0.4.0 Production                                                      
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production                         
NLSRTL Version 10.2.0.4.0 - Production                                          

SQL> set linesize 1000
SQL> select * from emp order by (select dno from (select deptno dno from dept where dept.deptno=emp.deptno));
select * from emp order by (select dno from (select deptno dno from dept where dept.deptno=emp.deptno))
                                                                                           *
ERROR at line 1:
ORA-00904: "EMP"."DEPTNO": invalid identifier 


SQL> spool off



I tried in Oracle 9i and it fails there too. What's changed in 10.2.0.1? Can you explain this behavior?
Also, I got this query from some wher eand was just testing. What does this order by do exactly?

Thanks,
Tom Kyte
July 08, 2009 - 3:38 pm UTC

it would be a bug in 10.2.0.1 on windows - I cannot reproduce

ops$tkyte%ORA10GR2> select *
  2    from emp
  3   order by (select dno from (select deptno dno from dept where dept.deptno=emp.deptno))
  4  /
 order by (select dno from (select deptno dno from dept where dept.deptno=emp.deptno))
                                                                          *
ERROR at line 3:
ORA-00904: "EMP"."DEPTNO": invalid identifier


ops$tkyte%ORA10GR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


as for "what does it mean", if you order by an expression, it is just like:

ops$tkyte%ORA10GR2> select *
  2    from (
  3  select ename, sal, comm ,
  4         (select deptno dno from dept where dept.deptno=emp.deptno) X
  5    from emp
  6         )
  7   order by X
  8  /

ENAME             SAL       COMM          X
---------- ---------- ---------- ----------
CLARK            2450                    10
MILLER           1300                    10
KING             5000                    10
FORD             3000                    20
SCOTT        92738.25                    20
JONES            2975                    20
ADAMS            1100                    20
SMITH             800                    20
ALLEN            1600        300         30
WARD             1250        500         30
JAMES             950                    30
BLAKE            2850                    30
MARTIN           1250       1400         30
TURNER           1500          0         30

14 rows selected.


ordering by an attribute (that is the result of that expression)

Alexander, December 06, 2011 - 9:56 am UTC

Hi,

I'm having some performance issues with an Oracle vendor app, and in one of their procedures, they're doing this:

create or replace procedure       p_w_position_dh_asisupdate is
cursor pdh_cur is 
    select * from w_position_dh where current_flg = 'y';
   
  type w_pos_dh_typ is table of w_position_dh%rowtype index by pls_integer;
  pos_dh_rec  w_pos_dh_typ;
  
begin
   open pdh_cur;
   loop
   
  /* fetch the data...*/
  fetch pdh_cur bulk collect into pos_dh_rec limit 1000;
  
  /* update the table w_position_dh */
  forall i in indices of pos_dh_rec
   update /*+ parallel */ w_position_dh dh1 set
         dh1.current_base_postn          = pos_dh_rec(i).base_postn,
         dh1.current_base_postn_id       = pos_dh_rec(i).base_postn_id,
         dh1.current_base_divn           = pos_dh_rec(i).base_divn,
         dh1.current_base_login          = pos_dh_rec(i).base_login,
         dh1.current_base_emp_full_name  = pos_dh_rec(i).base_emp_full_name,
         dh1.current_base_emp_id         = pos_dh_rec(i).base_emp_id,
         dh1.current_lvl1anc_postn       = pos_dh_rec(i).lvl1anc_postn,
         dh1.current_lvl1anc_postn_id    = pos_dh_rec(i).lvl1anc_postn_id,
         dh1.current_lvl1anc_divn        = pos_dh_rec(i).lvl1anc_divn,
         dh1.current_lvl1anc_login       = pos_dh_rec(i).lvl1anc_login,
         dh1.current_lvl1_emp_full_name  = pos_dh_rec(i).lvl1_emp_full_name,
         dh1.current_lvl1anc_emp_id      = pos_dh_rec(i).lvl1anc_emp_id,
         dh1.current_lvl2anc_postn       = pos_dh_rec(i).lvl2anc_postn,
         dh1.current_lvl2anc_postn_id    = pos_dh_rec(i).lvl2anc_postn_id,
         dh1.current_lvl2anc_divn        = pos_dh_rec(i).lvl2anc_divn,
         dh1.current_lvl2anc_login       = pos_dh_rec(i).lvl2anc_login,
         dh1.current_lvl2_emp_full_name  = pos_dh_rec(i).lvl2_emp_full_name,
         dh1.current_lvl2anc_emp_id      = pos_dh_rec(i).lvl2anc_emp_id,
         dh1.current_lvl3anc_postn       = pos_dh_rec(i).lvl3anc_postn,
         dh1.current_lvl3anc_postn_id    = pos_dh_rec(i).lvl3anc_postn_id,
         dh1.current_lvl3anc_divn        = pos_dh_rec(i).lvl3anc_divn,
         dh1.current_lvl3anc_login       = pos_dh_rec(i).lvl3anc_login,
         dh1.current_lvl3_emp_full_name  = pos_dh_rec(i).lvl3_emp_full_name,
         dh1.current_lvl3anc_emp_id      = pos_dh_rec(i).lvl3anc_emp_id,
         dh1.current_lvl4anc_postn       = pos_dh_rec(i).lvl4anc_postn,
         dh1.current_lvl4anc_postn_id    = pos_dh_rec(i).lvl4anc_postn_id,
         dh1.current_lvl4anc_divn        = pos_dh_rec(i).lvl4anc_divn,
         dh1.current_lvl4anc_login       = pos_dh_rec(i).lvl4anc_login,
         dh1.current_lvl4_emp_full_name  = pos_dh_rec(i).lvl4_emp_full_name,
         dh1.current_lvl4anc_emp_id      = pos_dh_rec(i).lvl4anc_emp_id,
         dh1.current_lvl5anc_postn       = pos_dh_rec(i).lvl5anc_postn,
         dh1.current_lvl5anc_postn_id    = pos_dh_rec(i).lvl5anc_postn_id,
         dh1.current_lvl5anc_divn        = pos_dh_rec(i).lvl5anc_divn,
         dh1.current_lvl5anc_login       = pos_dh_rec(i).lvl5anc_login,
         dh1.current_lvl5_emp_full_name  = pos_dh_rec(i).lvl5_emp_full_name,
         dh1.current_lvl5anc_emp_id      = pos_dh_rec(i).lvl5anc_emp_id,
         dh1.current_lvl6anc_postn       = pos_dh_rec(i).lvl6anc_postn,
         dh1.current_lvl6anc_postn_id    = pos_dh_rec(i).lvl6anc_postn_id,
         dh1.current_lvl6anc_divn        = pos_dh_rec(i).lvl6anc_divn,
         dh1.current_lvl6anc_login       = pos_dh_rec(i).lvl6anc_login,
         dh1.current_lvl6_emp_full_name  = pos_dh_rec(i).lvl6_emp_full_name,
         dh1.current_lvl6anc_emp_id      = pos_dh_rec(i).lvl6anc_emp_id,
         dh1.current_lvl7anc_postn       = pos_dh_rec(i).lvl7anc_postn,
         dh1.current_lvl7anc_postn_id    = pos_dh_rec(i).lvl7anc_postn_id,
         dh1.current_lvl7anc_divn        = pos_dh_rec(i).lvl7anc_divn,
         dh1.current_lvl7anc_login       = pos_dh_rec(i).lvl7anc_login,
         dh1.current_lvl7_emp_full_name  = pos_dh_rec(i).lvl7_emp_full_name,
         dh1.current_lvl7anc_emp_id      = pos_dh_rec(i).lvl7anc_emp_id,
         dh1.current_lvl8anc_postn       = pos_dh_rec(i).lvl8anc_postn,
         dh1.current_lvl8anc_postn_id    = pos_dh_rec(i).lvl8anc_postn_id,
         dh1.current_lvl8anc_divn        = pos_dh_rec(i).lvl8anc_divn,
         dh1.current_lvl8anc_login       = pos_dh_rec(i).lvl8anc_login,
         dh1.current_lvl8_emp_full_name  = pos_dh_rec(i).lvl8_emp_full_name,
         dh1.current_lvl8anc_emp_id      = pos_dh_rec(i).lvl8anc_emp_id,
         dh1.current_lvl9anc_postn       = pos_dh_rec(i).lvl9anc_postn,
         dh1.current_lvl9anc_postn_id    = pos_dh_rec(i).lvl9anc_postn_id,
         dh1.current_lvl9anc_divn        = pos_dh_rec(i).lvl9anc_divn,
         dh1.current_lvl9anc_login       = pos_dh_rec(i).lvl9anc_login,
         dh1.current_lvl9_emp_full_name  = pos_dh_rec(i).lvl9_emp_full_name,
         dh1.current_lvl9anc_emp_id      = pos_dh_rec(i).lvl9anc_emp_id,
         dh1.current_lvl10anc_postn      = pos_dh_rec(i).lvl10anc_postn,
         dh1.current_lvl10anc_postn_id   = pos_dh_rec(i).lvl10anc_postn_id,
         dh1.current_lvl10anc_divn       = pos_dh_rec(i).lvl10anc_divn,
         dh1.current_lvl10anc_login      = pos_dh_rec(i).lvl10anc_login,
         dh1.current_lvl10_emp_full_name = pos_dh_rec(i).lvl10_emp_full_name,
         dh1.current_lvl10anc_emp_id     = pos_dh_rec(i).lvl10anc_emp_id,
         dh1.current_lvl11anc_postn      = pos_dh_rec(i).lvl11anc_postn,
         dh1.current_lvl11anc_postn_id   = pos_dh_rec(i).lvl11anc_postn_id,
         dh1.current_lvl11anc_divn       = pos_dh_rec(i).lvl11anc_divn,
         dh1.current_lvl11anc_login      = pos_dh_rec(i).lvl11anc_login,
         dh1.current_lvl11_emp_full_name = pos_dh_rec(i).lvl11_emp_full_name,
         dh1.current_lvl11anc_emp_id     = pos_dh_rec(i).lvl11anc_emp_id,
         dh1.current_lvl12anc_postn      = pos_dh_rec(i).lvl12anc_postn,
         dh1.current_lvl12anc_postn_id   = pos_dh_rec(i).lvl12anc_postn_id,
         dh1.current_lvl12anc_divn       = pos_dh_rec(i).lvl12anc_divn,
         dh1.current_lvl12anc_login      = pos_dh_rec(i).lvl12anc_login,
         dh1.current_lvl12_emp_full_name = pos_dh_rec(i).lvl12_emp_full_name,
         dh1.current_lvl12anc_emp_id     = pos_dh_rec(i).lvl12anc_emp_id,
         dh1.current_lvl13anc_postn      = pos_dh_rec(i).lvl13anc_postn,
         dh1.current_lvl13anc_postn_id   = pos_dh_rec(i).lvl13anc_postn_id,
         dh1.current_lvl13anc_divn       = pos_dh_rec(i).lvl13anc_divn,
         dh1.current_lvl13anc_login      = pos_dh_rec(i).lvl13anc_login,
         dh1.current_lvl13_emp_full_name = pos_dh_rec(i).lvl13_emp_full_name,
         dh1.current_lvl13anc_emp_id     = pos_dh_rec(i).lvl13anc_emp_id,
         dh1.current_lvl14anc_postn      = pos_dh_rec(i).lvl14anc_postn,
         dh1.current_lvl14anc_postn_id   = pos_dh_rec(i).lvl14anc_postn_id,
         dh1.current_lvl14anc_divn       = pos_dh_rec(i).lvl14anc_divn,
         dh1.current_lvl14anc_login      = pos_dh_rec(i).lvl14anc_login,
         dh1.current_lvl14_emp_full_name = pos_dh_rec(i).lvl14_emp_full_name,
         dh1.current_lvl14anc_emp_id     = pos_dh_rec(i).lvl14anc_emp_id,
         dh1.current_lvl15anc_postn      = pos_dh_rec(i).lvl15anc_postn,
         dh1.current_lvl15anc_postn_id   = pos_dh_rec(i).lvl15anc_postn_id,
         dh1.current_lvl15anc_divn       = pos_dh_rec(i).lvl15anc_divn,
         dh1.current_lvl15anc_login      = pos_dh_rec(i).lvl15anc_login,
         dh1.current_lvl15_emp_full_name = pos_dh_rec(i).lvl15_emp_full_name,
         dh1.current_lvl15anc_emp_id     = pos_dh_rec(i).lvl15anc_emp_id,
         dh1.current_lvl16anc_postn      = pos_dh_rec(i).lvl16anc_postn,
         dh1.current_lvl16anc_postn_id   = pos_dh_rec(i).lvl16anc_postn_id,
         dh1.current_lvl16anc_divn       = pos_dh_rec(i).lvl16anc_divn,
         dh1.current_lvl16anc_login      = pos_dh_rec(i).lvl16anc_login,
         dh1.current_lvl16_emp_full_name = pos_dh_rec(i).lvl16_emp_full_name,
         dh1.current_lvl16anc_emp_id     = pos_dh_rec(i).lvl16anc_emp_id,
         dh1.current_top_lvl_postn       = pos_dh_rec(i).top_lvl_postn,
         dh1.current_top_lvl_postn_id    = pos_dh_rec(i).top_lvl_postn_id,
         dh1.current_top_lvl_divn        = pos_dh_rec(i).top_lvl_divn,
         dh1.current_top_lvl_login       = pos_dh_rec(i).top_lvl_login,
         dh1.current_top_emp_full_name   = pos_dh_rec(i).top_emp_full_name,
         dh1.current_top_lvl_emp_id      = pos_dh_rec(i).top_lvl_emp_id
      where
         dh1.base_postn_id = pos_dh_rec(i).base_postn_id 
         and dh1.datasource_num_id = pos_dh_rec(i).datasource_num_id;
       
       commit work;
       
       exit when pdh_cur%notfound;
       
       end loop;
       
       close pdh_cur;
         
end;
/


My question is, can that not be a single sql statement?

Like,

update w_position_dh set
column list =  (select column list from w_position_dh where current_flg = 'y');


Tom Kyte
December 06, 2011 - 11:33 am UTC

this should probably be a merge

merge into w_position_dh w
using (select * from w_position_dh where current_flg = 'y') x
on ( ... join condition ... )
when matched then update set ......;


that could then, if desired, use parallel DML to execute in parallel.

It seems they wanted parallel (the hint, which isn't going to be used since this is an update with a single row update taking place - 1,000 times each call) - so maybe that would be something they should look at.


Alexander, December 06, 2011 - 11:43 am UTC

Can't we use parallel DML on an update like what I was going for (not that I'm even sure it's necessary)?
Tom Kyte
December 07, 2011 - 12:49 pm UTC

your update would be:

update w_position_dh dh1
set (column, list) = (select column, list
from w_position_dh dh2
where current_flg = 'y'
and dh1.base_postn_id = dh2.base_postn_id
and dh1.datasource_num_id = dh2.datasource_num_id);


it depends, can the optimizer figure out how to unroll that correlated subquery into a join, do the constraints exist to permit it, is everything in place?

We do NOT want a single index to be used anywhere here - full scans and hash joins rule for this sort of processing. the index would be slow by slow processing.

Alexander, December 08, 2011 - 9:43 am UTC

No constraints, this thing is dogging. Not that many rows either I don't understand, a very simple update. I can provide the schema so you know what I'm talking about but it's a lot of columns, is that cool if i paste that in here?
Tom Kyte
December 08, 2011 - 12:55 pm UTC

get an example with less columns.

take your existing data, cut out 98% of the columns, add a column that is wide to make up for the missing columns.

build a small test case, post the tkprof.


Alexander, December 08, 2011 - 2:48 pm UTC

I think I'm losing it. I'm trying to reproduce the problem:

create table mytable
(
    row_wid                     number(10,0)        default 0 not null,
    scd1_wid                    number(10,0)        default 0 not null,
    position_wid                number(10,0)        default 0 not null,
    fixed_hier_level            number(10,0)        default 0 not null,
    base_postn                  varchar2(50 char)   default 'unspecified' not null,
    base_postn_id               varchar2(30 char)   default 'unspecified' not null,
    base_divn                   varchar2(100 char)  default 'unspecified' not null,
    base_login                  varchar2(50 char)   default 'unspecified' not null,
    base_emp_full_name          varchar2(102 char)  default 'unspecified' not null,
    current_flg                 char(1 char)        null,
    datasource_num_id           number(10,0)        default 0 not null,
    current_base_postn          varchar2(4000 char) null
);

SQL> INSERT
  2      INTO
  3          mytable
  4          (
  5              row_wid,
  6              scd1_wid,
  7              position_wid,
  8              fixed_hier_level,
  9              base_postn,
            base_divn,
 10   11              base_login,
 12              base_emp_full_name,
 13              current_base_postn
 14          )
 15      (SELECT
 16           row_wid,
 17           scd1_wid,
         position_wid,
 18   19           fixed_hier_level,
 20           base_postn,
 21           base_divn,
 22           base_login,
 23           base_emp_full_name,
 24           current_base_postn FROM W_POSITION_DH);

2356695 rows created.

SQL> commit;

Commit complete.

SQL> update mytable a
  2      set
  3          (   a.row_wid,
  4              a.scd1_wid,
  5              a.position_wid,
  6              a.fixed_hier_level,
  7              a.base_postn,
  8              a.base_divn,
  9              a.base_login,
 10              a.base_emp_full_name,
            a.current_base_postn
 11   12           )
 13          = (select
 14                 row_wid,
               scd1_wid,
 15   16                 position_wid,
 17                 fixed_hier_level,
 18                 base_postn,
 19                 base_divn,
 20                 base_login,
 21                 base_emp_full_name,
 22                 current_base_postn
 23             from mytable b
 24             where a.base_postn_id = b.base_postn_id
 25               and a.datasource_num_id = b.datasource_num_id
 26               and current_flg = 'Y');
        (   a.row_wid,
            *
ERROR at line 3:
ORA-01407: cannot update ("AJA"."MYTABLE"."ROW_WID") to NULL


SQL> select count(*) from mytable where row_wid is null;

  COUNT(*)
----------
         0


So because of that I haven't got the tkprof yet. Must be something obvious but I'm too far in the weeds.

Slow co-related queires

Manu Batham, January 18, 2014 - 5:18 pm UTC

Hi Tom,

Going back to the third comment:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1197999096334#5744114418123

update big_table a
set comm = (select avg(sal) from small_table b
where a.deptno = b.deptno);

I saw these queries are relatively very slow on large tables (Small = 2 Million Records, Large = 24 Million Records), and I have do them in cursor (something like in very first comment). Please suggest how to run it fast through SQL only.

Many thanks,
Manu Batham

Update Query

kaushlesh, May 19, 2014 - 9:23 am UTC

Very useful example

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.