11g: Mixed Name + Positional Notations

Changes with 11g:

• you can mix named with positional parameters in a call.  
• the SQL code matches the PL/SQL code.

Notes:

• Exclusionary Notation: one or more can be excluded if optional. (When formal parameters have defaults they are optional.)
• You still can't call a function from SQL when any of its formal parameters are defined as IN OUT or OUT mode–only variables. 
• Mixed must list all positional parameters first.

CREATE OR REPLACE FUNCTION Family
     ( Boy Varchar2  := 'Mickey'
     , Girl Varchar2 := 'Minney'
     , Dog Varchar2  := 'Pluto' ) 
                       RETURN Varchar2 IS
BEGIN
     RETURN Boy||'+'||Girl||'+'||Dog;
END;
/

Column Notation Format A15
Column Family   Format A15

Select 'Positional'           As Notation,
       Family('Dick'
             ,'Jane'
             ,'Spot')         As Family
From Dual;

Select 'Named'                As Notation,
       Family(Girl => 'Jane'
             ,Dog  => 'Spot'
             ,Boy  => 'Dick') As Family
From Dual;

Select 'Mixed'                As Notation,
       Family(        'Dick'
             ,Dog  => 'Spot'
             ,Girl => 'Jane') As Family
From Dual;

Select 'Exclusionary'         As Notation,
       Family(        'Dick'
             ,Girl => 'Jane') As Family
From Dual;

11g: SIMPLE_INTEGER is faster than PLS_INTEGER

Reading the improvements on 11g, I came across these new data types. They have NOT NULL constraints and should be faster.

• SIMPLE_INTEGER - based on PLS_INTEGER
• SIMPLE_FLOAT  - based on BINARY_FLOAT
• SIMPLE_DOUBLE - based on BINARY_DOUBLE.

With a basic time test, I did find SIMPLE_INTEGER to be faster, and I don't know where I go wrong on the others, but they're are actually running slower for me.  Also, I'm suppose to see a difference if  plsql_code_type is native instead of interpreted.  At least with this test, it was incredible minor.  Anyway, here's some code you can copy, and test with.  If you know why they're not all faster, please let me know.

ALTER SESSION SET plsql_code_type = 'NATIVE'; -- or INTERPRETED

SET SERVEROUTPUT ON
DECLARE
    v_pls_integer    PLS_INTEGER    := 0;
    v_simple_integer SIMPLE_INTEGER := 0;
    v_start          NUMBER;
    v_end            NUMBER;
    v_pls_time       NUMBER;
    v_simple_time    NUMBER;
    v_loops          NUMBER         := 90000000;
BEGIN
    ----------------------------------------------------------
    -- SIMPLE_INTEGER test (New datatype)
    ----------------------------------------------------------
    v_start := DBMS_UTILITY.GET_TIME;
    
    FOR i in 1..v_loops LOOP
        v_simple_integer := v_simple_integer + 1;
    END LOOP;
    
    v_end         := DBMS_UTILITY.GET_TIME;
    v_simple_time := v_end - v_start;
    
    DBMS_OUTPUT.PUT_LINE ( 'The new Simple_Integer took '
                        || (v_end - v_start)
                        || q'[ 100th's of a second.]');
    ----------------------------------------------------------
    -- PLS_INTEGER TEST (Old datatype)
    ----------------------------------------------------------
    v_start := DBMS_UTILITY.GET_TIME;
    
    FOR i in 1..v_loops LOOP
        v_pls_integer := v_pls_integer + 1;
    END LOOP;
    
    v_end      := DBMS_UTILITY.GET_TIME;
    v_pls_time := v_end - v_start;
    
    DBMS_OUTPUT.PUT_LINE ( 'The old Pls_Integer took    '
                        || (v_end - v_start)
                        || q'[ 100th's of a second.]');
    ----------------------------------------------------------
    -- Calculate 
    ----------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE ( 'With'
                        || To_char(v_loops,'99,999,999')
                        || ' iterations, there was a '
                        || Round(100 - v_Simple_Time/v_Pls_Time * 100,1)
                        ||'% improvement.');
END;
/

Tiny URL: http://tinyurl.com/simpleinteger