Compare Oracle Collections

In programming we don't just compare variables/integers with each other, we tend to compare arrays too. Collections aka arrays can be compared in different ways, lets see them in detail.

Traditional Method

Option 1

               The traditional method to compare collection would be just to loop through them and compare one with another, like below         

SQL> set timing on
SQL> 
SQL> set serveroutput on
SQL> declare
  2  type basket is table of number;
  3  num_balls basket := basket(1,2,3);
  4  num_color_balls basket := basket(3,2,1);
  5  inter_match number:=0;
  6  begin
  7  if num_balls.count = num_color_balls.count then
  8    for i in num_balls.first .. num_balls.last loop
  9      for j in num_color_balls.first .. num_color_balls.last loop
 10        if num_balls(i) = num_color_balls(j) then
 11            inter_match := inter_match+1;
 12        end if;
 13      end loop;
 14    end loop;
 15    if inter_match = num_balls.count then
 16        dbms_output.put_line('Arrays are equal');
 17    end if;
 18  else
 19  dbms_output.put_line('Arrays are not equal');
 20  end if;
 21  end;
 22  /
Arrays are equal                                                                

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL> 

Option 2 

              Using Table Cast with MINUS operator thus avoiding loops

SQL> 
SQL> create type basket is table of number;
  2  /

Type created.

Elapsed: 00:00:00.72
SQL> 
SQL> declare
  2  num_balls basket := basket(1,2,3);
  3  num_color_balls basket := basket(3,2,1);
  4  v_count number;
  5  
  6  begin
  7  
  8  select column_value into v_count from
  9  (
 10  (select column_value from table(cast(num_balls as basket)))
 11  minus
 12  (select column_value from table(cast(num_color_balls as basket)))
 13  );
 14  
 15  if v_count>0 then
 16  dbms_output.put_line('Arrays are not equal');
 17  end if;
 18  
 19  exception
 20  when no_data_found then
 21  dbms_output.put_line('Arrays are equal');
 22  
 23  end;
 24  /
Arrays are equal                                                                

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> 

You will have to go only with the above two options until Oracle 10g, From 10g we have got many options to compare collections, lets see them in detail. 

 Comparing collection is now as same as comparing variables/integers, example given below

SQL> 
SQL> declare
  2  num_balls basket := basket(1,2,3);
  3  num_color_balls basket := basket(1,2,3);
  4  begin
  5    if num_balls = num_color_balls then
  6      dbms_output.put_line('Arrays are equal');
  7    else
  8      dbms_output.put_line('Arrays are not equal');
  9    end if;
 10  end;
 11  /
Arrays are equal                                                                

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> 

Note : The order of elements in an array is irrelevant while comparing collections. It means that an array with data 1,2,3 is equal to 3,2,1 and that's what we have seen above. Ok this is fine, can we do the same with Associative and Varrays 

Varrays

SQL> 
SQL> declare
  2  --type basket is table of number;
  3  type basket is varray(10) of number;
  4      num_balls basket := basket(1,2,3);
  5      num_color_balls basket := basket(1,2,3);
  6      begin
  7        if num_balls = num_color_balls then
  8          dbms_output.put_line('Arrays are equal');
  9        else
 10          dbms_output.put_line('Arrays are not equal');
 11        end if;
 12     end;
 13     /
      if num_balls = num_color_balls then
                   *
ERROR at line 7:
ORA-06550: line 7, column 20: 
PLS-00306: wrong number or types of arguments in call to '=' 
ORA-06550: line 7, column 7: 
PL/SQL: Statement ignored 

Elapsed: 00:00:00.01
SQL> 

Means you cannot compare all elements at one shot, have to do it one by one

SQL> 
SQL> declare
  2  --type basket is table of number;
  3  type basket is varray(10) of number;
  4      num_balls basket := basket(1,2,3);
  5      num_color_balls basket := basket(1,2,3);
  6      begin
  7        if num_balls(1) = num_color_balls(1) then
  8          dbms_output.put_line('Arrays are equal');
  9        else
 10          dbms_output.put_line('Arrays are not equal');
 11        end if;
 12     end;
 13     /
