learn-oracle

Learn Oracle



You are just 10 steps away from becoming a Oracle DBA. Materialize your dream by following the The 10 Simple Steps .

Partitioning

Extended Composite Partitioning

With composite partitioning—a scheme introduced in Oracle8i Database—you can create subpartitions from partitions, allowing further granularity of the table. But in that release, you could subpartition range-partitioned tables only via hash subpartitioning. In Oracle9i, composite partitioning was expanded to include range-list subpartitioning.

These schemes cater to most circumstances but not all. For example, say you have a table called SALES with many columns including two special ones that are candidates for partitioning: state_code, which stores a two-digit code for the state where the sale was made, ostensibly for the purpose of calculating the sales tax; and product_code, a three-digit number identifying the product sold by that sales record. Users query on the table filtering on both columns equally, and the archival requirements are also based on both these two columns. When you apply the principles of partitioning decisions, you find that both these columns are good candidates for partitioning keys.

In Oracle Database 11g, you can solve the problem fairly easily. In this release, Oracle DBA are not limited to range-hash and range-list composite partitioning. Rather, your choices are virtually limitless; you can create composite partitions in any combination.

In this example case, you can decide to LIST partition the table on product_code, as that column has more discrete values and then subpartition on state_code, again as list. This code sample shows how to do that:


create table sales
(
   sales_id     number,
   product_code number,
   state_code   varchar2(2)
)
partition by list (product_code)
subpartition by list (state_code)
(
   partition p101 values (101)
   (
      subpartition p101_ct values ('CT'),
      subpartition p101_ny values ('NY'),
      subpartition p101_def values (default)
   ),
   partition p201 values (201)
   (
      subpartition p201_ct values ('CT'),
      subpartition p201_ny values ('NY'),
      subpartition p201_def values (default)
   )
)

The options are not limited to what is shown here. Oracle DBA can also create LIST-RANGE composite partitions. Suppose, in the example above, product code is not discrete but is more of a range. You would want to list partition on state_code and then subpartition on product_code. Here is the code sample that does it.

create table sales1
(
   sales_id     number,
   product_code number,
   state_code   varchar2(2)
)
partition by list (state_code)
subpartition by range (product_code)
(
   partition CT values ('CT')
   (
      subpartition ct_100 values less than (101),
      subpartition ct_200 values less than (201)
   ),
   partition NY values ('NY')
   (
      subpartition NY_100 values less than (101),
      subpartition NY_200 values less than (201)
   )
)




You can create range-range composite subpartitions as well, which come in very handy when you may have two date fields. Consider, for instance, a table for a sales processing system that has a transaction date and delivery date. Oracle DBA may want to range partition on one date and then also range subpartition on the other. This scheme allows you to do backups, archiving, and purges based on dates.

In summary, you can create the following types of composite partitions available in Oracle Database 11g:

  • Range-range
  • Range-hash
  • Range-list
  • List-range
  • List-hash
  • List-list

Reference Partitioning


Here is a typical problem in designing partitioning schemes: not all the tables have the same columns on which you need to partition. Suppose you are creating a sales system with two simple tables, sales and customers:

create table customers
(
   cust_id   number primary key,
   cust_name varchar2(200),
   rating    varchar2(1) not null
)
partition by list (rating)
(
   partition pA values ('A'),
   partition pB values ('B')
);

The table sales is created as shown below. This is a child table of the customers table.

create table sales
(
   sales_id    number primary key,
   cust_id     number not null,
   sales_amt   number,
   constraint  fk_sales_01
    foreign key (cust_id)
    references customers
);

Ideally, Oracle DBA would want to partition the table sales in the same manner as table customers: list partitioned on the column rating. But there is a serious problem: table sales does not have a column called rating! So how do you partition it on a non-existent column?

In Oracle Database 11g you can, using a new feature called Reference Partitioning. Here is an example to show how you can apply it to the sales table:


create table sales
(
   sales_id    number primary key,
   cust_id     number not null,
   sales_amt   number,
   constraint  fk_sales_01
    foreign key (cust_id)
    references customers
)
partition by reference (fk_sales_01);

