Here’s a question from the OTN forum that I’ve seen fairly frequently in the last couple of years:
I have a table abc(c_date date, c_id varchr2(20), c_name varchar2);
The table is already range partitoned on “c_date” column and I have lot of data in that table.
Now my task is to create the hash partions under range; hash partiton needs 16 partitons.
I dont want to create another table with range and hash partitons and migrate the data. The amount of data I have is 3 terabytes.
The statement of requirements is a little ambigious in this case – does the OP want a table where new partitions should be hash partitioned, or does he want the entire data set to be range/hash partitioned. If it’s the latter then he will have to do something that rewrites (and re-indexes) the entire data set; if the former then he can run through a process that does a small amount of data dictionary manipulation to get to the required state. Here’s a quick outline that I hacked together in a few minutes between sessions (okay, I’ll admit it also needed a few minutes during a session) at the ACED briefing.
Here’s a code demonstration – but it doesn’t include the stats patching, and it’s not a model you should use exactly as it is on a production system.
First we create a range-based table for our demonstration:
create table pt_range ( id1 not null, id2 not null, grp, small_vc, padding ) partition by range(id1) ( partition p200 values less than (200), partition p400 values less than (400) ) as select cast(rownum as number(8)) id1, cast(trunc(dbms_random.value(1,64)) as number(8)) id2, cast(trunc(rownum/50) as number(4)) grp, lpad(to_char(trunc(rownum/20)),10) small_vc, rpad('x',100) padding from all_objects where rownum <= 350 ;
Now we can create an empty copy – but introduce the hash subpartition – and two staging tables.
create table pt_range_hash ( id1 number(8,0) not null, id2 number(8,0) not null, grp number(4), small_vc varchar2(10), padding varchar2(100) ) partition by range(id1) subpartition by hash (id2) subpartitions 1 ( partition p200 values less than (200), partition p400 values less than (400) ) ; create table transfer_r ( id1 number(8,0) not null, id2 number(8,0) not null, grp number(4), small_vc varchar2(10), padding varchar2(100) ) ; create table transfer_h ( id1 number(8,0) not null, id2 number(8,0) not null, grp number(4), small_vc varchar2(10), padding varchar2(100) ) partition by hash (id2) ( partition p1 ) ;
You’ll notice that I’ve matched the partition names of the full clone using a very simple subpartition name for the one hash subpartition, and repeated that simple name for one partition of the hash-partitioned table. This isn’t absolutely necessary, but I think it makes the next process simpler to understand.
begin for r in ( select partition_name from user_tab_partitions where table_name = 'PT_RANGE' order by partition_position ) loop dbms_output.put_line(r.partition_name); execute immediate 'alter table PT_RANGE exchange partition ' || r.partition_name || ' with table transfer_r including indexes without validation' ; execute immediate 'alter table transfer_h exchange partition ' || 'p1' || ' with table transfer_r including indexes without validation' ; execute immediate 'alter table pt_range_hash exchange partition ' || r.partition_name || ' with table transfer_h including indexes without validation' ; end loop; end; /
When this block completes your data hasn’t moved, it hasn’t been copied, but it is now in a range/hash partitioned table. The statistics that used to exist at the partition level on the original table will now be on the new table at the subpartition level, and the new table won’t have partition-level or table-levle stats. (I may find time to write part 2 to this note with code that fixes the stats – but any reader who wants to have a shot at it instead is welcome to supply the code).
Now we can change the default behaviour of the new table to specify the number of hash partitions for future partitions:
alter table pt_range_hash set subpartition template 16 ;
NOTE: I did say that this is a model that you shouldn’t follow in a production system. The main reason for this is that if something does go wrong in the loop then you could end up with partitions switching around into the wrong place in the wrong table. (And you definitely don’t want to drop any objects until you’ve checked them all!). Realistically you would probably transfer one partition at a time, and check the results after each transfer. Bear in mind, also, that this was just a little hack put together very quickly. Even though the general strategy is sound I may have overlooked some critical detail and, obviously, there are some variations on theme (IOTS, for example) where you would need to work out some code changes; there’s also an implicit assumption that the only indexes on the table are local indexes – if there are any global, or globally partitioned, indexes then the code has to be modified to allow for them, and the time for the various exchanges will be affected by the choice of index maintenance options.