DBA Hub

📋Steps in this guide1/4

Online Conversion of a Non-Partitioned Table to a Partitioned Table in Oracle Database 12c Release 2 (12.2)

Oracle Database 12c Release 2 makes it easier than ever to convert a non-partitioned table to a partitioned table, requiring only a single command and no downtime.

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

Setup

Create and populate a test table. You will need to repeat this between each test. We can see the data is spread across three years.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
DROP TABLE t1 PURGE;

CREATE TABLE t1 (
  id           NUMBER,
  description  VARCHAR2(50),
  created_date DATE,
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

CREATE INDEX t1_created_date_idx ON t1(created_date);

INSERT INTO t1
SELECT level,
       'Description for ' || level,
       ADD_MONTHS(TO_DATE('01-JAN-2017', 'DD-MON-YYYY'), -TRUNC(DBMS_RANDOM.value(1,4)-1)*12)
FROM   dual
CONNECT BY level <= 10000;
COMMIT;

SELECT created_date, COUNT(*)
FROM   t1
GROUP BY created_date
ORDER BY 1;

CREATED_D   COUNT(*)
--------- ----------
01-JAN-15       3340
01-JAN-16       3290
01-JAN-17       3370

SQL>
2

Partition a Table

We convert the table to a partitioned table using the command. Here are some basic examples of this operation. Adding the keyword allows the operation to be completed online, meaning most DML operations can continue during the conversion. We gather statistics and check the table partitions. We can see the data has been split between the partitions as expected. When we check the indexes we see the index on the column has been converted to a locally partitioned index. By default all prefixed indexes, an index with the partition key in the column list, will be converted to locally partitioned indexes. All indexes are left in a valid state at the end of the operation. The offline operation is similar, but we omit the keyword. The outcome will be the same as the online operation, but DML will not be available during the operation. We can influence the index conversion using the clause. This can indicate the type of index to be rebuilt as part of the operation, as well as some storage parameters. In the following example we want the index on the column to remain as a global index. After running the last example we can see the indexes as both non-partitioned, and as a result there are no index partitions.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
-- Online operation.
ALTER TABLE t1 MODIFY
  PARTITION BY RANGE (created_date) (
    PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
    PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
    PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))
  ) ONLINE;

-- Gather statistics.
EXEC DBMS_STATS.gather_table_stats(NULL, 'T1');

-- Check table partitions.
SELECT table_name, partition_name, num_rows
FROM   user_tab_partitions
ORDER BY 1,2;

TABLE_NAME           PARTITION_NAME         NUM_ROWS
-------------------- -------------------- ----------
T1                   T1_PART_2015               3340
T1                   T1_PART_2016               3290
T1                   T1_PART_2027               3370

SQL>

-- Check indexes.
SELECT index_name, partitioned, status
FROM   user_indexes
ORDER BY 1;

INDEX_NAME           PARTITIONED STATUS
-------------------- ----------- --------
T1_CREATED_DATE_IDX  YES         N/A
T1_PK                NO          VALID

SQL>

-- Check index partitions.
SELECT index_name, partition_name, status
FROM   user_ind_partitions
ORDER BY 1,2;

INDEX_NAME           PARTITION_NAME       STATUS
-------------------- -------------------- --------
T1_CREATED_DATE_IDX  T1_PART_2015         USABLE
T1_CREATED_DATE_IDX  T1_PART_2016         USABLE
T1_CREATED_DATE_IDX  T1_PART_2017         USABLE

SQL>

-- Offline operation.
ALTER TABLE t1 MODIFY
  PARTITION BY RANGE (created_date) (
    PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
    PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
    PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))
  );

-- Online operation with modification of index partitioning.
ALTER TABLE t1 MODIFY
  PARTITION BY RANGE (created_date) (
    PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
    PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
    PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))
  ) ONLINE
  UPDATE INDEXES
  (
    t1_pk GLOBAL,
    t1_created_date_idx GLOBAL
  );

-- Check indexes.
SELECT index_name, partitioned, status
FROM   user_indexes
ORDER BY 1;

INDEX_NAME           PARTITIONED STATUS
-------------------- ----------- --------
T1_CREATED_DATE_IDX  NO          VALID
T1_PK                NO          VALID

SQL>

-- Check index partitions.
SELECT index_name, partition_name, status
FROM   user_ind_partitions
ORDER BY 1,2;

