Sunday 14 March 2021
Generating Function - Methods for obtaining characteristic roots for determining explicit solution
The Question is:
Answer for a)
$\begin{align*}
f(x)&= & a_0 & + & a_1x & + & a_2x^2 & + & a_3x^3 & + &\cdots \\
-xf(x)&= & & - & a_0x & - & a_1x^2 & -& a_2x^3 & - &\cdots \\
-5x^2f(x)&= & & & & - & 5a_0x^2 & - & 5a_1x^3 & - &\cdots \\
-3x^3f(x)&= & & & & & & - & 3a_0x^3 & - &\cdots \\
\hline \\
\end{align*}
\\
f(x)[1-x-5x^2-3x^3]=a_0+x(a_1-a_0)+x^2(a_2-a_1-a_0)+0+\cdots
\\
f(x)=\frac{a_0+x(a_1-a_0)+x^2(a_2-a_1-5a_0)}{1-x-5x^2-3x^3}
\\$
Answer for b). The technique used is based on this video by Mayur Gohil
$\text{ let }a_n=a^n, \text{ then }a^n=a^{n-1}+5a^{n-2}+3a^{n-3} \Rightarrow \frac{a^n}{a^{n-3}}=\frac{a^{n-1}}{a^{n-3}}+\frac{5a^{n-2}}{a^{n-3}}+\frac{3a^{n-3}}{a^{n-3}} \Rightarrow\\
\text{ characteristic polynomial is } a^3-a^2-5a-3=0 \text{ and the characteristic roots are } a=-1 \text { with multiplicity 2 and } a=3 \\ $
$\text{ when }n=0,a_0=1 \Rightarrow 1=A_1(3)^0+(A_2+A_3(0))(-1)^0 \Rightarrow 1=A_1+A_2 \\
\text{ when }n=1,a_1=1 \Rightarrow 1=A_1(3)^1+(A_2+A_3(1))(-1)^1 \Rightarrow 1=3A_1-A_2-A_3 \\
\text{ when }n=2,a_2=6 \Rightarrow 1=A_1(3)^2+(A_2+A_3(2))(-1)^2 \Rightarrow 6=9A_1+A_2+2A_3 \\
$
$
\text{ create an augmented matrix for the system to solve for } A_1,A_2,A_3 \text { using Gaussian elimination } \\
\left(\begin{array}{@{}ccc|c@{}}
1 & 1 & 0 & 1 \\
3 & -1 & -1 & 1 \\
9 & 1 & 2 & 6
\end{array}\right) \longrightarrow \left(\begin{array}{@{}ccc|c@{}}
1 & 0 & 0 & 9/16 \\
0 & 1 & 0 & 7/16 \\
0 & 0 & 1 & 1/4
\end{array}\right) \\
$
$\text{ Explicit solution for } A_1=\frac{9}{16},A_2=\frac{7}{16},A_3=\frac{1}{4} \Rightarrow a_n=\frac{9}{16}(3^n)+\bigl(\frac{7}{16}+\frac{1}{4}(n)\bigr)(-1)^n \Rightarrow \frac{9}{16}3^n+\frac{7}{16}(-1)^n +\frac{1}{4}n(-1)^n \\ \\ $
Answer given in notebook:
Tuesday 10 December 2013
User defined locking with DBMS_LOCK
User defined locks with DBMS_LOCKS often brings up unexpected results, especially when it occurs within a mix of transactions in an application layer. There are essentially three procedures for creating user defined locks,
namely: DBMS_LOCK.REQUEST, DBMS_LOCK.ALLOCATE_UNIQUE and
DBMS_LOCK.RELEASE. ALLOCATE_UNIQUE issues an implicit commit. The four anonymous blocks below highlight what is expected for the LOCK STATUS, whenever a REQUEST and RELEASE are performed, versus the actual value. All scripts were tested in an 11.2.0.3 release.
The main source of confusion arises from the use of the RELEASE_ON_COMMIT parameter coupled with the fact that an implicit COMMIT that occurs when calling DBMS_LOCK.ALLOCATE_UNIQUE. Some developers attempt to use PRAGMA AUTONOMOUS_TRANSACTION to skirt around the issue, which often masks the source of the underlying issue. Four scenarios in the form of scripts highlight the unexpected results that can occur.
Possible values for LOCK STATUS include:
0 - Success
3 - Parameter error
4 - Lock not owned
5 - Illegal lock handle
Scenario 1
Use an explicit lock ID as a handle, i.e. not generated with ALLOCATE_UNIQUE and set RELEASE_ON_COMMIT to TRUE.
SQL> set serveroutput on
SQL> declare
2 lock_id number := 34789;
3 l_Lock_Status number;
4 b_release_on_commit boolean := true;
5 begin
6 l_Lock_Status:= dbms_lock.request
7 (id =>lock_id
8 ,lockmode =>dbms_lock.x_mode
9 ,release_on_commit=>b_release_on_commit);
10
11 dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
12
13 l_Lock_Status:= dbms_lock.request
14 (id =>lock_id
15 ,lockmode =>dbms_lock.x_mode
16 ,release_on_commit=>b_release_on_commit );
17
18 dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
19
20 l_Lock_Status:= dbms_lock.release(id => lock_id);
21 dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
22
23 l_Lock_Status:= dbms_lock.release(id => lock_id );
24 dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
25
26 end;
27 /
Expect 0. Actual: 0
Expect 4. Actual: 4
Expect 0. Actual: 0
Expect 4. Actual: 4
PL/SQL procedure successfully completed.
Exactly as expected, all values match, so far so good :-)
Scenario 2
Use a lock handle, generated with ALLOCATE_UNIQUE and set RELEASE_ON_COMMIT to TRUE. In addition, ensure an explicit RELEASE occurs.SQL> set serveroutput on
SQL> declare
2 l_Lock_Status number;
3 b_Release_On_Commit boolean := true;
4 l_Lock_Handle varchar2(128 byte);
5
6 begin
7 dbms_lock.allocate_unique('Lock 1',l_Lock_Handle);
8
9 l_Lock_Status:= dbms_lock.request
10 (lockhandle =>l_Lock_Handle
11 ,lockmode =>dbms_lock.x_mode
12 ,release_on_commit=>b_release_on_commit);
13
14 dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
15
16 l_Lock_Status:= dbms_lock.request
17 (lockhandle => l_Lock_Handle
18 ,lockmode => dbms_lock.x_mode
19 ,release_on_commit=> b_release_on_commit);
20
21 dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
22 dbms_lock.allocate_unique('Lock 1', l_Lock_Handle);
23
24 l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
25 dbms_output.put_line( 'Expect 0. Actual: '||l_Lock_Status);
26
27 l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
28 dbms_output.put_line( 'Expect 4. Actual: '||l_Lock_Status);
29
30 end;
31 /
Expect 0. Actual: 0
Expect 4. Actual: 4
Expect 0. Actual: 4
Expect 4. Actual: 4
PL/SQL procedure successfully completed.
As can be seen from values on the third line, expected is 0, but actual is 4!!! The root cause of the problem is that an implicit COMMIT occurs when calling DBMS_LOCK.ALLOCATE_UNIQUE. The implicit COMMIT coupled with RELEASE_ON_COMMIT set to TRUE causes the ownership of the lock to change.
Use the following useful query to identify locks requested, but not yet expired:
select * from dba_lock dl
join dbms_lock_allocated dla on dl.lock_id1 = dla.lockid
where dla.name like 'Lock 1';
Use as follows for scenario 3:
SQL> set serveroutput on
SQL> declare
2 l_Lock_Status number;
3 b_Release_On_Commit boolean := true;
4 l_Lock_Handle varchar2(128 byte);
5
6 ty_Lock_Rec dba_lock%rowtype;
7
8 l_Lock_Name varchar2(10) := 'Lock 3';
9
10 begin
11 dbms_lock.allocate_unique(l_Lock_Name,l_Lock_Handle);
12
13 l_Lock_Status:= dbms_lock.request
14 (lockhandle =>l_Lock_Handle
15 ,lockmode =>dbms_lock.x_mode
16 ,release_on_commit=>b_Release_On_Commit);
17
18 dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
19
20 l_Lock_Status:= dbms_lock.request
21 (lockhandle => l_Lock_Handle
22 ,lockmode => dbms_lock.x_mode
23 ,release_on_commit=> b_Release_On_Commit);
24
25 begin
26 select dl.session_id
27 ,dl.lock_type
28 ,dl.mode_held
29 ,dl.mode_requested
30 ,dl.lock_id1
31 ,dl.lock_id2
32 ,dl.last_convert
33 ,dl.blocking_others
34 into
35 ty_Lock_Rec
36 from
37 dba_lock dl
38 join
39 dbms_lock_allocated dla on dl.lock_id1 = dla.lockid
40 where dla.name like l_Lock_Name;
41
42 dbms_output.put_line('Session ID :' || ty_Lock_Rec.Session_ID);
43 dbms_output.put_line('Lock type :' || ty_Lock_Rec.Lock_Type);
44 dbms_output.put_line('Mode held :' || ty_Lock_Rec.Mode_Held);
45 dbms_output.put_line('Mode requested :' || ty_Lock_Rec.Mode_Requested);
46 dbms_output.put_line('Lock ID 1 :' || ty_Lock_Rec.lock_id1);
47 dbms_output.put_line('Lock ID 2 :' || ty_Lock_Rec.lock_id2);
48 dbms_output.put_line('Last Convert :' || ty_Lock_Rec.last_convert);
49 dbms_output.put_line('Blocking Others:' || ty_Lock_Rec.blocking_others);
50
51 exception
52 when no_data_found then
53 dbms_output.put_line('No data exists in DBA_LOCK');
54 end;
55
56 dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
57 dbms_lock.allocate_unique(l_Lock_Name, l_Lock_Handle);
58
59 begin
60 select dl.session_id
61 ,dl.lock_type
62 ,dl.mode_held
63 ,dl.mode_requested
64 ,dl.lock_id1
65 ,dl.lock_id2
66 ,dl.last_convert
67 ,dl.blocking_others
68 into
69 ty_Lock_Rec
70 from
71 dba_lock dl
72 join
73 dbms_lock_allocated dla on dl.lock_id1 = dla.lockid
74 where dla.name like l_Lock_Name;
75
76 dbms_output.put_line('Session ID : ' || ty_Lock_Rec.Session_ID);
77 dbms_output.put_line('Lock type :' || ty_Lock_Rec.Lock_Type);
78 dbms_output.put_line('Mode held :' || ty_Lock_Rec.Mode_Held);
79 dbms_output.put_line('Mode requested :' || ty_Lock_Rec.Mode_Requested);
80 dbms_output.put_line('Lock ID 1 :' || ty_Lock_Rec.lock_id1);
81 dbms_output.put_line('Lock ID 2 :' || ty_Lock_Rec.lock_id2);
82 dbms_output.put_line('Last Convert :' || ty_Lock_Rec.last_convert);
83 dbms_output.put_line('Blocking Others:' || ty_Lock_Rec.blocking_others);
84
85 exception
86 when no_data_found then
87 dbms_output.put_line('No data exists in DBA_LOCK');
88 end;
89
90 l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
91
92 dbms_output.put_line( 'Expect 0. Actual: '||l_Lock_Status);
93
94
95 l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
96 dbms_output.put_line( 'Expect 4. Actual: '||l_Lock_Status);
97
98 end;
99 /
Expect 0. Actual: 0
Session ID :159
Lock type :PL/SQL User Lock
Mode held :Exclusive
Mode requested :None
Lock ID 1 :1073742997
Lock ID 2 :0
Last Convert :0
Blocking Others:Not Blocking
Expect 4. Actual: 4
No data exists in DBA_LOCK
Expect 0. Actual: 4
Expect 4. Actual: 4
PL/SQL procedure successfully completed.
Use the following useful query to identify locks requested, but not yet expired:
select * from dba_lock dl
join dbms_lock_allocated dla on dl.lock_id1 = dla.lockid
where dla.name like 'Lock 1';
Use as follows for scenario 3:
SQL> set serveroutput on
SQL> declare
2 l_Lock_Status number;
3 b_Release_On_Commit boolean := true;
4 l_Lock_Handle varchar2(128 byte);
5
6 ty_Lock_Rec dba_lock%rowtype;
7
8 l_Lock_Name varchar2(10) := 'Lock 3';
9
10 begin
11 dbms_lock.allocate_unique(l_Lock_Name,l_Lock_Handle);
12
13 l_Lock_Status:= dbms_lock.request
14 (lockhandle =>l_Lock_Handle
15 ,lockmode =>dbms_lock.x_mode
16 ,release_on_commit=>b_Release_On_Commit);
17
18 dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
19
20 l_Lock_Status:= dbms_lock.request
21 (lockhandle => l_Lock_Handle
22 ,lockmode => dbms_lock.x_mode
23 ,release_on_commit=> b_Release_On_Commit);
24
25 begin
26 select dl.session_id
27 ,dl.lock_type
28 ,dl.mode_held
29 ,dl.mode_requested
30 ,dl.lock_id1
31 ,dl.lock_id2
32 ,dl.last_convert
33 ,dl.blocking_others
34 into
35 ty_Lock_Rec
36 from
37 dba_lock dl
38 join
39 dbms_lock_allocated dla on dl.lock_id1 = dla.lockid
40 where dla.name like l_Lock_Name;
41
42 dbms_output.put_line('Session ID :' || ty_Lock_Rec.Session_ID);
43 dbms_output.put_line('Lock type :' || ty_Lock_Rec.Lock_Type);
44 dbms_output.put_line('Mode held :' || ty_Lock_Rec.Mode_Held);
45 dbms_output.put_line('Mode requested :' || ty_Lock_Rec.Mode_Requested);
46 dbms_output.put_line('Lock ID 1 :' || ty_Lock_Rec.lock_id1);
47 dbms_output.put_line('Lock ID 2 :' || ty_Lock_Rec.lock_id2);
48 dbms_output.put_line('Last Convert :' || ty_Lock_Rec.last_convert);
49 dbms_output.put_line('Blocking Others:' || ty_Lock_Rec.blocking_others);
50
51 exception
52 when no_data_found then
53 dbms_output.put_line('No data exists in DBA_LOCK');
54 end;
55
56 dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
57 dbms_lock.allocate_unique(l_Lock_Name, l_Lock_Handle);
58
59 begin
60 select dl.session_id
61 ,dl.lock_type
62 ,dl.mode_held
63 ,dl.mode_requested
64 ,dl.lock_id1
65 ,dl.lock_id2
66 ,dl.last_convert
67 ,dl.blocking_others
68 into
69 ty_Lock_Rec
70 from
71 dba_lock dl
72 join
73 dbms_lock_allocated dla on dl.lock_id1 = dla.lockid
74 where dla.name like l_Lock_Name;
75
76 dbms_output.put_line('Session ID : ' || ty_Lock_Rec.Session_ID);
77 dbms_output.put_line('Lock type :' || ty_Lock_Rec.Lock_Type);
78 dbms_output.put_line('Mode held :' || ty_Lock_Rec.Mode_Held);
79 dbms_output.put_line('Mode requested :' || ty_Lock_Rec.Mode_Requested);
80 dbms_output.put_line('Lock ID 1 :' || ty_Lock_Rec.lock_id1);
81 dbms_output.put_line('Lock ID 2 :' || ty_Lock_Rec.lock_id2);
82 dbms_output.put_line('Last Convert :' || ty_Lock_Rec.last_convert);
83 dbms_output.put_line('Blocking Others:' || ty_Lock_Rec.blocking_others);
84
85 exception
86 when no_data_found then
87 dbms_output.put_line('No data exists in DBA_LOCK');
88 end;
89
90 l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
91
92 dbms_output.put_line( 'Expect 0. Actual: '||l_Lock_Status);
93
94
95 l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
96 dbms_output.put_line( 'Expect 4. Actual: '||l_Lock_Status);
97
98 end;
99 /
Expect 0. Actual: 0
Session ID :159
Lock type :PL/SQL User Lock
Mode held :Exclusive
Mode requested :None
Lock ID 1 :1073742997
Lock ID 2 :0
Last Convert :0
Blocking Others:Not Blocking
Expect 4. Actual: 4
No data exists in DBA_LOCK
Expect 0. Actual: 4
Expect 4. Actual: 4
PL/SQL procedure successfully completed.
Scenario 3
Use a lock handle, generated with ALLOCATE_UNIQUE and set RELEASE_ON_COMMIT to FALSE. Perform an explicit RELEASE.SQL> set serveroutput on
SQL> declare
2 l_Lock_Status number;
3 b_Release_On_Commit boolean := false;
4 l_Lock_Handle varchar2(128 byte);
5
6 begin
7 dbms_lock.allocate_unique('Lock 1',l_Lock_Handle);
8
9 l_Lock_Status:= dbms_lock.request
10 (lockhandle => l_Lock_Handle
11 ,lockmode => dbms_lock.x_mode
12 ,release_on_commit=> b_release_on_commit);
13
14 dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
15
16 l_Lock_Status:= dbms_lock.request
17 (lockhandle => l_Lock_Handle
18 ,lockmode => dbms_lock.x_mode
19 ,release_on_commit=> b_release_on_commit);
20
21 dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
22 dbms_lock.allocate_unique('Lock 1', l_Lock_Handle);
23
24 l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
25 dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
26
27 l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
28 dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
29
30 end;
31 /
Expect 0. Actual: 0
Expect 4. Actual: 4
Expect 0. Actual: 0
Expect 4. Actual: 4
PL/SQL procedure successfully completed.
Setting REALEASE_ON_COMMIT to FALSE resolves the problem. The implicit COMMIT does not effect the explicit release so results will match. In some cases developers include a PRAGMA AUTONOMOUS_TRANSACTION when calling ALLOCATE_UNIQUE to hide the implicit COMMIT. Although this does seem to work, this can have even more unexpected results and should be avoided.
Scenario 4
Use a lock handle, generated with ALLOCATE_UNIQUE and set RELEASE_ON_COMMIT to TRUE. Perform an explicit RELEASE. Wrap the ALLOCATE_UNIQUE in a PRAGMA AUTONOMOUS TRANSACTION.
SQL> set serveroutput on
SQL> declare
2 l_Lock_Status number;
3 b_Release_On_Commit boolean := true;
4 l_Lock_Handle varchar2(128 byte);
5
6 function Get_Lock(p_Lock_In in varchar2) return varchar2
7 as
8 pragma autonomous_transaction;
9 v_lock_handle varchar2(128 byte);
10 begin
11 dbms_lock.allocate_unique(lockname =>p_Lock_In
12 ,lockhandle=>v_lock_handle);
13 return v_lock_handle;
14 end Get_Lock;
15
16 begin
17
18 l_Lock_Handle := Get_lock('Lock 1');
19
20 l_Lock_Status:= dbms_lock.request
21 (lockhandle => l_Lock_Handle
22 ,lockmode => dbms_lock.x_mode
23 ,release_on_commit => b_release_on_commit);
24
25 dbms_output.put_line( 'Expect 0. Actual: '||l_Lock_Status);
26
27 l_Lock_Status:= dbms_lock.request
28 (lockhandle => l_Lock_Handle
29 ,lockmode => dbms_lock.x_mode
30 ,release_on_commit => b_release_on_commit);
31
32 dbms_output.put_line('Expect 4. Actual: '|| l_Lock_Status);
33 l_Lock_Handle := Get_Lock('Lock 1');
34
35 l_Lock_Status:=dbms_lock.release(lockhandle=>l_Lock_Handle);
36 dbms_output.put_line( 'Expect 0. Actual: '||l_Lock_Status);
37
38 l_Lock_Status:=dbms_lock.release(lockhandle=>l_Lock_Handle);
39 dbms_output.put_line( 'Expect 4. Actual: '||l_Lock_Status);
40
41 end;
42 /
Expect 0. Actual: 0
Expect 4. Actual: 4
Expect 0. Actual: 0
Expect 4. Actual: 4
PL/SQL procedure successfully completed.
Thursday 5 December 2013
Exchange Partition and data validation
One of the thorny issues that keeps reoccurring
in any related partition maintenance operation is ensuring rows end up in valid
partitions based on the partition key associated with each row. The issue does
not apply to hash partitioned tables, but does rear its head for list, range and composite
partitioned tables.
A scenario will be shown that simulates a typical load and data related problem using the partition exchange technique. Identifying mismatched rows and a few data cleansing suggestions will follow. The same sample tables that occur in a previous post will be used.
A few data rows will be inserted first, to both partitioned and non-partitioned tables tables. The non-partitioned table will then be loaded into a partition using the partition exchange technique where the partition keys are mismatched, as follows:
2 (
3 Acc_ID number not null
4 ,Date_Created date not null
5 ,is_Joint_Account varchar2(1) default 'N' not null
6 ,Acc_Type_ID number
7 )
8 partition by range (date_created)
9 (
10 partition p131109 values less than(to_date('2013-11-10', 'YYYY-MM-DD')),
11 partition p131110 values less than(to_date('2013-11-11', 'YYYY-MM-DD')),
12 partition p131111 values less than(to_date('2013-11-12', 'YYYY-MM-DD')),
13 partition p131112 values less than(to_date('2013-11-13', 'YYYY-MM-DD')),
14 partition p131113 values less than(to_date('2013-11-14', 'YYYY-MM-DD')),
15 partition p131114 values less than(to_date('2013-11-15', 'YYYY-MM-DD')),
16 partition pmax values less than (maxvalue));
Table created.
SQL> create table trade_account_load as
2 (select * from trade_account where rownum < 1);
Table created.
SQL> insert into Trade_Account values
2 (1,to_date('20131112','yyyymmdd'),'Y',1);
1 row created.
SQL> insert into Trade_Account values
2 (2,to_date('20131113','yyyymmdd'),'N',1);
1 row created.
SQL> commit;
Commit complete.
Three rows will be inserted into the non-partitioned table, which will be used as the loading table. Two of the rows would map to the P131114 partition in an exchange partition. The row with a date of 20131109 however would not map to the P131114 partition.
SQL> insert into Trade_Account_load values
2 (5,to_date('20131114','yyyymmdd'),'Y',1);
1 row created.
SQL> insert into Trade_Account_load values
2 (6,to_date('20131114','yyyymmdd'),'N',1);
1 row created.
SQL> insert into Trade_Account_load values
2 (7,to_date('20131109','yyyymmdd'),'N',1); --date mismatch with partition key
1 row created.
SQL> commit;
Commit complete.
Gather stats and verify number of rows per partition in the partitioned table:
SQL> begin
2 dbms_stats.gather_table_stats(sys_context('userenv','current_schema')
3 , 'TRADE_ACCOUNT');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select partition_name,num_rows
2 from user_tab_partitions
3 where table_name = 'TRADE_ACCOUNT';
PARTITION_NAME NUM_ROWS
---------------------- ----------
P131109 0
P131110 0
P131111 0
P131112 1
P131113 1
P131114 0
PMAX 0
7 rows selected.
Ensure that the primary keys match or else an exchange partition will raise an exception.
2 add constraint trade_account_pk primary key(acc_id, date_created)
3 using index
4 (create unique index trade_account_pk
5 on trade_account(acc_id,date_created) local);
Table altered.
SQL> alter table trade_account_load
2 add constraint trade_account_load_pk primary key (acc_id,date_created);
Table altered.
Perform the exchange with validation (default behaviour):
SQL> alter table trade_account
2 exchange partition P131114 with table trade_account_load
3 including indexes;
exchange partition P131114 with table trade_account_load
*
ERROR at line 2:
ORA-14099: all rows in table do not qualify for specified partition
2 exchange partition P131114 with table trade_account_load
3 including indexes;
exchange partition P131114 with table trade_account_load
*
ERROR at line 2:
ORA-14099: all rows in table do not qualify for specified partition
An exception is raised since the row which does not match the partition key fails to load. All rows will therefore fail to load as can be seen from gathering the stats again, which shows no change from the previous stats gather above:
SQL> begin
2 dbms_stats.gather_table_stats(sys_context('userenv','current_schema')
3 , 'TRADE_ACCOUNT');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select partition_name,num_rows
2 from user_tab_partitions
3 where table_name = 'TRADE_ACCOUNT';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P131109 0
P131110 0
P131111 0
P131112 1
P131113 1
P131114 0
PMAX 0
7 rows selected.
2 dbms_stats.gather_table_stats(sys_context('userenv','current_schema')
3 , 'TRADE_ACCOUNT');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select partition_name,num_rows
2 from user_tab_partitions
3 where table_name = 'TRADE_ACCOUNT';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P131109 0
P131110 0
P131111 0
P131112 1
P131113 1
P131114 0
PMAX 0
7 rows selected.
If loading large volumes of data using an exchange partition, then not loading all rows due to a single row mismatch is generally an inefficient approach. A better approach, in cases where the majority of rows match, would be to load all and then attempt to identify rows that occur in a mismatched partition. This can be done using the WITHOUT VALIDATION clause as follows:
SQL> alter table trade_account
2 exchange partition P131114 with table trade_account_load
3 including indexes without validation;
Table altered.
2 exchange partition P131114 with table trade_account_load
3 including indexes without validation;
Table altered.
All rows were loaded into the P131114 partition as can be seen from the updated stats:
SQL> begin
2 dbms_stats.gather_table_stats(sys_context('userenv','current_schema')
3 , 'TRADE_ACCOUNT');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select partition_name,num_rows
2 from user_tab_partitions
3 where table_name = 'TRADE_ACCOUNT';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P131109 0
P131110 0
P131111 0
P131112 1
P131113 1
P131114 3
PMAX 0
7 rows selected.
2 dbms_stats.gather_table_stats(sys_context('userenv','current_schema')
3 , 'TRADE_ACCOUNT');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select partition_name,num_rows
2 from user_tab_partitions
3 where table_name = 'TRADE_ACCOUNT';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P131109 0
P131110 0
P131111 0
P131112 1
P131113 1
P131114 3
PMAX 0
7 rows selected.
Identifying the single mismatched row can be done by using the ANALYZE with the associated VALIDATE STRUCTURE statement. The statement loads all mismatched rows into a table, INVALID_ROWS (created using $ORACLE_HOME/rdbms/admin/utlvalid.sql). Ensure the table exists prior to issuing the statement:
SQL> create table invalid_rows (
2 owner_name varchar2(30),
3 table_name varchar2(30),
4 partition_name varchar2(30),
5 subpartition_name varchar2(30),
6 head_rowid rowid,
7 analyze_timestamp date);
Table created.
2 owner_name varchar2(30),
3 table_name varchar2(30),
4 partition_name varchar2(30),
5 subpartition_name varchar2(30),
6 head_rowid rowid,
7 analyze_timestamp date);
Table created.
SQL> analyze table trade_account partition (P131114) validate structure;
Table analyzed.
SQL> select head_rowid
2 from
3 invalid_rows
4 where
5 owner_name = sys_context('userenv','current_schema')
6 and table_name = 'TRADE_ACCOUNT'
7 and partition_name= 'P131114';
HEAD_ROWID
------------------
AAAd63AAIAAADKmAAC
SQL> select * from trade_account
2 where rowid =
3 (select head_rowid
4 from
5 invalid_rows
6 where
7 owner_name = sys_context('userenv','current_schema')
8 and table_name = 'TRADE_ACCOUNT'
9 and partition_name= 'P131114');
ACC_ID DATE_CREATED I ACC_TYPE_ID
---------- ------------------- - -----------
7 01/11/2013 00:00:00 N 1
Table analyzed.
SQL> select head_rowid
2 from
3 invalid_rows
4 where
5 owner_name = sys_context('userenv','current_schema')
6 and table_name = 'TRADE_ACCOUNT'
7 and partition_name= 'P131114';
HEAD_ROWID
------------------
AAAd63AAIAAADKmAAC
SQL> select * from trade_account
2 where rowid =
3 (select head_rowid
4 from
5 invalid_rows
6 where
7 owner_name = sys_context('userenv','current_schema')
8 and table_name = 'TRADE_ACCOUNT'
9 and partition_name= 'P131114');
ACC_ID DATE_CREATED I ACC_TYPE_ID
---------- ------------------- - -----------
7 01/11/2013 00:00:00 N 1
The single mismatched row is easily identifiable using the ROWID. The row can be deleted and an alternative exchange partition using a different non-partition table could be performed to load mismatched rows.
2 (select head_rowid
3 from
4 invalid_rows
5 where
6 owner_name = sys_context('userenv','current_schema')
7 and table_name = 'TRADE_ACCOUNT'
8 and partition_name= 'P131114');
1 row deleted.
SQL> commit;
Commit complete.
Gathering stats shows the number of rows for the P131114 partition has reduced by one.
SQL> begin
2 dbms_stats.gather_table_stats(sys_context('userenv','current_schema')
3 , 'TRADE_ACCOUNT');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select partition_name,num_rows
2 from user_tab_partitions
3 where table_name = 'TRADE_ACCOUNT';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P131109 0
P131110 0
P131111 0
P131112 1
P131113 1
P131114 2
PMAX 0
7 rows selected.
Wednesday 4 December 2013
Exchange partition and ensuring indexes are usable
In my previous post I highlighted a few of the issues one can come across when dealing with indexes and the Partition Exchange Technique (PET). The index was marked as unusable, even though the index columns and constraint type (primary) matched.
For example:
SQL> alter table trade_account
2 exchange partition P131113 with table trade_account_load;
Table altered.
SQL> select uix1.index_name
2 ,null as partition_name
3 ,uix1.status
4 from user_indexes uix1
5 where uix1.table_name = 'TRADE_ACCOUNT'
6 and uix1.partitioned = 'NO'
7 union all
8 select uip.index_name
9 ,uip.partition_name
10 ,uip.status
11 from user_ind_partitions uip
12 inner join user_indexes uix2 on uip.index_name = uix2.index_name
13 and uix2.table_name = 'TRADE_ACCOUNT'
14 order by 2;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK P131109 USABLE
TRADE_ACCOUNT_PK P131110 USABLE
TRADE_ACCOUNT_PK P131111 USABLE
TRADE_ACCOUNT_PK P131112 USABLE
TRADE_ACCOUNT_PK P131113 UNUSABLE
TRADE_ACCOUNT_PK P131114 USABLE
TRADE_ACCOUNT_PK PMAX USABLE
7 rows selected.
As can be seen, partition P131113 is marked as unusable. The local partion index would have to therefore be rebuilt, as follows:
SQL> alter index TRADE_ACCOUNT_PK rebuild partition P131113;
Index altered.
SQL> select uix1.index_name
2 ,null as partition_name
3 ,uix1.status
4 from user_indexes uix1
5 where uix1.table_name = 'TRADE_ACCOUNT'
6 and uix1.partitioned = 'NO'
7 union all
8 select uip.index_name
9 ,uip.partition_name
10 ,uip.status
11 from user_ind_partitions uip
12 inner join user_indexes uix2 on uip.index_name = uix2.index_name
13 and uix2.table_name = 'TRADE_ACCOUNT'
14 order by 2;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ -------
TRADE_ACCOUNT_PK P131109 USABLE
TRADE_ACCOUNT_PK P131110 USABLE
TRADE_ACCOUNT_PK P131111 USABLE
TRADE_ACCOUNT_PK P131112 USABLE
TRADE_ACCOUNT_PK P131113 USABLE
TRADE_ACCOUNT_PK P131114 USABLE
TRADE_ACCOUNT_PK PMAX USABLE
7 rows selected.
The local partion index is now shown as usable. Assuming the indexes/constraints match, one can skip the rebuild by including INCLUDE INDEXES in the exchange statement, for example:
SQL> select uix1.index_name
2 ,null as partition_name
3 ,uix1.status
4 from user_indexes uix1
5 where uix1.table_name = 'TRADE_ACCOUNT'
6 and uix1.partitioned = 'NO'
7 union all
8 select uip.index_name
9 ,uip.partition_name
10 ,uip.status
11 from user_ind_partitions uip
12 inner join user_indexes uix2 on uip.index_name = uix2.index_name
13 and uix2.table_name = 'TRADE_ACCOUNT'
14 order by 2;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK P131109 USABLE
TRADE_ACCOUNT_PK P131110 USABLE
TRADE_ACCOUNT_PK P131111 USABLE
TRADE_ACCOUNT_PK P131112 USABLE
TRADE_ACCOUNT_PK P131113 USABLE
TRADE_ACCOUNT_PK P131114 USABLE
TRADE_ACCOUNT_PK PMAX USABLE
7 rows selected.
SQL> alter table trade_account
2 exchange partition P131113 with table trade_account_load
3 including indexes;
Table altered.
SQL> select uix1.index_name
2 ,null as partition_name
3 ,uix1.status
4 from user_indexes uix1
5 where uix1.table_name = 'TRADE_ACCOUNT'
6 and uix1.partitioned = 'NO'
7 union all
8 select uip.index_name
9 ,uip.partition_name
10 ,uip.status
11 from user_ind_partitions uip
12 inner join user_indexes uix2 on uip.index_name = uix2.index_name
13 and uix2.table_name = 'TRADE_ACCOUNT'
14 order by 2;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK P131109 USABLE
TRADE_ACCOUNT_PK P131110 USABLE
TRADE_ACCOUNT_PK P131111 USABLE
TRADE_ACCOUNT_PK P131112 USABLE
TRADE_ACCOUNT_PK P131113 USABLE
TRADE_ACCOUNT_PK P131114 USABLE
TRADE_ACCOUNT_PK PMAX USABLE
7 rows selected.
The good news is that the local partitioned index remains usable. The same however does not apply to global indexes. For global indexes one can specify the UPDATE GLOBAL INDEXES, that will ensure they are usable as well, as follows:
SQL> alter table trade_account
2 exchange partition P131113 with table trade_account_load
3 including indexes
4 update global indexes;
Table altered.
Bear in mind, specifying that global indexes are updated as part of the exchange will add a significant workload for large volumed tables and might take a considerable amount of time. For global indexes there are several parallel techniques that can be employed instead, that can be initiated after the exchange. The only downside is that execution plans that refer to the global index will no longer be reproducible whilst the index remains in an unusable state.
It is however possible to force Oracle to ignore the fact that an index is unusable, which will ensure that the optimizer will continue to use the same execution plans where unusable indexes are referenced. The initilisation parameter to modify in order to enable such behaviour is SKIP_UNUSABLE_INDEXES and can be set at either the session or system level as follows;
SQL> alter system set SKIP_UNUSABLE_INDEXES = false;
System altered.
SQL> alter session set SKIP_UNUSABLE_INDEXES = false;
Session altered.
It is however possible to force Oracle to ignore the fact that an index is unusable, which will ensure that the optimizer will continue to use the same execution plans where unusable indexes are referenced. The initilisation parameter to modify in order to enable such behaviour is SKIP_UNUSABLE_INDEXES and can be set at either the session or system level as follows;
SQL> alter system set SKIP_UNUSABLE_INDEXES = false;
System altered.
SQL> alter session set SKIP_UNUSABLE_INDEXES = false;
Session altered.
It is advisable to keep to the default behaviour, which is SKIP_UNUSABLE_INDEXES set to TRUE, as the impact on execution and application behaviour would be unpredictable.
Another option is to specify PARALLEL along with the UPDATE GLOBAL INDEXES, which to a degree mitigates the expected workload, but should be used with caution as one cannot guarantee the overall Oracle workload and resource usage at the time of issuing the statement, and may ultimately cause disruption to other users. Use of the PARALLEL clause, looks as follows:
SQL> alter table trade_account
2 exchange partition P131113 with table trade_account_load
3 including indexes
4 update global indexes parallel;
Table altered.
2 exchange partition P131113 with table trade_account_load
3 including indexes
4 update global indexes parallel;
Table altered.
Friday 22 November 2013
Exchange partitions with local unique and primary keys
The following steps are a typical approach for bulk loading a large set of data into a partitioned table;
1. SQL Loader or bulk INSERT into non partitioned table with no indexes
2. Create indexes and/or primary key constraints (if need be, in parallel)
3. Exchange partitions
4. Rebuild local and/or global indexes
5. Gather optimizer statistics
Exchanging partitions with a non-partitioned tables requires a little care when dealing with the partitioned tables indexes.
Firstly, create a sample partitioned table:
SQL> create table Trade_Account
2 (
3 Acc_ID number not null
4 ,Date_Created date not null
5 ,is_Joint_Account varchar2(1) default 'N' not null
6 ,Acc_Type_ID number
7 )
8 partition by range (date_created)
9 (
10 partition p131109 values less than(to_date('2013-11-10', 'YYYY-MM-DD')),
11 partition p131110 values less than(to_date('2013-11-11', 'YYYY-MM-DD')),
12 partition p131111 values less than(to_date('2013-11-12', 'YYYY-MM-DD')),
13 partition p131112 values less than(to_date('2013-11-13', 'YYYY-MM-DD')),
14 partition p131113 values less than(to_date('2013-11-14', 'YYYY-MM-DD')),
15 partition p131114 values less than(to_date('2013-11-15', 'YYYY-MM-DD')),
16 partition pmax values less than (maxvalue)
17 );
Table created.
Next, create a sample loading table:
SQL> create table trade_account_load as (select * from trade_account);
Table created.
Create sample data in both tables:
SQL> insert into Trade_Account values
2 (1,to_date('20131112','yyyymmdd'),'Y',1);
1 row created.
SQL> insert into Trade_Account values
2 (2,to_date('20131113','yyyymmdd'),'N',1);
1 row created.
SQL> insert into Trade_Account_load values
2 (3,to_date('20131113','yyyymmdd'),'Y',1);
1 row created.
SQL> insert into Trade_Account_load values
2 (4,to_date('20131113','yyyymmdd'),'N',1);
1 row created.
SQL> commit;
Gather stats and show how many rows exist in each partition:
SQL> exec dbms_stats.gather_table_stats(sys_context('userenv','current_schema'), 'TRADE_ACCOUNT');
PL/SQL procedure successfully completed.
SQL> select partition_name,num_rows
2 from user_tab_partitions
3 where table_name = 'TRADE_ACCOUNT';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P131109 0
P131110 0
P131111 0
P131112 1
P131113 1
P131114 0
PMAX 0
7 rows selected.
Create a global index first and then attempt an exchange:
SQL> alter table trade_account
2 add constraint trade_account_pk primary key (acc_id);
Table altered.
SQL> alter table trade_account
2 exchange partition P131113 with table trade_account_load;
alter table trade_account
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION
The reason for the exception is that the unique index that is used to enforce the primary key constraint is not local. Retry with a local index:
SQL> alter table trade_account drop constraint trade_account_pk;
Table altered.
SQL> alter table trade_account
2 add constraint trade_account_pk primary key (acc_id) using index local;
alter table trade_account
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
SQL> alter table trade_account
2 add constraint trade_account_pk
3 primary key (acc_id,date_created) using index local;
Table altered.
SQL> alter table trade_account
2 exchange partition P131113 with table trade_account_load;
alter table trade_account
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION
The partitioning alignment will be shown as either PREFIXED or NON-PREFIXED, for example:
PREFIXED
SQL> alter table trade_account
2 add constraint trade_account_pk
3 primary key (date_created, acc_id) using index local;
Table altered.
SQL> select upi.table_name
2 ,upi.index_name
3 ,upi.partitioning_type
4 ,upi.locality,alignment
5 from
6 user_part_indexes upi
7 where
8 upi.table_name = 'TRADE_ACCOUNT';
TABLE_NAME INDEX_NAME PARTITION LOCALI ALIGNMENT
--------------- ----------------- --------- ------ ------------
TRADE_ACCOUNT TRADE_ACCOUNT_PK RANGE LOCAL PREFIXED
NON-PREFIXED
SQL> alter table trade_account drop constraint trade_account_pk;
Table altered.
SQL> alter table trade_account
2 add constraint trade_account_pk
3 primary key (acc_id, date_created) using index local;
Table altered.
It is worth creating unqiue/primary key constraints separately to the unique index, since one can disable and re-enable the constraint without dropping the index. When loading a large volume of rows the constraint can be disabled, but the index will be maintained. There are several ways to create them seperately;
Or, as two seperate DDL statements:
The following indicates how all the local partitioned indexes remain usable even though the constraint has been disabled.
SQL> alter table trade_account drop constraint trade_account_pk;
Table altered.
SQL> create unique index trade_account_pk
2 on trade_account(acc_id,date_created) local;
Index created.
SQL> alter table trade_account
2 add constraint trade_account_pk primary key (acc_id,date_created)
3 using index trade_account_pk;
Table altered.
SQL> alter table trade_account disable constraint trade_account_pk;
Table altered.
SQL>
SQL> select uip.index_name
2 ,uip.partition_name
3 ,uip.status
4 from user_ind_partitions uip
5 where uip.index_name = 'TRADE_ACCOUNT_PK'
6 order by 2;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK P131109 USABLE
TRADE_ACCOUNT_PK P131110 USABLE
TRADE_ACCOUNT_PK P131111 USABLE
TRADE_ACCOUNT_PK P131112 USABLE
TRADE_ACCOUNT_PK P131113 USABLE
TRADE_ACCOUNT_PK P131114 USABLE
TRADE_ACCOUNT_PK PMAX USABLE
7 rows selected.
SQL> select status from user_constraints
2 where constraint_name = 'TRADE_ACCOUNT_PK';
STATUS
--------
DISABLED
The index will now appear as NON_PREFIXED in the data dictionary.
SQL> select upi.table_name
2 ,upi.index_name
3 ,upi.partitioning_type
4 ,upi.locality,alignment
5 from
6 user_part_indexes upi
7 where
8 upi.table_name = 'TRADE_ACCOUNT';
TABLE_NAME INDEX_NAME PARTITION LOCALI ALIGNMENT
--------------- ----------------- --------- ------ ------------
TRADE_ACCOUNT TRADE_ACCOUNT_PK RANGE LOCAL NON_PREFIXED
Be aware that there are performance implications for non-prefixed local indexes. Refer to the 11gR2 docuementaion here for more detail.
Create a primary key on the non-partitioned table that matches the primary key on the partitioned table in terms of the columns used and then retry the exchange:
SQL> alter table trade_account_load add constraint trade_account_load_pk
2 primary key (acc_id,date_created);
Table altered.
SQL> alter table trade_account
2 exchange partition P131113 with table trade_account_load;
Table altered.
SQL> select uix1.index_name
2 ,null as partition_name
3 ,uix1.status
4 from user_indexes uix1
5 where uix1.table_name = 'TRADE_ACCOUNT'
6 and uix1.partitioned = 'NO'
7 union all
8 select uip.index_name
9 ,uip.partition_name
10 ,uip.status
11 from user_ind_partitions uip
12 inner join user_indexes uix2 on uip.index_name = uix2.index_name
13 and uix2.table_name = 'TRADE_ACCOUNT'
14 order by 2;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK P131109 USABLE
TRADE_ACCOUNT_PK P131110 USABLE
TRADE_ACCOUNT_PK P131111 USABLE
TRADE_ACCOUNT_PK P131112 USABLE
TRADE_ACCOUNT_PK P131113 UNUSABLE
TRADE_ACCOUNT_PK P131114 USABLE
TRADE_ACCOUNT_PK PMAX USABLE
7 rows selected.
There are no global indexes, but a single local partition index that is unusable;
SQL> alter index TRADE_ACCOUNT_PK rebuild partition P131113;
Index altered.
SQL> select uix1.index_name
2 ,null as partition_name
3 ,uix1.status
4 from user_indexes uix1
5 where uix1.table_name = 'TRADE_ACCOUNT'
6 and uix1.partitioned = 'NO'
7 union all
8 select uip.index_name
9 ,uip.partition_name
10 ,uip.status
11 from user_ind_partitions uip
12 inner join user_indexes uix2 on uip.index_name = uix2.index_name
13 and uix2.table_name = 'TRADE_ACCOUNT'
14 order by 2;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK P131109 USABLE
TRADE_ACCOUNT_PK P131110 USABLE
TRADE_ACCOUNT_PK P131111 USABLE
TRADE_ACCOUNT_PK P131112 USABLE
TRADE_ACCOUNT_PK P131113 USABLE
TRADE_ACCOUNT_PK P131114 USABLE
TRADE_ACCOUNT_PK PMAX USABLE
7 rows selected.
Finally, run the stats gathering again to verify whether the rows were loaded:
SQL> exec dbms_stats.gather_table_stats(sys_context('userenv','current_schema'), 'TRADE_ACCOUNT');
PL/SQL procedure successfully completed.
SQL> select partition_name,num_rows
2 from user_tab_partitions
3 where table_name = 'TRADE_ACCOUNT';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P131109 0
P131110 0
P131111 0
P131112 1
P131113 2
P131114 0
PMAX 0
7 rows selected.
1. SQL Loader or bulk INSERT into non partitioned table with no indexes
2. Create indexes and/or primary key constraints (if need be, in parallel)
3. Exchange partitions
4. Rebuild local and/or global indexes
5. Gather optimizer statistics
Exchanging partitions with a non-partitioned tables requires a little care when dealing with the partitioned tables indexes.
Firstly, create a sample partitioned table:
SQL> create table Trade_Account
2 (
3 Acc_ID number not null
4 ,Date_Created date not null
5 ,is_Joint_Account varchar2(1) default 'N' not null
6 ,Acc_Type_ID number
7 )
8 partition by range (date_created)
9 (
10 partition p131109 values less than(to_date('2013-11-10', 'YYYY-MM-DD')),
11 partition p131110 values less than(to_date('2013-11-11', 'YYYY-MM-DD')),
12 partition p131111 values less than(to_date('2013-11-12', 'YYYY-MM-DD')),
13 partition p131112 values less than(to_date('2013-11-13', 'YYYY-MM-DD')),
14 partition p131113 values less than(to_date('2013-11-14', 'YYYY-MM-DD')),
15 partition p131114 values less than(to_date('2013-11-15', 'YYYY-MM-DD')),
16 partition pmax values less than (maxvalue)
17 );
Table created.
Next, create a sample loading table:
SQL> create table trade_account_load as (select * from trade_account);
Table created.
Create sample data in both tables:
SQL> insert into Trade_Account values
2 (1,to_date('20131112','yyyymmdd'),'Y',1);
1 row created.
SQL> insert into Trade_Account values
2 (2,to_date('20131113','yyyymmdd'),'N',1);
1 row created.
SQL> insert into Trade_Account_load values
2 (3,to_date('20131113','yyyymmdd'),'Y',1);
1 row created.
SQL> insert into Trade_Account_load values
2 (4,to_date('20131113','yyyymmdd'),'N',1);
1 row created.
SQL> commit;
Gather stats and show how many rows exist in each partition:
SQL> exec dbms_stats.gather_table_stats(sys_context('userenv','current_schema'), 'TRADE_ACCOUNT');
PL/SQL procedure successfully completed.
SQL> select partition_name,num_rows
2 from user_tab_partitions
3 where table_name = 'TRADE_ACCOUNT';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P131109 0
P131110 0
P131111 0
P131112 1
P131113 1
P131114 0
PMAX 0
7 rows selected.
Create a global index first and then attempt an exchange:
SQL> alter table trade_account
2 add constraint trade_account_pk primary key (acc_id);
Table altered.
SQL> alter table trade_account
2 exchange partition P131113 with table trade_account_load;
alter table trade_account
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION
The reason for the exception is that the unique index that is used to enforce the primary key constraint is not local. Retry with a local index:
SQL> alter table trade_account drop constraint trade_account_pk;
Table altered.
SQL> alter table trade_account
2 add constraint trade_account_pk primary key (acc_id) using index local;
alter table trade_account
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
Attempting to create a local unique index, regardless of whether the index is used to enforce a primary key constraint, without the partition key generates an exception. Another exception will occur if the primary key on the non-partitioned table does not exist, for example:
SQL> alter table trade_account
2 add constraint trade_account_pk
3 primary key (acc_id,date_created) using index local;
Table altered.
SQL> alter table trade_account
2 exchange partition P131113 with table trade_account_load;
alter table trade_account
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION
The partitioning alignment will be shown as either PREFIXED or NON-PREFIXED, for example:
PREFIXED
SQL> alter table trade_account
2 add constraint trade_account_pk
3 primary key (date_created, acc_id) using index local;
Table altered.
SQL> select upi.table_name
2 ,upi.index_name
3 ,upi.partitioning_type
4 ,upi.locality,alignment
5 from
6 user_part_indexes upi
7 where
8 upi.table_name = 'TRADE_ACCOUNT';
TABLE_NAME INDEX_NAME PARTITION LOCALI ALIGNMENT
--------------- ----------------- --------- ------ ------------
TRADE_ACCOUNT TRADE_ACCOUNT_PK RANGE LOCAL PREFIXED
NON-PREFIXED
SQL> alter table trade_account drop constraint trade_account_pk;
Table altered.
SQL> alter table trade_account
2 add constraint trade_account_pk
3 primary key (acc_id, date_created) using index local;
Table altered.
It is worth creating unqiue/primary key constraints separately to the unique index, since one can disable and re-enable the constraint without dropping the index. When loading a large volume of rows the constraint can be disabled, but the index will be maintained. There are several ways to create them seperately;
Simplest way:
SQL> alter table trade_account
2 add constraint trade_account_pk primary key(acc_id, date_created)
3 using index
4 (create unique index trade_account_pk
5 on trade_account(acc_id,date_created) local
6 );
Table altered.
2 add constraint trade_account_pk primary key(acc_id, date_created)
3 using index
4 (create unique index trade_account_pk
5 on trade_account(acc_id,date_created) local
6 );
Table altered.
Or, as two seperate DDL statements:
SQL> alter table trade_account drop constraint trade_account_pk;
Table altered.
SQL> create unique index trade_account_pk
2 on trade_account(acc_id,date_created) local;
Index created.
SQL> alter table trade_account
2 add constraint trade_account_pk primary key (acc_id,date_created)
3 using index trade_account_pk;
Table altered.
Table altered.
SQL> create unique index trade_account_pk
2 on trade_account(acc_id,date_created) local;
Index created.
SQL> alter table trade_account
2 add constraint trade_account_pk primary key (acc_id,date_created)
3 using index trade_account_pk;
Table altered.
The following indicates how all the local partitioned indexes remain usable even though the constraint has been disabled.
SQL> alter table trade_account drop constraint trade_account_pk;
Table altered.
SQL> create unique index trade_account_pk
2 on trade_account(acc_id,date_created) local;
Index created.
SQL> alter table trade_account
2 add constraint trade_account_pk primary key (acc_id,date_created)
3 using index trade_account_pk;
Table altered.
SQL> alter table trade_account disable constraint trade_account_pk;
Table altered.
SQL>
SQL> select uip.index_name
2 ,uip.partition_name
3 ,uip.status
4 from user_ind_partitions uip
5 where uip.index_name = 'TRADE_ACCOUNT_PK'
6 order by 2;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK P131109 USABLE
TRADE_ACCOUNT_PK P131110 USABLE
TRADE_ACCOUNT_PK P131111 USABLE
TRADE_ACCOUNT_PK P131112 USABLE
TRADE_ACCOUNT_PK P131113 USABLE
TRADE_ACCOUNT_PK P131114 USABLE
TRADE_ACCOUNT_PK PMAX USABLE
7 rows selected.
SQL> select status from user_constraints
2 where constraint_name = 'TRADE_ACCOUNT_PK';
STATUS
--------
DISABLED
The index will now appear as NON_PREFIXED in the data dictionary.
SQL> select upi.table_name
2 ,upi.index_name
3 ,upi.partitioning_type
4 ,upi.locality,alignment
5 from
6 user_part_indexes upi
7 where
8 upi.table_name = 'TRADE_ACCOUNT';
TABLE_NAME INDEX_NAME PARTITION LOCALI ALIGNMENT
--------------- ----------------- --------- ------ ------------
TRADE_ACCOUNT TRADE_ACCOUNT_PK RANGE LOCAL NON_PREFIXED
Be aware that there are performance implications for non-prefixed local indexes. Refer to the 11gR2 docuementaion here for more detail.
Create a primary key on the non-partitioned table that matches the primary key on the partitioned table in terms of the columns used and then retry the exchange:
SQL> alter table trade_account_load add constraint trade_account_load_pk
2 primary key (acc_id,date_created);
Table altered.
SQL> alter table trade_account
2 exchange partition P131113 with table trade_account_load;
Table altered.
Rebuild the unusable indexes. If the indexes are local, then only the index local to the partition that was exchanged would have to be rebuilt. If the index is global then the entire global index requires rebuilding, which could take a considerable amount of time, so it might be worth investigating whether the rebuild can occur in parallel.
2 ,null as partition_name
3 ,uix1.status
4 from user_indexes uix1
5 where uix1.table_name = 'TRADE_ACCOUNT'
6 and uix1.partitioned = 'NO'
7 union all
8 select uip.index_name
9 ,uip.partition_name
10 ,uip.status
11 from user_ind_partitions uip
12 inner join user_indexes uix2 on uip.index_name = uix2.index_name
13 and uix2.table_name = 'TRADE_ACCOUNT'
14 order by 2;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK P131109 USABLE
TRADE_ACCOUNT_PK P131110 USABLE
TRADE_ACCOUNT_PK P131111 USABLE
TRADE_ACCOUNT_PK P131112 USABLE
TRADE_ACCOUNT_PK P131113 UNUSABLE
TRADE_ACCOUNT_PK P131114 USABLE
TRADE_ACCOUNT_PK PMAX USABLE
7 rows selected.
There are no global indexes, but a single local partition index that is unusable;
SQL> alter index TRADE_ACCOUNT_PK rebuild partition P131113;
Index altered.
SQL> select uix1.index_name
2 ,null as partition_name
3 ,uix1.status
4 from user_indexes uix1
5 where uix1.table_name = 'TRADE_ACCOUNT'
6 and uix1.partitioned = 'NO'
7 union all
8 select uip.index_name
9 ,uip.partition_name
10 ,uip.status
11 from user_ind_partitions uip
12 inner join user_indexes uix2 on uip.index_name = uix2.index_name
13 and uix2.table_name = 'TRADE_ACCOUNT'
14 order by 2;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK P131109 USABLE
TRADE_ACCOUNT_PK P131110 USABLE
TRADE_ACCOUNT_PK P131111 USABLE
TRADE_ACCOUNT_PK P131112 USABLE
TRADE_ACCOUNT_PK P131113 USABLE
TRADE_ACCOUNT_PK P131114 USABLE
TRADE_ACCOUNT_PK PMAX USABLE
7 rows selected.
Finally, run the stats gathering again to verify whether the rows were loaded:
SQL> exec dbms_stats.gather_table_stats(sys_context('userenv','current_schema'), 'TRADE_ACCOUNT');
PL/SQL procedure successfully completed.
SQL> select partition_name,num_rows
2 from user_tab_partitions
3 where table_name = 'TRADE_ACCOUNT';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P131109 0
P131110 0
P131111 0
P131112 1
P131113 2
P131114 0
PMAX 0
7 rows selected.
Subscribe to:
Posts (Atom)