Thursday, April 23, 2015

Filtering inner (null-supplying) table in outer join

Let us consider below tables to demonstrate filtering inner table in outer join

SQL> select * from t1;

         A          B
---------- ----------
         1         10
         2         20
         3         30

3 rows selected.

SQL> select * from t2;

         B          C
---------- ----------
        20        200
        30        300
        40        400

3 rows selected.

This is regular outer join where t1 is outer (row preserving) table and t2 is inner (null supplying) table.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1 left outer join t2 on t1.b = t2.b;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         2         20         20        200
         3         30         30        300
         1         10

3 rows selected.

Filter predicate in WHERE clause: It filters from result like below, after performing join operation.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1 left outer join t2 on t1.b = t2.b where t2.c=300;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         3         30         30        300

1 row selected.

Filter predicate in join condition: It filters rows from inner (null supplying) table t2, before performing join operation.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1 left outer join t2 on t1.b = t2.b and t2.c=300;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         3         30         30        300
         2         20
         1         10

3 rows selected.

Here are the equivalent SQLs in Oracle's dialect.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1, t2 where t1.b = t2.b(+) and t2.c=300;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         3         30         30        300

1 row selected.

Observe (+) in filter predicate of t2 below, which actually filters rows before join operation.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1, t2 where t1.b = t2.b(+) and t2.c(+)=300;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         3         30         30        300
         2         20
         1         10

3 rows selected.

Unzip multi part zip archive on Linux

This works with zip command version 3.0 or above (available in RHEL 6 onwards). Here is an example that unzips multi part zip archive of an Informatica software.


# ls -l dac_win_11g_infa_linux_64bit_951*
-rw-r--r-- 1 root root 2097152000 Nov 12  2013 dac_win_11g_infa_linux_64bit_951.z01
-rw-r--r-- 1 root root 2097152000 Nov 12  2013 dac_win_11g_infa_linux_64bit_951.z02
-rw-r--r-- 1 root root 2097152000 Nov 12  2013 dac_win_11g_infa_linux_64bit_951.z03
-rw-r--r-- 1 root root 1060733887 Nov 12  2013 dac_win_11g_infa_linux_64bit_951.zip

Note: there is a hyphen before and after 's' in below command
# zip -s- dac_win_11g_infa_linux_64bit_951.zip --out inf2.zip
 copying: 951HF2_Client_Installer_win32-x86.zip
 copying: 951HF2_Server_Installer_linux-x64.tar
 copying: DAC11gInstaller.zip
 copying: Infa951Docs.zip
 copying: Oracle_All_OS_Prod.key

# unzip inf2.zip
Archive:  inf2.zip
 extracting: 951HF2_Client_Installer_win32-x86.zip  
  inflating: 951HF2_Server_Installer_linux-x64.tar  
 extracting: DAC11gInstaller.zip     
 extracting: Infa951Docs.zip         
  inflating: Oracle_All_OS_Prod.key