Home > Oracle Database Usage Infor... > Database Objects: Usage Inf...
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 |
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:
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
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.
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.
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.
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:
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.
Indexes can be categorized in a number of ways. The primary options are:
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.
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.
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.
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.
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.
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:
Create a temporary tablespace using the CREATE
TABLESPACE
statement.
Use the TEMPORARY
TABLESPACE
option of the ALTER
USER
statement to make this your temporary tablespace.
Create the index using the CREATE
INDEX
statement.
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.
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.
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.
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.
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.
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.
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.
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:
CURRVAL
: Returns the current value of a sequence
NEXTVAL
: Increments the sequence and returns the next value
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
.
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.