jump to navigation

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.
trackback

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 :)

About these ads

Comments»

1. Richard Foote - January 15, 2014

Don’t ask me what the hell happened to the formatting of my previous attempt at this post, I have no idea !!

Hopefully, a bit easier to read now ;)

2. Adrian Angelov - January 15, 2014

Thanks for the useful post, Richard

We’ve tried to compress partitions online, while on 11g, but hit too many problems back then( because of global and local indexes presence … and bugs). Details are available on https://community.oracle.com/message/10406641

Definitely worth testing how 12c handles these.

Thanks again

Jonathan Lewis - January 15, 2014

I’m sorry I didn’t see your OTN posting when it first came out. I’m a little surprised you saw the problem on inserts into the primary key (global) index – since that’s the one that shouldn’t have changed much on maintenance.

The doubling (or worse) in size of the non-unique global indexes doesn’t surprise me, and I think you’ll see it in 12c as well. Oracle has to delete and insert all the index data for the final “exchange”, and since the rowid components (on the non-unique indexes) have changed every row value will have to appear (temporarily) twice in the index.

It’s possible that a variant of the 12c “delayed global index maintenance” could come into play, of course, so Oracle could leave the garbage in place and not try handling it until an overnight task cleans it all up. Otherwise your best bet still is probably to do a “shrink space (compact)” or a “rebuild online” on the indexes immediately after the compression.

In your original post I did wonder why no-one asked about “create table as select from partition; index; exchange” Since you’re using basic compression it looks like you’re not expecting the partition to be updated – so copy and swap should be safe.

Adrian Angelov - January 15, 2014

Thanks a lot for the provided input, Jonathan.

In our case there were rare, but still possible, DMLs on the currently modified(compression in progress) old partition rows. The idea was to compress the data and save storage space, and ignore that some of the rows will be uncompressed later.
We had to do it online to avoid blocking of such sessions.

The very first thing we tried was the EXCHANGE PARTITION( actually DBMS_REDEFINITION does it this way(via alter table … exchange partition …) under the hood(verified by tracing it) and the problems are described in the OTN thread ) but it didn’t go well because of the global indexes.

Our conclusion at the time was that if we want to do it online in 11g( locking is for a minimal amount of time during the compression) we had to get rid of the global indexes( by replacing them with local ones) thus EXCHANGE PARTITION works instantaneously.

Thank you

3. Uwe Hesse - January 15, 2014

I still wonder why ALTER TABLE … MOVE ONLINE is not also allowed, because it should be technically possible as well. Do you have an idea?

Matthias Rogel - January 20, 2014

Interesting indeed.
And it seems to be documented that it *is* allowed see

http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_3001.htm#SQLRF53294

( for restrictions , no restriction is mentioned )

Richard Foote - February 14, 2014

Hi Uwe

I suspect it’ll come.

12c has spent lots of time with partitioning related enhancements and there are far more use cases in relation to information lifecycle management where it makes sense to move just a partition vs. moving the whole table.

Perhaps changing the code path for non-partitioned objects was a step too far for insufficient return for 12c R1 but if enough Oracle customers demand it, then I suspect it’ll eventually come :)

4. Uwe Hesse - January 21, 2014

The documentation is correct in so far as the ONLINE clause is not a syntax mistake. But it works only for index organized tables – so far.

Matthias Rogel - January 21, 2014

Should be mentioned in the restrictions then, shouldn’t it ?

Now, that’s really weird:
A non-partitioned table can only be moved online when it is index organized whereas a partition of a table can only be moved online when it is not index-organized:

sokrates@12.1 > create table t( i int primary key ) ;

Table created.

sokrates@12.1 > alter table t move online;
alter table t move online
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option


sokrates@12.1 > drop table t;

Table dropped.

sokrates@12.1 > create table t( i int primary key ) organization index;

Table created.

sokrates@12.1 > alter table t move online;

Table altered.

sokrates@12.1 > drop table t;

Table dropped.

sokrates@12.1 > create table t( i int primary key ) partition by range(i) ( partition p values less than (maxvalue) );

Table created.

sokrates@12.1 > alter table t move partition p online;

Table altered.

sokrates@12.1 > drop table t;

Table dropped.

sokrates@12.1 > create table t( i int primary key ) organization index partition by range(i) ( partition p values less than (maxvalue) );

Table created.

sokrates@12.1 > alter table t move partition p online;
alter table t move partition p online
            *
ERROR at line 1:
ORA-14808: ONLINE MOVE PARTITION is not supported for this table


Richard Foote - February 14, 2014

When Oracle come up with IOTs, it had an interesting problem regarding whether to treat it as a table or index. Because it’s kinda both/neither. But because it’s an index like structure, it can be rebuilt just like any other index structure and because you can rebuild an index online, you can also rebuild an IOT online.

I guess Oracle could have gone with ALTER TABLE bowie REBUILD ONLINE for IOT but went with reusing the MOVE syntax instead :)

5. lcdsantos - January 30, 2014

It´s really a complete nonsense that we can move online a partition from a partioned table (in fact, a data segment) and not a non-partitioned table (in fact, also a data segment).

6. Richard Foote - February 14, 2014

If enough Oracle customers see merit in moving whole tables online then I’m sure such functionality will eventually be added. But see my previous comment.

7. Martin Berger (berx) - May 16, 2014

I try to see the “MOVE PARTITION UPDATE INDEX ONLINE” as a reuse of the technology Oracle needs for their Information Lifecycle Management (ILM). So they provide the same technology twice: once for ILM (with additional license) and as simple table syntax for educated users.
just my thoughts without checking the details (yet)

8. Richard Foote - July 8, 2014

Hi Martin

That’s precisely why these improvements have been introduced. I might add that the dbms_redefinition package has been very much simplified such that a simple move of a table to a new tablespace can be very easily performed with just calling redef_table.

9. Today’s activity « Oracle-dba - August 30, 2014

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,918 other followers

%d bloggers like this: