Posts Tagged partitioning

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

, ,

7 Comments

Partition-Pruning: Do & Don’t

I’d like to share a little demonstration from my present course in Vienna: Oracle Database 11g: Implement Partitioning. It is about how to write SQL in a way that supports Partition-Pruning – and what should be avoided.  The playing field looks as follows:

SQL> select * from v$version;
BANNER
 --------------------------------------------------------------------------------
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
 PL/SQL Release 11.2.0.2.0 - Production
 CORE    11.2.0.2.0      Production
 TNS for Linux: Version 11.2.0.2.0 - Production
 NLSRTL Version 11.2.0.2.0 - Production

SQL> select table_name,partitioning_type,partition_count from user_part_tables where table_name='SALES_YEAR';
TABLE_NAME                     PARTITION PARTITION_COUNT
 ------------------------------ --------- ---------------
 SALES_YEAR                     RANGE             1048575

SQL> select segment_name,partition_name,sum(bytes)/1024/1024 as mb
 from user_segments where segment_name='SALES_YEAR'
 group by rollup (segment_name,partition_name)
 order by 1,2; 

SEGMENT_NAME                   PARTITION_NAME                         MB
 ------------------------------ ------------------------------ ----------
 SALES_YEAR                     P1                                     16
 SALES_YEAR                     SYS_P181                               32
 SALES_YEAR                     SYS_P182                               32
 SALES_YEAR                     SYS_P183                               32
 SALES_YEAR                     SYS_P184                               32
 SALES_YEAR                     SYS_P185                               32
 SALES_YEAR                     SYS_P186                               32
 SALES_YEAR                     SYS_P187                               32
 SALES_YEAR                     SYS_P188                               32
 SALES_YEAR                     SYS_P189                               32
 SALES_YEAR                     SYS_P190                               32
 SALES_YEAR                     SYS_P191                               32
 SALES_YEAR                     SYS_P192                               32
 SALES_YEAR                     SYS_P193                               32
 SALES_YEAR                     SYS_P194                               32
 SALES_YEAR                     SYS_P195                               32
 SALES_YEAR                     SYS_P196                               32
 SALES_YEAR                     SYS_P197                               32
 SALES_YEAR                     SYS_P198                               32
 SALES_YEAR                     SYS_P199                               32
 SALES_YEAR                     SYS_P200                               32
 SALES_YEAR                     SYS_P201                               32
 SALES_YEAR                     SYS_P202                               32
 SALES_YEAR                     SYS_P203                               32
 SALES_YEAR                     SYS_P204                               32
 SALES_YEAR                     SYS_P205                               32
 SALES_YEAR                     SYS_P206                               32
 SALES_YEAR                     SYS_P207                               24
 SALES_YEAR                                                           872
 872
30 rows selected.

SQL> select to_char(order_date,'yyyy'),count(*) from sales_year group by to_char(order_date,'yyyy') order by 1;


TO_C   COUNT(*)
 ---- ----------
 1985     158000
 1986     365000
 1987     365000
 1988     366000
 1989     365000
 1990     365000
 1991     365000
 1992     366000
 1993     365000
 1994     365000
 1995     365000
 1996     366000
 1997     365000
 1998     365000
 1999     365000
 2000     366000
 2001     365000
 2002     365000
 2003     365000
 2004     366000
 2005     365000
 2006     365000
 2007     365000
 2008     366000
 2009     365000
 2010     365000
 2011     365000
 2012     346000
28 rows selected.

My moderately sized table is Interval partitioned (therefore PARTITION_COUNT in USER_PART_TABLES shows the possible  maximum number)  by the year on ORDER_DATE with 28 partitions. Now imagine we want to have the summarized AMOUNT_SOLD of the year 2011. What about this statement?

SQL> set timing on
SQL> select sum(amount_sold) from sales_year where to_char(order_date,'yyyy')='2011';

SUM(AMOUNT_SOLD)
----------------
      1825000000

Elapsed: 00:00:05.15
SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  cv54q4mt7ajjr, child number 0
-------------------------------------
select sum(amount_sold) from sales_year where
to_char(order_date,'yyyy')='2011'

Plan hash value: 3345868052

---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |       |       | 24384 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |            |     1 |    22 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|            |   287K|  6181K| 24384   (2)| 00:00:07 |     1 |1048575|
|*  3 |    TABLE ACCESS FULL | SALES_YEAR |   287K|  6181K| 24384   (2)| 00:00:07 |     1 |1048575|
---------------------------------------------------------------------------------------------------

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

   3 - filter(TO_CHAR(INTERNAL_FUNCTION("ORDER_DATE"),'yyyy')='2011')

It produces the required result, but using a Full Table Scan across all partitions. Very much better instead:

SQL> select sum(amount_sold) from sales_year where order_date between to_date('01.01.2011','dd.mm.yyyy') and to_date('31.12.2011','dd.mm.yyyy');

SUM(AMOUNT_SOLD)
----------------
      1825000000

Elapsed: 00:00:00.11
SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID  6rwm3z7rhgmd6, child number 0
-------------------------------------
select sum(amount_sold) from sales_year where order_date between
to_date('01.01.2011','dd.mm.yyyy') and
to_date('31.12.2011','dd.mm.yyyy')

Plan hash value: 767904852

