Partition table in Oracle is a good feature of Oracle. It can divide large tables into several small tables, so as to improve the efficiency of SQL execution for the large table, and each partition is transparent to the application.
Each partition in the partition table has its own storage characteristics, including table space, PCT_FREE, etc. What is the relationship between the partition table spaces in the partition table? Which table space will the new partition be created in? Which table space will the corresponding local partition index use? Here is an example to illustrate the above problems.
Create test partition tables:
zx@TEST>create table t (id number,name varchar2(10)) 2 tablespace users 3 partition by range(id) 4 ( 5 partition p1 values less than (10) tablespace example, 6 partition p2 values less than (20) tablespace system, 7 partition p3 values less than (30) 8 );
A range partition table T is created above, which specifies table space as users, partition p1 table space as example, partition p2 table space as system, and partition p3 table space as not specified.
Next, look at the corresponding table spaces from the user_tables, user_tab_partitions views, respectively
zx@TEST>col tablespace_name for a30 zx@TEST>col partition_name for a30 zx@TEST>select tablespace_name,partitioned from user_tables where table_name='T'; TABLESPACE_NAME PARTITION ------------------------------ --------- YES zx@TEST>select partition_name,tablespace_name from user_tab_partitions where table_name='T'; PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ P1 EXAMPLE P2 SYSTEM P3 USERS
From the query above, we can see that partition table T does not record table space name information in user_tables view, partition P1 and P2 correspond to the partition specified in the table building statement, partition P3 corresponds to the partition specified in table T is USERS. Doesn't table T have table space attributes? We use dbms_metadata.get_ddl to view the statement of table T:
As can be seen from the figure above, table T also has table space attributes, that is, the USERS table space specified when the table is built. Partition P3 inherits this property. So why does partition P3 inherit this property? Let's query the following view:
zx@TEST>col table_name for a30 zx@TEST>select table_name,def_tablespace_name from user_part_tables; TABLE_NAME DEF_TABLESPACE_NAME ------------------------------ --------------------------------- T USERS
The official document interprets column def_tablespace_name as Default tablespace to be used when adding a partition. As you can see from the above query, the partition of table T will use USERS table space if no table space is specified explicitly. Is that the case? Now add a table space to Table T:
zx@TEST>alter table t add partition p4 values less than (40); Table altered. zx@TEST>select partition_name,tablespace_name from user_tab_partitions where table_name='T'; PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ P1 EXAMPLE P2 SYSTEM P3 USERS P4 USERS
As you can see above, the table space corresponding to the newly added partition P4 is USERS, which confirms the previous view.
If the current table space is no longer expandable, would it be possible to create new partitions into other table spaces without specifying table space information? The answer is yes.
zx@TEST>alter table t modify default attributes tablespace example; Table altered. zx@TEST>select table_name,def_tablespace_name from user_part_tables; TABLE_NAME DEF_TABLESPACE_NAME ------------------------------ ------------------------------------------------------------------------------------------ T EXAMPLE zx@TEST>alter table t add partition p5 values less than (50); Table altered. zx@TEST>select partition_name,tablespace_name from user_tab_partitions where table_name='T'; PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ P1 EXAMPLE P2 SYSTEM P3 USERS P4 USERS P5 EXAMPLE
As you can see above, after modifying table T's table space properties, the new partition P5 is created in EXAMPLE table space.
Next, look at the table space corresponding to the local partition index. First create a partition index on the table.
zx@TEST>create index idx_t on t(id) local; Index created.
Let's look at the attributes of the table space corresponding to the local partition index:
zx@TEST>select tablespace_name,partitioned from user_indexes where index_name='IDX_T'; TABLESPACE_NAME PARTITION ------------------------------ --------- YES zx@TEST>select partition_name,partition_position,tablespace_name from user_ind_partitions where index_name='IDX_T'; PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ P1 1 EXAMPLE P2 2 SYSTEM P3 3 USERS P4 4 USERS P5 5 EXAMPLE
From the above query, we can see that there is no table space information on the local partition index, and the table space name corresponding to each index partition is consistent with the table space where the corresponding partition is located. We also use the dbms_metadata package to view the indexed table statements:
As you can see from the above figure, index IDX_T does not have table space attributes. Let's check user_part_index again to see if it's true:
zx@TEST>col index_name for a30 zx@TEST>col def_tablespace_name for a30 zx@TEST>select index_name,def_tablespace_name from user_part_indexes where index_name='IDX_T'; INDEX_NAME DEF_TABLESPACE_NAME ------------------------------ ------------------------------ IDX_T
From the above query, you can see that index IDX_T has no default table space storage option, but in the official document you can see: New partitions or subpartitions added to the local index will be created in the same tablespace(s) as the corresponding partitions or subpartitions of the underlying table. Explain that the local partition index defaults to be in the same table space as the associated table partition, and the above query can also validate this conclusion. Can the table space where the local partition index is located be separated from the table space where the table partition is located? The answer is yes. When creating a local index, enter the specified table space parameters:
zx@TEST>drop index idx_t; Index dropped. zx@TEST>create index idx_t on t(id) local tablespace sysaux; Index created. zx@TEST>select tablespace_name,partitioned from user_indexes where index_name='IDX_T'; TABLESPACE_NAME PARTITION ------------------------------ --------- YES zx@TEST>select partition_name,partition_position,tablespace_name from user_ind_partitions where index_name='IDX_T'; PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ P1 1 SYSAUX P2 2 SYSAUX P3 3 SYSAUX P4 4 SYSAUX P5 5 SYSAUX zx@TEST>select index_name,def_tablespace_name from user_part_indexes where index_name='IDX_T'; INDEX_NAME DEF_TABLESPACE_NAME ------------------------------ ------------------------------ IDX_T SYSAUX
From the above query, you can see that all partitioned index tablespaces are SYSAUX.
Create a new partition to see if the corresponding partition index is still in the SYSAUX table space:
zx@TEST>alter table t add partition p6 values less than (60); Table altered. zx@TEST>select partition_name,partition_position,tablespace_name from user_ind_partitions where index_name='IDX_T'; PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ P1 1 SYSAUX P2 2 SYSAUX P3 3 SYSAUX P4 4 SYSAUX P5 5 SYSAUX P6 6 SYSAUX
As can be seen from the above, the table space where the new partition index is located is still SYSAUX.
Let's look at how to modify the corresponding table space created by the new partition index:
zx@TEST>alter index idx_t modify default attributes tablespace users; Index altered. zx@TEST>select index_name,def_tablespace_name from user_part_indexes where index_name='IDX_T'; INDEX_NAME DEF_TABLESPACE_NAME ------------------------------ ------------------------------ IDX_T USERS zx@TEST>alter table t add partition p7 values less than (70); Table altered. zx@TEST>select partition_name,partition_position,tablespace_name from user_ind_partitions where index_name='IDX_T'; PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ P1 1 SYSAUX P2 2 SYSAUX P3 3 SYSAUX P4 4 SYSAUX P5 5 SYSAUX P6 6 SYSAUX P7 7 USERS
As can be seen from the above results, the table space of the partition index corresponding to the new partition becomes the newly specified USERS. The modification was successful.