Modifying a Primary Key Index in Oracle 11.2.0.x

It is entirely possible with later releases of Oracle to use a non-unique index to enforce a primary key constraint. An advantage of this strategy is the index remains if the constraint is, at some time dropped, provided the index was not created as part of the ‘add constraint’ statement. Disadvantages to this strategy can include changed redo/undo generation volume and execution plans that may not be the same as when a unique index is used. It is possible to ‘replace’ the non-unique index with a unique index of the same name, and do it without an outage. Yes, it’s a remote chance that such a task may be necessary, but knowing how to do it in Oracle 11.2.0.x and earlier releases can make things much easier if this ever does come to pass.

We start this endeavor by creating a table, a non-unique index and a primary key constraint that uses the non-unique index:

 
SQL> 
SQL> --
SQL> -- Create and load table
SQL> --
SQL> create table uniq_pk_idx_tst(
  2  	     uniq_key	     number,
  3  	     somestuff	     varchar2(40),
  4  	     somedt	     date,
  5  	     someother	     varchar2(24));

Table created.

SQL> 
SQL> begin
  2  	     for i in 1..1000000 loop
  3  		     insert into uniq_pk_idx_tst(uniq_key, somestuff, somedt, someother)
  4  		     values(i, 'Some stuff '||i, sysdate+i, 'Other '||i);
  5  	     end loop;
  6  
  7  	     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Create non-unique index
SQL> --
SQL> create index uniq_pk_idx_tst_pk on uniq_pk_idx_tst(uniq_key);

Index created.

SQL> --
SQL> -- Use non-unique index to enforce primary key
SQL> --
SQL> alter table uniq_pk_idx_tst add constraint uniq_pk_idx_tst_pk primary key(uniq_key);

Table altered.

