Re: Query Optimization?

On 13 Jul 1998 22:04:36 GMT, Joe R Jurgena <jjurgena_at_shell.clark.net> wrote:

>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
>

But wait, there is no WHERE-clause on your UPDATE statement, means every record on tbl_a going to get updated. How would explain plan be for something like this: UPDATE tbl_a a SET EOB_ATT = 'Y' WHERE EXISTS

   ( SELECT *

       FROM tbl_b b
      WHERE a.claim_id = b.claim_id
        AND a.co_id = b.co_id);

--
Regards,
Roland Received on Tue Jul 14 1998 - 12:57:59 CDT