Database Objects: Usage Information

This topic discusses creating and managing database objects in your schema, plus design considerations and indexing guidelines when developing applications with Oracle Database. It contains the following topics:


See Also:

Oracle Database SQL Language Reference for information about database objects, object names, and data types

Overview of Managing Objects

You need to create tables, indexes, and possibly other database objects in a schema before you start developing your application. A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user, such as the HR schema. Schema objects are logical structures created by users. Objects can define areas of the database to hold data, such as tables or indexes, or can consist just of a definition, such as a views or synonyms.

Database Objects: Usage Information discusses tables, indexes, views, sequences, and synonyms. Other database (schema) objects include functions, packages, procedures, and triggers. Functions, packages, and procedures are discussed in Subprograms and Packages: Usage Information. Triggers are discussed in Triggers: Usage Information.

Some object types have many more management options than others, but most have a number of similarities. Every object in the database belongs to just one schema and has a unique name within that schema. Therefore, when you create an object, you must ensure it is in the schema where you intend to store it. Generally, you place all of the objects that belong to a single application in the same schema.

A database object name must abide by certain rules. In addition to being unique within a schema, a schema object name cannot be longer than 30 bytes and must begin with a letter. If you attempt to create an object with a name that violates any of these rules, the database raises an error.

To include lowercase characters, special characters, or spaces in an object name, you must enclose the name in quotation marks (" ") when you type it in a Name field in SQL Developer. Example: "My table"

The following topics describe how to view, create, and manage the various types of objects in your database schemas:

Managing Tables

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:

You can build a relational table with the following storage characteristics:

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

Columns and Rows

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.

Oracle Data Types

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.


See Also:

Data Types: Usage Information for additional usage and reference information

Character Data 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.

VARCHAR2 and CHAR Data Types

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.

NCHAR and NVARCHAR2 Data Types

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.

Numeric Data Types

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.

NUMBER Data Type

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.

Floating-Point Numbers

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.

Datetime Data Types

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.

DATE Data Type

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.

TIMESTAMP Data Type

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.

Rowid Data Types

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.

Use 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 data type. For example, you can define an exception table with a column of data type ROWID to store the rowids of rows that violate integrity constraints. Columns defined using the ROWID data type behave like other table columns: values can be updated, and so on.

ROWID Pseudocolumn

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 data type 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.

ROWID Pseudocolumn

SQL> SELECT ROWID FROM employees WHERE employee_id = 100;
 
ROWID
------------------
AAAPecAAFAAAABSAAA

Format Models and Data Types

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

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.

Object Tables

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.

Temporary Tables

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.

Temporary Table Creation

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.

Segment Allocation in Temporary Tables

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.

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

External Table Creation

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.

External Table Access Drivers

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 Administrator's Guide to learn about managing external tables, external connections, and directory objects

  • Oracle Database Utilities to learn about external tables

  • Oracle Database SQL Language Reference for information about creating and querying external tables


Table Storage

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.

Table Organization

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.

Row Storage

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.

Rowids of Row Pieces

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.

Table Compression

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, ... );

Storage of Null Values

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.

Managing Indexes

Indexes are optional structures associated with tables. You can create them to improve query performance. Just as the index in this book helps you to quickly locate specific information, an Oracle Database index provides a quick access path to table data.

You can create indexes on one or more columns of a table. After an index is created, it is automatically maintained and used by Oracle Database. Changes to a table's data or structure, such as adding new rows, updating rows, or deleting rows, are automatically incorporated into all relevant indexes with complete transparency to the user.

Some indexes are created implicitly through constraints that are placed on a table. For example, a column with the constraint that its values be unique causes Oracle Database to create a unique key index.

Oracle Database automatically creates the indexes necessary to support data integrity defined with constraints when you add or enable those constraints. For performance purposes, you might want to add an index to the columns you define in a child table when adding a foreign key constraint. Before you add additional indexes, you should examine the performance of your database. You can then compare performance after the new indexes are added.

After index creation, Oracle Database automatically synchronizes the index with any subsequent inserts, updates, or deletes to the base table.

Indexes are generally of value to queries and to SQL statements that need to operate on a single, existing row or a small number of existing rows. Too many indexes can cause serious problems by increasing the processing overhead for statements that add, modify, or delete rows. In some cases, a statement could use two or more indexes and the optimizer picks just one of them. Unless other statements can take advantage of the unused indexes, they are not providing any benefit. Therefore, you might find yourself deleting indexes if you create too many.

This topic contains the following topics:

Creating Indexes for Use with Constraints

All enabled unique and primary keys require corresponding indexes, although in most cases the indexes are created automatically. Note the following:

  • Constraints use existing indexes where possible, rather than creating new ones.

  • Unique and primary keys can use non-unique as well as unique indexes. They can even use just the first few columns of non-unique indexes.

  • At most one unique or primary key can use each non-unique index.

  • The column orders in the index and the constraint do not need to match.

You should almost always index foreign keys; the database does not do this for you automatically.

Index Types

Indexes can be categorized in a number of ways. The primary options are:

Normal

A standard, B-tree index contains an entry for each value in the index key along with an address to the row where the value is stored. A B-tree index is the default and most common type of index in an Oracle database.

Ascending and Descending

The default search through an index is from lowest to highest value, where character data is sorted by ASCII values, numeric data from smallest to largest number, and date from the earliest to the latest value. This default behavior is performed in indexes created as ascending indexes. You can cause index searches to reverse the search order by creating the related index with the descending option.

Column and Function-based

Typically, an index entry is based on the value or values found in the table's column or columns. This is a column index. Alternatively, you can create a function-based index in which the indexed value is derived from the table data. For example, to find character data that can be in mixed case, you could use a function-based index to look for the values as if they were all in uppercase characters.

Single Column and Concatenated

You can create an index on just one column, which is called a single column index, or on multiple columns, which is called a concatenated index. Concatenated indexes are useful when all of the columns are likely to be included in the WHERE clause of frequently executed SQL statements.

For concatenated indexes, you should define the columns used in the index carefully so that the column with the fewest duplicate values is named first, the column with next fewest duplicate values is second, and so on. Columns with many duplicate values or many rows with NULL values should not be included or should be the last-named columns in the index definition.

Guidelines for Creating Indexes

You can create indexes on columns to speed up queries. Indexes provide faster access to data for operations that return a small portion of a table's rows.

In general, you should create an index on a column in any of the following situations:

  • The column is queried frequently.

  • A referential integrity constraint exists on the column.

  • A UNIQUE key integrity constraint exists on the column.

You can create an index on any column; however, if the column is not used in any of these situations, creating an index on the column does not increase performance and the index takes up resources unnecessarily.

Although the database creates an index for you on a column with an integrity constraint, explicitly creating an index on such a column is recommended.

Typically, you insert or load data into a table (using SQL*Loader or Import) before creating indexes. Otherwise, the overhead of updating the index slows down the insert or load operation. The exception to this rule is that you must create an index for a cluster before you insert any data into the cluster.

Creating a Temporary Tablespace Before Creating Indexes

When you create an index on a table that has data, Oracle Database must use sort space to create the index. The database uses the sort space in memory allocated for the creator of the index (the amount for each user is determined by the initialization parameter SORT_AREA_SIZE), but the database must also swap sort information to and from temporary segments allocated on behalf of the index creation. If the index is extremely large, Oracle recommends following these steps:

  1. Create a temporary tablespace using the CREATE TABLESPACE statement.

  2. Use the TEMPORARY TABLESPACE option of the ALTER USER statement to make this your temporary tablespace.

  3. Create the index using the CREATE INDEX statement.

  4. Drop this tablespace using the DROP TABLESPACE statement. Then use the ALTER USER statement to reset your temporary tablespace to your original temporary tablespace.

Under certain conditions, you can load data into a table with the SQL*Loader "direct path load", and an index can be created as data is loaded.

Index the Correct Tables and Columns

Use these guidelines for determining when to create an index:

  • Create an index if you frequently want to retrieve less than about 15% of the rows in a large table. This threshold percentage varies greatly, however, according to the relative speed of a table scan and how clustered the row data is about the index key. The faster the table scan, the lower the percentage; the more clustered the row data, the higher the percentage.

  • Index columns that are used for joins to improve join performance.

  • Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key.

  • Small tables do not require indexes; if a query is taking too long, then the table might have grown from small to large.

Some columns are strong candidates for indexing. Columns with one or more of these characteristics are good candidates for indexing:

  • Values are unique in the column, or there are few duplicates.

  • There is a wide range of values (good for regular indexes).

  • There is a small range of values (good for bitmap indexes).

  • The column contains many nulls, but queries often select all rows having a value. In this case, a comparison that matches all the non-null values, such as:

    WHERE COL_X >= -9.99 *power(10,125)
    

    is preferable to

    WHERE COL_X IS NOT NULL
    

    This is because the first uses an index on COL_X (if COL_X is a numeric column).

Columns with these characteristics are less suitable for indexing:

  • There are many nulls in the column and you do not search on the non-null values.

