Data Set Options for Relational Databases

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.

Default value:	none
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Oracle

Syntax
Syntax Description
Details
Example

Syntax

OR_PARTITION=name of a partition in a partitioned Oracle table

Syntax Description

name of a partition in a partitioned Oracle table: The name of the partition must be valid or an error occurs.

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.

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.

Example

This example shows one way you can use this option.

libname x oracle user=scott pw=tiger path=oraclev9;

proc delete data=x.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;

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

libname x oracle user=scott pw=tiger path=oraclev9;
proc delete data=x.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;

Only the P4 partition is read in this next example.

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

In this example, rows that belong to only the single P4 partition are updated.

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

The above example also illustrates how a particular partition can be updated. However, 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, the following 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;

All rows in the P4 partition are deleted in this example.

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

In this next example, 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;

The next 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;

Top of Page