------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |       |       |  1033 (100)|          |       |       |
|   1 |  SORT AGGREGATE         |            |     1 |    22 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|            |   378K|  8128K|  1033  (16)| 00:00:01 |    27 |    27 |
|*  3 |    TABLE ACCESS FULL    | SALES_YEAR |   378K|  8128K|  1033  (16)| 00:00:01 |    27 |    27 |
------------------------------------------------------------------------------------------------------

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

   3 - filter(("ORDER_DATE">=TO_DATE(' 2011-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ORDER_DATE"<=TO_DATE(' 2011-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

The same result but much faster, scanning only one partition!

Conclusion: It is quite important to have no functions around the partition key in the WHERE-clause here. Personally, the first SQL looks easier to me and has less coding, but it is obviously not as good as the second. Might be worth to spend some time thinking and adding some more characters to the code to make Partition-Pruning possible. Don’t believe it, test it! With some big enough tables, I mean :-)

,

12 Comments

Materialized Views & Partition Change Tracking

During the 11g Data Warehouse Administration course that I delivered this week in Munich, I have shown this demonstration about Partition Change Tracking (PCT) that I’d like to share with the Oracle Community. You may have a look here for an Introduction into Materialized Views if that topic is new for you. Often, the Base Tables of Materialized Views are partitioned, which gives use additional options for the Refresh as well as for the Rewrite:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam
Connected.
SQL> create table sales
 (product     varchar2(50),
  channel_id  number,
  cust_id     number,
  amount_sold number(10,2),
  time_id     date)
partition by list (channel_id)
(partition c0 values (0),
 partition c1 values (1),
 partition c2 values (2),
 partition c3 values (3),
 partition c4 values (4)
);
Table created.
SQL> alter table sales nologging;

Table altered.
SQL> insert /*+ append */ into sales
select
'Oracle Enterprise Edition' as product,
mod(rownum,5) as channel_id,
mod(rownum,1000) as cust_id ,
5000 as amount_sold,
to_date
('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2010' ,'dd.mm.yyyy')
as time_id
from dual connect by level<=1e6; 
1000000 rows created.

SQL> commit;

Commit complete.

The above was my demo setup, done with 11.2.0.1 Most of the shown should work with lower versions also, though. We will now see a Materialized View that contains the Partition Key of the Base Table, which makes it easily possible for the system to associate the rows of the Materialized View with the Partitions. Otherwise, we would need to explain that with a Partition Marker.

SQL> create materialized view mv_pct
enable query rewrite as
select channel_id,sum(amount_sold) from sales group by channel_id;  

Materialized view created.

The MV is now fresh and may get used for Query Rewrite:

SQL> select mview_name,last_refresh_type,staleness from user_mviews;

MVIEW_NAME                     LAST_REF STALENESS
------------------------------ -------- -------------------
MV_PCT                         COMPLETE FRESH

SQL> set autotrace on explain
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
         0       1000000000
         1       1000000000
         2       1000000000
         3       1000000000
         4       1000000000

Execution Plan
----------------------------------------------------------
Plan hash value: 470332451

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     5 |   130 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_PCT |     5 |   130 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Now we modify one row:

SQL> set autotrace off
SQL> update sales set amount_sold=1 where rownum<2;

1 row updated.

SQL> commit;

Commit complete.:

This makes the MV stale right? Well actually not all of it:

SQL> select mview_name,last_refresh_type,staleness from user_mviews;

MVIEW_NAME                     LAST_REF STALENESS
------------------------------ -------- -------------------
MV_PCT                         COMPLETE NEEDS_COMPILE

SQL> select detail_partition_name, freshness from user_mview_detail_partition;

DETAIL_PARTITION_NAME          FRESH
------------------------------ -----
C4                             FRESH
C3                             FRESH
C2                             FRESH
C1                             FRESH
C0                             STALE

The second Dictionary View is new in 11g, not the behavior of the next query. Only Partition C0 is stale, which is why queries that do not need channel_id 0 can still use the MV:

SQL> set autotrace on explain
SQL> select sum(amount_sold) from sales where channel_id=1;

SUM(AMOUNT_SOLD)
----------------
      1000000000


Execution Plan
----------------------------------------------------------
Plan hash value: 1805549181

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |    26 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |        |     1 |    26 |            |          |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL| MV_PCT |     1 |    26 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - filter("MV_PCT"."CHANNEL_ID"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)

The Rewrite is still possible although part of the MV is stale. We call that PCT Rewrite. Furthermore, we can now do a PCT Fast Refresh, although there is no MV Log on the Base Table sales. The PCT Refresh does not need to scan the whole Base Table but only the stale partitions, which is in this case about 5 times faster than a Complete Refresh, because we have 5 equally sized partitions. The value ‘P‘ is new in 11g, but it also works with ‘F’ or ‘?’ in earlier versions.

SQL> set autotrace off
SQL> exec dbms_mview.refresh('MV_PCT','P')

PL/SQL procedure successfully completed.

SQL> select mview_name,last_refresh_type,staleness from user_mviews;

MVIEW_NAME                     LAST_REF STALENESS
------------------------------ -------- -------------------
MV_PCT                         FAST_PCT FRESH

Conclusion: Partitioning delivers some extra benefits when used together with Materialized Views. Especially, Rewrite & Refresh can be done on the Partition layer then. As always: “Don’t believe it, test it!” :-)

, , ,

12 Comments

Follow

Get every new post delivered to your Inbox.

Join 2,429 other followers

%d bloggers like this: