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.trackback
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 🙂
Thank you Richard for sharing this new functionality in 12.2.
More and more operations can be performed online.
LikeLike
Richard,
I have a client who has hit a bug with the online rebuild of partitions with attribute clustering enabled. Data corruption can occur. I’ve checked to see if I could find the bug in MoS, but failed; but I thought I’d pass the warning on anyway.
It’s a real nuisance because attribute clustering would be a massive win for their application, and a slow continuous cycle of rebuilding a few (hash sub-)partitions each day would have been extremely beneficial.
LikeLiked by 1 person
Hi Jonathan
Thanks for the heads-up. Can you share the database version? I recently ran a seminar where a customer mentioned a similar issue on 12.2 on Exadata but didn’t have support info at hand and hadn’t tested in 18c/19c.
LikeLike
This was on 12.1.0.2.
I haven’t tried emulating it – issues involving high volume and high levels of concurrency can be particularly difficult to model. It’s far too easy to spend a lot of time building a model that doesn’t reveal the problem.
Hoping to hear more on their SR in the near future.
LikeLike