jump to navigation

12.2 Some Cool Partitioning New Features (Big Wheels) April 5, 2017

Posted by Richard Foote in 12c Rel 2, Oracle Indexes, Partitioning.
2 comments

ELO-Out_of_the_Blue_Lp

I previously discussed just how easy it is to convert online a non-partitioned table to be partitioned with Oracle Database 12.2.

Thought I might run through a number of really cool new partitioning features and capabilities that were also introduced in 12.2.

To start, I’m just going to create a basic range-partitioning table and populate it with a year’s worth of data:

 

SQL> create table ziggy
2 (prod_id NUMBER,
3 cust_id NUMBER,
4 time_id DATE,
5 quantity NUMBER)
6 PARTITION BY RANGE (time_id)
7 (PARTITION z_2016_q1 VALUES LESS THAN (TO_DATE('01-APR-2016','dd-MON-yyyy')),
8 PARTITION z_2016_q2 VALUES LESS THAN (TO_DATE('01-JUL-2016','dd-MON-yyyy')),
9 PARTITION z_2016_q3 VALUES LESS THAN (TO_DATE('01-OCT-2016','dd-MON-yyyy')),
10 PARTITION z_2016_q4 VALUES LESS THAN (TO_DATE('01-JAN-2017','dd-MON-yyyy')));

Table created.

SQL> insert into ziggy select mod(rownum,10), mod(rownum,100), sysdate-dbms_random.value(94, 454), 100
from dual connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

I’ll then create both a global, non-partitioned index and a locally partitioned index:

SQL> create index ziggy_prod_id_i on ziggy(prod_id);

Index created.

SQL> select index_name, num_rows, status from dba_indexes
where index_name='ZIGGY_PROD_ID_I';

INDEX_NAME             NUM_ROWS STATUS
-------------------- ---------- --------
ZIGGY_PROD_ID_I          100000 VALID

SQL> create index ziggy_time_id_i on ziggy(time_id) local;

Index created.

SQL> select index_name, partition_name, num_rows, status from
dba_ind_partitions
where index_name='ZIGGY_TIME_ID_I';

INDEX_NAME           PARTITION_NAME         NUM_ROWS STATUS
-------------------- -------------------- ---------- --------
ZIGGY_TIME_ID_I      Z_2016_Q1                 23941 USABLE
ZIGGY_TIME_ID_I      Z_2016_Q2                 25276 USABLE
ZIGGY_TIME_ID_I      Z_2016_Q3                 25522 USABLE
ZIGGY_TIME_ID_I      Z_2016_Q4                 25261 USABLE

 

OK, the first 12.2 new feature is the capability to now “Split” a partition online (previously this was an offline only operation that resulted in invalid global indexes and invalid corresponding local indexes):

SQL> alter table ziggy
2 split PARTITION z_2016_q4 into
3 (PARTITION z_2016_oct VALUES LESS THAN (TO_DATE('01-NOV-2016','dd-MON-yyyy')),
4 PARTITION z_2016_nov VALUES LESS THAN (TO_DATE('01-DEC-2016','dd-MON-yyyy')),
5 PARTITION z_2016_dec) online;

Table altered.

SQL> select index_name, num_rows, status from dba_indexes
where index_name='ZIGGY_PROD_ID_I';

INDEX_NAME             NUM_ROWS STATUS
-------------------- ---------- --------
ZIGGY_PROD_ID_I          100000 VALID

SQL> select index_name, partition_name, num_rows, status from dba_ind_partitions where index_name='ZIGGY_TIME_ID_I';

INDEX_NAME           PARTITION_NAME         NUM_ROWS STATUS
-------------------- -------------------- ---------- --------
ZIGGY_TIME_ID_I      Z_2016_DEC                 8276 USABLE
ZIGGY_TIME_ID_I      Z_2016_NOV                 8298 USABLE
ZIGGY_TIME_ID_I      Z_2016_OCT                 8687 USABLE
ZIGGY_TIME_ID_I      Z_2016_Q1                 23941 USABLE
ZIGGY_TIME_ID_I      Z_2016_Q2                 25276 USABLE
ZIGGY_TIME_ID_I      Z_2016_Q3                 25522 USABLE

6 rows selected.

 

Nice !!

OK, let’s quickly check how many rows I have for each PROD_ID value that belongs within the Q1 partition:

SQL> select prod_id, count(*) from ziggy where time_id
between '01-JAN-2016' and '31-MAR-2016'
group by prod_id order by prod_id;

   PROD_ID   COUNT(*)
