Brief Introduction into Partitioning in #Oracle

Partitioning is a great way to deal with large tables. This post will give you a quick start with examples that you can reproduce easily for yourself. Focus will be on Range-Partitioning, which is still the most popular kind.

First things first: You should only consider to implement partitioning for really large (GB range or more) objects, because it is an extra charged option and the benefits do not show significantly with small objects.

The two major reasons why you may want to use partitioning are Performance and Manageability. Let’s look at this picture:

Picture of a Range Partitioned TableAbove table is partitioned by the quarter. You will see that the table name and the columns are known by the application layer (INSERT and SELECT statements come from there), while the partitioned nature of the table needs to be known by the DBA only. I’m going to implement this on my demo system:

SQL> grant dba to adam identified by adam;
Grant succeeded.
SQL> connect adam/adam
 Connected.
 SQL> create table sales (id number, name varchar2(20),
 amount_sold number, shop varchar2(20), time_id date)
 partition by range (time_id)
 (
 partition q1 values less than (to_date('01.04.2012','dd.mm.yyyy')),
 partition q2 values less than (to_date('01.07.2012','dd.mm.yyyy')),
 partition q3 values less than (to_date('01.10.2012','dd.mm.yyyy')),
 partition q4 values less than (to_date('01.01.2013','dd.mm.yyyy'))
 );
Table created.

From the viewpoint of the application, this is transparent, but the value of the TIME_ID column determines into which partition the inserted rows will go. And also, if subsequent SELECT statements have the partition key in the WHERE clause, the optimizer knows which partitions need not to be scanned. This is called Partition Pruning:

Picture of INSERTand SELECT on Partitioned TableI’ll show the application perspective first:

 
SQL> insert into sales values ( 1, 'John Doe', 5000, 'London', date'2012-02-16' );
1 row created.
SQL> commit;
Commit complete.
SQL> select sum(amount_sold)
 from sales
 where time_id between
 date'2012-01-01' and date'2012-03-31';
SUM(AMOUNT_SOLD)
 ----------------
 5000
SQL> set lines 300
SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID	crtwzf8j963h7, child number 0
-------------------------------------
select sum(amount_sold) from sales where time_id between
date'2012-01-01' and date'2012-03-31'

Plan hash value: 642363238

