Re: Suggestion on compression

Can somebody guide us here please?

On Wed, 4 Jan, 2023, 11:45 pm yudhi s, <learnerdatabase99_at_gmail.com> wrote:

> 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