and…
IF you have many repeating values on a column you are compressing (or set of
columns), it *may* be useful to physically order your data in that order. It
will save space, especially if you have many blocks that can end up with a
single substantial in storage size value per block by doing this.
IF you have time based partitions that become static, the appropriate rebuild
may only need to be done once after it becomes static and you won’t see a
downside from packing very tightly (zero percent free) in the partitions that
no longer change.
IF there is also a correspondence and compatibility of the compressed columns
and a commonly used index, this *may* significantly shrink the amount of disk
your need to read to retrieve row data as well as informing the CBO that the
cluster factor is optimal.
Please don’t miss any of the IFs, and only get on a rebuilding treadmill for a
very good and well understood reason. Rebuilding once, or once per partition,
is a very different proposition from recurrent rebuilding. Often folks get a
dramatic improvement in both size and performance from a “first rebuilding” of
an object that has accumulated. The mental trap is to therefore add wholesale
rebuilding to your operational schedule when the benefit is not recurrent.
This message is best evaluated AFTER getting up to speed in accordance with the
explanations on JL’s pages.
Good luck!
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jonathan Lewis
Sent: Tuesday, March 02, 2021 12:02 PM
To: mohamed.houri@xxxxxxxxx
Cc: Shane Borden; Lok P; Oracle L
Subject: Re: Space reclamation
Mohamed,
Thanks for mentioning that one - but you should have mentioned that you've also
published variations on the theme to address partitoined indexes, which I never
got around to doing:
https://www.red-gate.com/simple-talk/sql/oracle/oracle-partitioned-index-efficiency/
Regards
Jonathan Lewis
On Tue, 2 Mar 2021 at 13:11, Mohamed Houri <mohamed.houri@xxxxxxxxx> wrote:
Lock,
As already mentioned by Shane, I think it is a better idea to focus your
attention, first, on the index size. If you want to find out indexes occupying
more space than they should then you can use the following script supplied by
Jonathan Lewis
https://jonathanlewis.wordpress.com/index-sizing/
I have used this script several times in real life running systems, like
financial software Calypso, where rebuilding 7 or 8 indexes reduce the overall
database size by 570GB.
In addition, the prevision of this script is very precise.
https://hourim.wordpress.com/2015/05/12/index-efficiency/
However, as you can read it in the comment part of Jonathan’s script, only
b-tree, and function based indexes are targeted by this script
Best regards.
Mohamed Houri
Le mar. 2 mars 2021 à 13:42, Shane Borden <dmarc-noreply@xxxxxxxxxxxxx> a écrit
:
Focus your efforts on indexes that consume more space than its table and also
utilize avg_row_len x num_rows compared to bytes used by the table to determine
tables that need attention.
It’s always worth maintaining your database.
Shane Borden
sborden76@xxxxxxxxx
Sent from my iPhone
On Mar 2, 2021, at 5:48 AM, Lok P <loknath.73@xxxxxxxxx> wrote:
Hello Listers, We have got three different Oracle databases with rdbms
version 11.2.0.4, 12.1.0.2.0 and 19C. We got an ask from management
suggesting to look into all the objects(Tables , Indexes) having >25% free
space and get those reclaimed to have some storage back to disk. The
intention is two fold 1)To have unused space back to the storage/disk(which
in turns saves cost) 2) It may help us in improving the table/index scan
because of compact size. And the team wants us to have those reclaim jobs
set/run on a regular basis so that it can automatically find and run the
reorg on its own . I have two questions here
1) Should we go by checking the empty_blocks column and compare it with the
total block to find the eligible segments and then rebuild those?
2)I read a few blog posts which suggest reclaiming space may not worth the
effort because those spaces will eventually be consumed again by data
insert/manipulation. So is that true and we should not take up this effort in
the first place?
Regards
Lok
