You Asked
Hi guys,
I want to take advantage of HCC compression in exadata and can see the best compression ratio's if compression takes place AFTER the data is loaded i.e alter table move partition compress... However, as with all databases today, the ask is for 24/7 online access so if I execute the move when users/jobs are querying the partition I get an ORA-08103. I am considering loading/compressing into a staging table first and then exchanging this partition with the "Live" table to minimise the chance of getting the ORA-08103 and will test this moving forward. However, would appreciate any comments or other suggestions you guys might have.
Many thanks,
KG
and Connor said...
My general policy is that for almost *anything* that is loading data into partitions, if you can take advantage of "exchange partition" then you should do so.
For example, even if you (say) had monthly partitions, and were loading just a day's worth of data, it *still* might be easier to load a staging table with the month to date, then load the new day, and then exchange the whole month.
Because
a) if any step fails, you're existing partitioned table is untouched - less risk
b) if any steps fails, you're staging table is a good source of debugging the why and where of what went wrong
c) you get the benefits you've already mentioned - you can compress the staged data with no impact to users (besides CPU consumption
d) You can validate the staged data in isolation, opening up possibilities of constraints being set to "rely" and hence no expensive continuous validation process whilst the data is loaded.
So yeah, exchange is very cool and you chould exploit it. Its also one of the reasons that local indexes are so cool.
Is this answer out of date? If it is, please let us know via a Comment