| PLS_INTEGER Vs NUMBER data type [message #404486] |
Fri, 22 May 2009 00:06  |
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 #404542 is a reply to message #404491] |
Fri, 22 May 2009 03:56  |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
