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
No comments:
Post a Comment