Query Optimization?

From: Joe R Jurgena <jjurgena_at_shell.clark.net>
Date: 13 Jul 1998 22:04:36 GMT
Message-ID: <6oe09k$87o$1@clarknet.clark.net>

Is it possible to force Oracle to use an index on the table being updated and to do a full table scan on the sub select? How? The clmlst table only has 345000 rows while a has 4million +. Both tables are analyzed.

explain plan
set statement_id = 'a_UPD'
into plan_table
for
UPDATE /* +INDEX(tbl_a tbl_a_new_pk) */ tbl_a a SET EOB_ATT = (

   SELECT
      'Y'
   FROM
      tbl_b b
   WHERE

      a.claim_id = b.claim_id
      AND a.co_id = b.co_id);

RESULTS: UPDATE STATEMENT Cost = 54079
2.1 TABLE ACCESS FULL TBL_A
2.2 INDEX RANGE SCAN TBL_B_INDX NON-UNIQUE Any Ideas??
JoeA Received on Mon Jul 13 1998 - 17:04:36 CDT