Re: Compression failing for partition object

Thank you for the guidance.

On Fri, 6 Aug 2021, 2:02 pm Pap, <oracle.developer35_at_gmail.com> wrote:

> Yes, I think in your scenario the only option would be to test the
> degradation of DML performance on the live/current partition with the
> compressed index on a test environment and accordingly take a decision if
> that is okay compared to the gain you are getting for the read queries +
> space usage. And if it's okay then go for drop and create that index from
> scratch as fully local compressed index.
>
> On Thu, Aug 5, 2021 at 2:01 PM Lok P <loknath.73_at_gmail.com> wrote:
>
>> Thank you Jonathan and Pap.
>>
>> I think I am stuck. As Pap mentioned, it seems we won't be able to
>> restrict the compression at subpartition to make it restrict to one day
>> range subpartition only. So no meaning in checking the feasibility of
>> directly achieving compression at partition/subpartition level. Which means
>> the only option left would be to drop and create the index as local
>> compressed. And we may have to validate what level of degradation we are
>> going to see in the dml performance on the live partition in presence of a
>> compressed index and if that is acceptable or not.
>>
>> But yes at least , it's learning and by using this "Advanced low"
>> compression while creating the compressed index we can go partition by
>> partition which would be in a controlled fashion rather than in one shot
>> drop and create. But again the real index we have is a function based
>> index. This advanced low option does not work for function based indexes
>> too :), i tested and below doc states that too. Also as I read through the
>> advanced low option , it looks like there is not much difference between
>> basic compression vs advanced low except few restrictions on advanced low
>> and also this 'advanced low' is better wrt compression ratio as compared to
>> basic compression.
>>
>> https://www.oracle.com/a/tech/docs/advanced-compression-poc-insights.pdf
>>
>> So basically in this case, we don't have much of an option but to drop
>> and create the local function based indexes with basic compression in one
>> shot(no partition by partition/subpartition possible). And have to live
>> with whatever DML performance degradation it will have on the live
>> subpartition. Please correct me if I'm interpreting anything wrong here.
>>
>> On Thu, Aug 5, 2021 at 4:25 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
>> wrote:
>>
>>> >> Actual requirement was on a 12.1 version database
>>> Here's the corresponding page on the 12.1 manual - it says exactly the
>>> same thing as the 19c manual (I don't know what happened to the UTL I tried
>>> to supply for the 19i reference - the thing that ended up in the email
>>> clearly wasn't it)
>>>
>>> https://docs.oracle.com/database/121/SQLRF/statements_1012.htm#BGBDJDEA
>>>
>>> According to that paragraph from the manual you shouldn't have been able
>>> to modify the partition to any type of compression because the index wasn't
>>> declared as compressed. And the first working syntax you showed shouldn't
>>> have worked at all with that mix of prefixed and andvanced compression:
>>>
>>> SQL> alter index USER1.CMPRS_IDx1 modify partition P_TABLE compress 2
>>> advanced low;
>>>
>>> Index altered.
>>> I have found, though, that on my 19c I can modify an index partition to
>>> "compres advanced low" or "high" even when the index was not declared as
>>> such - so maybe the manual (or this specific piece of this manual) is
>>> wrong. The manuals do sometimes contradict each other.
>>>
>>> So, contrary to the manuals, it looks as if you can create the index
>>> index without compression, compress to "advanced low" all the existing
>>> partitions and subpartiions - although this will presumably lock the table
>>> (or, at least, the relevant [sub]partition) while the rebuilds are
>>> happening and still have new [sub]partitions appearing without compression.
>>>
>>> One word of warning - anything with the word "Advanced" in its name may
>>> be subject to a separate licence, so check whether or not advanced indexed
>>> compression is part of a separately licensed "advanced compression" option;
>>> and if it is check that you have licensed it before you use it.
>>>
>>> For more information on advanced index compression, Richard Foote's blog
>>> is the best site to search - e.g google with
>>> compress advanced low site:richardfoote.wordpress.com
>>>
>>>
>>> Regards
>>> Jonathan Lewis
>>>
>>>
>>>
>>>
>>>
>>> On Wed, 4 Aug 2021 at 20:53, Lok P <loknath.73_at_gmail.com> wrote:
>>>
>>>> Thank You Jonathan. Actual requirement was on a 12.1 version database.
>>>>
>>>

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