Hi, We are using version-11.2.0.4 of Oracle exadata. We have some of the daily range partition tables(having all local indexes). And to save storage we compress one of the very last partition each day as we move forward to next day. We have noticed couple of times the execution path of queries got changed during this time, as because the compress(query high) partition leaves the index partition in unusable state, (even we are not compressing index partitions) and all the queries executing during that time(before index partition rebuild occurs) which uses/refers the global statistics doesn't consider the index because of its UNUSABLE state endup following bad execution path.
We want to make it(compress partition) online so that no application queries gets impacted during this period due to index being UNUSABLE. We were thinking of isolating this activity, and were thinking to ask no application queries to be executed during this period. But it seems that business is not ready to have this downtime each day. And thinking of doing this on weekends , it will compress all ~7days of partition and will longer time. Need experts suggestions , how should we proceed in this scenario, if any alternatives?
We have few tables in which each of table partition is having ~60Gb in size holding(~500million records) and index partition having ~30GB in size. and have ~4-5 indexes in it. Currently for compressing one partition for all of the tables and getting those respective indexes rebuild takes around ~40minutes, so If we go by online compression with "Update Indexes" option, will it run for significantly longer time?
If we opt for online option even it run for longer , but atleast we will ensure no indexes would be in UNUSABLE state so application queries wont get impacted negatively. Is this online option having any other demerits/restrictions associated like locking etc?