SQL - Subquery into join

Hullo!

I got a question for CBO wizards.
I have a query:

UPDATE PS_ITEM
SET PROCESS_INSTANCE = 15422
WHERE PROCESS_INSTANCE = 0
AND EXISTS (SELECT 'X' FROM PS_RP_PNDITM_TAO11

            WHERE PS_RP_PNDITM_TAO11.PROCESS_INSTANCE = 15422 
            AND PS_RP_PNDITM_TAO11.BUSINESS_UNIT =
PS_ITEM.BUSINESS_UNIT
            AND PS_RP_PNDITM_TAO11.CUST_ID = PS_ITEM.CUST_ID 
            AND PS_RP_PNDITM_TAO11.ITEM = PS_ITEM.ITEM 
            AND PS_RP_PNDITM_TAO11.ITEM_LINE = PS_ITEM.ITEM_LINE) 

This is a 3rd party app (PeopleSoft Finacials) and I, alas, have no access to
code at the moment. I'm trying to make behave through optimizer manupulations
(yes, I'm aware of side effects). Anyway, here is the skinny: Both tables indexes on (BUSINESS_UNIT,CUST_ID,ITEM,ITEM_LINE), the one on PS_ITEM is unique. The PS_ITEM table is relatively large (~1M rows), another
one is smaller (1-8k rows - it varies as the app uses it as temp "bucket" of
sorts, I estimate max number rows by high water mark figure). I do have correct
stats on both (had to play with dbms_stat to import/export statistics for the
goddamn buckets). Oracle 8.1.7.4, optimizer_index_cost_adj=15. Current
Execution Plan

   0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=2862 Card=28314 Bytes=651222)

   1 0 UPDATE OF 'PS_ITEM'

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'PS_ITEM' (Cost=2862 Card=28314
          Bytes=651222)
   4    2       INDEX (RANGE SCAN) OF 'PSARP_PNDITM_TAO11' (NON-UNIQUE
         ) (Cost=2 Card=1 Bytes=56)

Now, I'd like to trasform the query into something more optimizer-digestible,
ideally but not necessarilly forcing it to do an index join. (BTW, I've tried to set _index_join_enabled = true, but it only core dumps; Oracle support nonplussed). Can I force to tranform the subquery into join as in:

UPDATE PS_ITEM
SET PROCESS_INSTANCE = 15422
WHERE (BUSINESS_UNIT,CUST_ID,ITEM,ITEM_LINE) in

   (select PS_ITEM.BUSINESS_UNIT,PS_ITEM.CUST_ID,PS_ITEM.ITEM,PS_ITEM.ITEM_LINE     from PS_ITEM, PS_RP_PNDITM_TAO11
    where PS_RP_PNDITM_TAO11.PROCESS_INSTANCE = 15422

    AND PS_ITEM.PROCESS_INSTANCE = 0 
    AND PS_RP_PNDITM_TAO11.BUSINESS_UNIT = PS_ITEM.BUSINESS_UNIT 
    AND PS_RP_PNDITM_TAO11.CUST_ID = PS_ITEM.CUST_ID 
    AND PS_RP_PNDITM_TAO11.ITEM = PS_ITEM.ITEM 
    AND PS_RP_PNDITM_TAO11.ITEM_LINE = PS_ITEM.ITEM_LINE)

or

UPDATE PS_ITEM
SET PROCESS_INSTANCE = 15422
WHERE rowid in

      (select PS_ITEM.rowid 
       from PS_ITEM, PS_RP_PNDITM_TAO11
       where PS_RP_PNDITM_TAO11.PROCESS_INSTANCE = 15422 
       AND PS_ITEM.PROCESS_INSTANCE = 0 
       AND PS_RP_PNDITM_TAO11.BUSINESS_UNIT = PS_ITEM.BUSINESS_UNIT 
       AND PS_RP_PNDITM_TAO11.CUST_ID = PS_ITEM.CUST_ID 
       AND PS_RP_PNDITM_TAO11.ITEM = PS_ITEM.ITEM 
       AND PS_RP_PNDITM_TAO11.ITEM_LINE = PS_ITEM.ITEM_LINE)

Sufficient into the day is the evil thereof... Thanks!
 --Cubicle morlock Received on Thu Aug 22 2002 - 15:02:36 CDT