Slow Update query

Databases
1

Here’s the overview, I have two tables: document and edrms

UPDATE edrms SET hasmapping = 1 WHERE docno IN (SELECT docno FROM document WHERE typecode=‘AB’);

UPDATE STATEMENT ()
UPDATE ()

2

Ignore the above I hit the enter button by mistake…

Here’s the overview, I have two tables: document and edrms

document

docno int PK
typecode varchar

edrms

docno int
hasmapping int

I have indexes on document.docno (primary key), document.typecode and edrms.docno.

Each of these table can contain millions of records.

Essentially I need to update the hasmapping column on edrms whenever a document typecode is specified on document.

Here’s one update statement I tried:

UPDATE edrms SET hasmapping = 1 WHERE docno IN (SELECT docno FROM document WHERE typecode=‘AB’);

Which is slow.

Which gives this execution plan:

UPDATE STATEMENT ()
  UPDATE ()     edrms
    NESTED LOOPS ()
      TABLE ACCESS (FULL)     edrms
      TABLE ACCESS (BY INDEX ROW)     document
        INDEX (UNIQUE SCAN)     document_pk

I’m guessing the full table scan is slowing the query down, but I don’t know how to get around it.

I’ve also tried this query:

UPDATE edrms SET hasmapping = 1 WHERE EXISTS (SELECT ‘x’ FROM document WHERE document.docno = edrms.docno AND typcode = ‘AB’)

UPDATE STATEMENT ()
  UPDATE ()     edrms
    NESTED LOOPS (SEMI)
      TABLE ACCESS (FULL)     edrms
      TABLE ACCESS (BY INDEX ROW)     document
        INDEX (UNIQUE SCAN)     document_pk

which is also slow.

Does anyone have any suggestions of things I could try? I’m a bit stumped!

BBB