Oracle中分区表中表空间属性

前端之家收集整理的这篇文章主要介绍了Oracle中分区表中表空间属性前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

Oracle中的分区表是Oracle中的一个很好的特性,可以把大表划分成多个小表,从而提高对于该大表的sql执行效率,而各个分区对应用又是透明的。

分区表中的每个分区有独立的存储特性,包括表空间、PCT_FREE等。那分区表中的各分区表空间之间有什么关系?新建的分区会创建在哪个表空间中呢?对应的local分区索引又会使用哪个表空间呢?下面使用一个例子来解释上面的这些问题。

创建测试分区表:

zx@TEST>createtablet(idnumber,namevarchar2(10))
2tablespaceusers
3partitionbyrange(id)
4(
5partitionp1valueslessthan(10)tablespaceexample,6partitionp2valueslessthan(20)tablespacesystem,7partitionp3valueslessthan(30)
8);

上面创建了一个range分区表T,对表T指定了表空间为users,分区p1表空间为example,分区p2表空间为system,分区p3表空间没有指定。

下面分别从user_tables、user_tab_partitions视图中查看对应的表空间

zx@TEST>coltablespace_namefora30
zx@TEST>colpartition_namefora30
zx@TEST>selecttablespace_name,partitionedfromuser_tableswheretable_name='T';

TABLESPACE_NAME	PARTITION
---------------------------------------
			YES

zx@TEST>selectpartition_name,tablespace_namefromuser_tab_partitionswheretable_name='T';

PARTITION_NAME		TABLESPACE_NAME
------------------------------------------------------------
P1			EXAMPLE
P2			SYSTEM
P3			USERS

从上面的查询可以看出,分区表T在user_tables视图中没有记录表空间名的信息,分区P1和P2对应的分区与建表语句中指定的一致,分区P3对应的分区与表T指定的表空间一致为USERS。难道表T就没有表空间属性么?我们使用dbms_Metadata.get_ddl查看表T的语句:

wKioL1km3uaiZzdhAAC7zPV8vnE762.png

从上图中可以看出表T其实也是有表空间属性的,就是在建表时指定的USERS表空间。而分区P3继承了这一属性。那为什么说是分区P3继承了这一属性呢,我们查询下面的视图:

zx@TEST>coltable_namefora30
zx@TEST>selecttable_name,def_tablespace_namefromuser_part_tables;

TABLE_NAME		DEF_TABLESPACE_NAME
---------------------------------------------------------------
T			USERS

官方文档对列def_tablespace_name的解释是Default tablespace to be used when adding a partition。从上面的查询可以知道,表T的分区如果没有明确指定表空间时都会使用USERS表空间。事实是这样么,下面给表T添加一个表空间:

zx@TEST>altertabletaddpartitionp4valueslessthan(40);

Tablealtered.

zx@TEST>selectpartition_name,tablespace_namefromuser_tab_partitionswheretable_name='T';

PARTITION_NAME		TABLESPACE_NAME
------------------------------------------------------------
P1			EXAMPLE
P2			SYSTEM
P3			USERS
P4			USERS

从上面可以看到,新添加的分区P4对应的表空间是USERS,证实了前面的观点。

如果当前的表空间已经无法扩展,想把新加的分区创建到其他表空间中,而在加表空间时不指定表空间信息,可以实现么?答案是肯定可以。

zx@TEST>altertabletmodifydefaultattributestablespaceexample;

Tablealtered.

zx@TEST>selecttable_name,def_tablespace_namefromuser_part_tables;

TABLE_NAME		DEF_TABLESPACE_NAME
------------------------------------------------------------------------------------------------------------------------
T			EXAMPLE

zx@TEST>altertabletaddpartitionp5valueslessthan(50);

Tablealtered.

zx@TEST>selectpartition_name,tablespace_namefromuser_tab_partitionswheretable_name='T';

PARTITION_NAME		TABLESPACE_NAME
------------------------------------------------------------
P1			EXAMPLE
P2			SYSTEM
P3			USERS
P4			USERS
P5			EXAMPLE

从上面可以看到在修改了表T的表空间属性后,新加的分区P5创建在EXAMPLE表空间中。

下面再来看local分区索引对应的表空间。先在表上创建一个分区索引。

zx@TEST>createindexidx_tont(id)local;

Indexcreated.

下面看看local分区索引对应的表空间的属性

