SQL & PL/SQL

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! I'm not sure why it's doing a full table scan on edrms when it's an indexed column and should only be using docno - any ideas?

BBB