Re: Suggestion on compression

Thank you for the response. Actually as I mentioned , as per the current usage of tables in this database, we are not going to perform any type of write operations on the compressed table/index partitions as those will be historical ones as per the date column on which we partitioned the table. The write operation will only be performed on the live/current partition which will stay uncompressed only.

On Wed, Jan 4, 2023 at 11:32 PM Pap <oracle.developer35_at_gmail.com> wrote:

> I think one caution, if you will perform writes(say update) on the HCC
> compressed partitions then smart scan may not happen because of possible
> row chaining effect. So test it carefully.
>
> On Wed, Jan 4, 2023 at 12:31 PM yudhi s <learnerdatabase99_at_gmail.com>
> wrote:
>
>> 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