I think we can use merge as well in this case.

Hi,

We were trying the below query
select
t1.unqid,
t1,md_dt,
newt.MD_DT
from
t1,
(SELECT TEMP.unqid,TEMP.MD_DT
FROM (
SELECT t2.unqid AS unqid,t2.MD_DT AS MD_DT,
ROW_NUMBER() OVER (PARTITION BY t2.unqid ORDER BY t2.MD_DT DESC) AS ROW_ID
FROM t2, t1
WHERE t2.x = 'x'
AND t2.y = 'y'
AND t2.MD_DT IS NOT NULL
AND t1.unqid=t2.unqid
AND t1.lt='hl'
) TEMP
WHERE TEMP.ROW_ID=1) Newt
where
newt.unqid=t1.unqid;

The select query that I shared completes in 13 seconds.

But the update query(after including the existence in t2) takes 250 seconds.

Can we use any other approach.
Or split the task into 2 - select all the rows and then update in size of say 1000 each.

rajesh@ORA11G> create table emp as select * from scott.emp;

Table created.

rajesh@ORA11G> create table dept as select * from scott.dept;

Table created.

rajesh@ORA11G> update ( select ename,dname
  2  from emp e, dept d
  3  where e.deptno = d.deptno )
  4  set ename = dname;
set ename = dname
    *
ERROR at line 4:
ORA-01779: cannot modify a column which maps to a non key-preserved table

The error message tell us that "deptno" column in dept table is not unique. so each and every record from emp table could join with more than one record in Dept table.
let say deptno=10 is repeated twice in dept table with different department names and emp table has three employees working for deptno=10 then join between these two tables would produce six rows in the output, database has no clue about which department name should be updated here. Hence this error message.

if update is turned into a merge, then we don't see this error message.

rajesh@ORA11G> merge into emp using dept
  2  on (emp.deptno = dept.deptno)
  3  when matched then
  4     update set ename = dname;

14 rows merged.

rajesh@ORA11G> rollback ;

Rollback complete.

rajesh@ORA11G>

that doesn't mean merge are solution to this case, the moment we insert deptno=10 twice into the dept table, then merge would result into an error like this.

rajesh@ORA11G> insert into dept
  2  select *
  3  from dept
  4  where deptno = 10;

1 row created.

rajesh@ORA11G>
rajesh@ORA11G> merge into emp using dept
  2  on (emp.deptno = dept.deptno)
  3  when matched then
  4     update set ename = dname;
merge into emp using dept
           *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables


rajesh@ORA11G> rollback ;

Rollback complete.

rajesh@ORA11G>

the only difference here is UPDATE does this check before execution, while MERGE does this as part of statement execution.
The right way to avoid this error is define either a primary key or a unique index/constraint on dept(deptno). That way deptno is not duplicated in DEPT table and every record from EMP table matches at most zero or one record in DEPT table.

rajesh@ORA11G> alter table dept add constraint dept_pk
  2  primary key(deptno);

Table altered.

rajesh@ORA11G> update ( select ename,dname
  2  from emp e, dept d
  3  where e.deptno = d.deptno )
  4  set ename = dname;

14 rows updated.

rajesh@ORA11G> rollback ;

Rollback complete.

rajesh@ORA11G> merge into emp using dept
  2  on (emp.deptno = dept.deptno)
  3  when matched then
  4     update set ename = dname;

14 rows merged.

rajesh@ORA11G> rollback;

Rollback complete.

rajesh@ORA11G>
rajesh@ORA11G> alter table dept drop constraint dept_pk;

Table altered.

rajesh@ORA11G> create unique index dept_pk on dept(deptno);

Index created.

rajesh@ORA11G> update ( select ename,dname
  2  from emp e, dept d
  3  where e.deptno = d.deptno )
  4  set ename = dname;

14 rows updated.

rajesh@ORA11G> rollback ;

Rollback complete.

rajesh@ORA11G> merge into emp using dept
  2  on (emp.deptno = dept.deptno)
  3  when matched then
  4     update set ename = dname;

14 rows merged.

rajesh@ORA11G> rollback;

Rollback complete.

rajesh@ORA11G>

select
t1.unqid,
t1,md_dt,
newt.MD_DT
from
t1,
(SELECT TEMP.unqid,TEMP.MD_DT
FROM (
SELECT t2.unqid AS unqid,t2.MD_DT AS MD_DT,
ROW_NUMBER() OVER (PARTITION BY t2.unqid ORDER BY t2.MD_DT DESC) AS ROW_ID
FROM t2, t1
WHERE t2.x = 'x'
AND t2.y = 'y'
AND t2.MD_DT IS NOT NULL
AND t1.unqid=t2.unqid
AND t1.lt='hl'
) TEMP
WHERE TEMP.ROW_ID=1) Newt
where
newt.unqid=t1.unqid;

Can we have unique key/primary key constraints on Newt.
If this is possible then i guess update will work and not give a non key-preserved table error.

Please help.

Please find the details(DDL & Execution Plan) asked as of below:

create table t1(uniqid varchar2(100) primary key, lt varchar2(100), MD_DT date);

-- no other constraints or indexes on this table

create table t2(id varchar2(100) primary key, uniqid varchar2(100), x varchar2(100),y varchar2(100), MD_DT date);

-- no other constraints or indexes on this table

Explain plan
Operation OBJECT_NAME CARDINALITY COST
UPDATE STATEMENT 9162 46282317

UPDATE t1

NESTED LOOPS

NESTED LOOPS 9162 14217

SORT 9163 5048

TABLE ACCESS t2 9163 5048

INDEX t1_pk 1 1

TABLE ACCESS t1 1 2

VIEW 1 5049

WINDOW 1 5049

FILTER

TABLE ACCESS t2 1 5048

Thanks for the DDL's. But where is your update statement? i dont see your update dml else where in this page apart from Initial response by Chirs.

share your update statement that is causing error.