This creates partitions identical to those in the parent table, customers. Note that there is no column called rating, yet the table has been partitioned on that column. The clause partition by reference (fk_sales_01) has the name of the foreign key in the partition definition. This instructs Oracle Database 11g to confirm the partitioning is done per the scheme used in the parent table—in this case, customers. Note the NOT NULL constraint for column cust_id.; this is required for reference partitioning.

If you check the partition boundaries for the partitions in sales table:


SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES';

PARTITION_NAME  HIGH_VALUE
--------------- -------------------------------
PA
PB

The high value is null, meaning that the boundaries here are derived from the parent table. The partitions have the same names as in the parent table. Oracle DBA can check the type of partitioning by querying the view user_part_tables. A special column called ref_ptn_constraint_name shows the foreign key constraint name.

SQL> select table_name, partitioning_type, ref_ptn_constraint_name
  2  from user_part_tables
  3  where table_name in ('CUSTOMERS','SALES');

TABLE_NAME                     PARTITION REF_PTN_CONSTRAINT_NAME
------------------------------ --------- --------------------------
CUSTOMERS                      LIST
SALES                          REFERENCE FK_SALES_01

Reference partitions come extremely handy when you want to partition a child table in the same fashion as in the parent table but do not have the same columns, and you do not want to introduce them just for the sake of partitioning. Furthermore, Oracle DBA do not need to explicitly declare a long partitioning clause for each child table.

Interval Partitioning


Range partitioning allows you to create partitions based on ranges of the values of the partition key column. Here is an example of the range partitioned table:

create table sales6
(
   sales_id    number,
   sales_dt    date
)
partition by range (sales_dt)
(
   partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd')),
   partition p0702 values less than (to_date('2007-03-01','yyyy-mm-dd'))
);

Here you have defined partitions for January 2007 and February 2007 only, so what happens if a record is inserted into the table that has the sales_dt in March 2007? The insert will fail with the following error:
ORA-14400: inserted partition key does not map to any partition
Obviously Oracle DBA need to add a partition for March 2007 before you can insert a record. But this is often easier said than done. Often you can't afford to create a lot of partitions beforehand and too few of them may result in this error.

Wouldn't it be better if Oracle somehow automatically sensed the need for new partitions and then created them? Oracle Database 11g does, with a feature called Interval Partitioning. Here, you don't define partitions and their boundaries but merely an interval that defines each partition's boundaries. Here is the same example in interval partitioning:


create table sales6
(
   sales_id    number,
   sales_dt    date
)
partition by range (sales_dt)
interval (numtoyminterval(1,'MONTH'))
(
   partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd'))
);
Note the clause: interval followed by the interval. Here Oracle DBA have instructed Oracle to create intervals of one month each. You have also created the initial partition named p0701, for the January 2007 data. Now, suppose you insert a record with June 2007 data:

SQL> insert into sales6 values (1,'01-jun-07');

1 row created.
Oracle does not return an error; rather; it successfully executes the statement. So where does the record go to? The partition p0701 can't have the record and we haven't defined a partition for June 2007. But at this time if you check the partitions of the table:

SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES6';

PARTITION_NAME  HIGH_VALUE
--------------- ----------------------------------------------------------------
P0701           TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
                ALENDAR=GREGORIA

SYS_P41         TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
                ALENDAR=GREGORIA
Note the partition named SYS_P1 with a high value of July 1, 2007, which will accommodate data up to the end of June. This partition was created dynamically by Oracle and has a system generated name.

Now suppose Oracle DBA enter a value lower than highest value, such as May 1, 2007. It should ideally have its own partition, as your partition interval is a month.


SQL> insert into sales6 values (1,'01-may-07');

1 row created.

SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES6';

PARTITION_NAME  HIGH_VALUE
--------------- ----------------------------------------------------------------
P0701           TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
                ALENDAR=GREGORIA

SYS_P41         TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
                ALENDAR=GREGORIA

SYS_P42         TO_DATE(' 2007-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
                ALENDAR=GREGORIA
Note the new partition SYS_P42, which has the upper bound as June 1—thus the partition can hold the May 2006 data. This partition was created by splitting the SYS_P41 partitions (for June). Thus, Oracle automatically creates and maintains the partitions when you define an interval partitioning scheme.

If you want to store the partitions in specific tablespaces, you can do so by using the store in clause:


interval (numtoyminterval(1,'MONTH'))
store in (TS1,TS2,TS3)
which stores the partitions in tablespaces TS1, TS2, and TS3 in a round robin manner.

How would an application developer address a specific partition? One way is to know the name, which may not be possible, and even if you know, it is highly error prone. To facilitate the access to specific partition, Oracle Database 11g offers a new syntax for partitioning SQLs:





SQL> select * from sales6 partition for (to_date('15-may-2007','dd-mon-yyyy'));

  SALES_ID SALES_DT
---------- ---------
         1 01-MAY-07
Note the new clause for (value), which allows you to directly reference partitions without explicitly calling them by their exact name. If you want to truncate or drop a partition, you can call this extended portioning syntax.

After the table is created in this manner, the PARTITIONING_TYPE column in view DBA_PART_TABLES shows INTERVAL.

System Partitioning


Although Oracle anticipates few if any practical uses for this feature, I want to describe it anyway because it's really cool.

Here is a rare but not inconceivable of use case: Imagine that Oracle DBA have a table that just can't be partitioned in any logical way. The result is a huge, monolithic table, which poses problems such as the need fo extended index maintenance and other operations.

So, the developers come forward with a solution: They promise that if the table can be partitioned somehow, they can write to the partitions in an intelligent manner. By doing so, the application can control which partition a specific record goes to. The DBA need merely define the partitions. Here is an example:


create table sales3
(
   sales_id   number,
   product_code number,
   state_code   number
)
partition by system
(
   partition p1 tablespace users,
   partition p2 tablespace users
);
Note that there is no partition key or the boundaries. So, the table is physically divided into two segments but is still a logical table. Once defined this way, the database creates two segments for the table, instead of just one monolithic table. You can check it by:

SQL> select partition_name
  2  from user_segments
  3  where segment_name = 'SALES3';

PARTITION_NAME
------------------------------
P1
P2
When you create a local index, it is also partitioned the same way.


SQL> create index in_sales3_state on sales3 (state_code) local;

Index created.

SQL> select partition_name
  2  from user_segments
  3  where segment_name = 'IN_SALES3_STATE';

PARTITION_NAME
------------------------------
P1
P2
You can check the type of partitioning by checking in user_part_tables:

SQL> select partitioning_type
  2  from user_part_tables
  3  where table_name = 'SALES3';

PARTITION
---------
SYSTEM
This shows up as SYSTEM, indicating system partitioning, of course. One point to note that the high_value column values are NULL for these types of tables.

SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES3';

PARTITION_NAME HIGH_VALUE
-------------- ---------------------
P1               
P2
Here's an interesting question: if there is no partitioning key or scheme such as range, list or hash,how does Oracle know to which partition the incoming record should go?

The answer is: Oracle doesn't. Here is an example of what happens if Oracle DBA want to insert a record into the table:


SQL> insert into sales3 values (1,101,1);
insert into sales3 values (1,101,1)
            *
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on
tables partitioned by the System method
The partitions bounds are not known so the application must provide that information by using the partition-aware syntax while inserting data. You would need to rewrite that statement as:

SQL> insert into sales3 partition (p1) values (1,101,1);

1 row created.
While deleting, you do not have to provide the partition-aware syntax—but remember, there is no concept of partition boundaries. So, when you issue a statement like this:

SQL> delete sales3 where state_code = 1;
Oracle has to scan all the partitions to see where the row resides. To avoid that, you should write it as:

SQL> delete sales3 partition (p1) where state_code = 1;
The same goes for updates. This limits the partitions where the record is searched.

System partitions offer tremendous advantages when a table can't be partitioned in any logical way. They let Oracle DBA take advantage of the benefits of partitioning while allowing free rein to the developers in deciding to which partition a record should go.

Tablespace Transport for a Single Partition


In earlier versions of Oracle Database, you gained the ability to transport a tablespace and later plug it into a different database or to the same one. The process involves the copying of datafiles so it is the fastest way to transfer data across databases. However, until now, you didn't have the ability to transport the tablespace of a single partition and then plug it back. In Oracle Database 11g, you can.

Suppose you have a table called SALES5, with several partitions named CT, NY, etc.


SQL> select partition_name, tablespace_name
  2  from user_tab_partitions
  3  where table_name = 'SALES5';


PARTITION_NAME TABLESPACE_NAME
-------------- ---------------
CT             TS1
NY             TS2
Now, Oracle DBA can transport the partition CT using the command shown below: $ expdp tables=scott.sales5:ct transportable=always directory=data_pump_dir dumpfile=p_ct.dmp Export: Release 11.1.0.4.0 - Beta on Sunday, 10 June, 2007 16:05:40 Copyright (c) 2003, 2005, Oracle. All rights reserved. Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.4.0 - Beta
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Oracle Database Vault options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA tables=scott.sales5:ct transportable=
  always directory=data_pump_dir dumpfile=p_ct.dmp 
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
****************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/oracle/admin/PROBE2/dpdump/p_ct.dmp
******************************************************************************
Datafiles required for transportable tablespace TS1:
  /home/oracle/oradata/PROBE2/PROBE2/ts1_01.dbf
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 16:05:55

Now, you can take these two files—p_ct.dmp and ts1_01.dmp—to another system and try to plug into the database. For learning purposes, let's try to plug into the same database. First, you need to drop the table and then the tablespace ts1.

SQL> drop table scott.sales5;
 
Table dropped.
 
SQL> drop tablespace ts1 including contents;
 
Tablespace dropped.
Now, plug the tablespace into the database. But here's a little problem: the table sales5 no longer exists and you had initially exported only one partition (ct), not the entire table. So how can you import just one partition of a non-existent table?

In Oracle Database 11g, a new command line option in Data Pump Import called partition_options makes that possible. If you specify the value departition, Data Pump will create a new table from the partitions exported. In a way this approach "breaks" partitions, so it's appropriately named departition. Let's see how it works.

$ impdp partition_options=departition dumpfile=p_ct.dmp
   transport_datafiles='/home/oracle/oradata/PROBE2/PROBE2
/ts1_01.dbf'
 
Import: Release 11.1.0.4.0 - Beta on Sunday, 10 June, 2007 21:58:08
 
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Username: / as sysdba
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.4.0 - Beta
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Oracle Database Vault options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_04" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_04":  /******** AS SYSDBA partition_options=
   departition dumpfile=p_ct.dmp transport_datafiles=/home/oracle/oradata/PROBE2/PROBE2/ts1_01.dbf 
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_04" successfully completed at 21:58:23
This SQL creates a table called sales5_ct, which is nothing but the ct partition of SALES5 table exported by transportable tablespace earlier. The table name, as Oracle DBA can see, is a combination of the original table and the partition names. Oracle DBA can conform the presence of the segment by checking the DBA_SEGMENTS view.

SQL> select segment_name
  2  from dba_segments
  3  where tablespace_name = 'TS1';
 
SEGMENT_NAME
-----------------
SALES5_CT
Oracle DBA can use single-partition transportable tablespace feature to plug in a single partition of a table to a different database. After plugging it in, Oracle DBA may want to perform an exchange partition operation to put that as a partition on some table there.

Partitioning on Virtual Columns


Let's see another common problem. In your table called sales, Oracle DBA have the following columns:

SQL> desc sales
 Name                                      Null?    Type
 ----------------------------------------- -------- ------
 SALES_ID                                  NOT NULL NUMBER
 CUST_ID                                   NOT NULL NUMBER
 SALES_AMT                                          NUMBER

Suppose you want to partition this table by some scheme that allows you to purge and archive is based on the amount of sale. Here are the four categories of sale:

If sale_amt is and cust_id is Then sale_category is
0-10000 anything LOW
10001-100000 0-100 LOW
10001-100000 101-200 MEDIUM
10001-100000 >200 HIGH
100001-1000000 0-100 MEDIUM
100001-1000000 101-200 HIGH
100001-1000000 >200 ULTRA
>1000000 Anything ULTRA
You want to partition this table on the column sale_category, but there is a problem: there is no column called sale_category. This is something you derived from the sale_amt column. How can you partition this table, then?

In earlier versions of Oracle Oracle DBA could have put a new column in the table called sale_category, and used a trigger to populate the column using the logic shown in the table. But the presence of the new column would have caused another performance hit due to the triggers.

In Oracle Database 11g, a new feature called Virtual Columns allows Oracle DBA to create a column that is not stored in the table but rather is computed at run time. Oracle DBA can also partition on this column. Using this feature, it's a breeze to partition this table.


create table sales
(
   sales_id      number,
   cust_id       number,
   sales_amt     number,
   sale_category varchar2(6)
   generated always as
   (
      case
         when sales_amt <= 10000
            then 'LOW'
         when sales_amt > 10000
            and sales_amt <= 100000
            then case
               when cust_id < 101 then 'LOW'
               when cust_id between 101 and 200 then 'MEDIUM'
               else 'MEDIUM'
            end
         when sales_amt > 100000
            and sales_amt <= 1000000
            then case
               when cust_id < 101 then 'MEDIUM'
               when cust_id between 101 and 200 then 'HIGH'
               else 'ULTRA'
            end
         else 'ULTRA'
      end
    ) virtual
)
partition by list (sale_category)
(
   partition p_low values ('LOW'),
   partition p_medium values ('MEDIUM'),
   partition p_high values ('HIGH'),
   partition p_ultra values ('ULTRA')
)
Now if Oracle DBA insert rows:

SQL> insert into sales (sales_id,cust_id,sales_amt) values (1,1,100);

1 row created.

SQL> insert into sales (sales_id,cust_id,sales_amt) values (2,1,1500);

1 row created.

SQL> insert into sales (sales_id,cust_id,sales_amt) values (3,102,1500);

1 row created.

SQL> insert into sales (sales_id,cust_id,sales_amt) values (4,102,10000);

1 row created.

SQL> commit;

Commit complete.
Note that Oracle DBA have not entered the value for sale_category. Now if you check for the records in the partition p_low, you will see the correct record:

SQL> select * from sales partition (p_low);

  SALES_ID    CUST_ID  SALES_AMT SALE_C
---------- ---------- ---------- ------
         1          1        100 LOW
The record was placed in the appropriate partition.

Partitioning on virtual columns allows you to create partitions that make sense for business even though the column itself is not present. Here you have used a very simple calculation for the virtual column but it can be as complex as you like. In those cases, partitioning on a virtual column becomes even more valuable.

Partition Advisor


Perhaps the biggest consideration in designing partitioning schemes is the decision to choose the partitioning scheme and the partitioning column(s). This is a task better left to seasoned professionals doing extensive workload analysis, and even then they may not get it right. You do get help in Oracle Database 11g in the form of a new advisor called Partition Advisor that analyzes the data and access patterns ad suggests partitioning schemes. You can read more about this tool in this installment.

Conclusion


Partitioning has always been one of the most useful tools but with Oracle Database 11g, it become even more useful:
  • Reference partitioning allows you to equi-partition related tables in the same database, even if the columns are not present in all the child tables.
  • Interval partitioning embodies that highly desirable fire-and-forget power—you define an interval and Oracle takes care of the maintenance, forever.
  • The extension of composite partitioning to range-range, list-range, list-hash, and list-list exposes new possibilities for better partitioning choices and manageability.
  • Data Pump now allows Oracle DBA to transport and plug-in a single partition, a feature that is guaranteed to be quite useful in archival and retention.
  • Finally, Oracle DBA can design the best possible partitioning strategy that mimics business flow by partitioning on virtual columns.

More Tutorials on Oracle dba ...



Liked it ? Want to share it ? Social Bookmarking
Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information


Source : Oracle Documentation | Oracle DBA

Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info