SQL> 
SQL> --
SQL> -- Generate stats then query the table
SQL> --
SQL> -- See if non-unique index increases query costs
SQL> -- and undo generation
SQL> --
SQL> exec dbms_stats.gather_table_stats('BING', 'UNIQ_PK_IDX_TST',estimate_percent=>100, method_opt=>'for all columns size auto', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> 

Now we query the table and see the plan in use.

 
SQL> set autotrace on
SQL> 
SQL> select uniq_key, somestuff, someother
  2  from uniq_pk_idx_tst
  3  where uniq_key between 6010 and 6100;

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6010 Some stuff 6010                          Other 6010                                                                                        
      6011 Some stuff 6011                          Other 6011                                                                                        
      6012 Some stuff 6012                          Other 6012                                                                                        
      6013 Some stuff 6013                          Other 6013                                                                                        
      6014 Some stuff 6014                          Other 6014                                                                                        
      6015 Some stuff 6015                          Other 6015                                                                                        
      6016 Some stuff 6016                          Other 6016                                                                                        
      6017 Some stuff 6017                          Other 6017                                                                                        
      6018 Some stuff 6018                          Other 6018                                                                                        
      6019 Some stuff 6019                          Other 6019                                                                                        
      6020 Some stuff 6020                          Other 6020                                                                                        

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6021 Some stuff 6021                          Other 6021                                                                                        
      6022 Some stuff 6022                          Other 6022                                                                                        
      6023 Some stuff 6023                          Other 6023                                                                                        
      6024 Some stuff 6024                          Other 6024                                                                                        
      6025 Some stuff 6025                          Other 6025                                                                                        
      6026 Some stuff 6026                          Other 6026                                                                                        
      6027 Some stuff 6027                          Other 6027                                                                                        
      6028 Some stuff 6028                          Other 6028                                                                                        
      6029 Some stuff 6029                          Other 6029                                                                                        
      6030 Some stuff 6030                          Other 6030                                                                                        
      6031 Some stuff 6031                          Other 6031                                                                                        

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6032 Some stuff 6032                          Other 6032                                                                                        
      6033 Some stuff 6033                          Other 6033                                                                                        
      6034 Some stuff 6034                          Other 6034                                                                                        
      6035 Some stuff 6035                          Other 6035                                                                                        
      6036 Some stuff 6036                          Other 6036                                                                                        
      6037 Some stuff 6037                          Other 6037                                                                                        
      6038 Some stuff 6038                          Other 6038                                                                                        
      6039 Some stuff 6039                          Other 6039                                                                                        
      6040 Some stuff 6040                          Other 6040                                                                                        
      6041 Some stuff 6041                          Other 6041                                                                                        
      6042 Some stuff 6042                          Other 6042                                                                                        

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6043 Some stuff 6043                          Other 6043                                                                                        
      6044 Some stuff 6044                          Other 6044                                                                                        
      6045 Some stuff 6045                          Other 6045                                                                                        
      6046 Some stuff 6046                          Other 6046                                                                                        
      6047 Some stuff 6047                          Other 6047                                                                                        
      6048 Some stuff 6048                          Other 6048                                                                                        
      6049 Some stuff 6049                          Other 6049                                                                                        
      6050 Some stuff 6050                          Other 6050                                                                                        
      6051 Some stuff 6051                          Other 6051                                                                                        
      6052 Some stuff 6052                          Other 6052                                                                                        
      6053 Some stuff 6053                          Other 6053                                                                                        

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6054 Some stuff 6054                          Other 6054                                                                                        
      6055 Some stuff 6055                          Other 6055                                                                                        
      6056 Some stuff 6056                          Other 6056                                                                                        
      6057 Some stuff 6057                          Other 6057                                                                                        
      6058 Some stuff 6058                          Other 6058                                                                                        
      6059 Some stuff 6059                          Other 6059                                                                                        
      6060 Some stuff 6060                          Other 6060                                                                                        
      6061 Some stuff 6061                          Other 6061                                                                                        
      6062 Some stuff 6062                          Other 6062                                                                                        
      6063 Some stuff 6063                          Other 6063                                                                                        
      6064 Some stuff 6064                          Other 6064                                                                                        

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6065 Some stuff 6065                          Other 6065                                                                                        
      6066 Some stuff 6066                          Other 6066                                                                                        
      6067 Some stuff 6067                          Other 6067                                                                                        
      6068 Some stuff 6068                          Other 6068                                                                                        
      6069 Some stuff 6069                          Other 6069                                                                                        
      6070 Some stuff 6070                          Other 6070                                                                                        
      6071 Some stuff 6071                          Other 6071                                                                                        
      6072 Some stuff 6072                          Other 6072                                                                                        
      6073 Some stuff 6073                          Other 6073                                                                                        
      6074 Some stuff 6074                          Other 6074                                                                                        
      6075 Some stuff 6075                          Other 6075                                                                                        

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6076 Some stuff 6076                          Other 6076                                                                                        
      6077 Some stuff 6077                          Other 6077                                                                                        
      6078 Some stuff 6078                          Other 6078                                                                                        
      6079 Some stuff 6079                          Other 6079                                                                                        
      6080 Some stuff 6080                          Other 6080                                                                                        
      6081 Some stuff 6081                          Other 6081                                                                                        
      6082 Some stuff 6082                          Other 6082                                                                                        
      6083 Some stuff 6083                          Other 6083                                                                                        
      6084 Some stuff 6084                          Other 6084                                                                                        
      6085 Some stuff 6085                          Other 6085                                                                                        
      6086 Some stuff 6086                          Other 6086                                                                                        

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6087 Some stuff 6087                          Other 6087                                                                                        
      6088 Some stuff 6088                          Other 6088                                                                                        
      6089 Some stuff 6089                          Other 6089                                                                                        
      6090 Some stuff 6090                          Other 6090                                                                                        
      6091 Some stuff 6091                          Other 6091                                                                                        
      6092 Some stuff 6092                          Other 6092                                                                                        
      6093 Some stuff 6093                          Other 6093                                                                                        
      6094 Some stuff 6094                          Other 6094                                                                                        
      6095 Some stuff 6095                          Other 6095                                                                                        
      6096 Some stuff 6096                          Other 6096                                                                                        
      6097 Some stuff 6097                          Other 6097                                                                                        

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6098 Some stuff 6098                          Other 6098                                                                                        
      6099 Some stuff 6099                          Other 6099                                                                                        
      6100 Some stuff 6100                          Other 6100                                                                                        

91 rows selected.


Execution Plan
----------------------------------------------------------                                                                                            
Plan hash value: 2575518852                                                                                                                           
                                                                                                                                                      
--------------------------------------------------------------------------------------------------                                                    
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |                                                    
--------------------------------------------------------------------------------------------------                                                    
|   0 | SELECT STATEMENT            |                    |    92 |  3312 |     4   (0)| 00:00:01 |                                                    
|   1 |  TABLE ACCESS BY INDEX ROWID| UNIQ_PK_IDX_TST    |    92 |  3312 |     4   (0)| 00:00:01 |                                                    
|*  2 |   INDEX RANGE SCAN          | UNIQ_PK_IDX_TST_PK |    92 |       |     3   (0)| 00:00:01 |                                                    
--------------------------------------------------------------------------------------------------                                                    
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   2 - access("UNIQ_KEY">=6010 AND "UNIQ_KEY"<=6100)                                                                                                  


Statistics
----------------------------------------------------------                                                                                            
          8  recursive calls                                                                                                                          
          4  db block gets                                                                                                                            
         18  consistent gets                                                                                                                          
          0  physical reads                                                                                                                           
        540  redo size                                                                                                                                
       4747  bytes sent via SQL*Net to client                                                                                                         
        586  bytes received via SQL*Net from client                                                                                                   
          8  SQL*Net roundtrips to/from client                                                                                                        
          0  sorts (memory)                                                                                                                           
          0  sorts (disk)                                                                                                                             
         91  rows processed                                                                                                                           

SQL> 
SQL> --
SQL> -- Update the table
SQL> --
SQL> -- Report undo generated
SQL> --
SQL> update uniq_pk_idx_tst
  2  set someother = 'YOYO'
  3  where uniq_key between 6010 and 6100;

91 rows updated.


Execution Plan
----------------------------------------------------------                                                                                            
Plan hash value: 1984512805                                                                                                                           
                                                                                                                                                      
----------------------------------------------------------------------------------------                                                              
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |                                                              
----------------------------------------------------------------------------------------                                                              
|   0 | UPDATE STATEMENT  |                    |    92 |  1656 |     4   (0)| 00:00:01 |                                                              
|   1 |  UPDATE           | UNIQ_PK_IDX_TST    |       |       |            |          |                                                              
|*  2 |   INDEX RANGE SCAN| UNIQ_PK_IDX_TST_PK |    92 |  1656 |     3   (0)| 00:00:01 |                                                              
----------------------------------------------------------------------------------------                                                              
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   2 - access("UNIQ_KEY">=6010 AND "UNIQ_KEY"<=6100)                                                                                                  


Statistics
----------------------------------------------------------                                                                                            
          3  recursive calls                                                                                                                          
         93  db block gets                                                                                                                            
          7  consistent gets                                                                                                                          
          0  physical reads                                                                                                                           
      23440  redo size                                                                                                                                
        846  bytes sent via SQL*Net to client                                                                                                         
        835  bytes received via SQL*Net from client                                                                                                   
          3  SQL*Net roundtrips to/from client                                                                                                        
          2  sorts (memory)                                                                                                                           
          0  sorts (disk)                                                                                                                             
         91  rows processed                                                                                                                           

SQL> 
SQL> set autotrace off
SQL> commit;

Commit complete.

SQL> 
SQL> set autotrace on
SQL> update uniq_pk_idx_tst
  2  set someother = null
  3  where uniq_key between 6010 and 6100;

91 rows updated.


Execution Plan
----------------------------------------------------------                                                                                            
Plan hash value: 1984512805                                                                                                                           
                                                                                                                                                      
----------------------------------------------------------------------------------------                                                              
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |                                                              
----------------------------------------------------------------------------------------                                                              
|   0 | UPDATE STATEMENT  |                    |    92 |  1656 |     4   (0)| 00:00:01 |                                                              
|   1 |  UPDATE           | UNIQ_PK_IDX_TST    |       |       |            |          |                                                              
|*  2 |   INDEX RANGE SCAN| UNIQ_PK_IDX_TST_PK |    92 |  1656 |     3   (0)| 00:00:01 |                                                              
----------------------------------------------------------------------------------------                                                              
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   2 - access("UNIQ_KEY">=6010 AND "UNIQ_KEY"<=6100)                                                                                                  


Statistics
----------------------------------------------------------                                                                                            
	  1  recursive calls                                                                                                                          
	 95  db block gets                                                                                                                            
	  3  consistent gets                                                                                                                          
	  0  physical reads                                                                                                                           
	  0  redo size                                                                                                                                
	846  bytes sent via SQL*Net to client                                                                                                         
	833  bytes received via SQL*Net from client                                                                                                   
	  3  SQL*Net roundtrips to/from client                                                                                                        
	  1  sorts (memory)                                                                                                                           
	  0  sorts (disk)                                                                                                                             
	 91  rows processed                                                                                                                           

SQL> 
SQL> set autotrace off
SQL> commit;

Commit complete.

SQL> 
SQL> 

The bytes of redo generated by the update for the non-unique index is 23440. This give us a reference for the next section, where the non-unique index is eventually replaced by a unique index of the same name. Note we need a ‘replacement’ index so that queries run in the interim still can use an index access path:

 
SQL> 
SQL> --
SQL> -- Create a non-unique 'replacement' index
SQL> --
SQL> -- Modify the PK constraint to use this new index
SQL> --
SQL> create index uniq_pk_idx_tst_nu on uniq_pk_idx_tst(uniq_key, 0) online;

Index created.

SQL> 
SQL> alter table uniq_pk_idx_tst modify primary key using index uniq_pk_idx_tst_nu;

Table altered.

SQL> 
SQL> --
SQL> -- Drop the old PK index
SQL> --
SQL> drop index uniq_pk_idx_tst_pk;

Index dropped.

SQL> 
SQL> --
SQL> -- Create a new, unique PK index with the original name
SQL> --
SQL> -- Re-generate statistics
SQL> --
SQL> create unique index uniq_pk_idx_tst_pk on uniq_pk_idx_tst(uniq_key) online;

Index created.

SQL> 
SQL> exec dbms_stats.gather_table_stats('BING', 'UNIQ_PK_IDX_TST',estimate_percent=>100, method_opt=>'for all columns size auto', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Alter the primary key to use this new unique index
SQL> --
SQL> alter table uniq_pk_idx_tst modify primary key using index uniq_pk_idx_tst_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Drop the interim index
SQL> --
SQL> drop index uniq_pk_idx_tst_nu;

Index dropped.

SQL> 

Now the constraint is using a unique index. Let’s again check the plan and see how much redo is generated for updates:

 
SQL> 
SQL> --
SQL> -- Query table again
SQL> --
SQL> -- See if unique index improves cost and
SQL> -- reduces undo
SQL> --
SQL> set autotrace on
SQL> 
SQL> select uniq_key, somestuff, someother
  2  from uniq_pk_idx_tst
  3  where uniq_key between 6010 and 6100;

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6010 Some stuff 6010                                                                                                                            
      6011 Some stuff 6011                                                                                                                            
      6012 Some stuff 6012                                                                                                                            
      6013 Some stuff 6013                                                                                                                            
      6014 Some stuff 6014                                                                                                                            
      6015 Some stuff 6015                                                                                                                            
      6016 Some stuff 6016                                                                                                                            
      6017 Some stuff 6017                                                                                                                            
      6018 Some stuff 6018                                                                                                                            
      6019 Some stuff 6019                                                                                                                            
      6020 Some stuff 6020                                                                                                                            

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6021 Some stuff 6021                                                                                                                            
      6022 Some stuff 6022                                                                                                                            
      6023 Some stuff 6023                                                                                                                            
      6024 Some stuff 6024                                                                                                                            
      6025 Some stuff 6025                                                                                                                            
      6026 Some stuff 6026                                                                                                                            
      6027 Some stuff 6027                                                                                                                            
      6028 Some stuff 6028                                                                                                                            
      6029 Some stuff 6029                                                                                                                            
      6030 Some stuff 6030                                                                                                                            
      6031 Some stuff 6031                                                                                                                            

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6032 Some stuff 6032                                                                                                                            
      6033 Some stuff 6033                                                                                                                            
      6034 Some stuff 6034                                                                                                                            
      6035 Some stuff 6035                                                                                                                            
      6036 Some stuff 6036                                                                                                                            
      6037 Some stuff 6037                                                                                                                            
      6038 Some stuff 6038                                                                                                                            
      6039 Some stuff 6039                                                                                                                            
      6040 Some stuff 6040                                                                                                                            
      6041 Some stuff 6041                                                                                                                            
      6042 Some stuff 6042                                                                                                                            

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6043 Some stuff 6043                                                                                                                            
      6044 Some stuff 6044                                                                                                                            
      6045 Some stuff 6045                                                                                                                            
      6046 Some stuff 6046                                                                                                                            
      6047 Some stuff 6047                                                                                                                            
      6048 Some stuff 6048                                                                                                                            
      6049 Some stuff 6049                                                                                                                            
      6050 Some stuff 6050                                                                                                                            
      6051 Some stuff 6051                                                                                                                            
      6052 Some stuff 6052                                                                                                                            
      6053 Some stuff 6053                                                                                                                            

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6054 Some stuff 6054                                                                                                                            
      6055 Some stuff 6055                                                                                                                            
      6056 Some stuff 6056                                                                                                                            
      6057 Some stuff 6057                                                                                                                            
      6058 Some stuff 6058                                                                                                                            
      6059 Some stuff 6059                                                                                                                            
      6060 Some stuff 6060                                                                                                                            
      6061 Some stuff 6061                                                                                                                            
      6062 Some stuff 6062                                                                                                                            
      6063 Some stuff 6063                                                                                                                            
      6064 Some stuff 6064                                                                                                                            

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6065 Some stuff 6065                                                                                                                            
      6066 Some stuff 6066                                                                                                                            
      6067 Some stuff 6067                                                                                                                            
      6068 Some stuff 6068                                                                                                                            
      6069 Some stuff 6069                                                                                                                            
      6070 Some stuff 6070                                                                                                                            
      6071 Some stuff 6071                                                                                                                            
      6072 Some stuff 6072                                                                                                                            
      6073 Some stuff 6073                                                                                                                            
      6074 Some stuff 6074                                                                                                                            
      6075 Some stuff 6075                                                                                                                            

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6076 Some stuff 6076                                                                                                                            
      6077 Some stuff 6077                                                                                                                            
      6078 Some stuff 6078                                                                                                                            
      6079 Some stuff 6079                                                                                                                            
      6080 Some stuff 6080                                                                                                                            
      6081 Some stuff 6081                                                                                                                            
      6082 Some stuff 6082                                                                                                                            
      6083 Some stuff 6083                                                                                                                            
      6084 Some stuff 6084                                                                                                                            
      6085 Some stuff 6085                                                                                                                            
      6086 Some stuff 6086                                                                                                                            

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6087 Some stuff 6087                                                                                                                            
      6088 Some stuff 6088                                                                                                                            
      6089 Some stuff 6089                                                                                                                            
      6090 Some stuff 6090                                                                                                                            
      6091 Some stuff 6091                                                                                                                            
      6092 Some stuff 6092                                                                                                                            
      6093 Some stuff 6093                                                                                                                            
      6094 Some stuff 6094                                                                                                                            
      6095 Some stuff 6095                                                                                                                            
      6096 Some stuff 6096                                                                                                                            
      6097 Some stuff 6097                                                                                                                            

  UNIQ_KEY SOMESTUFF                                SOMEOTHER                                                                                         
---------- ---------------------------------------- ------------------------                                                                          
      6098 Some stuff 6098                                                                                                                            
      6099 Some stuff 6099                                                                                                                            
      6100 Some stuff 6100                                                                                                                            

91 rows selected.


Execution Plan
----------------------------------------------------------                                                                                            
Plan hash value: 2575518852                                                                                                                           
                                                                                                                                                      
--------------------------------------------------------------------------------------------------                                                    
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |                                                    
--------------------------------------------------------------------------------------------------                                                    
|   0 | SELECT STATEMENT            |                    |    92 |  3312 |     4   (0)| 00:00:01 |                                                    
|   1 |  TABLE ACCESS BY INDEX ROWID| UNIQ_PK_IDX_TST    |    92 |  3312 |     4   (0)| 00:00:01 |                                                    
|*  2 |   INDEX RANGE SCAN          | UNIQ_PK_IDX_TST_PK |    92 |       |     3   (0)| 00:00:01 |                                                    
--------------------------------------------------------------------------------------------------                                                    
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   2 - access("UNIQ_KEY">=6010 AND "UNIQ_KEY"<=6100)                                                                                                  


Statistics
----------------------------------------------------------                                                                                            
         59  recursive calls                                                                                                                          
         46  db block gets                                                                                                                            
         74  consistent gets                                                                                                                          
          0  physical reads                                                                                                                           
       4972  redo size                                                                                                                                
       4033  bytes sent via SQL*Net to client                                                                                                         
        586  bytes received via SQL*Net from client                                                                                                   
          8  SQL*Net roundtrips to/from client                                                                                                        
          6  sorts (memory)                                                                                                                           
          0  sorts (disk)                                                                                                                             
         91  rows processed                                                                                                                           

SQL> 
SQL> --
SQL> -- Update the table
SQL> --
SQL> -- Report undo generated
SQL> --
SQL> update uniq_pk_idx_tst
  2  set someother = 'YOYO'
  3  where uniq_key between 6010 and 6100;

91 rows updated.


Execution Plan
----------------------------------------------------------                                                                                            
Plan hash value: 1984512805                                                                                                                           
                                                                                                                                                      
----------------------------------------------------------------------------------------                                                              
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |                                                              
----------------------------------------------------------------------------------------                                                              
|   0 | UPDATE STATEMENT  |                    |    92 |  1656 |     4   (0)| 00:00:01 |                                                              
|   1 |  UPDATE           | UNIQ_PK_IDX_TST    |       |       |            |          |                                                              
|*  2 |   INDEX RANGE SCAN| UNIQ_PK_IDX_TST_PK |    92 |  1656 |     3   (0)| 00:00:01 |                                                              
----------------------------------------------------------------------------------------                                                              
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   2 - access("UNIQ_KEY">=6010 AND "UNIQ_KEY"<=6100)                                                                                                  


Statistics
----------------------------------------------------------                                                                                            
          3  recursive calls                                                                                                                          
         93  db block gets                                                                                                                            
          7  consistent gets                                                                                                                          
          0  physical reads                                                                                                                           
      21280  redo size                                                                                                                                
        847  bytes sent via SQL*Net to client                                                                                                         
        835  bytes received via SQL*Net from client                                                                                                   
          3  SQL*Net roundtrips to/from client                                                                                                        
          2  sorts (memory)                                                                                                                           
          0  sorts (disk)                                                                                                                             
         91  rows processed                                                                                                                           

SQL> 
SQL> set autotrace off
SQL> commit;

Commit complete.

SQL> 
SQL> set autotrace on
SQL> update uniq_pk_idx_tst
  2  set someother = null
  3  where uniq_key between 6010 and 6100;

91 rows updated.


Execution Plan
----------------------------------------------------------                                                                                            
Plan hash value: 1984512805                                                                                                                           
                                                                                                                                                      
----------------------------------------------------------------------------------------                                                              
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |                                                              
----------------------------------------------------------------------------------------                                                              
|   0 | UPDATE STATEMENT  |                    |    92 |  1656 |     4   (0)| 00:00:01 |                                                              
|   1 |  UPDATE           | UNIQ_PK_IDX_TST    |       |       |            |          |                                                              
|*  2 |   INDEX RANGE SCAN| UNIQ_PK_IDX_TST_PK |    92 |  1656 |     3   (0)| 00:00:01 |                                                              
----------------------------------------------------------------------------------------                                                              
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   2 - access("UNIQ_KEY">=6010 AND "UNIQ_KEY"<=6100)                                                                                                  


Statistics
----------------------------------------------------------                                                                                            
          1  recursive calls                                                                                                                          
         95  db block gets                                                                                                                            
          3  consistent gets                                                                                                                          
          0  physical reads                                                                                                                           
          0  redo size                                                                                                                                
        848  bytes sent via SQL*Net to client                                                                                                         
        833  bytes received via SQL*Net from client                                                                                                   
          3  SQL*Net roundtrips to/from client                                                                                                        
          1  sorts (memory)                                                                                                                           
          0  sorts (disk)                                                                                                                             
         91  rows processed                                                                                                                           

SQL> 
SQL> set autotrace off
SQL> commit;

Commit complete.

SQL> 
SQL>

The bytes of redo generated with the unique index is reported as 21280, slightly less than with the non-unique index (23440); the plan didn’t change (and I expect that may be a rare occurrence, depending on the data). Since we only updated a small portion of the data the difference in redo generation could be much greater for larger updates.

The more you know the easier being a DBA can be.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles