Suggestion on compression

From: yudhi s <learnerdatabase99_at_gmail.com>
Date: Wed, 4 Jan 2023 12:30:11 +0530
Message-ID: <CAEzWdqdv+xeNL1cr0oaJmiVWNC-AFyjgk0EZ81h=Eeg5PZ+Q5g_at_mail.gmail.com>



Hello Experts, Happy new year to all.

Considering Oracle database version 19C(19.15 to be specific and its Exadata-X8 machine). The database having majority of the transaction tables are daily range partitioned on a business date column holding truncated date values(without any time component). These tables are holding ~1 year+ worth of data i.e. ~365+ partitions.

The DMLS/write only happens on the live partitions or say today's day data(Say Nth partition).

The querying or reading happens mainly last ~5days data or latest N-5 partitions. However during monthend process(which runs once in a month) , it reads/scans a month worth of data i.e. all the partitions > sysdate-30 or latest N-30 partitions. Rest of the data/partitions are read/written in adhoc basis with minimal frequency.

Now, we are seeing a space crunch in the box and also considering future growth we have been asked to compress all the possible table/indexes partitions. My question is , in the above scenario, will it be a good approach..

1)To just HCC compress all the historical table partitions i.e. all the partitions with partition position <N-30th using 'compress for query high' option.
2)And for all <N-30th local index partitions with 'compress advanced low' option. As "ADVANCE" index compression will let oracle choose the best index prefix key in local partition level and leaf block level automatically rather than us to worry about that.

Or any other strategy would be best in the above situation for these transaction tables?

As I understand the writing will not be a concern as we wont be touching the live partitions, however in case the reading(i.e. index scan, cell smart scan etc.) will happen on the compressed table/index data , it can change the current performance figures. Is my above understanding correct here?

Regards
Yudhi

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 04 2023 - 08:00:11 CET

Original text of this message