Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: March 22, 2017 - 10:54 am UTC

Last updated: March 23, 2017 - 3:45 am UTC

Version: 11.2.4.0

Viewed 1000+ times

You Asked

Hi Chirs/Connor,

We have 10 tables (Size around 510 GigaBytes).
We need to convert these non-partitioned table to Partition based on certain columns we identifed.

Currently we are running on Oracle 11g and there is no direct option available to convert non-partitioned table to Partition one (I tried sample test by running DBMS_REDEFINITION to do that in 11g but it keeps on running for hours).

In 12c there is direct command -
create table tb_test as
select rownum x, 'junk' y from dual
connect by level <= 1000;

alter table tb_test modify partition by range (x) interval (100) (
partition p1 values less than (101)
);

exec dbms_stats.gather_table_stats(user, 'tb_test');

Could you please suggest which will be the better approach like -
1. How ti get this done in 11g using DBMS_REDEFINITION (considering constraint dependencies, Indexes, Triggers)
2. How it can be done in 12c using ALTER command (considering constraint dependencies, Indexes, Triggers)

Thanks in advance!!

and Chris said...

Converting a table from non-partitioned to partitioned is a 12.2 feature. So your biggest hurdle is upgrading to this version!

Once you're on 12.2 you should use "alter table ... modify partition". As it's a single command, it's way easier to do than using dbms_redefinition. And you can do it online!

You can also specify whether you want the indexes to be local or global:

create table t ( x int, y int, z int ); 

create index iy on t (y);
create index iz on t (z);

alter table t modify partition by range (x) interval (100) (
  partition p1 values less than (100)
) update indexes (
  iy local,
  iz global
) online;

select index_name, partitioned 
from   user_indexes
where  table_name = 'T';

INDEX_NAME  PARTITIONED  
IY          YES          
IZ          NO


https://blogs.oracle.com/sql/entry/12_things_developers_will_love#partition-online
http://docs.oracle.com/database/122/SQLRF/ALTER-TABLE.htm#GUID-552E7373-BF93-477D-9DA3-B2C9386F2877__GUID-81BABA76-0CE0-4A67-835A-BF004D28ABA4

If you're not ready to upgrade yet, you can find a dbms_redefinition example on Oracle-base:

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

Rating

  (2 ratings)

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

Comments

Thanks a lot!!

A reader, March 22, 2017 - 12:01 pm UTC

Thanks for your quick help Chris.

So considering 12c, "alter table t modify partition by range (x) interval (100)" on 450GB size table can be done ONLINE and without downtime??
Connor McDonald
March 23, 2017 - 3:23 am UTC

12.2 yes.

How "online" is it really?

stephan, March 22, 2017 - 5:55 pm UTC

Hi Chris,

I'm curious as to how truly "online" this operation is. Is it "online" the way rebuilding an index online is "online"?
Connor McDonald
March 23, 2017 - 3:45 am UTC

What do you mean by the quotes ? online is pretty much online , eg

SQL> create table t as
  2  select *
  3  from dba_objects d;

Table created.

SQL>
SQL> create index t_ix on t ( object_id );

Index created.

--
-- lets do an uncommitted delete every 0.2 seconds
--

SQL>
SQL> set serverout on
SQL> begin
  2    for i in 1 .. 100 loop
  3       dbms_output.put_line('-------------------------');
  4       dbms_output.put_line(systimestamp);
  5       delete from t where owner != 'SYS' and rownum = 1;
  6       dbms_output.put_line(systimestamp);
  7       dbms_lock.sleep(0.2);
  8    end loop;
  9  end;
 10  /
-------------------------
23-MAR-17 11.40.22.441000000 AM +08:00
23-MAR-17 11.40.22.442000000 AM +08:00
-------------------------
23-MAR-17 11.40.22.643000000 AM +08:00
23-MAR-17 11.40.22.643000000 AM +08:00
-------------------------
23-MAR-17 11.40.22.842000000 AM +08:00
23-MAR-17 11.40.22.854000000 AM +08:00
-------------------------
23-MAR-17 11.40.23.055000000 AM +08:00
23-MAR-17 11.40.23.055000000 AM +08:00
-------------------------
...
...
...

--
-- meanwhile in another session
--
SQL> alter index t_ix rebuild online;

--
-- meanwhile in another session again
--
SQL> delete from t where owner = 'SCOTT' and rownum = 1;

1 row deleted.

SQL> commit;

Commit complete.

So other sessions are not blocked, even though the rebuild is still running.

Now session one finishes

23-MAR-17 11.40.42.068000000 AM +08:00
23-MAR-17 11.40.42.068000000 AM +08:00
-------------------------
23-MAR-17 11.40.42.268000000 AM +08:00
23-MAR-17 11.40.42.268000000 AM +08:00

PL/SQL procedure successfully completed.


and then when I commit, the operations will complete as expected

SQL> commit;

Commit complete.

--
-- and the rebuild session shows
--
SQL> alter index t_ix rebuild online;

Index altered.


So the index rebuild did not block anyone
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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.