Online redefinition to (hopefully) solve our stats problems

For years we've been plagued by a particular application table that has ridicoulous data skew in it that a normal histogram cannot deal with - resulting in bad plans and poor performance.

We're now trying a new approach using hash partitioning - to implement this we made use of online redefinition to not cause an outage to the application.

Basic tests have shown the plans are much better with this new partitioned approach but we'll see how it really copes when it actually goes live.

The steps we followed to redefine the table are shown below - this was a very simple table with just one index (no fk's etc to worry about - so a very simple candidate for online redef).

The steps are as follows:

First create the table with the definition we want

create table DEMO_redef (DEMO number(38),unique_id number(38)) partition by hash (UNIQUE_ID) partitions 512;





Then tell oracle we are planning to switch DEMO with DEMO_REDEF at some point - this sets up some bits and pieces (mainly an mview log to track changes to the original). Note we use the special option DBMS_REDEFINITION.CONS_USE_ROWID as we do not have a PK on this table.

EXEC DBMS_REDEFINITION.start_redef_table(USER, 'DEMO', 'DEMO_REDEF',options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);



Now we create the new partitioned index on the partitoned table.

create unique index DEMO_REDEF_idx on DEMO_redef (unique_id,DEMO) local;

Now we gather stats on the new objects (now in theory we have 254* 512 buckets for the histograms)

EXEC DBMS_STATS.GATHER_table_STATS (OWNNAME => USER, TABNAME => 'DEMO_REDEF',GRANULARITY=>'ALL',METHOD_OPT => 'FOR COLUMNS SIZE 254 unique_id',estimate_percent=>null);



Now we complete the redefinition, basically catch up using the mview log and then do a switch n the data dictionary so that DEMO is renamed to DEMO_REDEF and vice versa.

EXEC DBMS_REDEFINITION.finish_redef_table(USER, 'DEMO', 'DEMO_REDEF');




And it's done - now we just need to drop the original index as we need to re-use that name for the new index.

drop index DEMO_INDEX;

alter index DEMO_REDEF_IDX rename to DEMO_INDEX;

We could now drop DEMO_REDEF as this is the 'old' table we dont want - but for now I've left it.

In the simple case like above DBMS_REDEFINITION is very simple - it can cope with the more complex things you need to do - but just needs some more thought - though newer version do ahndle a lot more for you.