Arrays are equal                                                                

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> 

Associative Array - Cannot be compared at one shot, should be looped,

SQL> REM ASSOCIATIVE ARRAYS
SQL> 
SQL> declare
  2  type basket is table of number index by pls_integer;
  3      num_balls basket;
  4      num_color_balls basket;
  5      begin
  6      select level bulk collect into num_balls from dual connect by level < 10;
  7      select level bulk collect into num_color_balls from dual connect by level < 10;
  8        if num_balls = num_color_balls then
  9          dbms_output.put_line('Arrays are equal');
 10        else
 11          dbms_output.put_line('Arrays are not equal');
 12        end if;
 13     end;
 14     /
      if num_balls = num_color_balls then
                   *
ERROR at line 8:
ORA-06550: line 8, column 20: 
PLS-00306: wrong number or types of arguments in call to '=' 
ORA-06550: line 8, column 7: 
PL/SQL: Statement ignored 


Elapsed: 00:00:00.01
SQL> 
SQL> 
SQL> declare
  2  type basket is table of number index by pls_integer;
  3      num_balls basket;
  4      num_color_balls basket;
  5      begin
  6      select level bulk collect into num_balls from dual connect by level < 10;
  7      select level bulk collect into num_color_balls from dual connect by level < 10;
  8        if num_balls(1) = num_color_balls(1) then
  9          dbms_output.put_line('Arrays are equal');
 10        else
 11          dbms_output.put_line('Arrays are not equal');
 12        end if;
 13     end;
 14     /
Arrays are equal                                                                

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>  

Will see about Multiset operators in the next post.

Partitions- Kind of Global Vs Local Partitioned Indexes

Had a great discussion with few techi folks today. It started with SQL tuning (my area of interest ) and eventually partitioning. I was sticking towards SQL Tuning + Partition Elimination + Global Indexes.  And they were sticking towards Full table Scan + Maintenance Activities + Local Indexes. The discussion was heating up and i made up my mind to switch lane :) when two more AK47 was kept before, yes Partitioned table with Tera Bytes of data + Archiving Strategy. 

             Does this look like OLTP vs OLAP. Big Table Maintenance + Archiving Strategy would shatter the usage of global indexes, but lets see what happens when global indexes are not present. Let's see them in detail. 

SQL> set timing on
SQL> 
SQL> drop table t2;

Table dropped.

Elapsed: 00:00:00.40
SQL> 
SQL> CREATE TABLE t2
  2      (
  3        x   int not null primary key,
  4        dt  date,
  5        y   varchar2(30)
  6      )
  7      PARTITION BY RANGE(dt)
  8      (
  9      PARTITION part1_FEB2013 VALUES LESS THAN (to_date('01-Feb-2013','dd-mon-yyyy')) ,
 10      PARTITION part1_MAR2013 VALUES LESS THAN (to_date('01-Mar-2013','dd-mon-yyyy')) ,
 11      PARTITION part1_APR2013 VALUES LESS THAN (to_date('01-Apr-2013','dd-mon-yyyy')) ,
 12      PARTITION part2_MAY2013 VALUES LESS THAN (to_date('01-May-2013','dd-mon-yyyy')),
 13      PARTITION junk VALUES LESS THAN (MAXVALUE)
 14     );

Table created.

Elapsed: 00:00:00.10
SQL> 
SQL> create index idx_y on t2(y);

Index created.

Elapsed: 00:00:00.03
SQL>

Loading data

SQL> insert into t2 values (1,sysdate-120,'ORACLE');

1 row created.

Elapsed: 00:00:00.02
SQL> insert into t2 values (2,sysdate-90,'ORACLE');

1 row created.

Elapsed: 00:00:00.02
SQL> insert into t2 values (3,sysdate-60,'ORACLE');

1 row created.

Elapsed: 00:00:00.03
SQL> insert into t2 values (4,sysdate-30,'ORACLE');

1 row created.

Elapsed: 00:00:00.01
SQL> insert into t2 values (5,sysdate,'ORACLE');

1 row created.

Elapsed: 00:00:00.02
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>

Lets check the status of my indexes

SQL> select index_name,status from user_indexes where table_name ='T2';

INDEX_NAME                     STATUS                                           
------------------------------ --------                                         
IDX_Y                          VALID                                            
SYS_C0012417                   VALID                                            

Elapsed: 00:00:00.08

As part of archiving strategy lets drop a Partition

SQL> alter table t2 drop partition part2_MAY2013;

Table altered.

Elapsed: 00:00:00.07
SQL> 
SQL> select index_name,status from user_indexes where table_name='T2'
  2  ;

INDEX_NAME                     STATUS                                           
------------------------------ --------                                         
IDX_Y                          UNUSABLE                                         
SYS_C0012417                   UNUSABLE                                         

Elapsed: 00:00:00.04
SQL>

Dropping partition leading to un-usability of indexes, If i create a Global Index, then would be looking forward to use my Global Index though skip_unusable_indexes defaults to true in Oracle 11g. In general archiving strategy can include the below options 

1. Drop Partition with Update Indexes Clause.Update Global Index makes you wait 
2. Truncate partition(Partition wise) with Update Indexes option + Drop Partition
3. Drop Partition + Rebuild the Index 
4. Delete data partition wise + Drop partition - Delete would update the Global Index And the easiest way would be
5. Drop partitions - Table without Global Indexes. 

 Now the question here is if we have to create a table without global index, how would the primary key look like,

SQL> CREATE TABLE t2
  2      (
  3        x   int not null,
  4        dt  date not null,
  5        y   varchar2(30)
  6      )
  7      PARTITION BY RANGE(dt)
  8      (
  9      PARTITION part1_FEB2013 VALUES LESS THAN (to_date('01-Feb-2013','dd-mon-yyyy')) ,
 10      PARTITION part1_MAR2013 VALUES LESS THAN (to_date('01-Mar-2013','dd-mon-yyyy')) ,
 11       PARTITION part1_APR2013 VALUES LESS THAN (to_date('01-Apr-2013','dd-mon-yyyy')) ,
 12       PARTITION part2_MAY2013 VALUES LESS THAN (to_date('01-May-2013','dd-mon-yyyy')),
 13       PARTITION junk VALUES LESS THAN (MAXVALUE)
 14     );

Table created.       

Lets create the unique index on column x

SQL> 
SQL> create unique index idx_xdt on t2(x) local;
create unique index idx_xdt on t2(x) local
                               *
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index 

In just simple words, it means that the partition key and key columns should have a link

SQL> REM - Lets create it in the form of a subset of key columns
SQL> 
SQL> create unique index idx_xdt on t2(x,dt) local;

Index created.
SQL> 
SQL> 
SQL> create index idx_y on t2(y);

Index created.
SQL> 
SQL> 
SQL> insert into t2 values (1,sysdate-120,'ORACLE');

1 row created.

SQL> insert into t2 values (2,sysdate-90,'ORACLE');

1 row created.

SQL> insert into t2 values (3,sysdate-60,'ORACLE');

1 row created.

SQL> insert into t2 values (4,sysdate-30,'ORACLE');

1 row created.

SQL> insert into t2 values (5,sysdate,'ORACLE');

1 row created.

SQL> commit;

Commit complete.

And now their impact over dropping partitions

SQL> 
SQL> select index_name,status from user_indexes where table_name in('T2');

INDEX_NAME                     STATUS                                           
------------------------------ --------                                         
IDX_Y                          VALID                                            
IDX_XDT                        N/A                                              

SQL> 
SQL> select index_name,locality from user_part_indexes where table_name in('T2');

INDEX_NAME                     LOCALI                                           
------------------------------ ------                                           
IDX_XDT                        LOCAL                                            

SQL> 
SQL> 
SQL> alter table t2 drop partition part2_MAY2013;

Table altered.

SQL> 
SQL> select index_name,status from user_indexes where table_name='T2';

INDEX_NAME                     STATUS                                           
------------------------------ --------                                         
IDX_Y                          UNUSABLE                                         
IDX_XDT                        N/A                                              

SQL> 

The actual core issue is GLOBAL INDEX becoming unusable and then they have to be rebuild. It really becomes too time consuming when the table is huge and DBA's would often start praying when they rebuild index

SQL> 
SQL> alter index idx_y rebuild;

Index altered.

SQL> select index_name,status from user_indexes where table_name='T2';

INDEX_NAME                     STATUS                                           
------------------------------ --------                                         
IDX_Y                          VALID                                            
IDX_XDT                        N/A                                              

SQL>

So worried about rebuilding global index post dropping a partition as part of archival strategy, Now lets see what happens when we don't have global index. To demonstrate the same, let me create a table with 133 partitions,

drop table t2;
CREATE TABLE t2
        (
          x   int not null,
          dt  date not null,
          y   varchar2(30)
        )
        PARTITION BY RANGE(dt)
        (
PARTITION part1_JAN2003 VALUES LESS THAN (to_date('01-JAN-2003','dd-mon-yyyy')) ,
PARTITION part1_FEB2003 VALUES LESS THAN (to_date('01-FEB-2003','dd-mon-yyyy')) ,
PARTITION part1_MAR2003 VALUES LESS THAN (to_date('01-MAR-2003','dd-mon-yyyy')) ,
PARTITION part1_APR2003 VALUES LESS THAN (to_date('01-APR-2003','dd-mon-yyyy')) ,
PARTITION part1_MAY2003 VALUES LESS THAN (to_date('01-MAY-2003','dd-mon-yyyy')) ,
PARTITION part1_JUN2003 VALUES LESS THAN (to_date('01-JUN-2003','dd-mon-yyyy')) ,
PARTITION part1_JUL2003 VALUES LESS THAN (to_date('01-JUL-2003','dd-mon-yyyy')) ,
PARTITION part1_AUG2003 VALUES LESS THAN (to_date('01-AUG-2003','dd-mon-yyyy')) ,
PARTITION part1_SEP2003 VALUES LESS THAN (to_date('01-SEP-2003','dd-mon-yyyy')) ,
PARTITION part1_OCT2003 VALUES LESS THAN (to_date('01-OCT-2003','dd-mon-yyyy')) ,
PARTITION part1_NOV2003 VALUES LESS THAN (to_date('01-NOV-2003','dd-mon-yyyy')) ,
PARTITION part1_DEC2003 VALUES LESS THAN (to_date('01-DEC-2003','dd-mon-yyyy')) ,
PARTITION part1_JAN2004 VALUES LESS THAN (to_date('01-JAN-2004','dd-mon-yyyy')) ,
PARTITION part1_FEB2004 VALUES LESS THAN (to_date('01-FEB-2004','dd-mon-yyyy')) ,
PARTITION part1_MAR2004 VALUES LESS THAN (to_date('01-MAR-2004','dd-mon-yyyy')) ,
PARTITION part1_APR2004 VALUES LESS THAN (to_date('01-APR-2004','dd-mon-yyyy')) ,
PARTITION part1_MAY2004 VALUES LESS THAN (to_date('01-MAY-2004','dd-mon-yyyy')) ,
PARTITION part1_JUN2004 VALUES LESS THAN (to_date('01-JUN-2004','dd-mon-yyyy')) ,
PARTITION part1_JUL2004 VALUES LESS THAN (to_date('01-JUL-2004','dd-mon-yyyy')) ,
PARTITION part1_AUG2004 VALUES LESS THAN (to_date('01-AUG-2004','dd-mon-yyyy')) ,
PARTITION part1_SEP2004 VALUES LESS THAN (to_date('01-SEP-2004','dd-mon-yyyy')) ,
PARTITION part1_OCT2004 VALUES LESS THAN (to_date('01-OCT-2004','dd-mon-yyyy')) ,
PARTITION part1_NOV2004 VALUES LESS THAN (to_date('01-NOV-2004','dd-mon-yyyy')) ,
PARTITION part1_DEC2004 VALUES LESS THAN (to_date('01-DEC-2004','dd-mon-yyyy')) ,
PARTITION part1_JAN2005 VALUES LESS THAN (to_date('01-JAN-2005','dd-mon-yyyy')) ,
......
PARTITION part1_DEC2013 VALUES LESS THAN (to_date('01-DEC-2013','dd-mon-yyyy')) ,
PARTITION junk VALUES LESS THAN (MAXVALUE)
);
Table created.

