Tuesday, October 22, 2013

Load mutliple files using multiple load options in sql loader

Learnt something new in Oracle, its all about handling mutliple load options for loading into multiple table using sql-loader.

Here is a quick demo of what i have.

rajesh@ORA10GR2> select * from t1;

no rows selected

rajesh@ORA10GR2> select * from t2;

         X          Y
---------- ----------
         2          1

1 row selected.

rajesh@ORA10GR2>

D:\app\179818\product\11.2.0\dbhome_1\BIN>sqlldr rajesh/oracle@iradsnvl control=d:\ctl.txt log=d:\log.txt data=d:\data.txt

SQL*Loader: Release 11.2.0.3.0 - Production on Tue Oct 22 13:58:52 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

D:\app\179818\product\11.2.0\dbhome_1\BIN>exit

rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from t1;

         X          Y
---------- ----------
         1          1
         1          2
         1          3

3 rows selected.

rajesh@ORA10GR2> select * from t2;

         X          Y
---------- ----------
         2          4

1 row selected.

rajesh@ORA10GR2>

now the data got loaded into T1 but got updated in T2.

Here is what i have in Control file ( Table T1 in TRUNCATE option and T2 in REPLACE option)

D:\>type ctl.txt
LOAD DATA
INTO TABLE T1
TRUNCATE
WHEN X='1'
FIELDS TERMINATED BY ','  TRAILING NULLCOLS
(
        X ,
        Y
)
INTO TABLE T2
REPLACE
WHEN X='2'
FIELDS TERMINATED BY ','  TRAILING NULLCOLS
(
        X position(1) ,
        Y
)

and data file contents are here.

D:\>type data.txt
1,1
1,2
1,3
2,4

Monday, October 14, 2013

Subprogram Inlining and plsql optimization in 11g

One of the most important optimization that happened to Oracle 11g plsql compiler is Sub-program in-lining. more about sub-program in-lining are available in docs.  In short its a new plsql enhancement in compiler to replace all sub-program invocation with a copy of invoked sub-program ( given that both invoking and invoked subprogram are in same plsql unit ). Which can happen by default if plsql_optimize_level is set to 2 or 3

with plsql_optimize_level=2 you must specify each subprogram to be inlined with INLINE pragma
with plsql_optimize_level=3 the plsql optimizer seeks opportunities to inline subprograms.

Consider a simple routine which validates a the input string is purely numerical or not.

rajesh@ORA11GR2> create or replace procedure p
  2  as
  3     function is_number(x in varchar2)
  4     return varchar2 as
  5             y number ;
  6     begin
  7             y := to_number(x);
  8                     return 'Y';
  9             exception
 10                     when others then
 11                     return 'N' ;
 12     end;
 13  begin
 14     dbms_output.put_line (' Results for 1A is = '|| is_number('1A') );
 15     dbms_output.put_line (' Results for 12 is = '|| is_number('12') );
 16  end;
 17  /

Procedure created.

rajesh@ORA11GR2>
rajesh@ORA11GR2> exec p ;
 Results for 1A is = N
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2>

When plsql_optimize_level set to 3 the execution changes like this.

rajesh@ORA11GR2> alter procedure p compile plsql_optimize_level=3;

Procedure altered.

rajesh@ORA11GR2> exec p ;
 Results for 1A is = Y
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2>

so in short what happened during plsql_optimize_level=3 is

a) to_number is pure and deterministic
b) the variable y in code is assigned but never used, so its dead. Its a dead code.
c) therefore the above code can be reduced to:

create or replace procedure p
as
    function is_number(x in varchar2)
    return varchar2 as
    begin
        return 'Y';
    end;
begin
    dbms_output.put_line (' Results for 1A is = '|| is_number('1A') );
    dbms_output.put_line (' Results for 12 is = '|| is_number('12') );
end;   
/


the way to code the "is_datatype" function - knowing that to_date/to_number are pure deterministic functions - is to include that non-inlinable function call

rajesh@ORA11GR2> create or replace procedure p
  2  as
  3     function is_number(x in varchar2)
  4     return varchar2
  5     as
  6             y number;
  7     begin
  8             y := to_number(x);
  9             return 'Y';
 10             exception
 11                     when others then
 12                             return 'N' ;
 13     end;
 14  begin
 15     pragma inline(is_number,'NO');
 16     dbms_output.put_line (' Results for 1A is = '|| is_number('1A') );
 17     dbms_output.put_line (' Results for 12 is = '|| is_number('12') );
 18  end;
 19  /

Procedure created.

rajesh@ORA11GR2>
rajesh@ORA11GR2> alter procedure p compile plsql_optimize_level=2;

Procedure altered.

rajesh@ORA11GR2> exec p;
 Results for 1A is = N
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2> alter procedure p compile plsql_optimize_level=3;

Procedure altered.

rajesh@ORA11GR2>
rajesh@ORA11GR2> exec p ;
 Results for 1A is = N
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2>

Another valid approach would be like this

rajesh@ORA11GR2> create or replace procedure p
  2  as
  3     function is_number(x in varchar2)
  4     return varchar2
  5     as
  6             y number;
  7     begin
  8             y := to_number(x);
  9             if y is not null then
 10                     return 'Y';
 11             else
 12                     return 'N';
 13             end if;
 14             exception
 15                     when others then
 16                     return 'N' ;
 17     end;
 18  begin
 19     dbms_output.put_line (' Results for 1A is = '|| is_number('1A') );
 20     dbms_output.put_line (' Results for 12 is = '|| is_number('12') );
 21  end;
 22  /

Procedure created.

rajesh@ORA11GR2>
rajesh@ORA11GR2> alter procedure p compile plsql_optimize_level=2;

Procedure altered.

rajesh@ORA11GR2> exec p;
 Results for 1A is = N
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2> alter procedure p compile plsql_optimize_level=3;

Procedure altered.

rajesh@ORA11GR2> exec p;
 Results for 1A is = N
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2>