2

What is the best datatype to use for storing the value returned from a PL/SQL ascii() function call? Example...

declare
x number; /* number, pls_integer, binary_integer, int, etc...? */
begin
    x := ascii('a');
end;
/

This will be happening in a number of very low-level functions that will end up being called within loops, so speed and storage need to be considered. For PL/SQL number storage we have these datatypes...

BINARY_INTEGER
BINARY_INTEGER Subtypes... NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE

NUMBER
NUMBER Subtypes... DEC, DECIMAL, DOUBLE, PRECISION, FLOAT, INTEGER, INT, NUMERIC, REAL, and SMALLINT

PLS_INTEGER

Because of this statement I'm leaning towards using PLS_INTEGER:

You use the PLS_INTEGER datatype to store signed integers. Its magnitude range is -2**31 .. 2**31. PLS_INTEGER values require less storage than NUMBER values. Also, PLS_INTEGER operations use machine arithmetic, so they are faster than NUMBER and BINARY_INTEGER operations, which use library arithmetic. For efficiency, use PLS_INTEGER for all calculations that fall within its magnitude range.

Would you agree that this is the best datatype to use, or is there something I'm not considering? Thanks!

gfrobenius
  • 3,987
  • 8
  • 34
  • 66
  • [PLS_INTEGER is a subtype of BINARY_INTEGER now](http://stackoverflow.com/a/7412823/266304), unless you're still on 9i; this also came up recently [in comments](http://stackoverflow.com/questions/22978970/100-strings-in-in-operator-oracle-pl-sql/22979502#comment35137096_22979502). So since [they are identical](http://docs.oracle.com/cd/E11882_01/appdev.112/e17126/datatypes.htm#LNPLS319) either would do, and `POSITIVE` ought to work the same in this case too as far as I can see. Why not test it though? – Alex Poole May 01 '14 at 16:56
  • 3
    IMO you're doing the wrong thing. The best time to optimize is never. The second-best time to optimize is when you have a performance problem. The WORST time to optimize is when you have no running code. Use NUMBER. If it doesn't work (fast enough) address it once it's an issue. In many years of PL/SQL development I have *never* had a performance problem caused by choice of data type. YMMV. Share and enjoy. – Bob Jarvis - Слава Україні May 01 '14 at 17:20
  • 2
    I think you should consider [documentation](http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions013.htm) that states: _The value returned is of data type NUMBER._ If you're using other data type you'll introduce implicit data type conversion. I also agree with @BobJarvis about the optimization. See also e.g. (The Rules of Code Optimization)[http://blogs.msdn.com/b/audiofool/archive/2007/06/14/the-rules-of-code-optimization.aspx]. – user272735 May 01 '14 at 17:40

1 Answers1

1

PLS_INTEGER (now the same as BINARY_INTEGER) will yield the fastest performance (and so will buying a new server :) )

Really to see the performance gain, you would need be looping like crazy.

This says it all: http://www.oracle-base.com/articles/misc/performance-of-numeric-data-types-in-plsql.php

Brian McGinity
  • 5,777
  • 5
  • 36
  • 46
  • Inside the real function there is a total of 4 `ascii()` calls on the same char. So I guess the better question is, what's faster, doing 4 `ascii()` calls or doing 1 and assigning it to a variable which then gets read 4 times? Probably the latter. But yeah, not really sure how to prove it, would have to be millions of loop tests. I guess I'm going with `PLS_INTEGER` for now. Thanks! – gfrobenius May 01 '14 at 19:05