Re: Massive MERGE statement causing massive locks

On 10/13/2016 09:01 AM, David Ramírez Reyes wrote:
> The problem was that the indexes were not rebuild after purging the
> records (every month there's a process that deletes any record older
> than 3 months); once rebuilded, all deadlocks disappeared.
>
> About the use of MERGE, I google it and found that in general gives a
> better performance than just using INSERT/UPDATE instead so, I think I
> can let it working as it is (just need to be sure that indexes are
> rebuilded after the monthly purging).
>
> I will try anyway the usage of parallel processing for it, that may
> help also,
>
> Thanks everyone!

Hmmmm, that's very strange! Indexes usually don't need rebuilding. I would be interested in what Chris Foote has to say about this, if he still follows this list. What particular resource was getting locked?

-- 
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com

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