-------------------------------------------------------------------------------------------------
| Id  | Operation		| Name	| Rows	| Bytes | Cost (%CPU)| Time	| Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	|	|	|	|    14 (100)|		|	|	|
|   1 |  SORT AGGREGATE 	|	|     1 |    22 |	     |		|	|	|
|   2 |   PARTITION RANGE SINGLE|	|     1 |    22 |    14   (0)| 00:00:01 |     1 |     1 |
|*  3 |    TABLE ACCESS FULL	| SALES |     1 |    22 |    14   (0)| 00:00:01 |     1 |     1 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("TIME_ID">=TO_DATE(' 2012-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
	      "TIME_ID"<=TO_DATE(' 2012-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

Notice the PSTART=1 and PSTOP=1 above, which indicates Partition Pruning. So only one quarter was scanned through, speeding up my Full Table Scan accordingly. When the table is partitioned by the day, that SELECT on a large, even filled table would run 365 times faster – which is not at all unusual, many customers have hundreds, even thousands of partitions exactly therefore.

Now to the Maintenance benefit: DBAs can now get rid of old data very fast with DROP PARTITION commands. DELETE would be an awful lot slower here – if millions of rows are deleted, that is. Or some kind of Information Life-cycle Management can be implemented like compressing old partitions. They can even be moved into other tablespaces that have their datafiles on cheaper storage:

SQL> alter table sales move partition q1 compress;
Table altered.

When you put indexes on a partitioned table, you have the choice between GLOBAL and LOCAL like on the next picture:

Picture of a Partitioned Table with GLOBAL and LOCAL IndexesThe LOCAL index partitions follow the table partitions: They have the same partition key & type, get created automatically when new table partitions are added and get dropped automatically when table partitions are dropped. Beware: LOCAL indexes are usually not appropriate for OLTP access on the table, because one server process may have to scan through many index partitions then. This is the cause of most of the scary performance horror stories you may have heard about partitioning!

A GLOBAL index spans all partitions. It has a good SELECT performance usually, but is more sensitive against partition maintenance than LOCAL indexes. The GLOBAL index needs to be rebuilt more often, in other words. Let’s implement them:

SQL> create index sales_id on sales (id);
Index created.
SQL> create index sales_name on sales (name) local;
Index created.

We have Dictionary Views for everything, of course :-)

SQL> select table_name, tablespace_name from user_tables;

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
SALES

SQL> select table_name, partitioning_type, partition_count from user_part_tables;

TABLE_NAME		       PARTITION PARTITION_COUNT
------------------------------ --------- ---------------
SALES			       RANGE		       4

SQL> select table_name, partition_name, tablespace_name, pct_free, compression from user_tab_partitions;

TABLE_NAME		       PARTITION_NAME		      TABLESPACE_NAME		       PCT_FREE COMPRESS
------------------------------ ------------------------------ ------------------------------ ---------- --------
SALES			       Q1			      USERS				      0 ENABLED
SALES			       Q4			      USERS				     10 DISABLED
SALES			       Q3			      USERS				     10 DISABLED
SALES			       Q2			      USERS				     10 DISABLED

SQL> select index_name, tablespace_name, status from user_indexes;

INDEX_NAME		       TABLESPACE_NAME		      STATUS
------------------------------ ------------------------------ --------
SALES_ID		       USERS			      VALID
SALES_NAME						      N/A

SQL> select index_name, partitioning_type, partition_count from user_part_indexes;

INDEX_NAME		       PARTITION PARTITION_COUNT
------------------------------ --------- ---------------
SALES_NAME		       RANGE		       4

SQL> select index_name, partition_name, tablespace_name,status from user_ind_partitions;

INDEX_NAME		       PARTITION_NAME		      TABLESPACE_NAME		     STATUS
------------------------------ ------------------------------ ------------------------------ --------
SALES_NAME		       Q1			      USERS			     USABLE
SALES_NAME		       Q4			      USERS			     USABLE
SALES_NAME		       Q3			      USERS			     USABLE
SALES_NAME		       Q2			      USERS			     USABLE

This should be enough to get you started. We have much more to say about partitioning, of course: VLDB and Partitioning Guide. The pictures in this posting are from an LVC demonstration that I have done recently to convince potential customers to use this new training format, and I thought to myself: There must be something additional that I can do with this stuff :-)

I hope you find it useful – feel free to comment, also if you’d like to share some of your experiences with partitioning that would be very much appreciated. Thank you!

Conclusion: Partitioning can be a very powerful tool in the DBA’s arsenal to transparently speed up applications and to ease maintenance. It is no silver bullet, though, so as always: Don’t believe it, test it :)

Related postings about Partitioning:

Partition Pruning & Interval Partitioning… shows Partitioning Pruning performance benefit with a larger table and how new range partitions are created automatically
Reducing Buffer Busy Waits with Automatic Segment Space Management & Hash Partitioning… shows why Hash Partitioning is often used for large OLTP tables to reduce contention
Partitioning a table online with DBMS_REDEFINITION… shows how to change the structure of a table while it is permanently accessed by end users
CELL_PARTITION_LARGE_EXTENTS now obsolete… shows that you get 8 MB initial extents for partitioned tables in recent versions
Partition-Pruning: Do & Don’t… shows how the SQL code determines whether Partition Pruning can be used or not

About these ads

, ,

  1. #1 by rajesh gN on January 28, 2013 - 04:16

    Hi ,

    Thanks for the post,Could you please advise whether its good to create a local index for partitions in OLAP table rather than global index.

    Note : I have 3000 partitions for a table could you also please advise the advantage and disadvantage of Local index plzzzz.

    Many Thanks
    Rajesh GN

  2. #2 by Uwe Hesse on February 4, 2013 - 09:20

    Rajesh GN, I don’t know your environment, but in general, local indexes are better suited for access in parallel, while global indexes are usually the way to go for sequential access. Please take your more detailed question to a forum like OTN Database general: http://forums.oracle.com/forums/forum.jspa?forumID=849&start=0

  3. #3 by Ramesh on September 2, 2013 - 23:25

    Very useful one.Thank you very much

  4. #4 by Uwe Hesse on September 4, 2013 - 08:45

    Ramesh, you’re welcome! Thanks for taking the time to leave such a nice feedback :)

  5. #5 by Rob Naastepad on January 24, 2014 - 12:01

    As always, a wonderful piece of Oracle technical documentation including examples. I was wondering if you can also elaborate on partitioning when used on Exadata X4-2. Should it or should it not be used, And if used, do you have any tips and tricks?

  6. #6 by Uwe Hesse on January 25, 2014 - 21:07

    Rob, partitioning is widely used on Exadata as well as on other platforms where large segments are in use. This is not Exadata specific, though. And thank you for the nice words :-)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,604 other followers

%d bloggers like this: