OR_PARTITION= Data Set Option

Allows reading, updating, and deleting from a particular partition in a partitioned table, also inserting and bulk loading into a particular partition in a partitioned table.

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Default:	none
Restriction:	The partition name must be valid or an error occurs.
Data source:	Oracle
Tip:	This option is appropriate when reading, updating, and deleting from a partitioned table, also when inserting into a partitioned table or bulk loading to a table. You can use it to boost performance.

Syntax

Details

Examples

Example 1: Read, Update, Delete, Load, and Insert from a Partitioned Table

Example 2: Create and Manipulate a Partitioned Table

Syntax

OR_PARTITION=name of a partition in a partitioned Oracle table

Syntax Description

name of a partition in a partitioned Oracle table: specifies the partition name.

Details

Use this option in cases where you are working with only one particular partition at a time in a partitioned table. Specifying this option boosts performance because you are limiting your access to only one partition of a table instead of the entire table.

Examples

Example 1: Read, Update, Delete, Load, and Insert from a Partitioned Table

This example shows one way that you can use this option.

libname x oracle user=scott pw=tiger path=oraclev9;
proc datasets library=x;
  delete orparttest;run;
data x.ORparttest ( dbtype=(NUM='int')
  DBCREATE_TABLE_OPTS='partition by range (NUM)
              (partition p1 values less than (11),
               partition p2 values less than (21),
               partition p3 values less than (31),
               partition p4 values less than (41),
               partition p5 values less than (51),
               partition p6 values less than (61),
               partition p7 values less than (71),
               partition p8 values less than (81)
              )' );
 do i=1 to 80;
    NUM=i;
 output;
 end;
 run;
options sastrace=",,t,d" sastraceloc=saslog nostsuffix;
/* input */
proc print data=x.orparttest ( or_partition=p4 );
run;
/* update */
proc sql;
/* update should fail with 14402, 00000, "updating partition key column would
cause a partition change"
// *Cause:  An UPDATE statement attempted to change the value of a partition
//          key column causing migration of the row to another partition
// *Action: Do not attempt to update a partition key column or make sure that
//          the new partition key is within the range containing the old
//          partition key.
*/
update x.orparttest ( or_partition=p4 ) set num=100;
update x.orparttest ( or_partition=p4 ) set num=35;
select * from x.orparttest ( or_partition=p4 );
select * from x.orparttest ( or_partition=p8 );
/* delete */
delete from x.orparttest ( or_partition=p4 );
select * from x.orparttest;
quit;
/* load to an existing table */
data new; do i=31 to 39; num=i; output;end;
run;
data new2; do i=1 to 9; num=i; output;end;
run;
proc append base= x.orparttest ( or_partition=p4 )   data= new;
run;
/* insert should fail 14401, 00000, "inserted partition key is outside
specified partition"
// *Cause:  the concatenated partition key of an inserted record is outside
//          the ranges of the two concatenated partition bound lists that
//          delimit the partition named in the INSERT statement
// *Action: do not insert the key or insert it in another partition
*/
proc append base= x.orparttest ( or_partition=p4 )   data= new2;
run;
/* load to an existing table */
proc append base= x.orparttest ( or_partition=p4 bulkload=yes
bl_load_method=truncate )   data= new;
run;
/* insert should fail 14401   */
proc append base= x.orparttest ( or_partition=p4 bulkload=yes
bl_load_method=truncate )   data= new2;
run;

Example 2: Create and Manipulate a Partitioned Table

Here are a series of sample scenarios that illustrate how you can use this option. The first shows how to create the ORPARTTEST table, on which all remaining examples depend.

libname x oracle user=scott pw=tiger path=oraclev9;
proc datasets library=x;
  delete orparttest;run;
data x.ORparttest ( dbtype=(NUM='int')
  DBCREATE_TABLE_OPTS='partition by range (NUM)
              (partition p1 values less than (11),
               partition p2 values less than (21),
               partition p3 values less than (31),
               partition p4 values less than (41),
               partition p5 values less than (51),
               partition p6 values less than (61),
               partition p7 values less than (71),
               partition p8 values less than (81)
              )' );
 do i=1 to 80;
    NUM=i; output;
 end;
 run;

In this example, only the P4 partition is read.

proc print data=x.orparttest ( or_partition-p4 );
run;

Next, rows that belong to only the single P4 partition are updated.

proc sql;
update x.orparttest ( or_partition=p4 ) set num=35;
quit;

Although this shows how a particular partition can be updated, updates and even inserts to the partition key column are done in such a way that it must be migrated to a different partition in the table. Therefore, this next example fails because the value 100 does not belong to the P4 partition.

proc sql;
update x.orparttest ( or_partition=p4 ) set num=100;
quit;

In this example, all rows in the P4 partition are deleted.

proc sql;
delete from x.orparttest ( or_partition=p4 );
quit;

Next, rows are added to the P4 partition in the table.

data new;
   do i=31 to 39; num=i; output;end;
run;
proc append base= x.orparttest ( or_partition=p4 );
   data= new;
run;

This example also adds rows to the P4 partition but uses the SQL*Loader instead.

proc append base= x.orparttest ( or_partition=p4 bulkload=yes );
   data= new;
run;