Choosing Column Data Type

http://www.itpub.net/thread-1938117-1-1.html

1. CHAR vs. VARCHAR2

It's always a good idea to choose VARCHAR2, because CHAR appends spaces to the string shorter than
defined length wasting storage. There's no storage or performance advantage in using CHAR. The only
"benefit" of CHAR is to save typing. If you have many VARCHAR2 fields and a few string types of
length 1 (e.g. columns sex, employee_status), CHAR(1) stands out when you describe the table in
Sqlplus. Since it's not possible to waste storage for CHAR(1), that's the only time it makes sense
to use CHAR. Benefits are as trivial as that. (Why is CHAR a data type in Oracle at all? Maybe
required by some SQL or RDBMS standard.)

2. CLOB vs. VARCHAR2

LOB is more cumbersome to manipulate than plain VARCHAR2, and (maybe up to the recent SecureFiles
LOBs) less performant. Its internal storage and management mechanism is quite different from those
of other data types. Unless your data is definitely longer than 4000 bytes, use VARCHAR2.

3. NUMBER precision and scale

If there's no requirement on precision or scale, there's no need to specify that. It's not uncommon
to see developers constantly requesting for expansion of the precision of NUMBER (and width of
VARCHAR2 as well). It's an unnecessary hassle.

4. LONG, LONG RAW

There was report of better performance for LONG/LONG RAW than CLOB/BLOB many years ago. If it was
true, it probably no longer is, especially when you decide to use SecureFiles LOB, which becomes
default in 12c. Due to numerous restrictions on LONG/LONG RAW, never use them. Consider LOB, and if
the data is text and shorter than 4000 bytes, use VARCHAR2.

5. BINARY_DOUBLE, BINARY_FLOAT

It's possible to get better performance by storing data as these types if your app does floating
point calculation a lot.

6. DATE vs. TIMESTAMP

If your data must be accurate down to millisecond, use TIMESTAMP. Otherwise use DATE. The storage
for TIMESTAMP is 11 bytes (when millisecond part of the data is not 0 i.e. is present) while that
for DATE is 7.


Appendix: Store data in files or database?

Store file data on the file system. A relational database is best used to store relational data,
including metadata about the files, so that they can be joined and cross-referenced to other data.
The data in files don't belong to this category. Secondly, RDBMS backup has a higher standard on
consistency and is generally more resource intensive.  Leaving files on the file system makes
backup easier, and restore way much easier and faster. Lastly, LOB is more cumbersome to manipulate
than plain varchar2 and number types, and (maybe up to the recent SecureFiles LOBs) less performant.

The benefit of storing file data inside the database is that you can bring the data anywhere in one
go, and in case of RAC, you don't need to worry about which node is used to store the files, if
they must be on the database server.  But the first benefit is too trivial. The second is
irrelevant in most cases since the files are often accessed through a Web server, the natural
location for those files.