A table is the basic unit of data organization in an Oracle database. A table describes an entity, which is something of significance about which information must be recorded. For example, an employee could be an entity.
Oracle Database tables fall into the following basic categories:
Relational tables
Relational tables are structured with simple columns and are the most common table type.
Object tables
The columns correspond to the top-level attributes of an object type.
You can build a relational table with the following storage characteristics:
A heap-organized table does not store rows in any particular order. This is the default type of relational table.
An index-organized table orders rows according to the primary key values. For some applications, index-organized tables enhance performance and use disk space more efficiently.
An external table is a read-only table whose metadata is stored in the database but whose data in stored outside the database.
A table is either permanent or temporary. A permanent table definition and data persist across sessions. A temporary table definition persists in the same way as a permanent table definition, but the data exists only for the duration of a transaction or session. Temporary tables are useful in applications where a result set must be held temporarily, perhaps because the result is constructed by running multiple operations.
This section contains the following topics:
|
See Also: Oracle Database 2 Day DBA and Oracle Database Administrator's Guide to learn how to manage tables |
A table definition includes a table name and set of columns. A column identifies an attribute of the entity described by the table. For example, the column employee_id in the employees table refers to the employee ID attribute of an employee entity.
In general, you give each column a column name, a data type, and a width when you create a table. For example, the data type for employee_id is NUMBER(6), indicating that this column can only contain numeric data up to 6 digits in width. The width can be predetermined by the data type, as in the case of DATE.
A table can contain a virtual column, which unlike a nonvirtual column does not consume disk space. The database derives the values in a virtual column on demand by computing a set of user-specified expressions or functions. For example, the virtual column income could be a function of the salary and commission_pct columns.
After you create a table, you can insert, query, delete, and update rows using SQL. A row is a collection of column information corresponding to a record in a table. For example, a row in the employees table describes the attributes of a specific employee.
Each column has a data type, which is associated with a specific storage format, constraints, and valid range of values. The data type of a value associates a fixed set of properties with the value. These properties cause Oracle Database to treat values of one data type differently from values of another. For example, you can multiply values of NUMBER data type, but not values of RAW data type.
When you create a table, you must specify a data type for each of its columns. Each value subsequently inserted in a column assumes the column data type.
Oracle Database provides several built-in data types. The most commonly used data types fall into the following categories:
Other important categories of built-in types include raw, large objects (LOBs), and collections. PL/SQL has data types for constants and variables, which include BOOLEAN, reference types, composite types (records), and user-defined types.
Character data types store character (alphanumeric) data in strings. The most commonly used character data type is VARCHAR2, which is the most efficient option for storing character data.
The byte values correspond to the character encoding scheme, generally called a character set or code page. The database character set is established at database creation. Examples of character sets are 7-bit ASCII, EBCDIC, and Unicode UTF-8.
The length semantics of character data types can be measured in bytes or characters. Byte semantics treat strings as a sequence of bytes. This is the default for character data types. Character semantics treat strings as a sequence of characters. A character is technically a codepoint of the database character set.
The VARCHAR2 data type stores variable-length character literals. The terms literal and constant value are synonymous and refer to a fixed data value. For example, 'LILA', 'St. George Island', and '101' are all character literals; 5001 is a numeric literal. Character literals are enclosed in single quotation marks so that the database can distinguish them from schema object names. (The terms text literal, character literal, and string are used interchangeably.)
When you create a table with a VARCHAR2 column, you specify a maximum string length. For example, a last_name column might have a data type of VARCHAR2(25), which means that any name stored in the column can have a maximum of 25 bytes.
For each row, Oracle Database stores each value in the column as a variable-length field unless a value exceeds the maximum length, in which case the database returns an error. For example, in a single-byte character set, if 10 characters are entered for the last_name column value in a row, then the column in the row piece stores only 10 characters (10 bytes), not 25. Using VARCHAR2 reduces space consumption.
In contrast to VARCHAR2, CHAR stores fixed-length character strings. When you create a table with a CHAR column, you must specify a string length. The default is 1 byte. The database uses blanks to pad the value to the specified length.
Oracle Database compares VARCHAR2 values using nonpadded comparison semantics and compares CHAR values using blank-padded comparison semantics.
The NCHAR and NVARCHAR2 data types store Unicode character data. Unicode is a universal encoded character set that can store information in any language using a single character set. NCHAR stores fixed-length character strings that correspond to the national character set, whereas NVARCHAR2 stores variable length character strings.
The character set of NCHAR and NVARCHAR2 data types can only be either AL16UTF16 or UTF8 and is specified at database creation time as the national character set. AL16UTF16 and UTF8 are both Unicode encoding.
When you create a table with an NCHAR or NVARCHAR2 column, the maximum size specified is always in character length semantics. Character length semantics is the default and only length semantics for NCHAR or NVARCHAR2.
The numeric data types store fixed and floating-point numbers, zero, and infinity. The numeric types also store values that are the undefined result of an operation, which is known as "not a number" or NAN.
Oracle Database stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent. The database uses up to 20 bytes to store the mantissa, which is the part of a floating-point number that contains its significant digits. Oracle Database does not store leading and trailing zeros.
The NUMBER data type stores fixed and floating-point numbers. Numbers of virtually any magnitude can be stored and are guaranteed portable among different systems operating Oracle Database. The NUMBER data type is recommended for most cases in which you need to store numeric data.
You specify a fixed-point number in the form NUMBER(p,s), where p and s refer to the following characteristics:
Precision
The precision specifies the total number of digits. If a precision is not specified, then the column stores the values exactly as provided by the application without any rounding.
Scale
The scale specifies the number of digits from the decimal point to the least significant digit. Positive scale counts digits to the right of the decimal point to and including the least significant digit. Negative scale counts digits to the left of the decimal point, up to but not including the least significant digit. If a precision is specified without a scale, as in NUMBER(6), then the scale is zero.
For example, a salary column might be type NUMBER(8,2), so precision is 8 and scale is 2. Thus, a salary of 100,000 is stored as 100000.00.
Oracle Database provides two numeric data types exclusively for floating-point numbers: BINARY_FLOAT and BINARY_DOUBLE. For an explanation of these types, see Using Floating-Point Number Formats.
The datetime data types are DATE and TIMESTAMP. Values of datetime data types are sometimes called datetimes. Oracle Database provides daylight savings support for datetime data types in the server.
|
See Also: Storing Datetime Data, which includes Using DATE and TIMESTAMP Data Types and Manipulating the DATE and TIME Formats |
The DATE data type stores date and time. Although datetimes can be represented in character or number data types, DATE has special associated properties. A hire_date column might have a DATE data type.
Oracle Database stores dates internally as numbers. Dates are stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second. The DATE data type always stores the year as a four digit-number internally, for example, 2009 instead of 09.
Because the database stores dates as numbers, you can perform arithmetic on dates.
The database displays dates according to the specified format model. A format model is a character literal that describes the format of a datetime in a character string. The standard date format is DD-MON-RR, which displays dates in the form 01-JAN-09.
RR is similar to YY (the last two digits of the year), but the century of the return value varies according to the specified two-digit year and the last two digits of the current year. Assume that in 1999 the database displays 01-JAN-09. If the date format uses DD, then 09 specifies 2009, whereas if the format uses YY, then 09 specifies 1909. You can change the default date format at both the instance and the session level.
Oracle Database stores time in 24-hour format—HH:MI:SS. If no time portion is entered, then by default the time in a date field is 00:00:00 A.M. In a time-only entry, the date portion defaults to the first day of the current month.
The TIMESTAMP data type is an extension of the DATE data type. It stores fractional seconds in addition to the information stored in the DATE data type. The TIMESTAMP data type is useful for storing precise time values, such as in applications that must track event order.
The DATETIME data types TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE are time-zone aware. When a user selects the data, the value is adjusted to the time zone of the user session. This data type is useful for collecting and evaluating date information across geographic regions.
Every row stored in the database has an address. Oracle Database uses a ROWID data type to store the address (rowid) of every row in the database. Rowids fall into the following categories:
Physical rowids store the addresses of rows in heap-organized tables, clustered tables, and table and index partitions.
Logical rowids store the addresses of rows in index-organized tables.
Foreign rowids are identifiers in foreign tables, such as DB2 tables accessed through a gateway. They are not standard Oracle Database rowids.
A data type called the universal rowid, or UROWID, supports all kinds of rowids.
Oracle Database uses rowids internally for the construction of indexes. Each key in an index is associated with a rowid that points to the associated row's address for fast access. End users and application developers can also use rowids for several important functions:
Rowids are the fastest means of accessing particular rows.
Rowids provide the ability to see how a table is organized.
Rowids are unique identifiers for rows in a given table.
You can also create tables with columns defined using the ROWID datatype. For example, you can define an exception table with a column of datatype ROWID to store the rowids of rows that violate integrity constraints. Columns defined using the ROWID datatype behave like other table columns: values can be updated, and so on.
Every table in an Oracle database has a pseudocolumn named ROWID. A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. A pseudocolumn is also similar to a SQL function without arguments. Functions without arguments typically return the same value for every row in the result set, whereas pseudocolumns typically return a different value for each row.
Values of the ROWID pseudocolumn are strings representing the address of each row. These strings have the datatype ROWID. This pseudocolumn is not evident when listing the structure of a table by executing SELECT or DESCRIBE, nor does the pseudocolumn consume space. However, the rowid of each row can be retrieved with a SQL query using the reserved word ROWID as a column name.
Example: ROWID Pseudocolumn queries the ROWID pseudocolumn to show the rowid of the row in the employees table for employee 100.
A format model is a character literal that describes the format of datetime or numeric data stored in a character string. A format model does not change the internal representation of the value in the database.
When you convert a character string into a date or number, a format model determines how the database interprets the string. In SQL, you can use a format model as an argument of the TO_CHAR and TO_DATE functions to format a value to be returned from the database or to format a value to be stored in the database.
The following statement selects the salaries of the employees in Department 80 and uses the TO_CHAR function to convert these salaries into character values with the format specified by the number format model '$99,990.99':
SQL> SELECT last_name employee, TO_CHAR(salary, '$99,990.99') 2 FROM employees 3 WHERE department_id = 80 AND last_name = 'Russell'; EMPLOYEE TO_CHAR(SAL ------------------------- ----------- Russell $14,000.00
The following example updates a hire date using the TO_DATE function with the format mask 'YYYY MM DD' to convert the string '1998 05 20' to a DATE value:
SQL> UPDATE employees
2 SET hire_date = TO_DATE('1998 05 20','YYYY MM DD')
3 WHERE last_name = 'Hunold';
Integrity constraints are named rules that restrict the values for one or more columns in a table. These rules prevent invalid data entry into tables. Also, constraints can prevent the deletion of a table when certain dependencies exist.
If a constraint is enabled, then the database checks data as it is entered or updated. Data that does not conform to the constraint is prevented from being entered. If a constraint is disabled, then data that does not conform to the constraint can be allowed to enter the database.
For example, the CREATE TABLE statement for an employees table might specify NOT NULL constraints for the last_name, email, hire_date, and job_id columns. The constraint clauses identify the columns and the conditions of the constraint. These constraints ensure that the specified columns contain no null values. For example, an attempt to insert a new employee without a job ID generates an error.
You can create a constraint when or after you create a table. Constraints can be temporarily disabled if needed. The database stores constraints in the data dictionary.
An Oracle object type is a user-defined type with a name, attributes, and methods. Object types make it possible to model real-world entities such as customers and purchase orders as objects in the database.
An object type defines a logical structure, but does not create storage. Example: Object Type creates an object type named department_typ.
Object Type
CREATE TYPE department_typ AS OBJECT
( d_name VARCHAR2(100),
d_address VARCHAR2(200) );
/
An object table is a special kind of table in which each row represents an object. The CREATE TABLE statement in Example: Object Table creates an object table named departments_obj_t of the object type department_typ. The attributes (columns) of this table are derived from the definition of the object type. The INSERT statement inserts a row into this table.
Object Table
CREATE TABLE departments_obj_t OF department_typ;
INSERT INTO departments_obj_t
VALUES ('hr', '10 Main St, Sometown, CA');
Like a relational column, an object table can contain rows of just one kind of thing, namely, object instances of the same declared type as the table. By default, every row object in an object table has an associated logical object identifier (OID) that uniquely identifies it in an object table. The OID column of an object table is a hidden column.
Oracle Database temporary tables hold data that exists only for the duration of a transaction or session. Data in a temporary table is private to the session, which means that each session can only see and modify its own data.
Temporary tables are useful in applications where a result set must be buffered. For example, a scheduling application enables college students to create optional semester course schedules. Each schedule is represented by a row in a temporary table. During the session, the schedule data is private. When the student decides on a schedule, the application moves the row for the chosen schedule to a permanent table. At the end of the session, the schedule data in the temporary data is automatically dropped.
The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table. The ON COMMIT clause specifies whether the table data is transaction-specific (default) or session-specific.
Unlike temporary tables in some other relational databases, when you create a temporary table in an Oracle database, you create a static table definition. The temporary table is a persistent object described in the data dictionary, but appears empty until your session inserts data into the table. You create a temporary table for the database itself, not for every PL/SQL stored procedure.
Because temporary tables are statically defined, you can create indexes for them with the CREATE INDEX statement. Indexes created on temporary tables are also temporary. The data in the index has the same session or transaction scope as the data in the temporary table. You can also create a view or trigger on a temporary table.
Like permanent tables, temporary tables are defined in the data dictionary. However, temporary tables and their indexes do not automatically allocate a segment when created. Instead, temporary segments are allocated when data is first inserted. Until data is loaded in a session the table appears empty. Temporary segments are deallocated at the end of the transaction for transaction-specific temporary tables and at the end of the session for session-specific temporary tables.
An external table accesses data in external sources as if this data were in a table in the database. You can use SQL, PL/SQL, and Java to query the external data.
External tables are useful for querying flat files. For example, a SQL-based application needs to access employee records in a text file. The records are in the following form:
100,Steven,King,SKING,515.123.4567,17-JUN-03,AD_PRES,31944,150,90 101,Neena,Kochhar,NKOCHHAR,515.123.4568,21-SEP-05,AD_VP,17000,100,90 102,Lex,De Haan,LDEHAAN,515.123.4569,13-JAN-01,AD_VP,17000,100,90
You could create an external table, copy the file to the location specified in the external table definition, and use SQL to query the records in the text file.
External tables are also valuable for performing ETL tasks common in data warehouse environments. For example, external tables enable the pipelining of the data loading phase with the transformation phase, eliminating the need to stage data inside the database in preparation for further processing inside the database.
Internally, creating an external table means creating metadata in the data dictionary. Unlike an ordinary table, an external table does not describe data stored in the database, nor does it describe how data is stored externally. Rather, external table metadata describes how the external table layer must present data to the database.
A CREATE TABLE ... ORGANIZATION EXTERNAL statement has two parts. The external table definition describes the column types. This definition is like a view that enables SQL to query external data without loading it into the database. The second part of the statement maps the external data to the columns.
External tables are read-only except when created with CREATE TABLE AS SELECT with the ORACLE_DATAPUMP access driver. Restrictions for external tables include no support for indexed columns, virtual columns, and column objects.
An access driver is an API that interprets the external data for the database. The access driver runs inside the database, which uses the driver to read the data in the external table. The access driver and the external table layer are responsible for performing the transformations required on the data in the data file so that it matches the external table definition.
Oracle provides the ORACLE_LOADER (default) and ORACLE_DATAPUMP access drivers for external tables. For both drivers, the external files are not Oracle data files.
ORACLE_LOADER enables read-only access to external files using SQL*Loader. You cannot create, update, or append to an external file using the ORACLE_LOADER driver.
The ORACLE_DATAPUMP driver enables you to unload external data. This operation involves reading data from the database and inserting it into an external table, represented by one or more external files. After external files are created, the database cannot update or append data to them. The driver also enables you to load external data, which involves reading an external table and loading its data into a database.
|
See Also:
|
Oracle Database uses a data segment in a tablespace to hold the table data. A segment is composed of data files made up of data blocks.
The data segment for a table (or cluster data segment, when dealing with a table cluster) is located in either the default tablespace of the table owner or in a tablespace named in the CREATE TABLE statement.
By default, a table is organized as a heap. A heap is an area of storage in which data is placed where it fits best rather than in a user-specified order. Thus, a heap-organized table is an unordered collection of rows. As rows are added, the database places them in the first available free space in the data segment. Rows are not guaranteed to be retrieved in the order in which they were inserted.
|
Note: Index-organized tables use a different principle of organization. |
The hr.departments table is an example of a heap-organized table. It has columns for department ID, name, manager ID, and location ID. As rows are inserted they are stored wherever they fit. A table block might contain the unordered rows shown in Example: Rows in Departments Table.
Rows in Departments Table
50,Shipping,121,1500 120,Treasury,,1700 70,Public Relations,204,2700 30,Purchasing,114,1700 130,Corporate Tax,,1700 10,Administration,200,1700 110,Accounting,205,1700
The column order is the same for all rows in a table. Columns are usually stored in the order in which they were listed in the CREATE TABLE statement, but this order is not guaranteed. For example, if a table has a column of type LONG, then Oracle Database always stores this column last in the row. Also, if a table is altered so that a new column is added, then the new column becomes the last column stored.
A table can contain a virtual column, which unlike normal columns does not consume space on disk. The database derives the values in a virtual column on demand by computing a set of user-specified expressions or functions. You can index virtual columns, collect statistics on them, and create integrity constraints. Thus, they are much like nonvirtual columns.
The database stores rows in data blocks. Each row of a table containing data for less than 256 columns is contained in one or more row pieces.
If possible, Oracle Database stores each row as one row piece. However, if all of the row data cannot be inserted into a single data block, or if an update to an existing row causes the row to outgrow its data block, then the database stores the row using multiple row pieces.
Rows in a table cluster contain the same information as rows in nonclustered tables. Additionally, rows in a table cluster contain information that references the cluster key to which they belong.
A rowid is effectively a 10-byte physical address of a row. As explained in Rowid Data Types, every row in a heap-organized table has a rowid unique to this table that corresponds to the physical address of a row piece. For table clusters, rows in different tables that are in the same data block can have the same rowid.
Oracle Database uses rowids internally for the construction of indexes. For example, each key in a B-tree index is associated with a rowid that points to the address of the associated row for fast access. Physical rowids provide the fastest possible access to a table row, enabling the database to retrieve a row in as little as a single I/O.
The database can use table compression to eliminate duplicate values in a data block. For tables with highly redundant data, compression saves disk space, reduces memory use in the database buffer cache, and in some cases speeds query execution. Table compression is transparent to database applications.
Oracle Database supports the following types of table compression:
DSS table compression
This type of compression is intended for data warehouse applications and offers a high compression ratio and low CPU overhead.
OLTP table compression
This type of compression is intended for OLTP applications and offers a high compression ratio and low CPU overhead.
Columnar compression
For infrequently updated data, this type of compression can provide a very high degree of size reduction. Database operations work transparently against these compressed objects so no application changes are required.
You can enable columnar compression at different levels. Each level corresponds to a different compression algorithm applied to incoming rows. Typically, a higher level has a higher compression ratio and cost during load and access time. You can use the compression advisor to estimate the compression ratios that different compression levels provide for a specified object.
With DSS and OLTP compression, compressed blocks look much like normal data blocks. Most database features and functions that work on regular data blocks also work on compressed blocks. In DSS and OLTP compression, duplicate values in the rows and columns in a data block are stored once at the beginning of the block in a symbol table. Duplicate values are replaced with a short reference to the symbol table. Thus, information needed to re-create the uncompressed data is stored in the block.
With columnar compression, the database stores the same column for a group of rows together. For example, the database stores the first column for a set of rows, then the second column for the same rows, and so on. This storage method differs from the default row-major format in which all columns of one row are stored together, followed by all columns of the next row, and so on. By storing column values together, the database can achieve good compression for columns with low cardinality.
You can declare compression at the tablespace, table, partition, or subpartition level. If specified at the tablespace level, then all tables created in that tablespace are compressed by default. The following example of part of a CREATE TABLE statement specifies compression for individual table partitions:
CREATE TABLE sales (
prod_id NUMBER NOT NULL,
cust_id NUMBER NOT NULL, ... )
PCTFREE 5 NOLOGGING NOCOMPRESS
PARTITION BY RANGE (time_id)
( partition sales_1995 VALUES LESS THAN(TO_DATE(...)) COMPRESS,
partition sales_1996 VALUES LESS THAN(TO_DATE(...)) COMPRESS, ... );
A null is the absence of a value in a column of a row. Nulls indicate missing, unknown, or inapplicable data.
Nulls are stored in the database if they fall between columns with data values. In these cases, they require 1 byte to store the length of the column (zero). Trailing nulls in a row require no storage because a new row header signals that the remaining columns in the previous row are null. For example, if the last three columns of a table are null, then no data is stored for these columns.