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@xxxxxxxxx> 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@xxxxxxxxx>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. >> -- >> //www.freelists.org/webpage/oracle-l >> >> >> > -- Sandy Transzap, Inc. -- //www.freelists.org/webpage/oracle-l