Impact of LEFT or RIGHT in SQL Server Partition Function's Definition

SQL Server ( since 2005 ) has introduced partitioning to effectively manage very large databases ( VLDBs) . 
Partitioning helps the DBA in 

a) maintaining the statistics at the partition level . 
b) easily load/unload  the data ( via sliding the partition ) 
c) optimal utilizing the storage for the datasets 
   (i.e., Current Year's Data can be housed in the faster storage ; archived data can be stored in cheaper / slower storage ) . 


Here are the steps to implement partitioning 

a) Define the partition function ( PF). 
   This step defines how the data should be partitioned . 
   The option of "RIGHT" or "LEFT" dictates how the upper boundary is treated by SQL Server. 
   
b) Define the partition schema ( PS) that uses the partition function created above
   This steps associates the physical storage ( aka filegroup) that corresponds to the partitions defined in the PF

c) Create a table that utilizes the PS  created above. 
  
In this blog post , I will show the differences between LEFT and RIGHT option definied in the PF. 
Rest of the partitioning 


Let us dive into a simple example . 

C:\>sqlcmd -W
1> use my2014db
2> go
Changed database context to 'my2014db'.

Step a) 
We will create a PF with LEFT range for three values ( 10 , 20 , 30) . 


1> Create Partition Function pf_seqno_l ( int) as range left for values ( 10 , 20 , 30 ) ;
2> go

This will create 
i) parition for any values less than or equal to 10 
ii) parition for any values greater than 10 and less than or equal to 20 
iii) parition for any values greater than 20 and less than or equal to 30 
iv) parition for any values greater than 30 

Step b) 
We will create a PS that will associate filegroup to the partitions . 
Here in this example , I have assigned ALL the paritions to go into PRIMARY filegroup; typically in production environments , it will be associated with multiple filegroups. 

1> Create Partition Scheme ps_seqno_l as partition pf_seqno_l ALL to ([PRIMARY]) ;
2> go
Partition scheme 'ps_seqno_l' has been created successfully. 'PRIMARY' is marked as the next used filegroup in partition scheme 'ps_seqno_l'.

Step c) 

We will create a table that will use the PS. 

1> Create table t_seqno_l
2> (   cid int identity ,
3>     seqno int )
4>      on ps_seqno_l(seqno) ;
5> go

Let us check , if the object does all the partitions . As we can see, it has 4 partitions . 

1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_l');
4> go

partition_number rows
---------------- ----
1 0
2 0
3 0
4 0

Let us insert a record and look at the partitions to confirm the number of records in the partitions . As the first record's value is 2 ( that <= 10 ) , that record is placed in the first partition. 


1> insert into t_seqno_l(seqno) values ( 2) ;
2> go

(1 rows affected)


1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_l');
4> go

partition_number rows
---------------- ----
1 1
2 0
3 0
4 0

(4 rows affected)

Second record is also placed in the first partition as the values is <= 10. 

1> insert into t_seqno_l(seqno) values ( 10) ;
2> go

(1 rows affected)
1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_l');
4> go
partition_number rows
---------------- ----
1 2
2 0
3 0
4 0

(4 rows affected)


Let us insert two more records. 

1> insert into t_seqno_l(seqno) values ( 20) ;
2> insert into t_seqno_l(seqno) values ( 50) ;
3> go

(1 rows affected)

(1 rows affected)

1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_l');
4> go
partition_number rows
---------------- ----
1 2
2 1
3 0
4 1

1> select * from t_seqno_l;
2> go
cid seqno
--- -----
3 2
4 10
5 20
6 50

(4 rows affected)

As you can see from the above , 
- Partition 1 has two records ( seqno with the value of 2 and 10) 
- Partition 2 has one record ( seqno with the value of 20) 
- Partition 3 has no record 
- Partition 4 has one record ( seqno with the value of 50 ; which is >= 30 ) 


Now , let us try PF with right option . 
Let us repeat the steps for creating PF , PS and the table. 

This will create 
i) partition for any values less than  10 
ii) partition for any values greater than or equal to  10 and less than  20 
iii) partition for any values greater than or equal to  20 and less than  30 
iv) partition for any values greater than or equal to  30 


1> Create Partition Function pf_seqno_r ( int) as range right for values ( 10 , 20 , 30 ) ;
2> go

1> Create Partition Scheme ps_seqno_r as partition pf_seqno_r ALL to ([PRIMARY]) ;
2> go
Partition scheme 'ps_seqno_r' has been created successfully. 'PRIMARY' is marked as the next used filegroup in partition scheme 'ps_seqno_r'.