zx@TEST>selecttablespace_name,partitionedfromuser_indexeswhereindex_name='IDX_T';

TABLESPACE_NAME	PARTITION
---------------------------------------
			YES

zx@TEST>selectpartition_name,partition_position,tablespace_namefromuser_ind_partitionswhereindex_name='IDX_T';

PARTITION_NAME		PARTITION_POSITIONTABLESPACE_NAME
------------------------------------------------------------------------------
P1						1EXAMPLE
P2						2SYSTEM
P3						3USERS
P4						4USERS
P5						5EXAMPLE

从上面的查询可以看出,local分区索引上没有表空间信息,而每个索引分区对应的表空间名与相应的分区所在的表空间一致。我们同样使用dbms_Metadata包来查看索引的建表语句:

wKiom1km5cmT08hBAADhEurAxtg660.png

从上图可以看到索引IDX_T确实没有表空间属性。我们再来查看user_part_index来验证一下是否是真的呢:

zx@TEST>colindex_namefora30
zx@TEST>coldef_tablespace_namefora30
zx@TEST>selectindex_name,def_tablespace_namefromuser_part_indexeswhereindex_name='IDX_T';

INDEX_NAME		DEF_TABLESPACE_NAME
------------------------------------------------------------
IDX_T

从上面的查询中可以看到索引IDX_T也没有默认的表空间存储选项,而在官方文档中看到: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。说明local分区索引默认与相关联的表分区在同一个表空间,上面的查询也可以验证这一结论。那可以把local分区索引所在的表空间与表分区所在的表空间分开来么?答案是肯定可以的。在创建本地索引进指定表空间参数即可:

zx@TEST>dropindexidx_t;

Indexdropped.

zx@TEST>createindexidx_tont(id)localtablespacesysaux;

Indexcreated.

zx@TEST>selecttablespace_name,partitionedfromuser_indexeswhereindex_name='IDX_T';

TABLESPACE_NAME	PARTITION
---------------------------------------
			YES
			
zx@TEST>selectpartition_name,tablespace_namefromuser_ind_partitionswhereindex_name='IDX_T';

PARTITION_NAME		PARTITION_POSITIONTABLESPACE_NAME
------------------------------------------------------------------------------
P1						1SYSAUX
P2						2SYSAUX
P3						3SYSAUX
P4						4SYSAUX
P5						5SYSAUX

zx@TEST>selectindex_name,def_tablespace_namefromuser_part_indexeswhereindex_name='IDX_T';

INDEX_NAME		DEF_TABLESPACE_NAME
------------------------------------------------------------
IDX_T			SYSAUX

从上面的查询中可以看到所有的分区索引的表空间都为SYSAUX。

创建一个新的分区,看对应的分区索引是否还是在SYSAUX表空间:

zx@TEST>altertabletaddpartitionp6valueslessthan(60);

Tablealtered.

zx@TEST>selectpartition_name,tablespace_namefromuser_ind_partitionswhereindex_name='IDX_T';

PARTITION_NAME		PARTITION_POSITIONTABLESPACE_NAME
------------------------------------------------------------------------------
P1						1SYSAUX
P2						2SYSAUX
P3						3SYSAUX
P4						4SYSAUX
P5						5SYSAUX
P6						6SYSAUX

从上面可以看出新的分区索引所在的表空间仍是SYSAUX。

下面来看如何修改新分区索引创建的对应的表空间:

zx@TEST>alterindexidx_tmodifydefaultattributestablespaceusers;

Indexaltered.

zx@TEST>selectindex_name,def_tablespace_namefromuser_part_indexeswhereindex_name='IDX_T';

INDEX_NAME		DEF_TABLESPACE_NAME
------------------------------------------------------------
IDX_T			USERS

zx@TEST>altertabletaddpartitionp7valueslessthan(70);

Tablealtered.

zx@TEST>selectpartition_name,tablespace_namefromuser_ind_partitionswhereindex_name='IDX_T';

PARTITION_NAME		PARTITION_POSITIONTABLESPACE_NAME
------------------------------------------------------------------------------
P1						1SYSAUX
P2						2SYSAUX
P3						3SYSAUX
P4						4SYSAUX
P5						5SYSAUX
P6						6SYSAUX
P7						7USERS

从上面的结果可以看出,新加分区对应的分区索引的表空间变为了新指定的USERS。修改成功。

原文链接:https://www.f2er.com/oracle/209080.html

猜你在找的Oracle相关文章