Original address: http://blog.sina.com.cn/s/blog_5014663501007n40.html
in the design of the database, choose the right data type, often can avoid a lot of problems, correctly understand the type of database, for storage space planning, application performance adjustment will be very helpful, here is my personal summary:
1.
CharFixed-length format string, stored in the database when the insufficient number of bits to fill the space, is not recommended to use, will bring unnecessary trouble A, string comparison, if you do not pay attention to (char less bit padding) will bring error B, string comparison, if using the trim function, This will invalidate the index on the field (sometimes causing serious performance problems) C, waste storage space
2.
Varchar2/varcharIndefinite long format string, for a string within 4000 bytes, it is recommended to use the type A, online has said Char better than varchar2 performance, but if you are interested in testing, you will find no difference (if a row migration, can be adjusted by Pctfree) B, make full use of storage space
3.
Long/long RawOracle is obsolete, just to keep backwards compatible, should all upgrade to Loblong type has a lot of restrictions a, the table can only have one column long type B, the long type does not support distributed transaction C, too many queries can not be used on long
4.
NumberThe method of defining number: Number (p,s) where p,s are optional: A, p for precision, the default is 38b, s for the number of decimal digits, the value range -84~127, the default value depends on whether p is specified, if p is established, the default s is 0, if no p is specified, the default is the maximum value. Several examples: A, number (5,0) =number (5) value range 99999~-99999b, number (5,2) range 999.99~-999.99
Note: The number of integers is only 3 bits and the number of decimal digits is 2 bits, calculated as follows:
Integer number of digits <=p-s
Number of decimal digits <=s
If the Insert 123.555 store becomes 123.56 in the database (rounded on the third bit of the decimal), if you insert 999.999, the database will be left in error. C, Number (5,-2) value range 9999900~-9999900 (integer digits <=p-s, no decimal digits)
If the Insert 9999949 store becomes 9999900 in the database (rounded on the second bit of the integer), if you insert 9999950, the database will be left in error. The other numeric types are derived from number, and the underlying numbers are, for example, Integer/int fully mapped to (38) performance-Related: # is a type of soft implementation, if you need to do a complex operation on number, It is recommended that you first convert number to floating-point type with cast built-in functions another thing to note is that number is a variable-length type, so remember when calculating the table storage space
5.
DateThe date type is a 7-byte fixed-length data type, nothing to say, an example: Performance a>b>c
A, wheredate_colum>=to_date (' 01-jan-2007 ', ' dd-mon-yyyy ')
and date_colum<div>
B, Wheretrunc (Date_colum, ' y ') =to_date (' 01-jan-2007 ', ' dd-mon-yyyy ')
C, Where To_char (date_colum, ' yyyy ') = ' 2007 '
6.
Timestamp/timestamp with time Zone/timestamp withlocal time zoneSimilar to date, except that it supports fractional seconds and time zones in addition. Syntax timestamp (n), n specifies the number of decimal digits in seconds, and the range of values 0~9. Optional.
7.
LobClob/blob implementation is more complex, here only to mention a few and performance-related points, of course, can not use the LOB as far as possible: A, a LOB field including Lobindex and LOBSEGMENTB, lob default can be stored in the Table (table field), the condition is: 1. Its size is less than 4KB 2. And is not used at the time of definition (disable storage inrow) sentence (the default is enable) when the LOB is greater than 4kb it will be stored in the Lobsegment C, when the lob is stored in the table, it can be cached, for Its operational efficiency is much higher than the lob stored in the lobsegment (no lobindex) d, the lob stored in lobsegment is not in the buffer cache, the read and write to the LOB is physical IO, the cost is very high, Therefore, for LOB fields greater than 4KB, do not update frequently, the efficiency is very low E, the lob stored in Lobsegment can be specified using the cache at the time of definition (NoCache by default), which is useful for medium-sized lobs (such as a few k~ dozens of k). Less physical IO
[Go]oracle Design database should choose the correct data type
