Oracle 10g Tutorials: Tables

Oracle 10g Free Training : 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

Table 1. 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 232 - 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 232 - 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 231 - 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 232 - 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 232 - 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 231 - 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

There are four types of tables as mentioned below in table 2.

Table 2. Types of tables

Type of Table	Description
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.

Restrictions to consider when Creating Tables

Here are some restrictions that may affect your table planning and usage:

· 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.

The table 2 below specifies the databse limits for the various scema objects.

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
	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

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.