Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    3

    Unanswered: PLS_INTEGER Vs. INTEGER or NUMBER

    Hi all,
    I am new to PL/SQL. I was going through some document on PL/SQL. Somewhere i found PLS_INTEGER data type is efficient than INTEGER or NUMBER. Is this true? Can anyone let me know how to check this or some sample code on the same.

    Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4

  3. #3
    Join Date
    May 2009
    Posts
    3
    Ya.. have read this document itself.. I want to test how efficient it is to use PLS_INTEGER rather than using NUMBER or INTEGER in terms of performance.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I want to test how efficient it is to use PLS_INTEGER rather than using NUMBER or INTEGER in terms of performance.
    So run your benchmark tests & post the results just like Tom Kyte.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2009
    Location
    New Delhi - India
    Posts
    22
    PLS_integer is better since it uses the machine's internal representation for operations performed on it whereas tyles like NUMBER and INTEGER use their own representation and thus have some overhead.

  6. #6
    Join Date
    Feb 2009
    Posts
    62
    It's not quite that simple....

    To be fair, the statement that pls_integers are more efficient only applies to addition and substraction in my experience, and only to iterative operations inside that category.

    Here's a test case:
    Code:
    declare
      v_iter      pls_integer := 100000000;
      v_time      pls_integer;
      v_int_val   pls_integer :=1;
      v_num_val   number := 1;
      
      v_int_const pls_integer := 15;
      v_num_const number      := 15;
      
      v_int_const2 pls_integer := 5;
      v_num_const2 number      := 5;  
    begin
    
      v_time := dbms_utility.get_time;
      for i in 1..v_iter loop
        v_int_val := v_int_const + v_iter;
      end loop;
      dbms_output.put_line('Test 1 pls add: '||to_char(dbms_utility.get_time - v_time));
      
      v_time := dbms_utility.get_time;
      for i in 1..v_iter loop
        v_num_val := v_num_const + v_iter;
      end loop;
      dbms_output.put_line('Test 1 num add: '||to_char(dbms_utility.get_time - v_time));  
    
      v_time := dbms_utility.get_time;
      for i in 1..v_iter loop
        v_int_val := v_int_const - v_iter;
      end loop;
      dbms_output.put_line('Test 2 pls subtr: '||to_char(dbms_utility.get_time - v_time));
      
      v_time := dbms_utility.get_time;
      for i in 1..v_iter loop
        v_num_val := v_num_const - v_iter;
      end loop;
      dbms_output.put_line('Test 2 num subtr: '||to_char(dbms_utility.get_time - v_time));  
      
      v_time := dbms_utility.get_time;
      for i in 1..v_iter loop
        v_int_val := v_int_const * v_iter;
      end loop;
      dbms_output.put_line('Test 3 pls mult: '||to_char(dbms_utility.get_time - v_time));
      
      v_time := dbms_utility.get_time;
      for i in 1..v_iter loop
        v_num_val := v_num_const * v_iter;
      end loop;
      dbms_output.put_line('Test 3 num mult: '||to_char(dbms_utility.get_time - v_time));  
      
      v_time := dbms_utility.get_time;
      for i in 1..v_iter loop
        v_int_val := v_int_const / v_int_const2;
      end loop;
      dbms_output.put_line('Test 4 pls div: '||to_char(dbms_utility.get_time - v_time));
      
      v_time := dbms_utility.get_time;
      for i in 1..v_iter loop
        v_num_val := v_num_const / v_num_const2;
      end loop;
      dbms_output.put_line('Test 4 num div: '||to_char(dbms_utility.get_time - v_time));  
    end;
    /
    With this general test of arithmetic functions,I find no difference in the performance, getting output like this:

    Code:
    Test 1 pls add: 121
    Test 1 num add: 121
    Test 2 pls subtr: 120
    Test 2 num subtr: 119
    Test 3 pls mult: 121
    Test 3 num mult: 119
    Test 4 pls div: 120
    Test 4 num div: 120

    Changing the test to an iterative one, where we repeatedly set a variable to itself with an operation performed:
    Code:
    declare
      v_iter      pls_integer := 100000000;
      v_time      pls_integer;
      v_int_val   pls_integer :=1;
      v_num_val   number := 1;
      
      v_int_const pls_integer := 15;
      v_num_const number      := 15;
      
      v_int_const2 pls_integer := 5;
      v_num_const2 number      := 5;  
    begin
    
      v_time := dbms_utility.get_time;
      for i in 1..v_iter loop
        v_int_val := v_int_val + 1;
      end loop;
      dbms_output.put_line('Test 1 pls add: '||to_char(dbms_utility.get_time - v_time));
      
      v_time := dbms_utility.get_time;
      for i in 1..v_iter loop
        v_num_val := v_num_val + 1;
      end loop;
      dbms_output.put_line('Test 1 num add: '||to_char(dbms_utility.get_time - v_time));  
    
      v_time := dbms_utility.get_time;
      for i in 1..v_iter loop
        v_int_val := v_int_val - 1;
      end loop;
      dbms_output.put_line('Test 2 pls subtr: '||to_char(dbms_utility.get_time - v_time));
      
      v_time := dbms_utility.get_time;
      for i in 1..v_iter loop
        v_num_val := v_num_val - 1;
      end loop;
      dbms_output.put_line('Test 2 num subtr: '||to_char(dbms_utility.get_time - v_time));  
      
      v_time := dbms_utility.get_time;
      for i in 1..v_iter loop
        v_int_val := v_int_val * 1;
      end loop;
      dbms_output.put_line('Test 3 pls mult: '||to_char(dbms_utility.get_time - v_time));
      
      v_time := dbms_utility.get_time;
      for i in 1..v_iter loop
        v_num_val := v_num_val * 1;
      end loop;
      dbms_output.put_line('Test 3 num mult: '||to_char(dbms_utility.get_time - v_time));  
      
      v_time := dbms_utility.get_time;
      for i in 1..v_iter loop
        v_int_val := v_int_val / 1;
      end loop;
      dbms_output.put_line('Test 4 pls div: '||to_char(dbms_utility.get_time - v_time));
      
      v_time := dbms_utility.get_time;
      for i in 1..v_iter loop
        v_num_val := v_num_val / 1;
      end loop;
      dbms_output.put_line('Test 4 num div: '||to_char(dbms_utility.get_time - v_time));  
      
    end;
    /
    We get quite different results:
    Code:
    Test 1 pls add: 194
    Test 1 num add: 576
    Test 2 pls subtr: 308
    Test 2 num subtr: 591
    Test 3 pls mult: 126
    Test 3 num mult: 126
    Test 4 pls div: 798
    Test 4 num div: 126

    Addition is much faster with pls_ints, subtraction a bit faster, multiplication is the same, and division is much slower.

    All in all, quite a complex situation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •