Oracle 10g Tutorials: Tables

In this tutorial you will learn about Tables, Summary of Oracle Built-In Datatypes, Types of tables, Restrictions to consider when Creating Tables along with logical databse limits.

Tables

Tables are the basic unit of data storage in an Oracle Database. A table consists of columns and rows. Data is stored in these rows and columns. A table definition required a table name and the description of the columns that would be contained in this table. Rows are automatically created when the data is inserted into the table. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. The table 1 below lists all the available datatypes in Oracle which can be used to define column types in a table.

Summary of Oracle Built-In Datatypes

Datatype	Description	Column Length / Default Values
`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.

You can also specify rules for each column of a table. These rules are called integrity constraints. One example is a NOT NULL integrity constraint. This constraint forces the column to contain a value in every row.

After you create a table, insert rows of data using SQL statements. Table data can then be queried, deleted, or updated using SQL.

Types of tables

Restrictions to consider when Creating Tables

· Tables containing object types cannot be imported into a pre-Oracle8 database.

· You cannot merge an exported table into a preexisting table having the same name in a different schema.

· You cannot move types and extent tables to a different schema when the original data still exists in the database.

· Oracle Database has a limit on the total number of columns that a table (or attributes that an object type) can have.

Logical Database Limits

Item	Type	Limit
`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
Indexes	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
Tables	Maximum per database	Unlimited

Further, when you create a table that contains user-defined type data, the database maps columns of user-defined type to relational columns for storing the user-defined type data. This causes additional relational columns to be created. This results in "hidden" relational columns that are not visible in a DESCRIBE table statement and are not returned by a SELECT * statement. Therefore, when you create an object table, or a relational table with columns of REF, varray, nested table, or object type, be aware that the total number of columns that the database actually creates for the table can be more than those you specify.

Oracle 10g Free Training : Tables

Tables

Summary of Oracle Built-In Datatypes

Types of tables

Restrictions to consider when Creating Tables

Logical Database Limits

Tags

George92 said: