Oracle SQL VARCHAR2 datatype storage

Question

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?

Answer 1

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.

Answer 2

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.