no rows selected

SQL>
3

Composite Partition (Sub-Partition) a Table

The original table can also be composite-partitioned using the command. In this example we convert the original table to a range-hash partitioned table. The sub-partitions of the table and partitioned index can be displayed using the following queries.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
ALTER TABLE t1 MODIFY
  PARTITION BY RANGE (created_date) SUBPARTITION BY HASH (id)(
    PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')) (
      SUBPARTITION t1_sub_part_2015_1,
      SUBPARTITION t1_sub_part_2015_2,
      SUBPARTITION t1_sub_part_2015_3,
      SUBPARTITION t1_sub_part_2015_4
    ),
    PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')) (
      SUBPARTITION t1_sub_part_2016_1,
      SUBPARTITION t1_sub_part_2016_2,
      SUBPARTITION t1_sub_part_2016_3,
      SUBPARTITION t1_sub_part_2016_4
    ),
    PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')) (
      SUBPARTITION t1_sub_part_2017_1,
      SUBPARTITION t1_sub_part_2017_2,
      SUBPARTITION t1_sub_part_2017_3,
      SUBPARTITION t1_sub_part_2017_4
    )
  ) ONLINE
  UPDATE INDEXES
  (
    t1_pk GLOBAL,
    t1_created_date_idx LOCAL
  );

COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20

SELECT table_name, partition_name, subpartition_name
FROM   user_tab_subpartitions
ORDER BY 1,2, 3;

TABLE_NAME           PARTITION_NAME       SUBPARTITION_NAME
-------------------- -------------------- --------------------
T1                   T1_PART_2015         T1_SUB_PART_2015_1
T1                   T1_PART_2015         T1_SUB_PART_2015_2
T1                   T1_PART_2015         T1_SUB_PART_2015_3
T1                   T1_PART_2015         T1_SUB_PART_2015_4
T1                   T1_PART_2016         T1_SUB_PART_2016_1
T1                   T1_PART_2016         T1_SUB_PART_2016_2
T1                   T1_PART_2016         T1_SUB_PART_2016_3
T1                   T1_PART_2016         T1_SUB_PART_2016_4
T1                   T1_PART_2017         T1_SUB_PART_2017_1
T1                   T1_PART_2017         T1_SUB_PART_2017_2
T1                   T1_PART_2017         T1_SUB_PART_2017_3
T1                   T1_PART_2017         T1_SUB_PART_2017_4

SQL>


COLUMN index_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20

SELECT index_name, partition_name, subpartition_name, status
FROM   user_ind_subpartitions
ORDER BY 1,2;

INDEX_NAME           PARTITION_NAME       SUBPARTITION_NAME    STATUS
-------------------- -------------------- -------------------- --------
T1_CREATED_DATE_IDX  T1_PART_2015         T1_SUB_PART_2015_1   USABLE
T1_CREATED_DATE_IDX  T1_PART_2015         T1_SUB_PART_2015_2   USABLE
T1_CREATED_DATE_IDX  T1_PART_2015         T1_SUB_PART_2015_3   USABLE
T1_CREATED_DATE_IDX  T1_PART_2015         T1_SUB_PART_2015_4   USABLE
T1_CREATED_DATE_IDX  T1_PART_2016         T1_SUB_PART_2016_1   USABLE
T1_CREATED_DATE_IDX  T1_PART_2016         T1_SUB_PART_2016_2   USABLE
T1_CREATED_DATE_IDX  T1_PART_2016         T1_SUB_PART_2016_4   USABLE
T1_CREATED_DATE_IDX  T1_PART_2016         T1_SUB_PART_2016_3   USABLE
T1_CREATED_DATE_IDX  T1_PART_2017         T1_SUB_PART_2017_1   USABLE
T1_CREATED_DATE_IDX  T1_PART_2017         T1_SUB_PART_2017_3   USABLE
T1_CREATED_DATE_IDX  T1_PART_2017         T1_SUB_PART_2017_2   USABLE
T1_CREATED_DATE_IDX  T1_PART_2017         T1_SUB_PART_2017_4   USABLE

SQL>
4

Restrictions

There are some restrictions associated with this functionality. - It can't be used to partition an index-organized table (IOT). - It can't be used if the table has a domain index. - You can only convert a table to a reference-partitioned child table in offline mode. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!