Re: Merge statement not using index

From: Igor Neyman <igor.neyman_at_gmail.com>
Date: Fri, 23 Mar 2012 14:11:05 -0400
Message-ID: <CAJoeKmt+8hx_RNcUsLZgdKYXpcHaUSXi+tc6Zkw7jZ6Z7bw1Vg_at_mail.gmail.com>

If you use "dynamic sql" inside your store procedure to do merge, Oracle will have to re-parse the statement, and m.b. it'll create "correct" execution plan using index instead of FTS. Regards,
Igor Neyman

On Fri, Mar 23, 2012 at 2:00 PM, Sandra Becker <sbecker6925_at_gmail.com>wrote:

> Oracle - EE 10.2.0.4
> Platform - IBM zSeries, SLES 10
>
> I have 19 related tables that need to have data archived daily. If I
> run my merge script manually, it uses the primary key index on all the
> tables, both source and target. For obvious reasons I want to
> automate the process. However, when I run the procedure to do the
> merge (so far only one table in the procedure), it does an FTS instead
> of using the pirmary key index. I read where others had the same
> problem and the suggestion was to use the index hint. It is ignoring
> my hint. Due to the size of the majority of these archive tables, an
> FTS is unacceptable.
>
> Can someone point me in the right direction for resolving this issue?
> I haven't found anything in the Oracle 10g docs or googling yet that
> has been helpful.
>
> Thanks.
> --
> Sandy
> Transzap, Inc.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 23 2012 - 13:11:05 CDT