Sorry to bother you with my questions. A last question, could you please elobrate about your last point: (In this respect, it's a bit like > the index that is being used as a FIFO queue, > which is the index most likely to degenerate > into 99% empty with most of it's data in 1% > of the leaf blocks). Just elobration of FIFO queue. On 11/24/05, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> wrote: > > Given that we are currently guessing that > the problem is an unlucky pattern of empty > blocks appearing on the truncate, you might > try to do a coalesce on the index that causes > the problem immediately after the truncate. > > The 'maintain global indexes' simply does > a bulk delete (like an sql*load in reverse) > from the index, it doesn't rebuild it. And this > is why you can get the empty blocks. > > It's important to note that the problem ISN'T > just the truncate/maintain - it is also an unlucky > side-effect of the nature of the index that is > being maintained. (In this respect, it's a bit like > the index that is being used as a FIFO queue, > which is the index most likely to degenerate > into 99% empty with most of it's data in 1% > of the leaf blocks). > > > 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/cbo_book/ind_book.html > Cost Based Oracle: Fundamentals > > http://www.jlcomp.demon.co.uk/appearances.html > Public Appearances - schedule updated 4th Nov 2005 > > ----- Original Message ----- > From: "The Human Fly" <sjaffarhussain@xxxxxxxxx> > To: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx> > Cc: "oracle-l" <oracle-l@xxxxxxxxxxxxx> > Sent: Thursday, November 24, 2005 9:13 AM > Subject: Re: Simple SQL waiting on 'log file sync' > > > You are 100% correct in your advice. I felt the same. Can you please > suggest me any other workaround to achieve the same, I mean to bypass > this oddity sequences what I am following. > It would be a great help and great relieaf for me, if you could give > any workaround. > Otherwise, should I include index coalesce as a practice after all the > prior sequences done? > My question is, when I am truncating partitions and updating global > indexes, wont that I am rebuilding them? If so, index still left with > empty leafs? > > Thanks for your time Jonathan. > > > > -- Best Regards, Syed Jaffar Hussain OCP 8i & 9i DBA, Banque Saudi Fransi, Saudi Arabia ---------------------------------------------------------------------------------- "Winners don't do different things. They do things differently." -- //www.freelists.org/webpage/oracle-l