jump to navigation

12.2 Online Conversion of a Non-Partitioned Table to a Partitioned Table (A Small Plot Of Land) March 27, 2017

Posted by Richard Foote in 12c Release 2 New Features, Attribute Clustering, Clustering Factor, Online DDL, Oracle, Oracle Indexes, Partitioning.
1 comment so far

Image result for outside bowie

In my previous post, I discussed how you can now move heap tables online with Oracle Database 12.2 and how this can be very beneficial in helping to address issues with the Clustering Factor of key indexes.

A problem with this technique is that is requires the entire table to be effectively reorganised when most of the data might already be well clustered. It would be much more efficient if we could somehow only move and reorganise just the portion of a table that has poorly clustered data introduced to the table since the last reorg.

Partitioning the table appropriately would help to address this disadvantage but converting a non-partitioned table to be partitioned can be a pain. To do this online with as little complication as possible one could use the dbms_redefintion package which has improved with latter releases.

However, with Oracle Database 12.2, there is now an even easier, more flexible method of performing such a conversion.

Using the same table definition and data as from my previous post, I’m going to first create a couple of additional indexes (on the ID column and on the DATE_CREATED column) :


SQL> create unique index ziggy_id_i on ziggy(id);

Index created.

SQL> create index ziggy_date_created_i on ziggy(date_created);

Index created.

To convert a non-partitioned table to a partitioned table online, we can now use this new extension to the ALTER TABLE syntax:


SQL> alter table ziggy
2 modify partition by range (date_created)
3 (partition p1 values less than (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
4 partition p2 values less than (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
5 partition p3 values less than (maxvalue)) online;

Table altered.

How simple is that !! We now have a table that is range partitioned based on the DATE_CREATED column and this conversion was performed online.

We notice not only is the table now partitioned with all the indexes remaining Valid, but the index based on the partitioning key (DATE_CREATED) has also been implicitly converted to be a Local partitioned index:


SQL> select table_name, status, partitioned from dba_tables
where table_name='ZIGGY';

TABLE_NAME   STATUS   PAR
------------ -------- ---
ZIGGY        VALID    YES

SQL> select index_name, status, partitioned, num_rows
from dba_indexes where table_name='ZIGGY';

INDEX_NAME           STATUS   PAR   NUM_ROWS
-------------------- -------- --- ----------
ZIGGY_DATE_CREATED_I      N/A YES    2000000
ZIGGY_CODE_I VALID             NO    2000000
ZIGGY_ID_I VALID               NO    2000000

SQL> select index_name, partition_name, status, leaf_blocks from dba_ind_partitions
     where index_name like 'ZIGGY%';

INDEX_NAME           PARTITION_NAME  STATUS   LEAF_BLOCKS
-------------------- --------------- -------- -----------
ZIGGY_DATE_CREATED_I              P1   USABLE         865
ZIGGY_DATE_CREATED_I              P2   USABLE        1123
ZIGGY_DATE_CREATED_I              P3   USABLE        1089

SQL> select index_name, partitioning_type, partition_count, locality
from dba_part_indexes where table_name='ZIGGY';

INDEX_NAME           PARTITION PARTITION_COUNT LOCALI
-------------------- --------- --------------- ------
ZIGGY_DATE_CREATED_I     RANGE               3 LOCAL

As part of the table conversion syntax, we have the option to also update all the associated indexes and partition them in any manner we may want. For example:


SQL> alter table ziggy
2 modify partition by range (date_created)
3 (partition p1 values less than (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
4 partition p2 values less than (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
5 partition p3 values less than (maxvalue)) online
6 update indexes
7 (ziggy_code_i local,
8 ziggy_id_i global partition by range (id)
9 (partition ip1 values less than (maxvalue)));

Table altered.

In this example, not only are we converting the non-partitioned table to be partitioned, but we’re also explicitly converting the index on the CODE column to be a Locally partitioned index and the index on the ID column to be Globally partitioned in its own manner.

If we look at the definition of these indexes, we see that they also have all been converted to partitioned indexes online along with the table:


SQL> select table_name, status, partitioned from dba_tables
where table_name='ZIGGY';

TABLE_NAME   STATUS   PAR
------------ -------- ---
ZIGGY           VALID YES

SQL> select index_name, status, partitioned from dba_indexes
where table_name = 'ZIGGY';

INDEX_NAME           STATUS   PAR
-------------------- -------- ---
ZIGGY_CODE_I              N/A YES
ZIGGY_ID_I                N/A YES
ZIGGY_DATE_CREATED_I      N/A YES

SQL> select index_name, partitioning_type, partition_count, locality
from dba_part_indexes where table_name='ZIGGY';

INDEX_NAME           PARTITION PARTITION_COUNT LOCALI
-------------------- --------- --------------- ------
ZIGGY_CODE_I             RANGE               3 LOCAL
ZIGGY_ID_I               RANGE               1 GLOBAL
ZIGGY_DATE_CREATED_I     RANGE               3 LOCAL

If we look at the Clustering Factor of the important CODE column index, we see that all partitions have an excellent Clustering Factor as all partitions have just been created.


SQL> select partition_name, num_rows, clustering_factor from dba_ind_partitions
where index_name='ZIGGY_CODE_I';

PARTITION_NAME         NUM_ROWS CLUSTERING_FACTOR
-------------------- ---------- -----------------
P1                       490000              2275
P2                       730000              3388
P3                       780000              3620

However, if we now add new rows to the table as would occur with a real application, the data from the “current” partition results in the Clustering Factor “eroding” over time for this partition.


SQL> insert into ziggy select 2000000+rownum, mod(rownum,100), sysdate, 'DAVID BOWIE'
from dual connect by level <= 500000; 500000 rows created. SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_index_stats(ownname=>null,indname=>'ZIGGY_CODE_I');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows, clustering_factor from dba_ind_partitions
     where index_name='ZIGGY_CODE_I';

PARTITION_NAME         NUM_ROWS CLUSTERING_FACTOR
-------------------- ---------- -----------------
P1                       490000              2275
P2                       730000              3388
P3                      1280000            238505

As discussed previously, the Clustering Attribute has no effect with standard DML operations. Therefore, the efficiency of the CODE index reduces over time in the partition where new data is being introduced. The Clustering Factor has now substantially increased from 3620 to 238505. Note for all the other partitions where there are no modifications to the data, the Clustering Factor remains excellent.

Having the table/index partitioned means we can therefore periodically reorg just the problematic partition:


SQL> alter table ziggy move partition p3 update indexes online;

Table altered.

SQL> select partition_name, num_rows, clustering_factor from dba_ind_partitions
     where index_name='ZIGGY_CODE_I';

PARTITION_NAME         NUM_ROWS CLUSTERING_FACTOR
-------------------- ---------- -----------------
P1                       490000              2275
P2                       730000              3388
P3                      1280000              5978

The Clustering Factor for this partition has now reduced substantially from 238505 to just 5978.

For those of you with the Partitioning database option, the ability in 12.2 to now so easily convert a non-partitioned table to be partitioned, along with its associated indexes is just brilliant 🙂

12c Enhanced Online Index DDL Operations (Lady Godiva’s Operation) February 17, 2014

Posted by Richard Foote in 12c, Drop Index, Invisible Indexes, Online DDL, Oracle Indexes, Unusable Indexes.
6 comments

In my last couple of posts, I discussed how table partitions can be moved online since 12c, keeping all indexes in sync as part of the process.

12c also introduced enhancements to a number of index related DDL statements, removing blocking locks and making their use online and far less intrusive. The following commands now have a new ONLINE option:

DROP INDEX ONLINE

ALTER INDEX UNUSABLE ONLINE

So if we look at a little example (initially on 11g R2), where we create a table and associated index on the CODE column:

SQL> create table radiohead (id number, code number, name varchar2(30));

Table created.

SQL> insert into radiohead select rownum, mod(rownum,1000), 'DAVID BOWIE' from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create index radiohead_code_i on radiohead(code);

Index created.

If we now insert a new row in one session but not commit:

SQL> insert into radiohead values (1000001, 42, 'ZIGGY STARDUST');

1 row created.

And then attempt any of the following DDL commands in another session:

SQL> drop index radiohead_code_i;

drop index radiohead_code_i
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter index radiohead_code_i invisible;

alter index radiohead_code_i invisible
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter index radiohead_code_i unusable;

alter index radiohead_code_i unusable
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

They all get the well-known “ORA-00054: resource busy” error.

If on the other hand, one of these DDL statements is already running in a session:

SQL> alter index radiohead_code_i unusable;

All DML statements in other sessions will hang until the DDL completes:

SQL> insert into radiohead values (1000002, 42, 'THIN WHITE DUKE');

Once the index is finally made unusable:

SQL> alter index radiohead_code_i unusable;

Index altered.

SQL> select index_name, status from dba_indexes where index_name = 'RADIOHEAD_CODE_I';

INDEX_NAME                     STATUS
------------------------------ --------
RADIOHEAD_CODE_I               UNUSABLE

SQL> select segment_name, blocks, extents from dba_segments where segment_name = 'RADIOHEAD_CODE_I';

no rows selected

We can see not only is the index now in an unusable state but the index segment has been dropped (in 11g r2) as the storage associated with the unusable index is of no further use.

So these commands prior to the Oracle 12c Database previously had locking related issues.

If we now perform the same setup in 12c and again have an outstanding transaction in a session:

SQL> drop index radiohead_code_i online;

The Drop Index command doesn’t now get the Ora-00054: resource busy, but rather hangs until all prior transactions complete.

However, while the Drop Index command hangs, it doesn’t in turn lock out transactions within other sessions. In another session:

SQL> insert into radiohead values (1000002, 42, 'THIN WHITE DUKE');

1 row created.

And in yet other session:

SQL> delete radiohead where id = 42;

1 row deleted.

SQL> commit;

Commit complete.

These all complete successfully. The Drop Index command itself will eventually complete successfully once all prior transaction have finished.

SQL> drop index radiohead_code_i online;

Index dropped.

Another more subtle difference in behaviour with 12c. If there’s an existing transaction when you decide to make an index unusable:

SQL> insert into radiohead values (1000001, 42, 'ZIGGY STARDUST');

1 row created.
SQL> alter index radiohead_code_i unusable online;

As in the previous demo, the alter index command will hang indefinitely until the previous transaction commits:

SQL> commit;

Commit complete.

SQL> alter index radiohead_code_i unusable online;

Index altered.

SQL> select index_name, status from dba_indexes where index_name = 'RADIOHEAD_CODE_I';

INDEX_NAME                STATUS
------------------------- --------
RADIOHEAD_CODE_I          UNUSABLE

SQL> select segment_name, blocks, extents from dba_segments where segment_name = 'RADIOHEAD_CODE_I';

SEGMENT_NAME         BLOCKS    EXTENTS
---------------- ---------- ----------
RADIOHEAD_CODE_I       2176         32

We note the index has eventually been made Unusable, however the segment has not now been dropped (as it was in the 11g R2 demo) due to the use of the ONLINE clause.

With the Oracle 12c Database, the locking implications and concurrency issues associated these index related DDL commands have been reduced with these new ONLINE options.