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
>
>
>Any Ideas??
>JoeA

Yes and No, itīs possible to use an index in a select statement. But, the first thing to know is: is the database setup rule or cost based. If the setup is rule base than you can use hint indexes.

Why controlling the update by yourself, is the second question. I think you're searching for the quickest way.

Maybe this works:
select the rowids from the table by the preferred index ( use /*+ and not /*<space>+ ) and use this rowids for updating

Eric de Jonge
The Netherlands
eric.de.jonge_at_icl.nl Received on Tue Jul 14 1998 - 03:11:54 CDT