SQL>
SQL> create unique index idx_xdt on t2(x,dt) local;

Index created.
SQL> select partition_count from user_part_tables where table_name = 'T2';

PARTITION_COUNT                                                                 
---------------                                                                 
            133 

Have loaded 12,60,000 records, which is off-course very tiny. Loaded using similar statements like below,

insert into t2 
select level, 
       to_date('01-JUL-2003','dd-mon-yyyy')+ mod(level,30),
       'ORACLE'||mod(level,1000) 
from dual 
connect by level < 10001; 

The records are spread across evenly, like below

SQL> select count(*),y from t2 group by y;

  COUNT(*) Y                                                                    
---------- ------------------------------                                       
      1260 ORACLE90                                                             
      1260 ORACLE101                                                            
      1260 ORACLE102                                                            
      1260 ORACLE107                                                            
      1260 ORACLE110                                                            
      1260 ORACLE118                                                            
      1260 ORACLE124                                                            
      1260 ORACLE132                                                            
      1260 ORACLE141                                                            
      1260 ORACLE157                                                            
      1260 ORACLE163                                                            
      1260 ORACLE171                                                            
      1260 ORACLE172                                                            
      1260 ORACLE177                                                            
      1260 ORACLE181                                                            
      1260 ORACLE185                      

....

Lets create a global index on column- Y

SQL> create index idx_y on t2(y) global;

Index created.
Elapsed: 00:00:31.30
SQL> exec dbms_stats.gather_table_stats('HARI','T2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:44.39
SQL> 

Issue the culprit :)

SQL> update t2 set y='ORACLE_ORACLE' where x = 124587;

1 row updated.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> 

Lets check the selection part,

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:12.13
SQL> 
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.11

SQL> select * from t2 where y='ORACLE_ORACLE';

         X DT        Y                                                          
---------- --------- ------------------------------                             
    124587 28-JUL-04 ORACLE_ORACLE                                              

Elapsed: 00:00:01.37
SQL> 
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID  c6tupfvyvdyaq, child number 0                                           
-------------------------------------                                           
select * from t2 where y='ORACLE_ORACLE'                                        
                                                                                
Plan hash value: 2614863613                                                     
                                                                                
------------------------------------------------------------------------------------------------------------                                                    
| Id  | Operation                          | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                    
------------------------------------------------------------------------------------------------------------                                                    
|   0 | SELECT STATEMENT                   |       |       |       |  1265 (100)|          |       |       |                                                    
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T2    |  1259 | 28957 |  1265   (0)| 00:00:16 | ROWID | ROWID |                                                    
|*  2 |   INDEX RANGE SCAN                 | IDX_Y |  1259 |       |     7   (0)| 00:00:01 |       |       |                                                    
------------------------------------------------------------------------------------------------------------                                                    
                                                                               
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
   2 - access("Y"='ORACLE_ORACLE')                                              

19 rows selected.

Elapsed: 00:00:02.68
SQL> 

Let me drop the Global Index and check the same

SQL> drop index idx_y;