---------- ----------
         0       2391
         1       2334
         2       2324
         3       2372
         4       2284
         5       2462
         6       2348
         7       2399
         8       2380
         9       2388

 

So we have a PROD_ID with values between 0 and 9 that have roughly the same number of rows.

Let’s now check the size of each table partition in blocks:

SQL> select partition_name, blocks from dba_tab_partitions
where table_name='ZIGGY';

PARTITION_NAME           BLOCKS
-------------------- ----------
Z_2016_DEC                   44
Z_2016_NOV                   44
Z_2016_OCT                   45
Z_2016_Q1                  1006
Z_2016_Q2                  1006
Z_2016_Q3                  1006

 

Note that the Q1 partition current has 1006 blocks allocated.

OK, the next cool new feature is to select which rows we make want to keep during a subsequent re-org operation. In the following example, I’m going to re-org the Q1 partition and compress the data, but I’m only going to keep those rows where the PROD_ID is between 1 and 8 (hence getting rid of all rows with PROD_ID that’s 0 or 9):

SQL> ALTER TABLE ziggy
2 MOVE PARTITION z_2016_q1 TABLESPACE users COMPRESS ONLINE
3 INCLUDING ROWS WHERE prod_id between 1 and 8;

Table altered.

 

The new INCLUDING ROWS clause explicitly states that I’m only going to include those rows where the PROD_ID is between 1 and 8.

If we now check the size of the partition and its contents:

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY');

PL/SQL procedure successfully completed.

SQL> select partition_name, blocks from dba_tab_partitions
where table_name='ZIGGY';

PARTITION_NAME           BLOCKS
-------------------- ----------
Z_2016_DEC                   44
Z_2016_NOV                   44
Z_2016_OCT                   45
Z_2016_Q1                    57
Z_2016_Q2                  1006
Z_2016_Q3                  1006

SQL> select prod_id, count(*) from ziggy where time_id
between '01-JAN-2016' and '31-MAR-2016'
group by prod_id order by prod_id;

   PROD_ID    COUNT(*)
---------- ----------
         1       2334
         2       2324
         3       2372
         4       2284
         5       2462
         6       2348
         7       2399
         8       2380

 

We see the Q1 partition has indeed decreased in size (down to just 57 blocks from 1006 blocks) because it has now been compressed AND because it now only has rows where the PROD_ID is between 1 and 8.

Nice !!

The next cool new feature is that we now have new syntax (FOR EXCHANGE WITH TABLE) to more easily create a table by which we wish to subsequently perform a partition exchange. This ensures that the new table is entirely compatible for such an exchange, although note that associated index are NOT created as part of this process:

SQL> CREATE TABLE ziggy_exchange
2 TABLESPACE users
3 FOR EXCHANGE WITH TABLE ziggy;

Table created.

SQL> ALTER TABLE ziggy
2 EXCHANGE PARTITION z_2016_q1 WITH TABLE ziggy_exchange;

Table altered.

 

If we look at the contents of each object, we can see the partition exchange has been successful:

SQL> select prod_id, count(*) from ziggy where time_id
between '01-JAN-2016' and '31-MAR-2016'
group by prod_id order by prod_id;

no rows selected

SQL> select prod_id, count(*) from ziggy_exchange
where time_id between '01-JAN-2016' and '31-MAR-2016'
group by prod_id order by prod_id;

   PROD_ID   COUNT(*)
---------- ----------
         1       2334
         2       2324
         3       2372
         4       2284
         5       2462
         6       2348
         7       2399
         8       2380

Nice !!

The final new 12.2 partitioning feature I want to introduce is the ability now to make a particular partition (or sub-partition) read only:

SQL> alter table ziggy modify partition z_2016_q1 read only;

Table altered.

SQL> select table_name, partition_name, read_only
from dba_tab_partitions where table_name='ZIGGY';

TABLE_NAME           PARTITION_NAME       READ
-------------------- -------------------- ----
ZIGGY                Z_2016_DEC           NO
ZIGGY                Z_2016_NOV           NO
ZIGGY                Z_2016_OCT           NO
ZIGGY                Z_2016_Q1            YES
ZIGGY                Z_2016_Q2            NO
ZIGGY                Z_2016_Q3            NO

SQL> insert into ziggy values (1,1,'13-JAN-2016', 1);
insert into ziggy values (1,1,'13-JAN-2016', 1)
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.

 

Nice !!

There are lots of great new features introduced with Oracle Database 12c R2, but sometimes it’s these lesser know features that can so terribly useful.