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.

Advertisements

Big Announcement – New Job, New Country (A New Career In A New Town) April 1, 2017

Posted by Richard Foote in Richard's Blog, Richard's Musings.
20 comments

washington

APRIL FOOLS

After 5 1/2 years at Oracle Corporation, I’ve decided to leave and take on a very exciting new challenge !!

President Trump’s office have accepted my nomination to head their IT department in Washington DC, where I’ll be responsible for ensuring all USA government data is stored in a extremely efficient and secure manner. So secure, that hopefully all those other US agencies (FBI, CIA, New York Times, etc.) that are trying to get access to it won’t be able to 🙂

Russia also expressed an interest in my services, but the beauty with this opportunity is that I’ll be able to work for both countries at the same time (obviously in a discreet, unofficial basis if you know what I mean).

I‘ll be in charge of all IT related matters with regard the new USA presidential administration, ensuring all White House related data is stored appropriately and securely (e.g. Fake News, Real News, Real Fake News, Fake Fake News, Fake News That Seems Fake But Is Really True, Real News Which Must Surely Be Fake But Is Remarkably True But Is To Be Marked Fake Regardless, Russian News Which Is Obviously True But Is Fake – Honest, etc. etc.). The tricky bit of course will be keeping all the inside leaks secret and working out what news belongs in what category. Unless it’s fake but sounds better if it were true (or fake), in which case it’s unclassified and to be replicated in the Fox News website.

Love to say I’ll miss Australia but to be honest, I’m getting a little worried about health services here as I unfortunately get older. That’s why I want to move to the USA, where they have a great universal health insurance system and I know if ever my luck is down and I can’t afford health insurance, I’ll still be well looked after.

For obvious reasons, I will have to live in Washington DC to perform my new role. Well I don’t actually have to be in Washington, I have Russian friends who say it’s dead easy to login into the USA Government systems from wherever. But if all the travel bans ever get implemented, I might not then be able to get into the US so it’s best I get in while I still can. I’m not Muslim which helps no end, but once the fake news gets out that I am Muslim with Mexican parents, well you see the problem here don’t you.

Only regret I do have in my new role will be having to stand occasionally behind President Trump and be photographed while he signs a presidential executive order. What can I say, the money is good and if the planet gets a little warmer, well won’t that mean more temperate winters and fewer mosquitos which has got to be a good thing right ?

Of course, one of my big responsibilities as head of IT in Washington is “modernizing” the computing infrastructure. That said, there does seem to be a culture of perhaps going back and doing things as they did in the past, so I’m kinda hoping my plan to implement Real Application Clusters using a bunch of re-processed Sinclair ZX81s will both work and fit in with my coal loving colleagues in the administration.

Love golf, I really do and to be perfectly honest this is my main motivation for taking on this job. My hours are basically to work for just a couple of mornings each week and spend the rest of the time playing golf and watching all that fantastic American TV. We’re under strict orders to only watch Fox News of course, but there are some really cool Russian dramas and reality TV shows we’re allowed to watch as well.

So exciting times ahead, looking forward to it all. Assuming of course the world doesn’t end first.

APRIL FOOLS