Oracle 10g Tutorials: Tables

CHAR
[(size [BYTE | CHAR])]

Fixed-length character data of length size bytes or characters.

Fixed for every row in the table (with trailing blanks); maximum size is 2000 bytes per row; default size is 1 byte per row. When neither BYTE nor CHAR is specified, the setting of NLS_LENGTH_SEMANTICS at the time of column creation determines which is used. Consider the character set (single-byte or multibyte) before setting size.

VARCHAR2
(size [BYTE | CHAR])

Variable-length character data, with maximum length size bytes or characters. BYTE or CHAR indicates that the column has byte or character semantics, respectively. A size must be specified.

Variable for each row, up to 4000 bytes per row. When neither BYTE nor CHAR is specified, the setting of NLS_LENGTH_SEMANTICS at the time of column creation determines which is used. Consider the character set (single-byte or multibyte) before setting size.

NCHAR [(size)]

Fixed-length Unicode character data of length size characters. The number of bytes is twice this number for the AL16UTF16 encoding and 3 times this number for the UTF8 encoding.)

Fixed for every row in the table (with trailing blanks). The upper limit is 2000 bytes per row. Default size is 1 character.

NVARCHAR2 (size)

Variable-length Unicode character data of maximum length size characters. The number of bytes may be up to 2 times size for a the AL16UTF16 encoding and 3 times this number for the UTF8 encoding. A size must be specified.

Variable for each row. The upper limit is 4000 bytes per row.

CLOB

Single-byte or multibyte character data. Both fixed-width and variable-width character sets are supported, and both use the CHAR character set.

Up to 2³² - 1 bytes * (database block size), or 4 gigabytes * block size.

NCLOB

Unicode national character set (NCHAR) data. Both fixed-width and variable-width character sets are supported, and both use the NCHAR character set.

Up to 2³² - 1 bytes * (database block size), or 4 gigabytes * block size.

LONG

Variable-length character data. Provided for backward compatibility.

Variable for each row in the table, up to 2³¹ - 1 bytes, or 2 gigabytes, per row.

BINARY_FLOAT

32-bit floating-point number.

4 bytes.

BINARY_DOUBLE

64-bit floating-point number.

8 bytes.

NUMBER
[(prec | prec, scale)]

Variable-length numeric data. Precision prec is the the total number of digits; scale scale is the number of digits to the right of the decimal point. Precision can range from 1 to 38. Scale can range from -84 to 127. With precision specified, this is a floating-point number; with no precision specified, it is a fixed-point number.

Variable for each row. The maximum space available for a given column is 21 bytes per row.

DATE

Fixed-length date and time data, ranging from Jan. 1, 4712 B.C.E. to Dec. 31, 9999 C.E.

Fixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-RR) specified by the NLS_DATE_FORMAT parameter.

INTERVAL YEAR
[(yr_prec)] TO MONTH

A period of time, represented as years and months. The yr_prec is the number of digits in the YEAR field of the date. The precision can be from 0 to 9, and defaults to 2 digits.

Fixed at 5 bytes.

INTERVAL DAY
[(day_prec)] TO SECOND [(frac_sec_prec)]

A period of time, represented as days, hours, minutes, and seconds. The day_prec and frac_sec_prec are the number of digits in the DAY and the fractional SECOND fields of the date, respectively. These precision values can each be from 0 to 9, and they default to 2 digits for day_prec and 6 digits for frac_sec_prec.

Fixed at 11 bytes.

TIMESTAMP
[(frac_sec_prec)]

A value representing a date and time, including fractional seconds. (The exact resolution depends on the operating system clock.)

The frac_sec_prec specifies the number of digits in the fractional second part of the SECOND date field. The frac_sec_prec can be from 0 to 9, and defaults to 6 digits.

Varies from 7 to 11 bytes, depending on the precision. The default is determined by the NLS_TIMESTAMP_FORMAT initialization parameter.

TIMESTAMP
[(frac_sec_prec)] WITH TIME ZONE

A value representing a date and time, plus an associated time zone setting. The time zone can be an offset from UTC, such as '-5:0', or a region name, such as 'US/Pacific'.

The frac_sec_prec is as for datatype TIMESTAMP.

Fixed at 13 bytes. The default is determined by the NLS_TIMESTAMP_TZ_FORMAT initialization parameter.

TIMESTAMP
[(frac_sec_prec)] WITH LOCAL TIME ZONE

Similar to TIMESTAMP WITH TIME ZONE, except that the data is normalized to the database time zone when stored, and adjusted to match the client's time zone when retrieved.

The frac_sec_prec is as for datatype TIMESTAMP.

Varies from 7 to 11 bytes, depending on frac_sec_prec. The default is determined by the NLS_TIMESTAMP_FORMAT initialization parameter.

BLOB

Unstructured binary data.

Up to 2³² - 1 bytes * (database block size), or 4 gigabytes * block size

BFILE

Address of a binary file stored outside the database. Enables byte-stream I/O access to external LOBs residing on the database server.

The referenced file can be up to 2³² - 1 bytes * (database block size), or 4 gigabytes * block size.

RAW (size)

Variable-length raw binary data. A size, which is the maximum number of bytes, must be specified. Provided for backward compatibility.

Variable for each row in the table, up to 2000 bytes per row.

LONG RAW

Variable-length raw binary data. Provided for backward compatibility.

Variable for each row in the table, up to 2³¹ - 1 bytes, or 2 gigabytes, per row.

ROWID

Base 64 binary data representing a row address. Used primarily for values returned by the ROWID pseudocolumn.

Fixed at 10 bytes (extended ROWID) or 6 bytes (restricted ROWID) for each row in the table.

UROWID [(size)]

Base 64 binary data representing the logical address of a row in an index-organized table. The optional size is the number of bytes in a column of type UROWID.

Maximum size and default are both 4000 bytes.

Ordinary (heap-organized) table

This is the basic, general-purpose type of table, which is the primary subject of this chapter. Its data is stored as an unordered collection (heap)

Clustered table

A clustered table is a table that is part of a cluster. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.

Index-organized table

Unlike an ordinary (heap-organized) table, data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Besides storing the primary key column values of an index-organized table row, each index entry in the B-tree stores the non-key column values as well.

Partitioned table

Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions, or even sub-partitions. Each partition can be managed individually, and can operate independently of the other partitions, thus providing a structure that can be better tuned for availability and performance.

GROUP BY clause

Maximum length

The GROUP BY expression and all of the nondistinct aggregate functions (for example, SUM, AVG) must fit within a single database block.

Indexes

Maximum per table

Unlimited

total size of indexed column

75% of the database block size minus some overhead

Columns

Per table

1000 columns maximum

Per index (or clustered index)

32 columns maximum

Per bitmapped index

30 columns maximum

Constraints

Maximum per column

Unlimited

Subqueries

Maximum levels of subqueries in a SQL statement

Unlimited in the FROM clause of the top-level query

255 subqueries in the WHERE clause

Partitions

Maximum length of linear partitioning key

4 KB - overhead

Maximum number of columns in partition key

16 columns

Maximum number of partitions allowed per table or index

64 KB - 1 partitions

Rows

Maximum number per table

Unlimited

Stored Packages

Maximum size

PL/SQL and Developer/2000 may have limits on the size of stored procedures they can call. The limits typically range from 2000 to 3000 lines of code.

Trigger Cascade Limit

Maximum value

Operating system-dependent, typically 32

Users and Roles

Maximum

2,147,483,638

Tables

Maximum per clustered table

32 tables

Maximum per database

Unlimited