Is there any drawback (except allowing the value or array to grow too large) to setting the max integer size of a VARCHAR or VARRAY to a value significantly larger than actually necessary (or setting it to the max size allowed)?
A:
Just don't use varray, use nested table. Nested tables can hold an arbitry number of elements.
Using varchar2(4000) instead of varchar2(20) in a table definition doesn't really hurt because Oracle doesn't claim this space when it isn't needed.
tuinstoel
2009-07-01 18:31:29
I'm considering using a VARRAY of BINARY_FLOAT's to store raw waveforms with tens of thousands of data points. If we decide to purchase new DAQ equipment which can read data faster, the number could easily exceed a hundred thousand.I'm just looking to plan ahead. I just didn't know if I'd lose efficiency with a VARRAY with a max size much larger than I need now.
Steven
2009-07-01 18:36:01
Read here: http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm#i35815
tuinstoel
2009-07-01 18:54:47
Note from the above referece: "You cannot rely on the order and subscripts of a nested table remaining stable as the nested table is stored in and retrieved from the database, because the order and subscripts are not preserved in the database." If that doesn't bother you then go for it. VARRAY doesn't store unused array entries either, so a VARRAY(100000) with one element doesn't cost any more storage than a VARRAY(10) with one element.
DCookie
2009-07-01 23:27:10
-1 because of the second paragraph. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:54274298311615
Rob van Wijk
2009-07-02 13:41:22
+1
A:
If you're talking about a column in a database table, it is advisable to set the maximum length of a VARCHAR2 to the minimum length allowable by the requirements - like any other constraint, it helps to use these inbuilt features of the database to ensure only valid data gets saved (e.g. if a surname with 1000 characters is input I'm pretty sure that will be wrong, and perhaps highlight a bug in a program somewhere).
On the PL/SQL side, it may interest you to know that there may be memory (PGA) usage differences depending what size you declare your strings in your PL/SQL programs. Internally, there is a threshold at 2000 bytes, where the PL/SQL engine switches between two different memory allocation schemes. e.g. the following declaration:
DECLARE v VARCHAR2(2000); BEGIN...
will allocate 2000 bytes in the user's memory area, whereas:
DECLARE v VARCHAR2(2001); BEGIN...
will only allocate memory when a value is assigned, and will only allocate as much memory as is required to hold the value assigned to it.
Jeffrey Kemp
2009-07-02 12:53:55