Sql update takes 4 days for 10 million records

Question

I want to update a database table which has over 10 million records from a temporary table. But my update query executes more than 4 days.

1.) I have already created an index for update search criteria for tax_ledger_item_tab. index creatred on party_type, identity, company. My search criteria are on party_type, identity, company, delivery_type_id as the following given query, those columns are not keys in the table.

I believe that I cant add delivery_type_id for the index as it will update by the query, if I add that into index performance will be worst.

2.) temporary table identity_invoice_info_cfv will also returns 70,000 records also.

So far I believe my update execution plan cost will be like around 70000*10 million records.

How can I get performance enhancement with following update query? I only want to update delivery_type_id, fetched columns only.

DECLARE

CURSOR get_records IS
  SELECT i.COMPANY, i.IDENTITY, i.CF$_DELIVERY_TYPE
    FROM identity_invoice_info_cfv i
   WHERE i.PARTY_TYPE_DB = 'CUSTOMER';

BEGIN

  FOR rec_ IN get_records LOOP
  dbms_output.put_line  (sysdate ); 

    UPDATE tax_ledger_item_tab t
       SET t.delivery_type_id = rec_.CF$_DELIVERY_TYPE, t.fetched = 'TRUE'
     WHERE t.party_type = 'CUSTOMER'
       AND t.identity = rec_.IDENTITY
       AND t.company = rec_.COMPANY
       AND t.delivery_type_id IS NULL;

    COMMIT;  

  END LOOP;

 END;

Answer 1

Use a MERGE statement:

Oracle Setup:

CREATE TABLE identity_invoice_info_cfv ( COMPANY, IDENTITY, CF$_DELIVERY_TYPE, PARTY_TYPE_DB ) AS
SELECT 'A', 123, 456, 'CUSTOMER' FROM DUAL;

CREATE TABLE tax_ledger_item_tab ( identity, company, party_type, delivery_type_id, fetched ) AS
SELECT 123, 'A', 'CUSTOMER', CAST( NULL AS NUMBER ), 'FALSE' FROM DUAL;

Merge:

MERGE INTO tax_ledger_item_tab t
USING identity_invoice_info_cfv i
ON (
    t.identity      = i.identity
AND t.company       = i.COMPANY
AND t.party_type    = 'CUSTOMER'
AND i.PARTY_TYPE_DB = 'CUSTOMER'
)
WHEN MATCHED THEN
  UPDATE
  SET delivery_type_id = i.CF$_DELIVERY_TYPE,
      fetched          = 'TRUE'
  WHERE t.delivery_type_id IS NULL;

Query:

SELECT * FROM tax_ledger_item_tab;

Output:

IDENTITY | COMPANY | PARTY_TYPE | DELIVERY_TYPE_ID | FETCHED
-------: | :------ | :--------- | ---------------: | :------
     123 | A       | CUSTOMER   |              456 | TRUE   

db<>fiddle here