Oracle SQL VARCHAR2 datatype storage

3
votes

From Oracle docs :

If you give every column the maximum length or precision for its data type, then your application needlessly allocates many megabytes of RAM. For example, suppose that a query selects 10 VARCHAR2(4000) columns and a bulk fetch operation returns 100 rows. The RAM that your application must allocate is 10 x 4,000 x 100—almost 4 MB. In contrast, if the column length is 80, the RAM that your application must allocate is 10 x 80 x 100—about 78 KB. This difference is significant for a single query, and your application will process many queries concurrently. Therefore, your application must allocate the 4 MB or 78 KB of RAM for each connection.

As I know varchar2 is variable length datatype, so DB will only allocate space actually used by column, i.e. if column is only 10 character in Unicode it will allocate 10 bytes. But according to above statement even if column (max) is only 10 character, but length of datatype is defined as 4000, it will still occupy 4000 bytes?

3
sqloracle
How is your application going to know before the SELECT is done how many characters you've actually used of that VARCHAR(4000)? It can't, so it has to allocate enough to accept the entire VARCHAR(4000). You're reading that paragraph wrong; it refers to space your app must allocate to read the data, not the storage actually used in the DB. - Ken White
Yes, the DB will use the declared length and this amount of data will be returned to your app even if it is not all being used. - Tim Biegeleisen
@TimBiegeleisen . . . Is that really true for varchar2? If so, why use it instead of char? - Gordon Linoff
Hi Gordon, maybe I misread the quoted documentation and perhaps you should give an answer. - Tim Biegeleisen
@KenWhite I don't understand why we are talking about 'application', while this process is happening inside DB, before it leaves it's boundaries in the first place. I believe DB engine will get result set and send them over the wire to DB client in chunks anyway, so it is dynamically allocated. And is it that hard for such sophisticated software as Oracle DB to find out that my column is not actually 4000 bytes, but actually is only 10 bytes, considering it is not blankpaded, as in case of char. Indeed DB know number of bytes it fetched into results and it can notify client about that. - joe.d

3 Answers

1
votes

The space allocated on disk will only be as long as required to store the actual data for each row.

The space allocated in memory will (in some cases) be the maximum required based on the datatype.

1
votes

The documentation itself is wrong/misleading in several ways. The sentence right before the quoted paragraph says "...length and precision affect storage requirements." And yet, right after that, the dufus who wrote the documentation article goes on to refer to RAM. Storage means on disk; RAM is memory. Unless we are talking about an in-memory database (which that documentation article does not), it makes no sense to talk about RAM after saying something "affects storage requirements." The declared length does NOT affect storage, but it MAY affect memory allocation.

Specifically, it MAY affect memory allocation when an application (often written in general languages like Java, C#, etc.) need to allocate memory ahead of time, when the only info they have is what's in the data dictionary. Memory can be allocated statically (at compilation time), but that means you can't use the extra info from the actual data, that all your strings are 100 bytes at most; all that is known AT THAT STAGE is 4000 bytes max. Memory can also be allocated DYNAMICALLY, and that can use the extra info - but it is MUCH, MUCH slower!

In many "interactions" between the DB and applications written in other languages, you don't even have the option of dynamic memory allocation; in the present world, the assumption is that "time" is worth much, much more than RAM, so if you find that your code runs out of memory, buy more RAM and don't worry about dynamic memory allocation. Which means that if you declare VARCHAR2(4000), you should expect that a lot of RAM will be allocated, potentially, in a wasteful way. Just declare VARCHAR2(100) if that's all you need.

0
votes

The source for that interesting question is here.

The article is very clear about VARCHAR2 storage:

Oracle Database blank-pads values stored in CHAR columns but not values stored in VARCHAR2 columns. Therefore, VARCHAR2 columns use space more efficiently than CHAR columns.

What they are saying about the RAM allocation is that your application would not know how much RAM to allocate if you had NOT defined a limit for your VARCHAR2 column. Also, if the limit is too high, it would allocate too much RAM to start with, so always choose the most efficient limit.

There is also a comprehensive article about the OCI usage of data types here.