Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, afsar.

Asked: February 10, 2016 - 9:36 am UTC

Last updated: February 11, 2016 - 10:31 am UTC

Version: 11.2.0.1

Viewed 1000+ times

You Asked

Can we alter a non-partitioned table into partition table (by Hash) using Alter table statement.If yes please guide me.My management dont want to use DBMS_redefination package.

Thanks & regards
Afsar Raza Bazmi


and Chris said...

No, you can't run an alter table to change the partitioning scheme.

You don't have to use DBMS_redefinition though. You can convert by creating a hash partitioned table. Then do a partition exchange from the non-partitioned to the partitioned.

You can find a worked example (for range partitioning) of this at:

https://oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Afsar, February 10, 2016 - 12:54 pm UTC

Thanks,

exchange non-partitioned => hash partitioned table

Markus, February 10, 2016 - 3:04 pm UTC

Hi,

Is it really possible to exchange a NON-partitioned table into a HASH partitioned table partition?

Thanks, Markus

Chris Saxon
February 10, 2016 - 5:35 pm UTC

Yep, at least as of 11.2:

http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm#VLDBG1157

I'm not sure when it came in.

create table t1 (
  x integer
);

create table t2 (
  x integer
) partition by hash (x) (
  partition p1
);

insert into t1 values (1);

alter table t2 exchange partition p1 with table t1;

select * from t1;

no rows selected

select * from t2;

         X
----------
         1

Exists even in 10.2.0.1

Rajeshwaran, Jeyabal, February 11, 2016 - 6:40 am UTC

I'm not sure when it came in.

Exists even in 10.2.0.1.

rajesh@ORA10G>
rajesh@ORA10G> create table t1 (
  2    x integer
  3  );

Table created.

rajesh@ORA10G>
rajesh@ORA10G> create table t2 (
  2    x integer
  3  ) partition by hash (x) (
  4    partition p1
  5  );

Table created.

rajesh@ORA10G> insert into t1 values (1);

1 row created.

rajesh@ORA10G> alter table t2 exchange partition p1 with table t1;

Table altered.

rajesh@ORA10G> select * from t1;

no rows selected

rajesh@ORA10G> select * from t2;

         X
----------
         1

1 row selected.

rajesh@ORA10G> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

5 rows selected.

rajesh@ORA10G>

Connor McDonald
February 11, 2016 - 10:31 am UTC

Afsar, just remember that once you've done this, then as you add partitions to presumably achieve more than a single partition, then make sure you end up with a power of 2 number of partitions - or they will be skewed.


SQL> drop table t1 purge;

Table dropped.

SQL> drop table t2 purge;

Table dropped.

SQL>
SQL> create table t1 (
  2        x integer
  3      );

Table created.

SQL>
SQL> create table t2 (
  2        x integer
  3      ) partition by hash (x) (
  4        partition p1
  5      );

Table created.

SQL>
SQL> insert into t1 select rownum from dual connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> alter table t2 exchange partition p1 with table t1;

Table altered.

SQL>
SQL> alter table t2 add partition p2;

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T2')

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'T2';

PARTITION_NAME         NUM_ROWS
-------------------- ----------
P1                        50154
P2                        49846

SQL>
SQL> alter table t2 add partition p3;

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T2')

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'T2';

PARTITION_NAME         NUM_ROWS
-------------------- ----------
P1                        24945
P2                        49846
P3                        25209

SQL>
SQL> alter table t2 add partition p4;

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T2')

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'T2';

PARTITION_NAME         NUM_ROWS
-------------------- ----------
P1                        24945
P2                        24956
P3                        25209
P4                        24890

SQL>


More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.