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) |
|---|---|
| Category: | Data Set Control |
| 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. |
Table of Contents
specifies the partition name.
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 example shows one way that you can use this option.
libname x oracle user=myusr1 pw=mypwd1 path=mypath;
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;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=myusr1 pw=mypwd1 path=mypath;
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 code shows how a particular partition can be updated, updates and inserts to the partition key column can be done so that data must be migrated to a different partition in the table. 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;