Sign In Account
Oracle Account

Account

Help

Sign Out
Oracle Account

Manage your account and access personalized content. Sign up for an Oracle Account

Sign in

Sign into Cloud

Access your cloud dashboard, manage orders, and more. Sign Up—Free Trial

Sign in
Country/Region
Contact

Online Data Reorganization & Redefinition

As business become more web and e-commerce enabled, high availability computing solutions are becoming increasingly important because of the extremely high cost of application downtime. According to a recent Gartner Group study, the cost of outage for corporations involved in e-commerce can easily reach millions of dollars per hour. Therefore, database availability and manageability have become increasingly important in many of today's mission-critical environments. One way to enhance availability and manageability is to allow users full access to the database during a data reorganization operation.

The Online Reorganization & Redefinition feature in Oracle Database 12c, offers administrators unprecedented flexibility to modify table physical attributes and transform both data and table structure while allowing users full access to the database. This capability improves data availability, query performance, response time and disk space utilization, all of which are important in a mission-critical environment and it can make the application upgrade process easier, safer and faster.

When a table is redefined online, it is accessible by all read and write operations during the redefinition process. During an application upgrade, administrators can start the redefinition process, then periodically synchronize the interim table so that the interim table includes the latest changes to the original table. This reduces the amount of time to complete the final redefinition step. Administrators can also validate and use the data in the interim table before completing the redefinition process. This is a very useful feature for administrators, who must ensure that the upgrade process goes smoothly. Administrators then have control over when to switch from the original to the newly redefined table. The switch process is very brief and is independent of the size of the table or the complexity of the redefinition. The redefinition process effectively creates a new table and improves its data block layout efficiency.

Oracle online data reorganization can be accomplished using either Enterprise Manager or SQL*Plus command line interface. Depending on the type of online reorganization that is required, it can be performed using either the DBMS_REDEFINITION package or using the SQL CREATE/ALTER TABLE and INDEX commands as shown in the table below.

Data Reorganization Feature Comparison

Action	Oracle Database 11g	Oracle Database 12c
Online Reorganization using the package DBMS_REDEFINITION	Table with materialized view logs or materialized views No recompilation of dependent objects when redefinition does not logically affect objects	Redefine multiple partitions in single session Redefine tables defined with Virtual Private Database (VPD) policies Simple, one-command redefinition with REDEF_TABLE
Index Create Online	DML lock-free online index creation, allowing transparent creation with no dependency on workload.
Index Rebuild Online	DML lock-free online index rebuild, allowing transparent rebuild with no dependency on workload.
Move Partition / Subpartition Online		Move a partition / subpartition from one table to another, with no impact to ongoing DML
Fast Add Column of Default Values	Instead of the default value being written to existing records and accessing each row in the table, they are simply maintained in the data dictionary. This enables 'add column' to complete in sub-seconds. Note: this is only supported for non-NULL columns.	Fast add column of default values is supported for NULL columns.
Invisible Indexes	An invisible index is an alternative to making an index unusable or even to drop it. An invisible index is maintained for any DML operation but is not used by the optimizer unless you explicitly specify the index with a hint. These indexes allow for quick testing of new indexes for specific operations without affecting the overall application.
DDL Enhancements	DDL operations can wait for a user configurable time period (DDL_LOCK_TIMEOUT), if underlying resource is busy, and is supported at system and session levels. Add/modify constraint, add column, index create/rebuild only require shared locks, not DML locks.	The following DDL operations do not impact ongoing DML: DROP INDEX ONLINE DROP CONSTRAINT ONLINE SET UNUSED COLUMN ONLINE ALTER INDEX UNUSABLE ONLINE ALTER INDEX [VISIBLE \| INVISIBLE]