Range of the interval partition store in Parameter

Range of the interval partition store in Parameter

Oracle 11g introduced interval partitions. In the past, partitions were manually or semi-automated scripts to implement partition expansion. However, the emergence of such interval partitions completely frees the expansion of partitions, here we will not discuss what is an interval partition. We will mainly talk about the store in parameter when creating an interval partition. The official introduction to this parameter is as follows:

The optional store in clause lets you specify one or more tablespaces into which the database stores interval partition data using a round-robin algorithm for subsequently created interval partitions.

The store in parameter can specify one or more tablespaces used by the separated partitions. It uses the cyclic algorithm to create the separated partitions.

Next, there are three methods to specify the tablespace of the interval partition. Let's look at their differences.

Method 1: set store in without pre-defined partition table space.

Create table interval_sales1
(Prod_id NUMBER (6)
, Cust_id NUMBER, time_id DATE, channel_id CHAR (1)
, Promo_id NUMBER (6)
, Quantity_sold NUMBER (3)
, Amount_sold NUMBER (10, 2)
)
Partition by range (time_id) INTERVAL (NUMTOYMINTERVAL (1, 'Year') store in (SALES_TBS1, SALES_TBS2, SALES_TBS3, SALES_TBS4)
(PARTITION p0 values less than (TO_DATE ('1-1-2003 ', 'dd-MM-YYYY ')),
PARTITION p1 values less than (TO_DATE ('1-1-2004 ', 'dd-MM-YYYY ')),
PARTITION p2 values less than (TO_DATE ('1-1-2005 ', 'dd-MM-YYYY ')),
PARTITION p3 values less than (TO_DATE ('1-1-2006 ', 'dd-MM-YYYY'); insert into interval_sales1 values (908001,101, to_date ('2017-8-10 ', 'yyyy-mm-dd'), 'A', 88001,100,200); insert into interval_sales1 values (908002,102, to_date ('2017-7-10', 'yyyy-mm-dd '), 'A', 88002,100,800); insert into interval_sales1 values (908003,103, to_date ('2017-5-30 ', 'yyyy-mm-dd'), 'A', 2004 ); insert into interval_sales1 values (908004,104, to_date ('1970-12-10', 'yyyy-mm-dd'), 'A', 2005); insert into interval_sales1 values (88004,100,600, to_date ('1970-11-14 ', 'yyyy-mm-dd'), 'A', 2007); commit;

Method 2: store in is not set,

Set the pre-defined partition table space.

Create table interval_sales2
(Prod_id NUMBER (6)
, Cust_id NUMBER, time_id DATE, channel_id CHAR (1)
, Promo_id NUMBER (6)
, Quantity_sold NUMBER (3)
, Amount_sold NUMBER (10, 2)
)
Partition by range (time_id) INTERVAL (NUMTOYMINTERVAL (1, 'Year '))
(PARTITION p0 values less than (TO_DATE ('1-1-2003 ', 'dd-MM-YYYY') tablespace SALES_TBS1,
PARTITION p1 values less than (TO_DATE ('1-1-2004 ', 'dd-MM-YYYY') tablespace SALES_TBS2,
PARTITION p2 values less than (TO_DATE ('1-1-2005 ', 'dd-MM-YYYY') tablespace SALES_TBS3,
PARTITION p3 values less than (TO_DATE ('1-1-2006 ', 'dd-MM-YYYY') tablespace SALES_TBS4); insert into interval_sales2 values (908001,101, to_date ('2014-8-10 ', 'yyyy-mm-dd'), 'A', 88001,100,200); insert into interval_sales2 values (908002,102, to_date ('2017-7-10', 'yyyy-mm-dd '), 'A', 88002,100,800); insert into interval_sales2 values (908003,103, to_date ('2017-5-30 ', 'yyyy-mm-dd'), 'A', 2004 ); insert into interval_sales2 values (908004,104, to_date ('1970-12-10', 'yyyy-mm-dd'), 'A', 2005); insert into interval_sales2 values (88004,100,600, to_date ('1970-11-14 ', 'yyyy-mm-dd'), 'A', 2007); commit;

Method 3: set store in and pre-defined partition table space.

Create table interval_sales3
(Prod_id NUMBER (6)
, Cust_id NUMBER, time_id DATE, channel_id CHAR (1)
, Promo_id NUMBER (6)
, Quantity_sold NUMBER (3)
, Amount_sold NUMBER (10, 2)
)
Partition by range (time_id) INTERVAL (NUMTOYMINTERVAL (1, 'Year') store in (SALES_TBS1, SALES_TBS2, SALES_TBS3, SALES_TBS4)
(PARTITION p0 values less than (TO_DATE ('1-1-2003 ', 'dd-MM-YYYY') tablespace SALES_TBS1,
PARTITION p1 values less than (TO_DATE ('1-1-2004 ', 'dd-MM-YYYY') tablespace SALES_TBS2,
PARTITION p2 values less than (TO_DATE ('1-1-2005 ', 'dd-MM-YYYY') tablespace SALES_TBS3,
PARTITION p3 values less than (TO_DATE ('1-1-2006 ', 'dd-MM-YYYY') tablespace SALES_TBS4); insert into interval_sales3 values (908001,101, to_date ('2014-8-10 ', 'yyyy-mm-dd'), 'A', 88001,100,200); insert into interval_sales3 values (908002,102, to_date ('2017-7-10', 'yyyy-mm-dd '), 'A', 88002,100,800); insert into interval_sales3 values (908003,103, to_date ('2017-5-30 ', 'yyyy-mm-dd'), 'A', 2004 ); insert into interval_sales3 values (908004,104, to_date ('1970-12-10', 'yyyy-mm-dd'), 'A', 2005); insert into interval_sales3 values (88004,100,600, to_date ('1970-11-14 ', 'yyyy-mm-dd'), 'A', 2007); commit;

We can see that the three methods are exhaustive for the store in and pre-defined partitions. Let's see how different methods differ IN the use of the separated Partition Table space.

Select table_name, partition_name, tablespace_name, high_value
From user_tab_partitions where table_name like 'interval % ';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
--------------------------------------------------------------------------------------------------
INTERVAL_SALES1 P0 USERS TO_DATE ('00:00:00 '...)
INTERVAL_SALES1 P1 USERS TO_DATE ('00:00:00 '...)
INTERVAL_SALES1 P2 USERS TO_DATE ('00:00:00 '...)
INTERVAL_SALES1 P3 USERS TO_DATE ('00:00:00 '...)
INTERVAL_SALES1 SYS_P64 SALES_TBS2 TO_DATE ('00:00:00 '...)
INTERVAL_SALES2 P0 SALES_TBS1 TO_DATE ('00:00:00 '...)
INTERVAL_SALES2 P1 SALES_TBS2 TO_DATE ('00:00:00 '...)
INTERVAL_SALES2 P2 SALES_TBS3 TO_DATE ('00:00:00 '...)
INTERVAL_SALES2 P3 SALES_TBS4 TO_DATE ('00:00:00 '...)
INTERVAL_SALES2 sys_65users TO_DATE ('00:00:00 '...)
INTERVAL_SALES3 P0 SALES_TBS1 TO_DATE ('00:00:00 '...)
INTERVAL_SALES3 P1 SALES_TBS2 TO_DATE ('00:00:00 '...)
INTERVAL_SALES3 P2 SALES_TBS3 TO_DATE ('00:00:00 '...)
INTERVAL_SALES3 P3 SALES_TBS4 TO_DATE ('00:00:00 '...)
INTERVAL_SALES3 SYS_P66 SALES_TBS2 TO_DATE ('00:00:00 '...) 15 rows selected.

As you can see,
1. Set store in. If the pre-defined partition table space is not set, the pre-defined partition uses the default tablespace USERS, and the extended partition cycle uses the partitions defined in store in.
2. If store in is not set and the pre-defined partition table space is set, the pre-defined partition uses the defined tablespace, and the extended partition uses the default tablespace USERS.
3. Set store in and pre-defined partition table space. All pre-defined partitions and extended partitions use the partitions defined in store in.
4. The scope of the store in parameter is the extended partition. The tablespace must be clearly written IN the pre-defined partition; otherwise, the default tablespace is used.

Summary:
From the common sense, the tablespace used by each partition should be clearly defined. Therefore, the tablespace parameter must be specified for the pre-defined partition and the tablespace must be defined for the extended partition using store in, if you ignore any partition, it will cause several partitions to be stored in the user's default tablespace. This will cause some confusion in partition management and maintenance. Therefore, we can see from the tablespace distribution of the separated partitions that we need to understand the principles and differences between different usages for any feature. Of course, experiment is the best touchstone.