1> Create table t_seqno_r
2> (   cid int identity ,
3>     seqno int )
4>      on ps_seqno_r(seqno) ;
5> go

1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_r');
4> go
partition_number rows
---------------- ----
1 0
2 0
3 0
4 0

(4 rows affected)

Let us insert a record. Let us insert a record and look at the partitions to confirm the number of records in the partitions . As the first record's value is 2 ( that <= 10 ) , that record is placed in the first partition. 

1> insert into t_seqno_r(seqno) values ( 2) ;
2> go

(1 rows affected)
1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_r');
4> go
partition_number rows
---------------- ----
1 1
2 0
3 0
4 0

(4 rows affected)

Let us second record . As you can see now , this record is placed in the second partition. Here the partition layout differ.  

1> insert into t_seqno_r(seqno) values ( 10) ;
2> go

(1 rows affected)
1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_r');
4> go
partition_number rows
---------------- ----
1 1
2 1
3 0
4 0

(4 rows affected)

1> insert into t_seqno_r(seqno) values ( 20) ;
2> insert into t_seqno_r(seqno) values ( 50) ;
3> go

(1 rows affected)

(1 rows affected)
1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_r');
4> go
partition_number rows
---------------- ----
1 1
2 1
3 1
4 1


In short , LEFT or RIGHT dictates how the upper boundary  is placed with in the partitioned table. 

In the next few posts , we will discuss other features of partitioning. 

Thanks for reading. 

Partitioning made easy

One of the nice features in Oracle 12c is the easier mainteannace of partitioning operations. 

In pre-12c oracle databases , to add , split , merge the partitions ,  the commands has to be done per partition .  Starting with Oracle 12c , the operations can be done in one single statement.

Let us see an example.

We will create a table range based partitioned table called t_part with two partitions.

SQL> Create table t_part

 2  ( productcode  int ,  productname varchar2(150) , company varchar2(150))

 3  partition by range (productcode)

 4  (

 5  partition product_1000 values less than (1000) ,

 6  partition product_2000 values less than (2000)

 7  )

 8  ;

Table created.

To add partitions in prior to 12c , we would need to issue three statements as below.

SQL> alter table t_part

 2  add partition product_3000 values less than ( 3000) ;

Table altered.

SQL> alter table t_part

 2  add partition product_4000 values less than ( 4000) ;

Table altered.

SQL> alter table t_part

 2  add partition product_5000 values less than ( 5000) ;

Table altered.

For the similar operations in oracle 12c , we can do it in one statement as below. 

In the following example , I am adding five partitions in one statement.

SQL> alter table t_part

 2  add

 3  partition product_6000 values less than ( 6000) ,

 4  partition product_7000 values less than ( 7000) ,

 5  partition product_8000 values less than ( 8000) ,

 6  partition product_9000 values less than ( 9000),

 7  partition product_10000 values less than ( 10000),

 8  partition product_15000 values less than ( 15000)

 9  ;

Table altered.

SQL> insert into t_part

 2  select object_id as productcode

 3  , object_name as productname

 4  , owner as company from all_objects

 5  where object_id <15000 span="">

 6  ;

14602 rows created.

SQL> exec dbms_stats.gather_table_stats ( user , 'T_PART') ;

PL/SQL procedure successfully completed.

SQL> select  table_name , partition_name , num_rows ,

 2  last_analyzed

 3  from user_tab_partitions where table_name ='T_PART'

 4  order by 2 ;

TABLE_NAME PARTITION_NAME         NUM_ROWS LAST_ANAL

---------- -------------------- ---------- ---------

T_PART     PRODUCT_1000                889 20-DEC-14

T_PART     PRODUCT_10000               986 20-DEC-14

T_PART     PRODUCT_15000              4978 20-DEC-14

T_PART     PRODUCT_2000                924 20-DEC-14

T_PART     PRODUCT_3000               1000 20-DEC-14

T_PART     PRODUCT_4000               1000 20-DEC-14

T_PART     PRODUCT_5000               1000 20-DEC-14

T_PART     PRODUCT_6000                985 20-DEC-14

T_PART     PRODUCT_7000                944 20-DEC-14

T_PART     PRODUCT_8000                928 20-DEC-14

T_PART     PRODUCT_9000                968 20-DEC-14

11 rows selected.

Similarly , I can split the partition in one statement as well as shown below.

