Shulou(Shulou.com)06/01 Report--

The partition table in Oracle is a good feature of Oracle, which can divide the large table into several small tables, thus improving the efficiency of SQL execution for the large table, and each partition is transparent to the application.

Each partition in the partition table has independent storage characteristics, including tablespaces, PCT_FREE, and so on. What is the relationship between the partitioned table spaces in the partitioned table? In which tablespace will the newly created partition be created? Which tablespace will be used by the corresponding local partitioned index? An example is used to explain the above problems.

Create a test partition table:

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, specifying table space users for table T, example for partition p1, system for partition p2, and no partition for p3.

Let's view the corresponding tablespaces from the user_tables and user_tab_partitions views, respectively.

Zx@TEST > col tablespace_name for a30zx@TEST > col partition_name for a30zx@TEST > select tablespace_name,partitioned from user_tables where table_name='T';TABLESPACE_NAME PARTITION---YESzx@TEST > select partition_name,tablespace_name from user_tab_partitions where table_name='T' PARTITION_NAME TABLESPACE_NAME---P1 EXAMPLEP2 SYSTEMP3 USERS

As can be seen from the above query, the partition table T does not record the information of the table space name in the user_tables view, the partition corresponding to partition P1 and P2 is the same as that specified in the table building statement, and the partition corresponding to partition P3 is USERS with the table space specified by table T. Does Table T have no tablespace attributes? We use dbms_metadata.get_ddl to look at the statement of table T:

You can see from the above figure that table T actually has tablespace attributes, that is, the USERS tablespace specified when the table is created. Partition P3 inherits this attribute. So why does partition P3 inherit this property? we query the following view:

Zx@TEST > col table_name for a30zx@TEST > select table_name,def_tablespace_name from user_part_tables;TABLE_NAME DEF_TABLESPACE_NAME---T USERS

The official documentation interprets column def_tablespace_name as Default tablespace to be used when adding a partition. As you can see from the above query, the USERS table space is used if the partition of table T is not explicitly specified. Is this the case? let's add a tablespace 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 EXAMPLEP2 SYSTEMP3 USERSP4 USERS

As you can see from the above, the table space for the newly added partition P4 is USERS, which confirms the previous point.

If the current table space cannot be expanded, can you create the newly added partition to another table space without specifying the table space information when adding the table space? 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 EXAMPLEzx@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 EXAMPLEP2 SYSTEMP3 USERSP4 USERSP5 EXAMPLE

From the above, you can see that after modifying the tablespace properties of Table T, the newly added partition P5 is created in the EXAMPLE tablespace.

Let's take a look at the tablespace corresponding to the local partitioned index. First create a partitioned index on the table.

Zx@TEST > create index idx_t on t (id) local;Index created.

Let's take a look at the properties of the table space corresponding to the local partitioned index:

Zx@TEST > select tablespace_name,partitioned from user_indexes where index_name='IDX_T';TABLESPACE_NAME PARTITION---YESzx@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 EXAMPLEP2 2 SYSTEMP3 3 USERSP4 4 USERSP5 5 EXAMPLE

As you can see from the above query, there is no table space information on the local partition index, and the table space name corresponding to each index partition is the same as the table space where the corresponding partition is located. We also use the dbms_metadata package to view the table creation statements for the index:

You can see from the figure above that the index IDX_T does not have tablespace attributes. Let's check user_part_index again to see if it's true:

Zx@TEST > col index_name for a30zx@TEST > col def_tablespace_name for a30zx@TEST > select index_name,def_tablespace_name from user_part_indexes where index_name='IDX_T';INDEX_NAME DEF_TABLESPACE_NAME-- IDX_T

You can see from the query above that the index IDX_T also has no default tablespace storage option, but you can see it in the official document: 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. Indicates that the local partitioned index defaults to the same table space as the associated table partition, and the above query can also verify this conclusion. Can you separate the table space where the local partition index is located from the table space where the table partition is located? The answer is yes. You can introduce the specified tablespace parameters in the creation of the local index:

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 SYSAUXP2 2 SYSAUXP3 3 SYSAUXP4 4 SYSAUXP5 5 SYSAUXzx@TEST > select index_name Def_tablespace_name from user_part_indexes where index_name='IDX_T' INDEX_NAME DEF_TABLESPACE_NAME---IDX_T SYSAUX

As you can see from the query above, the table space of all partitioned indexes is SYSAUX.

Create a new partition to see if the corresponding partition index is still in the SYSAUX tablespace:

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 SYSAUXP2 2 SYSAUXP3 3 SYSAUXP4 4 SYSAUXP5 5 SYSAUXP6 6 SYSAUX

You can see from the above that the table space of the new partitioned index is still SYSAUX.

Here's how to modify the corresponding tablespace created by the new partitioned 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 USERSzx@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 SYSAUXP2 2 SYSAUXP3 3 SYSAUXP4 4 SYSAUXP5 5 SYSAUXP6 6 SYSAUXP7 7 USERS

As can be seen from the above results, the table space of the partitioned index corresponding to the newly added partition has changed to the newly specified USERS. The modification was successful.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.