Index dropped.
Elapsed: 00:00:01.68
SQL> 
SQL> select * from t2 where y='ORACLE_ORACLE';

         X DT        Y                                                          
---------- --------- ------------------------------                             
    124587 28-JUL-04 ORACLE_ORACLE                                              

Elapsed: 00:00:05.72
SQL> 
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID  c6tupfvyvdyaq, child number 0                                           
-------------------------------------                                           
select * from t2 where y='ORACLE_ORACLE'                                        
                                                                                
Plan hash value: 2884929167                                                     
                                                                                
--------------------------------------------------------------------------------------------                                                                    
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                    
--------------------------------------------------------------------------------------------                                                                    
|   0 | SELECT STATEMENT    |      |       |       |  1486 (100)|          |       |       |                                                                    
|   1 |  PARTITION RANGE ALL|      |  1260 | 28980 |  1486   (2)| 00:00:18 |     1 |   133 |                                                                    
|*  2 |   TABLE ACCESS FULL | T2   |  1260 | 28980 |  1486   (2)| 00:00:18 |     1 |   133 |                                                                    
--------------------------------------------------------------------------------------------                                                                    
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
   2 - filter("Y"='ORACLE_ORACLE')                                              
19 rows selected.

Elapsed: 00:00:02.47
SQL> 

As expected Partition Elimination doesn't happen and the optimizer has to look into all the partition. Though the elapsed time for FTS + Partition ALL is slightly cheaper in this case, we will have to pay when the table grows and Partition Pruning doesn't happen. Global Indexes helps us in identifying the partition. But for a moment do you think you can go with Local Index, Ok lets check that too :)

SQL> create index idx_local_y on t2(y) local;

Index created.

Elapsed: 00:00:11.94
SQL> select index_name, locality from user_part_indexes where table_name='T2';

INDEX_NAME                     LOCALI                                           
------------------------------ ------                                           
IDX_XDT                        LOCAL                                            
IDX_LOCAL_Y                    LOCAL                                            

Elapsed: 00:00:00.23
SQL> 
SQL> select * from t2 where y='ORACLE_ORACLE';

         X DT        Y                                                          
---------- --------- ------------------------------                             
    124587 28-JUL-04 ORACLE_ORACLE                                              

Elapsed: 00:00:03.26
SQL> 
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID  c6tupfvyvdyaq, child number 0                                           
-------------------------------------                                           
select * from t2 where y='ORACLE_ORACLE'                                        
                                                                                
Plan hash value: 2685712566                                                     
                                                                                
------------------------------------------------------------------------------------------------------------------                                              
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                              
------------------------------------------------------------------------------------------------------------------                                              
|   0 | SELECT STATEMENT                   |             |       |       |  1396 (100)|          |       |       |                                              
|   1 |  PARTITION RANGE ALL               |             |  1259 | 28957 |  1396   (1)| 00:00:17 |     1 |   133 |                                              
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T2          |  1259 | 28957 |  1396   (1)| 00:00:17 |     1 |   133 |                                              
|*  3 |    INDEX RANGE SCAN                | IDX_LOCAL_Y |  1259 |       |   137   (0)| 00:00:02 |     1 |   133 |                                              
------------------------------------------------------------------------------------------------------------------                                              
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
   3 - access("Y"='ORACLE_ORACLE')                                              
                                                                                

20 rows selected.

Elapsed: 00:00:05.22
 

The above is a clear example of reading multiple volumes of a book with each chapter being identified through Book Index :), Got it, i.e, the local index is being used to read all partitions of the table. 

Conclusion 

         Can partitioned table exists without a Single Global Index.Yes, Rebuilding Index is a nightmare in few circumstances, but at what cost, shouldn't i help the optimizer in performing Partition Pruning through Global Index. Dropping Partitions-Wise is definitely the efficient/effective and easiest way to perform data archival(Facilitated through Local Index). Its always Benefit Vs Cost, It is up to the application/architect team is decide to achieve maximum benefit.