12c Online Partitioned Table Reorganisation Part I (Prelude) January 7, 2014
Posted by Richard Foote in 12c, Oracle Indexes, Partitioning, Unusable Indexes, Update Indexes, Update Indexes Online.trackback
First post for 2014 !!
Although it’s generally not an overly common activity with Oracle databases, reorganising a table can be somewhat painful, primarily because of the associated locking implications and the impact it has on indexes.
If we look at the following example:
SQL> create table muse2 (id number, status varchar2(6), name varchar2(30)); Table created. SQL> insert into muse2 select rownum, 'CLOSED', 'DAVID BOWIE' from dual connect by level <= 3000000; 3000000 rows created. SQL> commit; Commit complete. SQL> create index muse2_id_pk on muse2(id); Index created. SQL> alter table muse2 add constraint muse2_id_pk primary key(id); Table altered. SQL> create index muse2_status_i on muse2(status); Index created.
So we have a table with a couple of indexes. We can’t move the table using the ONLINE option as it’s only applicable for Index Organized Tables:
SQL> alter table muse2 move online; alter table muse2 move online * ERROR at line 1: ORA-01735: invalid ALTER TABLE option
If in one session, we have a current transaction on the table (i.e. not committed):
SQL> insert into muse2 values (3000001, 'OPEN', 'ZIGGY STARDUST'); 1 row created.
An attempt to MOVE the table in another session will fail with locking issues:
SQL> alter table muse2 move; alter table muse2 move * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
On the other hand, if the table MOVE command proceeds:
SQL> alter table muse2 move; Table altered.
It in turn locks other transactions out during the duration and leaves all indexes in an UNUSABLE state:
SQL> insert into muse2 values (3000001, 'OPEN', 'ZIGGY STARDUST'); insert into muse2 values (3000001, 'OPEN', 'ZIGGY STARDUST') * ERROR at line 1: ORA-01502: index 'BOWIE.MUSE2_ID_PK' or partition of such index is in unusableย state SQL> select index_name, status from dba_indexes where table_name='MUSE2'; INDEX_NAME STATUS --------------- -------- MUSE2_ID_PK UNUSABLE MUSE2_STATUS_I UNUSABLE
If we now look at a similar Partitioned Table example:
SQL> create table muse (id number, status varchar2(6), name varchar2(30)) 2 partition by range (id) 3 (partition p1 values less than (1000001), 4 partition p2 values less than (2000001), 5 partition p3 values less than (maxvalue)); Table created. SQL> insert into muse select rownum, 'CLOSED', 'DAVID BOWIE' from dual connect by level <= 3000000; 3000000 rows created. SQL> commit; Commit complete. SQL> create index muse_id_pk on muse(id); Index created. SQL> alter table muse add constraint muse_id_pk primary key(id); Table altered. SQL> create index muse_status_i on muse(status) local; Index created.
Similar locking and indexing issues occur if we try and reorganise a partition, even if we UPDATE INDEXES on the fly. For example, if we have an active transaction in one session:
SQL> insert into muse values (3000001, 'OPEN', 'ZIGGY STARDUST'); 1 row created.
While in another session:
SQL> alter table muse move partition p3 tablespace users update indexes; alter table muse move partition p3 tablespace users update indexes * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
We get the same old resource busy error. If we tried things the other way around, so in one session we first attempt to move a table partition:
SQL> alter table muse move partition p3 tablespace users update indexes; alter table muse move partition p3 tablespace users update indexes * ERROR at line 1: ORA-14327: Some index [sub]partitions could not be rebuilt
We can eventually get the above error if in another session we then attempt to insert a new row into this partition:
SQL> insert into muse values (3000002, 'OPEN', 'ZIGGY STARDUST'); 1 row created.
which in turn hangs for the period of time until the above error is generated.
The associated local index is now not a happy chappy:
SQL> select index_name, partition_name, status from dba_ind_partitions where index_name='MUSE_STATUS_I' union select index_name, null, status from dba_indexes where index_name='MUSE_ID_PK'; INDEX_NAME PARTITION_NAME STATUS --------------- --------------- -------- MUSE_ID_PK VALID MUSE_STATUS_I P1 USABLE MUSE_STATUS_I P2 USABLE MUSE_STATUS_I P3 UNUSABLE
So Moving tables and table partitions around can all get a bit messy, especially if high availability is required.
The DBMS_REDEFINITION package is designed specifically to enable the online redefinition of objects, but it has its own issues and is not as clean and simplistic as a simple MOVE operation when we just want to perform a table re-org.
So what was has changed in Oracle Database 12c ?
We’ll see in the next post although the title here does rather give it away ๐
It’s too beautiful to be true ๐ – http://docs.oracle.com/cd/E16655_01/license.121/e17614/options.htm#DBLIC142 – “Online Move Partition (to any compressed format)” belongs to AC add-on and I already see license audit/sales guys claiming that “NULL/NO compression” is still compression because contains “compression” keyword and requires paid option (in addition to EE). Sorry Richard, but I couldn’t resist, sarcasm fully intended but I still have my doubts regarding how they can classify even when moving to non-compressed partition ๐
BTW1: in your next post do you plan to describe internals/en-queues being taken during this operation?
BTW2: could you verify dba_features_usage after performing move online partition w/o compression and or looking for such symptoms in DBA_TAB_PARTITIONS.COMPRESS_FOR? ๐
– your long time dedicated and pessimistic reader ๐
LikeLike
Hi Jakub
No, it really is beautiful ๐
If you don’t move online compressed partitions, then everything is fine, you do NOT need the AC option. The NO in NOCOMPRESS is quite relevant ๐
In answer to your questions:
1) Not initially, but perhaps subsequently if there’s interest, as the locking internal scenarios with several indexes in place can be a touch involved.
2) The feature usage statistics is as one would expect when one doesn’t use compression, all clear. The COMPRESS_FOR value is null in my example. So no, there is no need for the Advanced Compression option in the above scenario.
Now stay on as a dedicated reader but let’s be a lot more optimistic please ๐
LikeLike