SQL> alter table t_part

 2  split

 3  partition product_15000

 4  into

 5  (

 6  partition product_11000 values less than ( 11000) ,

 7  partition product_12000 values less than ( 12000) ,

 8  partition product_13000 values less than ( 13000) ,

 9  partition product_14000 values less than ( 14000) ,

10  partition product_15000

11  ) ;

Table altered.

SQL> select  table_name , partition_name , num_rows ,

 2  last_analyzed

 3  from user_tab_partitions where table_name ='T_PART'

 4  order by 2 ;

TABLE_NAME PARTITION_NAME         NUM_ROWS LAST_ANAL

---------- -------------------- ---------- ---------

T_PART     PRODUCT_1000                889 20-DEC-14

T_PART     PRODUCT_10000               986 20-DEC-14

T_PART     PRODUCT_11000

T_PART     PRODUCT_12000

T_PART     PRODUCT_13000

T_PART     PRODUCT_14000

T_PART     PRODUCT_15000

T_PART     PRODUCT_2000                924 20-DEC-14

T_PART     PRODUCT_3000               1000 20-DEC-14

T_PART     PRODUCT_4000               1000 20-DEC-14

T_PART     PRODUCT_5000               1000 20-DEC-14

TABLE_NAME PARTITION_NAME         NUM_ROWS LAST_ANAL

---------- -------------------- ---------- ---------

T_PART     PRODUCT_6000                985 20-DEC-14

T_PART     PRODUCT_7000                944 20-DEC-14

T_PART     PRODUCT_8000                928 20-DEC-14

T_PART     PRODUCT_9000                968 20-DEC-14

15 rows selected.

This enhancement is one of the best in Oracle 12c.

Welcome your comments.

ORA-14257 - One of the misleading error message.

Sometimes , you wonder why Oracle would give misleading error message .  
This is reference to ORA-14257.


SQL Error: ORA-14257: cannot move partition other than a Range, List, System, or Hash partition
14257. 00000 -  "cannot move partition other than a Range or Hash partition"
*Cause:    User attempt to move a partition that is not a Range or Hash
           partition which is illegal
*Action:   Specify MOVE PARTITION for a Range or Hash partition only

As part of database maintenance route , we may need to change the storage of some segments . In this case , we are moving the partition(s) to a different tablespace.   When you a simple partitioned table , we need specify the move command at the partition level ; but if  is a composite partitioned table , the move command should be at the sub-partition level.  If you wrongfully specified the partition level move for a composite partitioned table , then Oracle throws ORA-14257 ( a misleading text).

Here is an example.

SQL> DROP TABLE storesales purge ;

Table dropped.

SQL> CREATE TABLE storesales
  2    (
  3      storekey  INT NOT NULL ,
  4      datekey   INT NOT NULL ,
  5      regionkey INT NOT NULL ,
  6      saleqty   INT NOT NULL ,
  7      CONSTRAINT PK_storesales_sp PRIMARY KEY (datekey , regionkey , storekey )
  8    )
  9    PARTITION BY LIST ( datekey )
 10    (
 11           PARTITION P_2012 VALUES ( 12 )
 12      ,    PARTITION P_2013 VALUES ( 13 )
 13    ) ;

Table created.

SQL>   SELECT table_name ,
  2    composite ,
  3    partition_name ,
  4    tablespace_name
  5  FROM user_tab_partitions
  6  WHERE table_name ='STORESALES' ;

TABLE_NAME                     COM PARTITION_NAME                 TABLESPACE_NAME
------------------------------ --- ------------------------------ ------------------------------
STORESALES                     NO  P_2012                         USERS
STORESALES                     NO  P_2013                         USERS

SQL> alter table storesales move partition p_2012 tablespace tbscsb;

Table altered.

SQL> alter table storesales move partition p_2013 tablespace tbscsb;

Table altered.

SQL> SELECT table_name ,
  2    composite ,
  3    partition_name ,
  4    tablespace_name
  5  FROM user_tab_partitions
  6  WHERE table_name ='STORESALES' ;

TABLE_NAME                     COM PARTITION_NAME                 TABLESPACE_NAME
------------------------------ --- ------------------------------ ------------------------------
STORESALES                     NO  P_2012                         TBSCSB
STORESALES                     NO  P_2013                         TBSCSB

SQL>

As we can see , this table is of simple partition  (composite = NO ) the table partitions have been moved to "TBSCSB" ( different tablespace) with no issues.

Now , let us create a composite partitioned table and perform similar operations. 

SQL>  drop table storesales_cp purge ;

Table dropped.

