update /*+ NO_CPU_COSTING */ dcag_document_item_lines a
set A.ALIEN_ITEM = 'N'
where EXISTS (SELECT b.ip_part_code
from pending_dcag_pt6_mra B
where A.CODE = B.ip_part_code)
AND A.item_line_type=('PA')
AND EXISTS (SELECT c.document_id
from dcag_documents c
where c.document_id = a.document_id
and c.document_date >= '01-jan-2007');

Statement Id=1638424 Type=
Cost=2.0960068036193E-317 TimeStamp=23-11-09::11::33:52

(1) UPDATE STATEMENT CHOOSE
Est. Rows: 18,609,815 Cost: 580,793
UPDATE DCAG.DCAG_DOCUMENT_ITEM_LINES
(6) HASH JOIN RIGHT SEMI
Est. Rows: 18,609,815 Cost: 580,793
(2) TABLE TABLE ACCESS FULL DCAG.DCAG_DOCUMENTS [Analyzed]
(2) Blocks: 633,918 Est. Rows: 12,113,924 of 24,916,950 Cost: 96,214
Tablespace: DCAG_T
(5) HASH JOIN RIGHT SEMI
Est. Rows: 25,321,324 Cost: 460,759
(3) INDEX INDEX FAST FULL SCAN DCAG.PENDING_DCAG_PT6_MRA_IDX [Analyzed]
Est. Rows: 1,700,302 Cost: 1,694
(4) TABLE TABLE ACCESS FULL DCAG.DCAG_DOCUMENT_ITEM_LINES [Analyzed]
(4) Blocks: 2,778,518 Est. Rows: 73,066,252 of 199,390,240 Cost: 421,708
Tablespace: DCAG_T

Edited by: user12260689 on 23-Nov-2009 03:33

select count(*) from dcag_document_item_lines a
                                     where EXISTS (SELECT b.ip_part_code
                                     from pending_dcag_pt6_mra B 
                                    where A.CODE = B.ip_part_code) 
                       AND A.item_line_type=('PA') 
                       AND EXISTS (SELECT c.document_id 
                                     from dcag_documents c 
                                    where c.document_id = a.document_id 
                                      and c.document_date >= '01-jan-2007')

select count(*) from dcag_document_item_lines a
                                     where EXISTS (SELECT b.ip_part_code
                                     from pending_dcag_pt6_mra B 
                                    where A.CODE = B.ip_part_code) 
                       AND A.item_line_type=('PA') 
                       AND EXISTS (SELECT c.document_id 
                                     from dcag_documents c 
                                    where c.document_id = a.document_id 
                                      and c.document_date >= '01-jan-2007')
                                      AND A.ALIEN_ITEM != 'N'

update /*+ NO_CPU_COSTING */ dcag_document_item_lines_t a 
                       set A.ALIEN_ITEM = 'Y' 
                     where NOT EXISTS (SELECT b.ip_part_code 
                                     from pending_dcag_pt6_mra B 
                                    where A.CODE = B.ip_part_code) 
                       AND A.item_line_type=('PA') 
                       AND EXISTS (SELECT c.document_id 
                                     from dcag_documents c 
                                    where c.document_id = a.document_id 
                                      and c.document_date >= '01-jan-2007')
                                      and A.ALIEN_ITEM = 'N'

Took 42 minutes for 858031 records updated.