My question is based on the following: I have a code block in which I have defined a varchar string, like this:
v_string varchar(100);
My code block is something like this:
...
v_string := 'x = ' || x || ', y = ' || y;
...
That is, v_string is a text that I'm using to log x and y values.
Can anyone explain how Oracle stores such concatenated strings? I mean, does it put these in a heap? If so, will (can) these be garbage collected? Does that affect performance too much?
Thanks!
There will be no interim objects created so there will be nothing to garbage collect.
When you declare v_string, Oracle allocates 100 bytes of storage (assuming your NLS_LENGTH_SEMANTICS is the default of BYTE) for the string (larger strings will not be pre-allocated like this and what constitutes a "larger string" is version dependent. In 11.2, the limit appears to be 4000 bytes though it used to be 2000 bytes and I'm not sure when it changed). Since the 100 byte buffer has been pre-allocated, it's easy enough to concatenate various other strings together in that pre-allocated buffer. There is no need to allocate any additional space to store interim results so there are no resources to free up.
At some point, v_string will go out of scope and the buffer that was allocated for it will be freed, of course. But that will be at the end of the block where v_string is declared.