Using Mview over tables having FKs - Consideration

主外鍵在資料庫的應用當中比較常見，它就像一把雙刃劍，用得好的話會給你的系統帶來堅固的限制關系。用得不好或者濫用的話會給維護和排錯帶來非常大的麻煩。

最近剛好接到一起資料複制的需求，主庫和mv庫面向的應用類型差不多，mv庫上面連接配接的應用需要通路主庫的一張表，現狀是有專門的人員負責對這份資料進行寫入，比較繁瑣，是以考慮使用mv的方式來進行資料的共享。

其中要求同步的是一張表，但是這張表在備庫有被其他表的fk關聯到，經過調查，相關聯的幾個表在主庫也是存在的，并且具有同樣的限制條件。

考慮到主外鍵的限制關系如果單獨同步可能導緻違反限制，是以的話有主外鍵限制的表必須放在一個事務進行同步。

經過了解，實際上這幾個表的資料都是從主庫來的，并且可以放在一起同步。總算可以繼續做下去。

那麼在mv節點需要對這幾個關聯到的限制進行屬性調整：

1. 初始限制校驗，後續限制校驗。

2. 開啟允許延緩檢查屬性。

3. 調整為事務結束校驗。

（從非延緩到延緩需要重建限制）

聯機文檔原文:

deferrable clause the <code>deferrable</code> and <code>not</code> <code>deferrable</code> parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the <code>set</code> <code>constraint</code>(<code>s</code>) statement. if you omit this clause, then the default is <code>not</code> <code>deferrable</code>.

specify <code>not</code> <code>deferrable</code> to indicate that in subsequent transactions you cannot use the<code>set</code> <code>constraint</code>[<code>s</code>] clause to defer checking of this constraint until the transaction is committed. the checking of a <code>not</code> <code>deferrable</code> constraint can never be deferred to the end of the transaction.

if you declare a new constraint <code>not</code> <code>deferrable</code>, then it must be valid at the time the<code>create</code> <code>table</code> or <code>alter</code> <code>table</code> statement is committed or the statement will fail.

specify <code>deferrable</code> to indicate that in subsequent transactions you can use the <code>set</code><code>constraint</code>[<code>s</code>] clause to defer checking of this constraint until after the transaction is committed. this setting in effect lets you disable the constraint temporarily while making changes to the database that might violate the constraint until all the changes are complete.

you cannot alter the deferrability of a constraint. that is, whether you specify either of these parameters, or make the constraint <code>not</code> <code>deferrable</code> implicitly by specifying neither of them, you cannot specify this clause in an <code>alter</code> <code>table</code> statement. you must drop the constraint and re-create it.

initially clause the <code>initially</code> clause establishes the default checking behavior for constraints that are <code>deferrable</code>. the <code>initially</code> setting can be overridden by a <code>set</code><code>constraint</code>(<code>s</code>) statement in a subsequent transaction.

specify <code>initially</code> <code>immediate</code> to indicate that oracle should check this constraint at the end of each subsequent sql statement. if you do not specify <code>initially</code> at all, then the default is <code>initially</code> <code>immediate</code>.

if you declare a new constraint <code>initially</code> <code>immediate</code>, then it must be valid at the time the <code>create</code> <code>table</code> or <code>alter</code> <code>table</code> statement is committed or the statement will fail.

specify <code>initially</code> <code>deferred</code> to indicate that oracle should check this constraint at the end of subsequent transactions.

this clause is not valid if you have declared the constraint to be <code>not</code> <code>deferrable</code>, because a<code>not</code> <code>deferrable</code> constraint is automatically <code>initially</code> <code>immediate</code> and cannot ever be<code>initially</code> <code>deferred</code>.

enable clause specify <code>enable</code> if you want the constraint to be applied to the data in the table.

if you enable a unique or primary key constraint, and if no index exists on the key, then oracle database creates a unique index. unless you specify <code>keep index</code> when subsequently disabling the constraint, this index is dropped and the database rebuilds the index every time the constraint is reenabled.

you can also avoid rebuilding the index and eliminate redundant indexes by creating new primary key and unique constraints initially disabled. then create (or use existing) nonunique indexes to enforce the constraint. oracle does not drop a nonunique index when the constraint is disabled, so subsequent <code>enable</code> operations are facilitated.

<code>enable</code> <code>validate</code> specifies that all old and new data also complies with the constraint. an enabled validated constraint guarantees that all data is and will continue to be valid.

if any row in the table violates the integrity constraint, the constraint remains disabled and oracle returns an error. if all rows comply with the constraint, oracle enables the constraint. subsequently, if new data violates the constraint, oracle does not execute the statement and returns an error indicating the integrity constraint violation.

if you place a primary key constraint in <code>enable</code> <code>validate</code> mode, the validation process will verify that the primary key columns contain no nulls. to avoid this overhead, mark each column in the primary key <code>not</code> <code>null</code> before entering data into the column and before enabling the primary key constraint of the table.

<code>enable</code> <code>novalidate</code> ensures that all new dml operations on the constrained data comply with the constraint. this clause does not ensure that existing data in the table complies with the constraint and therefore does not require a table lock.

if you specify neither <code>validate</code> nor <code>novalidate</code>, the default is <code>validate</code>.

if you change the state of any single constraint from <code>enable</code> <code>novalidate</code> to <code>enable</code> <code>validate</code>, the operation can be performed in parallel, and does not block reads, writes, or other ddl operations.

restriction on the enable clause you cannot enable a foreign key that references a disabled unique or primary key.

mv的操作就不用說了，把關聯的mv放到一個重新整理組搞定。