oracle reference partition引用分割槽(一)
SQL> create table t_hash_partition_parent(a int primary key,b int)
2 partition by hash(a)
3 partitions 2
4
SQL> /
2 partition by hash(a)
3 partitions 2
4
SQL> /
Table created.
---報錯原因:a int後應有,號
SQL> create table t_reference_partition(id int primary key,a int constraint fk_a
foreign key(a), references t_hash_partition_parent(a))
2 partition by reference(fk_a)
3 /
create table t_reference_partition(id int primary key,a int constraint fk_a fore
ign key(a), references t_hash_partition_parent(a))
*
ERROR at line 1:
ORA-02253: constraint specification not allowed here
SQL> create table t_reference_partition(id int primary key,a int constraint fk_a
foreign key(a), references t_hash_partition_parent(a))
2 partition by reference(fk_a)
3 /
create table t_reference_partition(id int primary key,a int constraint fk_a fore
ign key(a), references t_hash_partition_parent(a))
*
ERROR at line 1:
ORA-02253: constraint specification not allowed here
---掃錯原因:外來鍵列必須是not null
SQL> ed
Wrote file afiedt.buf
SQL> ed
Wrote file afiedt.buf
1 create table t_reference_partition(id int primary key,a int constraint fk_a
foreign key(a), references t_hash_partition_parent(a))
2* partition by reference(fk_a)
SQL> create table t_reference_partition(id int primary key,a int,constraint fk_a
foreign key(a) references t_hash_partition_parent(a))
2 partition by reference(fk_a)
3 /
partition by reference(fk_a)
*
ERROR at line 2:
ORA-14652: reference partitioning foreign key is not supported
foreign key(a), references t_hash_partition_parent(a))
2* partition by reference(fk_a)
SQL> create table t_reference_partition(id int primary key,a int,constraint fk_a
foreign key(a) references t_hash_partition_parent(a))
2 partition by reference(fk_a)
3 /
partition by reference(fk_a)
*
ERROR at line 2:
ORA-14652: reference partitioning foreign key is not supported
SQL> ed
SQL> create table t_reference_partition(id int primary key,a int not null,constr
aint fk_a foreign key(a) references t_hash_partition_parent(a))
2 partition by reference(fk_a)
3 /
Table created.
-----分表的分割槽數
SQL> select partition_name from user_tab_partitions where table_name='T_HASH_PAR
TITION_PARENT';
SQL> select partition_name from user_tab_partitions where table_name='T_HASH_PAR
TITION_PARENT';
PARTITION_NAME
------------------------------------------------------------
SYS_P43
SYS_P44
------------------------------------------------------------
SYS_P43
SYS_P44
---引用分割槽的分割槽表,結論:引用分割槽的分割槽表取決於父分割槽表的分割槽個數
SQL> select partition_name from user_tab_partitions where table_name='T_REFERENC
E_PARTITION';
PARTITION_NAME
------------------------------------------------------------
SYS_P45
SYS_P46
------------------------------------------------------------
SYS_P45
SYS_P46
-----引用分割槽之constraint使用說明:
---引用分割槽必須要引用外來鍵約束,且主分割槽表的父鍵必須構建pk或unique constraint,同時要滿足enable validate not deferenable
You must specify a referential integrity constraint defined on the table being created, which must refer to a primary key or unique constraint on the parent table.
The constraint must be in ENABLE VALIDATE NOT DEFERRABLE state, which is the default when you specify a referential integrity constraint during table creation.
---所有引用的外來鍵必須定義為not null
All foreign key columns referenced in constraint must be NOT NULL.
---如指定約束,不能再指定on delete set null;即在操作父表時,不能同時設定匹配子表的記錄為空
When you specify the constraint, you cannot specify the ON DELETE SET NULL clause of the references_clause.
---所引用的父表必須是分割槽表;且父表分表方法不能是interval mode
The parent table referenced in the constraint must be an existing partitioned table. It can be partitioned by any method except interval partitioning.
---引用分割槽和父分割槽表所引用的外來鍵及父鍵絕不能包含基於pl sql function or lob列的虛擬列
The foreign and parent keys cannot contain any virtual columns that reference PL/SQL functions or LOB columns.
You must specify a referential integrity constraint defined on the table being created, which must refer to a primary key or unique constraint on the parent table.
The constraint must be in ENABLE VALIDATE NOT DEFERRABLE state, which is the default when you specify a referential integrity constraint during table creation.
---所有引用的外來鍵必須定義為not null
All foreign key columns referenced in constraint must be NOT NULL.
---如指定約束,不能再指定on delete set null;即在操作父表時,不能同時設定匹配子表的記錄為空
When you specify the constraint, you cannot specify the ON DELETE SET NULL clause of the references_clause.
---所引用的父表必須是分割槽表;且父表分表方法不能是interval mode
The parent table referenced in the constraint must be an existing partitioned table. It can be partitioned by any method except interval partitioning.
---引用分割槽和父分割槽表所引用的外來鍵及父鍵絕不能包含基於pl sql function or lob列的虛擬列
The foreign and parent keys cannot contain any virtual columns that reference PL/SQL functions or LOB columns.
---引用分割槽的操作限制:
-----引用分割槽約束取決於父分割槽表的分割槽策略,即父分割槽是什麼分割槽型別,它就隸屬於什麼分割槽型別
Restrictions for reference partitioning are derived from the partitioning strategy of the parent table.
----iot,外部表,域索引儲存表不能使用引用分割槽
You cannot specify this clause for an index-organized table, an external table, or a domain index storage table.
----父分割槽表也可以是引用分割槽,但約束不能是自包含
The parent table can be partitioned by reference, but constraint cannot be self-referential. The table being created cannot be partitioned based on a reference to itself.
----如父分割槽表啟用了行移動,引用分割槽也須啟行移動
If ROW MOVEMENT is enabled for the parent table, it must also be enabled for the child table.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29067253/viewspace-2145788/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle Partition 分割槽詳細總結Oracle
- 分割槽Partition
- Oracle查詢Interval partition分割槽表內資料Oracle
- 分割槽partition知識點
- Spark學習——分割槽Partition數Spark
- 分割槽函式Partition By的基本用法函式
- Kafka分割槽分配策略(Partition Assignment Strategy)Kafka
- oracle分割槽表和分割槽表exchangeOracle
- 分割槽函式partition by的基本用法【轉載】函式
- oracle分割槽表和非分割槽表exchangeOracle
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- Oracle分割槽表基礎運維-07增加分割槽(2 HASH分割槽)Oracle運維
- 使用parted建立大分割槽時 mkpart Warning: The resulting partition is not properly
- Oracle分割槽表基礎運維-07增加分割槽(1範圍分割槽)Oracle運維
- ORACLE分割槽表梳理系列Oracle
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- Oracle分割槽表基礎運維-09刪除分割槽Oracle運維
- Oracle分割槽表基礎運維-05組合分割槽Oracle運維
- Oracle分割槽表基礎運維-02範圍分割槽Oracle運維
- Oracle分割槽表基礎運維-03HASH分割槽Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- Oracle12c:建立主分割槽、子分割槽,實現自動分割槽插入效果Oracle
- MySQL全面瓦解29:使用Partition功能實現水平分割槽MySql
- Oracle分割槽表基礎運維-04列表分割槽Oracle運維
- 從10046看Oracle分割槽裁剪Oracle
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- Oracle分割槽表基礎運維-01分割槽表分類Oracle運維
- oracle分割槽交換(exchange)技術Oracle
- 對oracle分割槽表的理解整理Oracle
- oracle將表配置為分割槽表Oracle
- oracle 普通表-分割槽表改造流程Oracle
- Oracle SQL調優之分割槽表OracleSQL
- c++之引用及記憶體分割槽模型C++記憶體模型
- Oracle分割槽表基礎運維-07增加分割槽(4 RANGE_HASH)Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(5RANGE_LIST)Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(6RANGE_RANGE)Oracle運維
- Oracle 12.2之後ALTER TABLE .. MODIFY轉換非分割槽表為分割槽表Oracle
- Oracle drop分割槽表單個分割槽無法透過閃回恢復Oracle
- oracle 線上重新定義,普通表改變分割槽表,分割槽表可以更改型別、分割槽欄位等Oracle型別