RE: dbms_space.space_usage and effectiveness of purge operations

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: Martin Klier <usn@xxxxxxxxx>, "mwf@xxxxxxxx" <mwf@xxxxxxxx>
  • Date: Tue, 5 Nov 2013 20:34:30 +0000

Not  a heretical question at all - though there are two or three ways of asking 
it, depending who you are and when you ask it.

The OP should have started the question (to self): I am deleting one day of 
history each data - will this result in any of the indexes on that table 
degenerating in a way that I care about (e.g. space wastage or performance) ?

As I pointed out in my earlier post, based on my guesses about the nature of 
the index specification and the pattern of the data, this doesn't sound like an 
index that needs to be coalesced or rebuilt.

Jonathan Lewis

From: Martin Klier [usn@xxxxxxxxx]
Sent: 05 November 2013 20:00
To: mwf@xxxxxxxx
Cc: Jonathan Lewis; stalinsk@xxxxxxxxx; 'oracle-l'
Subject: Re: dbms_space.space_usage and effectiveness of purge operations

Sorry if I'm obfuscating the intentions of th OP, but maybe some basic
thoughts about the motivation to coalesc indexes are to be asked for.

Why would we want to coalesce indexes anyway? Ok, after deleting huge
amounts of data that will never come back, to save space and maybe
reduce the index blevel.

But for a "breathing" or steadily growing dataset, why bother?

It might be a heretical question, but it would be nice to hear opinions. :)

Thanks a lot

Am 05.11.2013 20:23, schrieb Mark W. Farnham:

> I do repeat my question about what his goal is. Densely packed leaf
> blocks not being a good thing for OLTP near random index insertion, I
> **hope** densely packing leaf blocks is not his goal.--
Usn's IT Blog for Oracle and Linux

Other related posts: