Script categories

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.

 

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:

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));

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.
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);

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
 

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.


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.


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.


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.


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


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.

SQL> delete from trade_account where rowid =
  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 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.


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


  
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.

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.

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.

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.