Oracle Character Types

1. Char
A fixed-length string is a string that is stored in the database with spaces filled with insufficient digits. The declaration method is as follows: Char (L). L is the string length. The default value is 1 and the maximum value is 2000. It is not recommended for use, which may cause unnecessary troubles.
A. errors may occur if you do not pay attention to the character string comparison (the char is not filled with spaces ).
B. When comparing strings, if the trim function is used, the index on the field becomes invalid (sometimes it causes serious performance problems)
C. A waste of storage space (the future storage size cannot be accurately calculated, and only enough space can be reserved; the length of the string is the size of the space occupied by it)
D. The efficiency of char is slightly higher than that of varchar2.

2. varchar2/varchar
Currently, varchar is a synonym for varchar2. The industry standard varchar type can store null strings, but Oracle does not, although it reserves the right to do so in the future. Oracle has developed a data type varchar2, which is not a standard varchar. It changes the feature that can store null strings in the varchar column of the database to store null values. If you want backward compatibility, we recommend that you use varchar2 instead of varchar.
The variable length Format String is declared as follows: varchar2 (L), L is the string length, no default value, the maximum is 4000. In a multi-byte environment, the actual number of characters stored may be less than L. For example, when the language
When the environment is Chinese (simplified chinese_china.zhs16gbk), a varchar2 (200) data column can store 200 English characters or 100 Chinese characters. This type is recommended for strings smaller than 4000 bytes.

A. Varchar2 saves space than char, and is slightly less efficient than Char. That is, to achieve efficiency, a certain amount of space must be sacrificed, this is what we often say in database design, "Change space for efficiency '.
B. Although varchar2 saves space than char, if a varchar2 column is often modified and the length of the data to be modified is different each time, this will cause row migration, this causes redundant I/O, which should be avoided during database design and adjustment. In this case, it would be better to replace varchar2 with Char. However, the difference is not too big. If you want to use varchar2, you can use pctfree to adjust the row migration and then reorganize the table data.

Nchar, nvarchar/nvarchar2 national character set, which is closely related to the language set specified by the Environment Variable NLS. These national character sets are used only when Unicode is used. nchar and nvarchar2 store data based on the selected character set. One character may occupy two or more bytes to prevent garbled characters.

3. Rows
Two types are available: raw and long raw. They are used to store binary data and are not converted between character sets.
Raw, similar to Char, is declared as raw (L). L is the length, in bytes, and serves as the maximum 2000 bytes of the database column and the maximum 32767 bytes of the variable.
Long raw, similar to long, is used as a database column to store up to 2G bytes of data and as a variable to 32760 bytes.
The row identifier, which is only one type-rowid, used to store the "Row identifier". You can use the rowidtochar function to convert the row identifier into a character.

4. Date
The date type is a 7-byte, fixed-length data type (from the century to the second.
For example, performance A> B> C
A. SQL>... where date_colum> = to_date ('01-Jan-2007 ', 'dd-mon-yyyy ')
And date_colum <to_date ('02-Jan-2007 ', 'dd-mon-yyyy ')
B. SQL>... where trunc (date_colum, 'y') = to_date ('01-Jan-2007 ', 'dd-mon-yyyy ')
C. SQL>... where to_char (date_colum, 'yyyy') = '20140901'

5. Timestamp
Oracle has extended the timestamp data type in the date data type, which includes information about all date data types, such as year, month, day, hour, minute, and second, and contains information about decimal seconds. If you want to convert the date type to the timestamp type, use the cast function.
Syntax: Timestamp (N). N indicates the number of decimal places in seconds. The value range is 0 ~ 9. The default value is 6.

SQL> select to_char (systimestamp, 'yyyy-mm-dd hh24: MI: Ss. ff8') from dual;

To_char (systimestamp, 'yyyy-mm-
------------------------------
2012-08-11 10:34:48. 89800000
This is because the display format is based on the default format specified by the nls_timestamp_format parameter.

Note:
1) when you move the data of the date type field in one table to the timestamp type field in another table, you can directly write the insert SELECT statement. Oracle will automatically convert the data for you.
2) The to_char function supports date and timestamp, but trunc does not support the timestamp data type.

A. Convert the date type to the timestamp type
SQL> select cast (sysdate as timestamp) from dual;

Cast (sysdateastimestamp)
--------------------------------------------------------------------------------
-12 10.35.40.000000 AM
As you can see, there is a section ". 000000" at the end of the converted time period ". This is because there is no decimal second information during the conversion from date. The default value is 0.

B. In order to obtain the system time, the Data Type of date is returned. You can use the sysdate function.
SQL> select sysdate from dual;

