Undo old mistakes with online table redefinition

There are times when you as a DBA wished you were involved before someone designed a data model. After release to production correcting mistakes is usually complicated and with risks.

Update 2016-04-20: Fixed errors in code.

There is an exception to this. Online table redefinition lets you change the structure of a table while everything is up and running. I have used it lately to partition large tables. During the process I also added compression, and moved some of the partitions to another tablespace that I have set read-only, in order to reduce backup time as well. LOBs in one table were moved to SecureFiles with deduplication.

Redefining tables online is explained in the Administrator's Guide, chapter 20 (11g and 12c). The PL/SQL package DBMS_REDEFINITION is used along SQL to create a new table with the desired structure.

The whole process can be quite simple, depending on how much you want to change, though it may require some time. Of course, in development, you can repeat the whole process in order to improve on the design in case you are not satisfied. In theory this can also be achieved in production, but I do recommend that you get tired of fixing this in development before you move on to production.

The example is made simple to get an overview, check the documentation for more details.

I did the whole process as user SYSTEM, but it may be executed as the owner of the table provided execute privilege on the package has been granted along with CREATE TABLE and CREATE MVIEW.

1. Verify that your table can be redefined this way: 
   
   

   begin 
     DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','BIG_TABLE'
      ,dbms_redefinition.cons_use_rowid);
   end;
   /
   

   
   The last parameter is for tables without a primary key (PK) defined, the default value here is dbms_redefinition.cons_use_pk that applies to tables with a PK. In other words, if your table has a PK (as it should have), skip this last parameter here and in step 3. If no error is returned you are good to go.
2. Create an empty interim table with the structure you want. Spend some time here in order to avoid new issues. I used SQL Developer to generate the SQL for the original table and edited  it to create the new table.  This is rather simple if all you want is to introduce partitioning or similar that doesn't change the columns and datatypes. Also note, remove all constraints on this interim table, they will be copied later. 
3.  Start the process with:
   
   

   begin
     DBMS_REDEFINITION.START_REDEF_TABLE ('SCOTT','BIG_TABLE'
     ,'BIG_TABLE_TMP',null
     ,dbms_redefinition.cons_use_rowid);
   end;
   /
   

   
   The 4^th parameter is null in this example, but is used when you want to reorder columns and need to use column mapping between the two tables. This statement starts the copying of rows from the original to the interim table, and will take some time depending on the amount of data. You may speed things up by enable parallel DML and query with:
   
   

   ALTER SESSION FORCE PARALLEL DML PARALLEL 8;
   ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;
   

   
   
4. Next step is to have Oracle copy all dependent objects of the table. That is, indexes, constraints, triggers, privileges, stats,  and more.
   
   

   declare
     l_err pls_integer;
   begin
     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>'SCOTT'
     ,orig_table=>'BIG_TABLE'
     , int_table=>'BIG_TABLE_TMP',num_errors=>l_err);
     dbms_output.put_line(l_err);
   end;
   /
   

   
   This procedure actually has options to skip certain object types like indexes, or triggers, etc. Check the documentation for details. Again, the amount of time this takes depends on the size of it all. But it is kind of cool that the Oracle database is working hard for you while you read Oracle blogs. If this procedure fails, it may be that you created the interim table (BIG_TABLE_TMP) with a NOT NULL constraint; easy to leave in there. Just remove the constraint and repeat this step; no need to start over from scratch.
5. Next step is to finish it all with:

   begin
      DBMS_REDEFINITION.FINISH_REDEF_TABLE ('SCOTT'
      ,'BIG_TABLE'
      ,'BIG_TABLE_TMP');
   end;
   /
   

   
   When this completes the dictionary has been updated and the table with name BIG_TABLE will have the structure you created in BIG_TABLE_TMP, and vice versa. You can now drop the BIG_TABLE_TMP table.

As usual with online operations there are some locks involved when the data dictionary is updated, so you may chose to finish the operation off peak time. Also in order to shorten the time spent in step 5 you may use DBMS_REDEFINITION.SYNC_INTERIM_TABLE   between step 4 and 5 to refresh the interim table first. Lastly, if you want to abort the procedure between step 3 and 5, just execute the ABORT_REDEF_TABLE procedure.

This feature has been around for some time, and is very stable. If you have SQL not performing well because a table grew too fast, or you have some other structural problems, this feature is really a low-hanging fruit. The idea behind here is that you don't touch the original table, but let Oracle do it for you when everything is ready (in step 5).

As always, keep it simple, life as a DBA has more than enough of excitements.

Table partitioning to improve stats

In a earlier post about legacy databases I mentioned that dirty data are more likely to enter the database on an earlier stage. One day while I was looking at the selectivity for a few columns I found the values a bit strange. After some digging I noticed that for most of the time the data was OK, even though a proper foreign key constraint was missing for the columns. But at some time many years ago somebody had inserted something completely different, which had clearly affected the selectivity (the columns are indexed).

Table partitioning is better to assist in the management of the data more than for tuning purposes, and I imagined that this would be another example. By range partitioning on transaction time dirty data would be kept on a partition separate from more recent history and thus improve the column stats. Though I haven't set up a test case (for the lack of a real problem) I believe that a correct selectivity will help the CBO and avoid these border problems where the CBO is tricked to throw off a wrong plan based on wrong statistics. That would be another example where the CBO is blamed for something that is not its fault.

This is why I don't like hints, it feels like cheating and being lazy. The CBO may have bugs, but I think you have to be as good as the author of this book to prove it; usually there is an underlying problem, like the one I encountered.

Anyway, I did a quick test, by copying the table in question into a partioned version and ran dbms_stats.gather_table_stats on it. In fact the selectivity did not change much between partitions, probably not enough to make an impact on CBO choice of plan. But I had used other parameters on the new table (most importantly I think, estimate_percent=>100), the columns in the two tables had very different values for selectivity. Which reiterated to me previous knowledge; checking the stats and the way they are collected is important, and sometimes a simple solution will do.