| wasted space from varchar columns being "too" long? [message #122174] |
Fri, 03 June 2005 09:43  |
wmm_ora
Messages: 1
Registered: June 2005
|
Junior Member |
|
|
Hi-
I have what I think is probably a novice question, and would be grateful for any pointers, be it ORA documentation or an explicit opinion/answer.
If I have a table with a varchar column that can hold, say, N characters, and I plan to split the storage of larger 'documents' into multiple rows, it seems my worst case wasted 'space' would be N-1 characters (ie: the document has 2*N+1 characters to store, for instance, populating 2 rows of data completely, and 1 row with just one character). My question is: would I actually save space by storing my documents in columns of smaller size (say, K, K << N), so that the max 'wasted' space per document is K-1? How and when would Oracle (i am using 9.2.0.5) 'claim' the required storage space for the document records, and does the mere fact that a column could hold more data than is currently stored in it 'waste' and table/disk space? Are there performance considerations, and how would I calculate the 'sweet' spot between optimized storage and optimized data retrieval/query performance?
thanks,
W
|
|
|
|
| Re: wasted space from varchar columns being "too" long? [message #122363 is a reply to message #122174] |
Mon, 06 June 2005 03:17  |
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
|
Member |
|
|
-Why are you using VARCHAR?
-What you will be storing in that column?
-What is the expected size of data for that column?
-Can you provide me with test data to check on my system? or a very firm example?
VARCHAR Datatype
The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. Oracle recommends that you use VARCHAR2 rather than VARCHAR. In the future, VARCHAR might be defined as a separate datatype used for variable-length character strings compared with different comparison semantics
VARCHAR2 Datatype
The VARCHAR2 datatype specifies a variable-length character string. When you create a VARCHAR2 column, you supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the column's maximum length. If you try to insert a value that exceeds the specified length, then Oracle returns an error.
You must specify a maximum length for a VARCHAR2 column. This maximum must be at least 1 byte, although the actual string stored is permitted to be a zero-length string (''). You can use the CHAR qualifier, for example VARCHAR2(10 CHAR), to give the maximum length in characters instead of bytes
Row Directory
This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area).
After the space has been allocated in the row directory of a data block's overhead, this space is not reclaimed when the row is deleted. Therefore, a block that is currently empty but had up to 50 rows at one time continues to' have 100 bytes allocated in the header for the row directory. Oracle reuses this space only when new rows are inserted in the block.
Overhead
The data block header, table directory, and row directory are referred to collectively as overhead. Some block overhead is fixed in size; the total block overhead size is variable. On average, the fixed and variable portions of data block overhead total 84 to 107 bytes.
Row Data
This portion of the data block contains table or index data. Rows can span blocks.
|
|
|
|
| Re: wasted space from varchar columns being "too" long? [message #122436 is a reply to message #122174] |
Mon, 06 June 2005 09:17 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I'm not sure I understand your purpose for breaking the varchar2 column apart in the first place? You have documents? What kind of documents? A varchar2 column can hold a good bit (I think it is 4KB, but don't quote me on that). There won't be any wasted space though, because it doesn't reserver that 4096, or 100, or 30, or however many; it only uses the space needed to hold the data that you put into the column. (incidentally, a number works in a similar way in this regard).
But, if you truly have documents, like say word documents or large text files, you may want to consider using BLOB's or CLOB's. Check into the oracle documentation on those datatypes. They have advantages in that they are designed specifically to handle large objects. For instance, a normal column would be stored right in the row in which it belongs. But a large object can be stored elsewhere (out-of-line) which would help performance wise if you need to scan columns in a table but not the large object column.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
