Oracle VARCHAR2 using the max length
I am working on oracle 11g and I am wondering if it's ok to put the length of the text column as max as it can be(4000 byte), as the oracle engine will allocate the used length only. or this action will effect the performance of the application that will read from this DB .
briefly: if the oracle engine will not allocate the 4000 byte why not to use it always.
Best Regards.
Answer
You may want to validate that the string is not longer for reasons other than database storage.
Not every constraint is about performance.
If you have text that is getting close to 4000 bytes, maybe a VARCHAR2 column is not the best choice. If you need text that long, you probably want it to get even longer, so maybe a CLOB is better.
If you intend to "use" this text in the database (as join columns or in indexes), it should probably not be getting anywhere near that long.