SQL>  Create table storesales_cp
  2   (    storekey int not null
  3      , datekey int not null
  4      , regionkey int not null
  5      , saleqty int not null
  6       , CONSTRAINT PK_storesales_cp PRIMARY KEY (datekey , regionkey , storekey )
  7      )
  8    PARTITION BY LIST (datekey)
  9    SUBPARTITION BY LIST (regionkey)
 10   (
 11   PARTITION P_2012 VALUES ( 12 )
 12   ( SUBPARTITION P2012_0  VALUES (0)     ,
 13     SUBPARTITION P2012_1  VALUES (1)     ,
 14     SUBPARTITION P2012_7  VALUES (7)     ,
 15     SUBPARTITION P2012_9  VALUES (9)
 16    )
 17   ,
 18   PARTITION P_2013 VALUES ( 13 )
 19    ( SUBPARTITION P2013_0  VALUES (0)     ,
 20     SUBPARTITION P2013_1  VALUES (1)     ,
 21     SUBPARTITION P2013_7  VALUES (7)     ,
 22     SUBPARTITION P2013_9  VALUES (9)
 23    )
 24    ) ;

Table created.

SQL>   SELECT table_name ,
  2    composite ,
  3    partition_name ,
  4    tablespace_name
  5  FROM user_tab_partitions
  6  WHERE table_name ='STORESALES_CP' ;

TABLE_NAME                     COM PARTITION_NAME                 TABLESPACE_NAME
------------------------------ --- ------------------------------ ------------------------------
STORESALES_CP                  YES P_2012                         USERS
STORESALES_CP                  YES P_2013                         USERS

SQL> alter table storesales_cp move partition p_2012 tablespace tbscsb;
alter table storesales_cp move partition p_2012 tablespace tbscsb
                                         *
ERROR at line 1:
ORA-14257: cannot move partition other than a Range, List, System, or Hash partition


Oracle should have provided me an error message that this is a composite partition but not the above.

Let us perform the move operations at the sub-partition level. 

SQL> SELECT table_name ,
  2    partition_name ,
  3    subpartition_name ,
  4    tablespace_name
  5  FROM user_tab_subpartitions
  6  WHERE table_name ='STORESALES_CP' ;

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
STORESALES_CP                  P_2012                         P2012_0                        USERS
STORESALES_CP                  P_2012                         P2012_1                        USERS
STORESALES_CP                  P_2012                         P2012_7                        USERS
STORESALES_CP                  P_2012                         P2012_9                        USERS
STORESALES_CP                  P_2013                         P2013_0                        USERS
STORESALES_CP                  P_2013                         P2013_1                        USERS
STORESALES_CP                  P_2013                         P2013_7                        USERS
STORESALES_CP                  P_2013                         P2013_9                        USERS

8 rows selected.

 
 SQL> alter table storesales_cp move subpartition p2012_0 tablespace tbscsb;

Table altered.

SQL> alter table storesales_cp  move subpartition p2012_1 tablespace tbscsb;

Table altered.

SQL> alter table storesales_cp  move subpartition p2012_7 tablespace tbscsb;

Table altered.

SQL> alter table storesales_cp  move subpartition p2012_9 tablespace tbscsb;

Table altered.

SQL> alter table storesales_cp move subpartition p2013_0 tablespace tbscsb;

Table altered.

SQL> alter table storesales_cp  move subpartition p2013_1 tablespace tbscsb;

Table altered.

SQL> alter table storesales_cp  move subpartition p2013_7 tablespace tbscsb;

Table altered.

SQL> alter table storesales_cp  move subpartition p2013_9 tablespace tbscsb;

Table altered.

SQL>


SQL> SELECT table_name ,
  2    partition_name ,
  3    subpartition_name ,
  4    tablespace_name
  5  FROM user_tab_subpartitions
  6  WHERE table_name ='STORESALES_CP' ;

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
STORESALES_CP                  P_2012                         P2012_0                        TBSCSB
STORESALES_CP                  P_2012                         P2012_1                        TBSCSB
STORESALES_CP                  P_2012                         P2012_7                        TBSCSB
STORESALES_CP                  P_2012                         P2012_9                        TBSCSB
STORESALES_CP                  P_2013                         P2013_0                        TBSCSB
STORESALES_CP                  P_2013                         P2013_1                        TBSCSB
STORESALES_CP                  P_2013                         P2013_7                        TBSCSB
STORESALES_CP                  P_2013                         P2013_9                        TBSCSB

8 rows selected.

SQL>


Now , the move operations are successful.  I believe, in the upcoming release of Oracle ( Oracle 12c) , some of the partition management are made easier . Hopefully , Oracle changes the text of this message as well. 

Comments welcome.