7
SQL Statements (continued)

READ ONLY  

establishes the current transaction as a read-only transaction. This clause established transaction-level read consistency. For more information on this topic, see Oracle8i Concepts.  

All subsequent queries in that transaction only see changes committed before the transaction began. Read-only transactions are useful for reports that run multiple queries against one or more tables while other users update these same tables.  

Restriction: Only the following statements are permitted in a read-only transaction:

• subqueries (that is, SELECT statements without the for_update_clause)

• LOCK TABLE

• SET ROLE

• ALTER SESSION

• ALTER SYSTEM

READ WRITE  

establishes the current transaction as a read-write transaction. This clause established statement-level read consistency, which is the default.  

Restriction: You cannot toggle between transaction-level and statement-level read consistency in the same transaction.  

ISOLATION LEVEL  

specifies how transactions containing database modifications are handled.  

SERIALIZABLE  

specifies serializable transaction isolation mode as defined in SQL92. If a serializable transaction contains data manipulation language (DML) that attempts to update any resource that may have been updated in a transaction uncommitted at the start of the serializable transaction, then the DML statement fails.  

Note: The COMPATIBLE initialization parameter must be set to 7.3.0 or higher for SERIALIZABLE mode to work.  

READ COMMITTED  

is the default Oracle transaction behavior. If the transaction contains DML that requires row locks held by another transaction, then the DML statement waits until the row locks are released.  

USE ROLLBACK SEGMENT  

assigns the current transaction to the specified rollback segment. This clause also implicitly establishes the transaction as a read-write transaction.  

This clause lets you to assign transactions of different types to rollback segments of different sizes. For example:

• If no long-running queries are concurrently reading the same tables, you can assign small transactions to small rollback segments, which are more likely to remain in memory.

• You can assign transactions that modify tables that are concurrently being read by long-running queries to large rollback segments, so that the rollback information needed for the read-consistent queries is not overwritten.

• You can assign transactions that insert, update, or delete large amounts of data to rollback segments large enough to hold the rollback information for the transaction.

You cannot use the READ ONLY clause and the USE ROLLBACK SEGMENT clause in a single SET TRANSACTION statement or in different statements in the same transaction. Read-only transactions do not generate rollback information and therefore are not assigned rollback segments.  

INITIAL  

specifies in bytes the size of the object's first extent. Oracle allocates space for this extent when you create the schema object. Use K or M to specify this size in kilobytes or megabytes.

The default value is the size of 5 data blocks. The minimum value is the size of 2 data blocks for nonbitmapped segments or 3 data blocks for bitmapped segments, plus one data block for each free list group you specify (see "FREELIST GROUPS"). The maximum value depends on your operating system. Oracle rounds values up to the next multiple of the data block size for values less than 5 data blocks, and rounds up to the next multiple of 5 data blocks for values greater than 5 data blocks.

Restriction: You cannot specify INITIAL in an ALTER statement.  

NEXT  

specifies in bytes the size of the next extent to be allocated to the object. Use K or M to specify the size in kilobytes or megabytes. The default value is the size of 5 data blocks. The minimum value is the size of 1 data block. The maximum value depends on your operating system. Oracle rounds values up to the next multiple of the data block size for values less than 5 data blocks. For values greater than 5 data blocks, Oracle rounds up to a value that minimizes fragmentation, as described in Oracle8i Concepts.

If you change the value of the NEXT parameter (that is, if you specify it in an ALTER statement), the next allocated extent will have the specified size, regardless of the size of the most recently allocated extent and the value of the PCTINCREASE parameter.  

PCTINCREASE  

specifies the percent by which the third and subsequent extents grow over the preceding extent. The default value is 50, meaning that each subsequent extent is 50% larger than the preceding extent. The minimum value is 0, meaning all extents after the first are the same size. The maximum value depends on your operating system.  

Oracle rounds the calculated size of each new extent to the nearest multiple of the data block size.

If you change the value of the PCTINCREASE parameter (that is, if you specify it in an ALTER statement), Oracle calculates the size of the next extent using this new value and the size of the most recently allocated extent.  

Suggestion: If you wish to keep all extents the same size, you can prevent SMON from coalescing extents by setting the value of PCTINCREASE to 0. In general, Oracle Corporation recommends a setting of 0 as a way to minimize fragmentation and avoid the possibility of very large temporary segments during processing.  

Restriction: You cannot specify PCTINCREASE for rollback segments. Rollback segments always have a PCTINCREASE value of 0.  

MINEXTENTS  

specifies the total number of extents to allocate when the object is created. This parameter enables you to allocate a large amount of space when you create an object, even if the space available is not contiguous. The default and minimum value is 1, meaning that Oracle allocates only the initial extent, except for rollback segments, for which the default and minimum value is 2. The maximum value depends on your operating system.  

If the MINEXTENTS value is greater than 1, then Oracle calculates the size of subsequent extents based on the values of the INITIAL, NEXT, and PCTINCREASE parameters.

Restriction: You cannot specify MINEXTENTS in an ALTER statement.  

MAXEXTENTS  

specifies the total number of extents, including the first, that Oracle can allocate for the object. The minimum value is 1 (except for rollback segments, which always have a minimum value of 2). The default value depends on your data block size.  

UNLIMITED  

specifies that extents should be allocated automatically as needed. Oracle Corporation recommends this setting as a way to minimize fragmentation.

However, do not use this clause for rollback segments. Rogue transactions containing inserts, updates, or deletes, that continue for a long time will continue to create new extents until a disk is full.  

Caution: A rollback segment that you create without specifying the storage_clause has the same storage parameters as the tablespace that the rollback segment is created in. Thus, if you create the tablespace with MAXEXTENTS UNLIMITED, then the rollback segment will also have the same default.  

FREELIST GROUPS  

for schema objects other than tablespace, specifies the number of groups of free lists for a table, partition, cluster, or index. The default and minimum value for this parameter is 1. Use this parameter only if you are using Oracle with the Parallel Server option in parallel mode.

Oracle uses one data block for each free list group. If you do not specify a large enough value for INITIAL to cover the minimum value plus one data block for each free list group, Oracle increases the value of INITIAL the necessary amount.  

FREELISTS  

for objects other than tablespace, specifies the number of free lists for each of the free list groups for the table, partition, cluster, or index. The default and minimum value for this parameter is 1, meaning that each free list group contains one free list. The maximum value of this parameter depends on the data block size. If you specify a FREELISTS value that is too large, Oracle returns an error indicating the maximum value.  

Restriction: You can specify the FREELISTS and the FREELIST GROUPS parameters only in CREATE TABLE, CREATE CLUSTER, and CREATE INDEX statements.  

OPTIMAL  

is relevant only to rollback segments. It specifies an optimal size in bytes for a rollback segment. Use K or M to specify this size in kilobytes or megabytes. Oracle tries to maintain this size for the rollback segment by dynamically deallocating extents when their data is no longer needed for active transactions. Oracle deallocates as many extents as possible without reducing the total size of the rollback segment below the OPTIMAL value.  

NULL  

specifies no optimal size for the rollback segment, meaning that Oracle never deallocates the rollback segment's extents. This is the default behavior.  

The value of OPTIMAL cannot be less than the space initially allocated for the rollback segment specified by the MINEXTENTS, INITIAL, NEXT, and PCTINCREASE parameters. The maximum value depends on your operating system. Oracle rounds values up to the next multiple of the data block size.  

BUFFER_POOL  

defines a default buffer pool (cache) for a schema object. All blocks for the object are stored in the specified cache. If a buffer pool is defined for a partitioned table or index, then the partitions inherit the buffer pool from the table or index definition, unless overridden by a partition-level definition.  

Note: BUFFER_POOL is not a valid clause for creating or altering tablespaces or rollback segments. For more information about using multiple buffer pools, see Oracle8i Tuning.  

KEEP  

retains the schema object in memory to avoid I/O operations.  

RECYCLE  

eliminates blocks from memory as soon as they are no longer needed, thus preventing an object from taking up unnecessary cache space.  

DEFAULT  

always exists for objects not assigned to KEEP or RECYCLE.  

TABLE  

specifies the schema and name of the table to be truncated. This table cannot be part of a cluster. If you omit schema, Oracle assumes the table is in your own cluster.  

You can truncate index-organized tables and temporary tables. When you truncate a temporary table, only the rows created during the current session are truncated.  

The table's storage parameter NEXT is changed to be the size of the last extent deleted from the segment in the process of truncation.  