Sysdate
-----------
1

C. In order to get the system time, the data type is returned as timestamp. You can use the aggregate impstamp function.
SQL> select distinct imestamp from dual;

Systimestamp
--------------------------------------------------------------------------------
-12 10.38.16.809000 AM +

D. You can set the initialization parameter fixed_date to specify the sysdate function to return a fixed value. This is used in code that is sensitive to test date and time. Note that this parameter is invalid for the systimestamp function.
SQL> alter system set fixed_date = '2017-01-01-10:00:00 ';
System altered

SQL> select sysdate from dual;
Sysdate
-----------
2006-1-1 10

SQL> select distinct imestamp from dual;
Systimestamp
--------------------------------------------------------------------------------
-12 10.38.16.809000 AM +

E. Restore the default value of fixed_date.
SQL> alter system set fixed_date = none;
System altered

SQL> select sysdate from dual;
Sysdate
-----------
1

6. Number
The method for defining number: Number (P, S); where P and S are optional:
P indicates the precision. The default value is 38.
S indicates the number of decimal places. value range:-84 ~ 127. The default value depends on whether P is specified. If P is specified, the default value S is 0. If P is not specified, the maximum value is used by default.

Examples:
A, number (99999) = Number (5) value range ~ -99999
B. The value range of number (5, 2) is 999.99 ~ -999.99
Note: The number of integer digits is only three digits, and the number of decimal places is two digits. The calculation is as follows:
Integer digits <= p-S
Decimal places <= s
If 123.555 is inserted and stored in the database becomes 123.56 (rounded up to the third digit of the decimal number), if 999.999 is inserted, the database will throw an error.
C. The value range of number (5,-2) is 9999900 ~ -9999900 (integer digits <= p-s, no decimal places)
If insert 9999949 is stored in the database and changed to 9999900 (rounded up to the second digit of the integer), if insert 9999950, the database will throw an error.
Other numeric types are derived from number, and the bottom layer is number. For example, integer/INT values are fully mapped to number (38)
Performance: number is a soft Implementation type. If you need to perform complex operations on number, we recommend that you first use the cast built-in function to convert number to the floating point type.
Note that number is a variable-length type, which must be kept in mind when calculating the table storage space.

7. lob
Clob/BLOB implementation is complicated. It is mainly used to store database fields with a large amount of data and can store up to 4 GB of unstructured data.
The Blob field in Oracle is special. It has much better performance than the long field and can be used to save binary data such as sample slices.
Writing BLOB fields is very different from writing other types of fields. Because blob itself has a cursor, you must use cursor to operate blob. Therefore, before writing blob, you must obtain the cursor to write data. How can you obtain the Blob cursor? This requires you to insert an empty blob first, which will create a blob cursor, and then you can query the cursor of the empty blob using select. In this way, you can perform two steps, you can get the Blob cursor to write BLOB data.

Here we only mention a few performance-related points. Of course, lob is not needed as much as possible:
Oralce has four types of Lob: clob, blob, nclob, and bfile.
Clob: large internal character object;
BLOB: large internal binary object;
Nclob: an object with a fixed length of multiple bytes;
Bfile: an external Binary large file. The data in this file can only be read-only and not included in the database. bfile is the direct inheritance of the early rdbms blob, as a database pointer stored inside the database, pointing to external operating system files.

A. By storage:
Internal lob: stored in the database, including blob, clob, and bclob
External file: it is stored outside the database, that is, bfile.
B. Data Types:
① Character type:
Clob: stores a large amount of single-byte character data.
Nlob: stores fixed-width, multi-byte character data.
② Binary type:
BLOB: stores large unstructured binary data.
③ Binary file type:
Bfile: stores binary files in operating system files outside the database. Path for storing files.

Performance of Lob Data Types
A. a lob field includes lobindex and lobsegment. the lob index is created implicitly.
B. By default, lob can be stored in a table (table field) with the following conditions:
1. Its size is smaller than 4 kb
2. The Disable storage inrow Statement (enable by default) is not used during definition)
When a lob is larger than 4 kb, it is stored in the lobsegment.
C. When a lob is stored in a table, it can be cached. Its operation efficiency is much higher than that of a lob stored in a lobsegment (lobindex is not required)
D. the lob stored in the lobsegment is not cached in the buffer by default. The lob read/write operations are physical Io and the cost is very high. Therefore, do not update the lob fields larger than 4 kb frequently, and the efficiency is very low.
E. when defining a lob stored in a lobsegment, you can specify a cache (nocache by default). For a lob of medium size (such as a few K ~ Dozens of K) is very useful, at the same time, it can reduce physical Io.