Home » SQL & PL/SQL » SQL & PL/SQL » PLS_INTEGER Vs NUMBER data type
PLS_INTEGER Vs NUMBER data type [message #404486] Fri, 22 May 2009 00:06 Go to next message
Shweta.Hegde
Messages: 1
Registered: May 2009
Junior Member
Hi all,
I am new to PL/SQL. I was going through some document on PL/SQL and got to know that PL/SQL PLS_INTEGER data type is more efficient than NUMBER data type. Can any one suggest me where will i get some sample programs to test this.
Re: PLS_INTEGER Vs NUMBER data type [message #404488 is a reply to message #404486] Fri, 22 May 2009 00:12 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://www.dbforums.com/oracle/1642830-pls_integer-vs-integer-number.html

Same post?

Sriram
Re: PLS_INTEGER Vs NUMBER data type [message #404490 is a reply to message #404488] Fri, 22 May 2009 00:19 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
Can any one suggest me where will i get some sample programs to test this.


Try this.It will show you how it is.....


http://www.oracleplsqlprogramming.com/oppnews_200512.html

Thanks & Regards
Sriram
Re: PLS_INTEGER Vs NUMBER data type [message #404491 is a reply to message #404486] Fri, 22 May 2009 00:21 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> I was going through some document on PL/SQL and got to know that PL/SQL PLS_INTEGER data type is more efficient than NUMBER data type.

Post URLs.
PLS INTEGERS do not persist across DB restarts.
How is that more efficient than NUMBER?
Re: PLS_INTEGER Vs NUMBER data type [message #404542 is a reply to message #404491] Fri, 22 May 2009 03:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd have to observe that a variable of type NUMBER, used in any context that a pls_integer can be used in also does not persist across a db restart.

What exactly were you trying to say?
Re: PLS_INTEGER Vs NUMBER data type [message #404550 is a reply to message #404542] Fri, 22 May 2009 04:33 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
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:
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:
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:
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:
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.

Previous Topic: Sequence having number with character
Next Topic: Getting max value without using group by (merged 3)
Goto Forum:
  


Current Time: Sun Dec 04 08:55:05 CST 2016

Total time taken to generate the page: 0.06983 seconds