LONG and LONG RAW columns cannot be indexed.

The size of a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block. Consult with the database administrator for assistance in determining the space required by an index.

Limit the Number of Indexes for Each Table

The more indexes, the more overhead is incurred as the table is altered. When rows are inserted or deleted, all indexes on the table must be updated. When a column is updated, all indexes on the column must be updated.

You must weigh the performance benefit of indexes for queries against the performance overhead of updates. For example, if a table is primarily read-only, you might use more indexes; but, if a table is heavily updated, you might use fewer indexes.

Choose the Order of Columns in Composite Indexes

Although you can specify columns in any order in the CREATE INDEX command, the order of columns in the CREATE INDEX statement can affect query performance. In general, you should put the column expected to be used most often first in the index. You can create a composite index (using several columns), and the same index can be used for queries that reference all of these columns, or just some of them.

For example, assume the columns of the vendor_parts table are as follows:


vendor_id    part_no    unit_cost
1010         10-440          0.27
1010         10-457          5.10
1012         08-300          1.19
1012         10-440          0.25
1012         10-441          0.39
1012         10-457          4.96
1220         08-300          1.33
1292         10-457          5.29

Assume that there are five vendors, and each vendor has about 1000 parts. Suppose that the vendor_parts table is commonly queried by SQL statements such as the following:


SELECT * FROM vendor_parts
    WHERE part_no = 457 AND vendor_id = 1012;

To increase the performance of such queries, you might create a composite index putting the most selective column first; that is, the column with the most values:

CREATE INDEX ind_vendor_id ON vendor_parts (part_no, vendor_id);

Composite indexes speed up queries that use the leading portion of the index. So in this example, queries with WHERE clauses using only the part_no column also note a performance gain. Because there are only five distinct values, placing a separate index on vendor_id would serve no purpose.

Gather Index Statistics

The database can use indexes more effectively when it has statistical information about the tables involved in the queries. You or the DBA can periodically gather statistics by invoking procedures such as DBMS_STATS.GATHER_TABLE_STATISTICS and DBMS_STATS.GATHER_SCHEMA_STATISTICS. For information about these procedures, see Oracle Database PL/SQL Packages and Types Reference.

Drop Indexes That Are No Longer Required

You might drop an index if:

  • It does not speed up queries. The table might be very small, or there might be many rows in the table but very few index entries.

  • The queries in your applications do not use the index.

To find out if an index is being used, you can monitor it. If you see that the index is never used, rarely used, or used in a way that seems to provide no benefit, you can either drop it immediately or you can make it invisible until you are sure that you do not need it, and then drop it. If you discover that you do need the invisible index, you can make it visible again.

When you drop an index, all extents of the index's segment are returned to the containing tablespace and become available for other objects in the tablespace.

If you drop a table, then all associated indexes are dropped.

To drop an index, the index must be contained in your schema or you must have the DROP ANY INDEX system privilege.

Managing Views

Views are customized presentations of data in one or more tables or other views. You can think of them as stored queries. Views do not actually contain data, but instead derive their data from the tables upon which they are based. These tables are referred to as the base tables of the view.

Like tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view actually affect the base tables of the view. Views provide an additional level of security by restricting access to a predetermined set of rows and columns of a table. They also hide data complexity and store complex queries.

Managing Sequences

A sequence is a database object that generates unique sequential values. These values are often used for primary and unique keys. Using a sequence generator to provide the value for a primary key in a table is an easy way to guarantee that the key value is unique.

You can refer to sequence values in SQL statements with these pseudocolumns:

You must qualify CURRVAL and NEXTVAL with the name of the sequence, such as employees_seq.CURRVAL or employees_seq.NEXTVAL.

When you create a sequence, you can define its initial value and the increment between its values. The first reference to NEXTVAL returns the initial value of the sequence. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value. Any reference to CURRVAL always returns the current value of the sequence, which is the value returned by the last reference to NEXTVAL.

Before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL.

Managing Synonyms

A synonym is an alias for any schema object such as a table or view. Synonyms provide an easy way to provide an alternative name for a database object and can be used to simplify SQL statements for database users. For example, you can create a synonym named emps as an alias for the employees table in the HR schema.

If a table in an application has changed, such as the personnel table has replaced the employees table, you can use the employees synonym to refer to the personnel table so that the change is transparent to the application code and the database users.

Because a synonym is simply an alias, it does not require any storage in the database other than its definition.

You can create both public and private synonyms. A public synonym is owned by the special user group named PUBLIC and every user in a database can access it. A private synonym is in the schema of a specific user who has control over its availability to others.