4

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.

Hany
  • 1,146
  • 4
  • 18
  • 26
  • 1
    Besides limiting lengths to keep data consistent; if you want to use multiple fields in an index, you'll hit [`ORA-01450`](http://www.dba-oracle.com/t_ora_01450_maximum_key_length_exceeded.htm) if the combined fields lengths are too high. – Joachim Isaksson Jun 17 '14 at 08:21
  • possible duplicate of [Impact of defining VARCHAR2 column with greater length](http://stackoverflow.com/questions/1882073/impact-of-defining-varchar2-column-with-greater-length) – Thilo Jun 17 '14 at 08:24
  • 1
    That linked duplicate has an interesting aspect: client applications may reserve unnecessarily large buffers when reading a VARCHAR2(4000) column that only needs to be VARCHAR2(20). – Thilo Jun 17 '14 at 08:26

1 Answers1

0

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.

Thilo
  • 257,207
  • 101
  • 511
  • 656