12c Online Partitioned Table Reorganisation Part II (Move On) January 15, 2014
Posted by Richard Foote in 12c, Move Partitions, Oracle Indexes, Update Indexes Online.15 comments
In Part I, we looked at some of the issues associated with locking and unusable indexes when Moving both tables and table partitions.
The Oracle 12c Database has introduced a number of great new capabilities associated with online DDL activities. One of these really useful features is the capability to now move table partitions online while maintaining associated indexes.
If we look at the same demo as with the previous post:
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.
If in one session we have an active transaction (i.e. not yet committed):
SQL> insert into muse values (3000001, 'OPEN', 'ZIGGY STARDUST'); 1 row created.
while we move a table partition in another session as we did previously:
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 resource busy error.
However, if we now use the new 12c UPDATE INDEXES ONLINE clause:
SQL> alter table muse move partition p3 tablespace users update indexes online;
The session now simply hangs until the transaction in session one completes, in a similar manner to that of an index rebuild online operation.
Once we commit in session one:
SQL> commit; Commit complete.
The Move Partition operation can obtain the necessary table partition lock and complete successfully:
SQL> alter table muse move partition p3 tablespace users update indexes online; Table altered.
The indexes remain in a USABLE state throughout:
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 USABLE
If we perform these operations the other way around with the Move Partition first:
SQL> alter table muse move partition p3 tablespace users update indexes online;
While in session two we now insert a new row into the partition being moved:
SQL> insert into muse values (3000004, 'OPEN', 'ZIGGY STARDUST'); 1 row created.
The insert operation is now not locked out by the Move Partition operation and can proceed. The Move Partition operation in turn requires a brief lock at the end of the process (in much the same way as a normal online index rebuild) and so will hang if there are any outstanding transactions on the partition being moved at that time. And again like an online index rebuild, this will not in turn lock out other DML transactions.
Once the commit is performed in session two:
SQL> commit; Commit complete.
The Move Partition can complete:
SQL> alter table muse4 move partition p3 tablespace users update indexes online; Table altered.
And importantly, all the associated indexes again remain in a USABLE state:
SQL> select index_name, partition_name, status from dba_ind_partitions where index_name='MUSE_CODE_I' union select index_name, null, status from dba_indexes where index_name='MUSE_ID_PK'; INDEX_NAME PARTITION_NAME STATUS --------------- -------------------- -------- MUSE_CODE_I P1 USABLE MUSE_CODE_I P2 USABLE MUSE_CODE_I P3 USABLE MUSE_ID_PK VALID
Now this is only applicable to the online move of table partitions. Moving a non-partitioned table still has the same issues and restrictions as before (as discussed in Part I).
Therefore for those with the Partitioning option, serious consideration could be given to converting tables to partitioned tables, even if the table only has the one partition:
SQL> create table muse3 (id number, status varchar2(6), name varchar2(30)) 2 partition by range (id) 3 (partition p1 values less than (maxvalue)); Table created.
Such a table is effectively the equivalent of a non-partitioned table, but can now take advantage of the flexibility that the new online move partition capability provides 🙂
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.2 comments
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 🙂
Demo Links To All Older Posts Now Accessible (Chains) January 3, 2014
Posted by Richard Foote in Richard's Blog.add a comment
OK, for a quite some time (too long probably !!!) people have been sending me emails and leaving comments that they have been unable to access a number of the demos to my older posts and those listed in my Presentations and Demos page. I previously would write an article but include a demo that illustrated the point I was trying to make in a separate link, primarily to not scare people off with overly long posts. However WordPress stopped supporting the plain text files I used to upload the demos and so people would get error messages when accessing the demos . This was one of the reasons I stopped using this format and instead simply included the demos as part of the text of my later blog articles. That and because many people didn’t bother following the links anyways and so missed out on a lot of the fun.
As people pleaded for access to a particular demo I would fix them but in order to start 2014 with a clean slate, I’ve gone through all my blog articles and where it had a link to a demo, re-formatted the associated file to PDF which WordPress does support. So hopefully, everyone can now access all my demos to all my articles both on the Home and Presentation/Demos page. If you happen to stumble across a link to a demo that doesn’t work, just let me know.
I feel like I’ve finally cleaned out the back shed, a job I kept putting off …
Richard Foote's Oracle Blog 