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_at_gmail.com> 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_at_freelists.org> 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_at_yahoo.com
>> Sent from my iPhone
>>
>> > On Mar 2, 2021, at 5:48 AM, Lok P <loknath.73_at_gmail.com> 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
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
> --
>
> Houri Mohamed
>
> Oracle DBA-Developer-Performance & Tuning
>
> Visit My - Blog <http://www.hourim.wordpress.com/>
>
> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>
> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
> <https://twitter.com/MohamedHouri>
>
>

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