Re: truncate partition

"Oradba_Linux" <techiey2k3_at_comcast.net> wrote in message news:IfydnfXW4OZ6dh7fRVn-qA_at_comcast.com...
> with update global indexes.
> We did this on 2 partitions of different tables today and timings were
> very different.One was 10 minutes and other was 100 minutes. Number of
> rows are similar but the table that took longer had one more index.
> But that index was on a nullable column and it was very small.
> I know i am little ambiguous here but just looking for some guesses.
>
> Thanks

To optimize the global delete on indexes, Oracle sorts the data from the truncated partition for each index in turn. It may simply be that the data from the slower
example required from resource in this
step of the process.

Then, when the actual update takes place, Oracle can optimise the generation of undo and redo by producing one record per
block changed, rather than one record per row. It may be that the indexes in the
slower example offered less opportunities for this optimisation (less repetitive data to be deleted, more select indexes etc.).

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005