Oracle also automatically truncates and resets any existing UNUSABLE indicators for the following indexes on table: range and hash partitions of local indexes and subpartitions of local indexes.

If table is not empty, Oracle marks UNUSABLE all nonpartitioned indexes and all partitions of global partitioned indexes on the table.  

For a domain index, this statement invokes the appropriate truncate routine to truncate the domain index data. For more information, see Oracle8i Data Cartridge Developer's Guide.  

If table (whether it is a regular or index-organized table) contains LOB columns, all LOB data and LOB index segments will be truncated.

If table is partitioned, all partitions or subpartitions, as well as the LOB data and LOB index segments for each partition or subpartition, will be truncated.  

Note: When you truncate a table, Oracle automatically deletes all data in the table's indexes and any materialized view direct-load INSERT information held in association with the table. (This information is independent of any materialized view/snapshot log.) If this direct-load INSERT information is deleted, an incremental refresh of the materialized view may lose data.  

Restrictions:

• You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and re-create the table.

• You cannot truncate the parent table of an enabled referential integrity constraint. You must disable the constraint before truncating the table. (An exception is that you may truncate the table if the integrity constraint is self-referential.)

• You cannot truncate a table if any domain indexes defined on any of its columns are marked LOADING or FAILED.

SNAPSHOT LOG  

specifies whether a snapshot log defined on the table is to be preserved or purged when the table is truncated. This clause allows snapshot master tables to be reorganized through export/import without affecting the ability of primary-key snapshots defined on the master to be fast refreshed. To support continued fast refresh of primary-key snapshots, the snapshot log must record primary-key information. For more information about snapshot logs and the TRUNCATE statement, see Oracle8i Replication.  

PRESERVE  

specifies that any snapshot log should be preserved when the master table is truncated. This is the default.  

PURGE  

specifies that any snapshot log should be purged when the master table is truncated.  

CLUSTER  

specifies the schema and name of the cluster to be truncated. You can truncate only an indexed cluster, not a hash cluster. If you omit schema, Oracle assumes the table is in your own cluster.  

When you truncate a cluster, Oracle also automatically deletes all data in the cluster's tables' indexes.  

DROP STORAGE  

deallocates all space from the deleted rows from the table or cluster except the space allocated by the table's or cluster's MINEXTENTS parameter. This space can subsequently be used by other objects in the tablespace. This is the default.  

REUSE STORAGE  

retains the space from the deleted rows allocated to the table or cluster. Storage values are not reset to the values when the table or cluster was created. This space can subsequently be used only by new data in the table or cluster resulting from inserts or updates.  

The DROP STORAGE clause and REUSE STORAGE clause also apply to the space freed by the data deleted from associated indexes.  

Note: If you have specified more than one free list for the object you are truncating, the REUSE STORAGE clause also removes any mapping of free lists to instances, and resets the high-water mark to the beginning of the first extent.  

hint  

is a comment that passes instructions to the optimizer on choosing an execution plan for the statement. For the syntax and description of hints, see "Hints" and Oracle8i Tuning.

You can place a parallel hint immediately after the UPDATE keyword to parallelize both the underlying scan and UPDATE operations. For detailed information about parallel DML, see Oracle8i Tuning, Oracle8i Parallel Server Concepts and Administration, and Oracle8i Concepts.  

table_expression_clause  

schema  

is the schema containing the table or view. If you omit schema, Oracle assumes the table or view is in your own schema.  

table | view | subquery  

is the name of the table or view, or the columns returned by a subquery, to be updated. Issuing an UPDATE statement against a table fires any UPDATE triggers associated with the table. If you specify view, Oracle updates the view's base table.

If table (or the base table of view) contains one or more domain index columns, this statement executes the appropriate indextype update routine. For more information on these routines, see Oracle8i Data Cartridge Developer's Guide.  

Restrictions:

• You cannot execute this statement if table (or the base table of view) contains any domain indexes marked LOADING or FAILED.

• You cannot specify the sample_clause in an UPDATE statement.

• You cannot specify the order_by_clause in the subquery of the table_expression_clause.

• You cannot update a view except with INSTEAD OF triggers if the view's defining query contains one of the following constructs:

  - A set operator

  - A DISTINCT operator

  - An aggregate function

  - A GROUP BY, ORDER BY, CONNECT BY, or START WITH clause

  - A collection expression in a SELECT list

  - A subquery in a SELECT list

  - Joins (with some exceptions). See Oracle8i Administrator's Guide for details.

• If a view was created with the WITH CHECK OPTION, you can update the view only if the resulting data satisfies the view's defining query.

• If you specify an index, index partition, or index subpartition that has been marked UNUSABLE, the UPDATE statement will fail unless the SKIP_UNUSABLE_INDEXES parameter has been set to TRUE. For more information, see "ALTER SESSION"

PARTITION (partition) | SUBPARTITION (subpartition)  

specifies the name of the partition or subpartition within table targeted for updates. You need not specify the partition name when updating values in a partitioned table. However in some cases specifying the partition name can be more efficient than a complicated where_clause.  

dblink  

is a complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see "Referring to Objects in Remote Databases". You can use a database link to update a remote table or view only if you are using Oracle's distributed functionality.  

If you omit dblink, Oracle assumes the table or view is on the local database.  

with_clause  

restricts the subquery in one of the following ways:  

• WITH READ ONLY specifies that the subquery cannot be updated.

• WITH CHECK OPTION specifies that Oracle prohibits any changes to that table that would produce rows that are not included in the subquery. See the WITH CHECK OPTION Example.

table_collection_expression  

informs Oracle that the collection value expression should be treated as a table. You can use a table_collection_expression to update rows in one table based on rows from another table. For example, you could roll up four quarterly sales tables into a yearly sales table.  

collection_expression  

is a subquery that selects a nested table column from table or view.  

Note: In earlier releases of Oracle, table_collection_expr was expressed as "THE subquery". That usage is now deprecated.  

t_alias  

provides a correlation name for the table, view, or subquery to be referenced elsewhere in the statement.  

Note: This alias is required if the table_expression_clause references any object type attributes or object type methods.  

set_clause  

column  

is the name of a column of the table or view that is to be updated. If you omit a column of the table from the set_clause, that column's value remains unchanged.

Restrictions:

• If column refers to a LOB object attribute, you cannot update it with a literal. Also, before you can update a LOB value, you must lock the row containing the LOB. See the LOB Locking Example.

• If column is part of the partitioning key of a partitioned table, UPDATE will fail if you change a value in the column that would move the row to a different partition or subpartition, unless you enable row movement. See the row_movement_clause of "CREATE TABLE" or "ALTER TABLE".

subquery  

is a subquery that returns exactly one row for each row updated.

• If you specify only one column in the set_clause, the subquery can return only one value.

• If you specify multiple columns in the set_clause, the subquery must return as many values as you have specified columns.

If the subquery returns no rows, then the column is assigned a null. See also "SELECT and Subqueries" and "Using Subqueries".  

expr  

is the new value assigned to the corresponding column. This expression can contain host variables and optional indicator variables. See the syntax description in "Expressions".  

Note: If you insert string literals into a RAW column, during subsequent queries, Oracle will perform a full table scan rather than using any index that might exist on the RAW column.  

where_clause  

restricts the rows updated to those for which the specified condition is TRUE. If you omit this clause, Oracle updates all rows in the table or view. See the syntax description of "Conditions".

The where_clause determines the rows in which values are updated. If you do not specify the where_clause, all rows are updated. For each row that satisfies the where_clause, the columns to the left of the equals (=) operator in the set_clause are set to the values of the corresponding expressions on the right. The expressions are evaluated as the row is updated.  

returning_clause  

retrieves the rows affected by the UPDATE statement.  

• When you are updating a single row, this clause can retrieve column expressions that use the updated columns of the row, rowid, and REFs to the updated row and store them in PL/SQL variables or bind variables.

• When you are updating multiple rows, this clause can stored the values from expressions, rowid, and REFs involving the updated rows in bind arrays.

• You can also use UPDATE with a returning_clause to update from views with single base tables.

expr list  

is some of the syntax descriptions in "Expressions". You must specify a column expression in the expr list for each variable in the data_item list.  

INTO  

indicates that the values of the changed rows are to be stored in the data_item variable(s) specified in data_item list.  

data_item  

is a PL/SQL variable or bind variable which stores the retrieved expr value in the expr list.  

Restrictions:

• You cannot use this clause with parallel DML or with remote objects.

• You cannot retrieve LONG types with this clause.