Re: Merge statement not using index

From: Sandra Becker <sbecker6925_at_gmail.com>
Date: Fri, 23 Mar 2012 12:34:58 -0600
Message-ID: <CAJzM94BC_ObLDDc4je0_FQjfvHThziZxnaxXtMzZLWFfr3__KQ_at_mail.gmail.com>

Good point, however, I am not using dynamic SQL in the procedure. I did move the procedure from running on the source to running from the target and that did give me the result I wanted--using the appropriate index. At least for the first table. I still need to go through the process for the remaining 18 tables.

Sandy

On 3/23/12, Igor Neyman <igor.neyman_at_gmail.com> wrote:
> 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
>>
>>
>>
>

-- 
Sandy
Transzap, Inc.
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 23 2012 - 13:34:58 CDT