Introduction To SQL
Structured
Query Language (SQL) is the set of statements with which all programs and users
access data in an Oracle database. Application programs and Oracle tools often
allow users access to the database without using SQL directly, but these
applications in turn must use SQL when executing the user's request. This
chapter provides background information on SQL as used by most database
systems.
This chapter contains these topics:
* History of SQL
* SQL Standards
* Embedded SQL
* Lexical Conventions
This chapter contains these topics:
* History of SQL
* SQL Standards
* Embedded SQL
* Lexical Conventions
SQL Standards
Oracle Corporation strives to comply with industry-accepted
standards and participates actively in SQL standards committees.
Industry-accepted committees are the American National Standards Institute
(ANSI) and the International Standards Organization (ISO), which is affiliated
with the International Electrotechnical Commission (IEC). Both ANSI and the
ISO/IEC have accepted SQL as the standard language for relational databases.
When a new SQL standard is simultaneously published by these organizations, the
names of the standards conform to conventions used by the organization, but the
standards are technically identical.
The latest SQL standard was adopted in July 1999 and is often called SQL:99. The formal names of this standard are:
* ANSI X3.135-1999, "Database Language SQL", Parts 1 ("Framework"), 2 ("Foundation"), and 5 ("Bindings")
* ISO/IEC 9075:1999, "Database Language SQL", Parts 1 ("Framework"), 2 ("Foundation"), and 5 ("Bindings")
How SQL Works
The strengths of SQL provide benefits for all types of users, including application programmers, database administrators, managers, and end users. Technically speaking, SQL is a data sublanguage. The purpose of SQL is to provide an interface to a relational database such as Oracle, and all SQL statements are instructions to the database. In this SQL differs from general-purpose programming languages like C and BASIC. Among the features of SQL are the following:
* It processes sets of data as groups rather than as individual units.
* It provides automatic navigation to the data.
* It uses statements that are complex and powerful individually, and that therefore stand alone. Flow-control statements were not part of SQL originally, but they are found in the recently accepted optional part of SQL, ISO/IEC 9075-5: 1996. Flow-control statements are commonly known as "persistent stored modules" (PSM), and Oracle's PL/SQL extension to SQL is similar to PSM.
Essentially, SQL lets you work with data at the logical level. You need to be concerned with the implementation details only when you want to manipulate the data. For example, to retrieve a set of rows from a table, you define a condition used to filter the rows. All rows satisfying the condition are retrieved in a single step and can be passed as a unit to the user, to another SQL statement, or to an application. You need not deal with the rows one by one, nor do you have to worry about how they are physically stored or retrieved. All SQL statements use the optimizer, a part of Oracle that determines the most efficient means of accessing the specified data. Oracle also provides techniques that you can use to make the optimizer perform its job better.
SQL provides statements for a variety of tasks, including:
* Querying data
* Inserting, updating, and deleting rows in a table
* Creating, replacing, altering, and dropping objects
* Controlling access to the database and its objects
* Guaranteeing database consistency and integrity
SQL unifies all of the above tasks in one consistent language.
Common Language for All Relational Databases
All major relational database management systems support SQL, so you can transfer all skills you have gained with SQL from one database to another. In addition, all programs written in SQL are portable. They can often be moved from one database to another with very little modification.
The latest SQL standard was adopted in July 1999 and is often called SQL:99. The formal names of this standard are:
* ANSI X3.135-1999, "Database Language SQL", Parts 1 ("Framework"), 2 ("Foundation"), and 5 ("Bindings")
* ISO/IEC 9075:1999, "Database Language SQL", Parts 1 ("Framework"), 2 ("Foundation"), and 5 ("Bindings")
How SQL Works
The strengths of SQL provide benefits for all types of users, including application programmers, database administrators, managers, and end users. Technically speaking, SQL is a data sublanguage. The purpose of SQL is to provide an interface to a relational database such as Oracle, and all SQL statements are instructions to the database. In this SQL differs from general-purpose programming languages like C and BASIC. Among the features of SQL are the following:
* It processes sets of data as groups rather than as individual units.
* It provides automatic navigation to the data.
* It uses statements that are complex and powerful individually, and that therefore stand alone. Flow-control statements were not part of SQL originally, but they are found in the recently accepted optional part of SQL, ISO/IEC 9075-5: 1996. Flow-control statements are commonly known as "persistent stored modules" (PSM), and Oracle's PL/SQL extension to SQL is similar to PSM.
Essentially, SQL lets you work with data at the logical level. You need to be concerned with the implementation details only when you want to manipulate the data. For example, to retrieve a set of rows from a table, you define a condition used to filter the rows. All rows satisfying the condition are retrieved in a single step and can be passed as a unit to the user, to another SQL statement, or to an application. You need not deal with the rows one by one, nor do you have to worry about how they are physically stored or retrieved. All SQL statements use the optimizer, a part of Oracle that determines the most efficient means of accessing the specified data. Oracle also provides techniques that you can use to make the optimizer perform its job better.
SQL provides statements for a variety of tasks, including:
* Querying data
* Inserting, updating, and deleting rows in a table
* Creating, replacing, altering, and dropping objects
* Controlling access to the database and its objects
* Guaranteeing database consistency and integrity
SQL unifies all of the above tasks in one consistent language.
Common Language for All Relational Databases
All major relational database management systems support SQL, so you can transfer all skills you have gained with SQL from one database to another. In addition, all programs written in SQL are portable. They can often be moved from one database to another with very little modification.
Embedded SQL
Embedded SQL refers to the use of standard SQL statements
embedded within a procedural programming language. The embedded SQL statements
are documented in the Oracle precompiler books.
Embedded SQL is a collection of these statements:
* All SQL commands, such as SELECT and INSERT, available with SQL with interactive tools
* Dynamic SQL execution commands, such as PREPARE and OPEN, which integrate the standard SQL statements with a procedural programming language
Embedded SQL also includes extensions to some standard SQL statements. Embedded SQL is supported by the Oracle precompilers. The Oracle precompilers interpret embedded SQL statements and translate them into statements that can be understood by procedural language compilers.
Each of these Oracle precompilers translates embedded SQL programs into a different procedural language:
* Pro*C/C++ precompiler
* Pro*COBOL precompiler
* SQL*Module for ADA
Embedded SQL is a collection of these statements:
* All SQL commands, such as SELECT and INSERT, available with SQL with interactive tools
* Dynamic SQL execution commands, such as PREPARE and OPEN, which integrate the standard SQL statements with a procedural programming language
Embedded SQL also includes extensions to some standard SQL statements. Embedded SQL is supported by the Oracle precompilers. The Oracle precompilers interpret embedded SQL statements and translate them into statements that can be understood by procedural language compilers.
Each of these Oracle precompilers translates embedded SQL programs into a different procedural language:
* Pro*C/C++ precompiler
* Pro*COBOL precompiler
* SQL*Module for ADA
Lexical Conventions
The
following lexical conventions for issuing SQL statements apply specifically to
Oracle's implementation of SQL, but are generally acceptable in other SQL
implementations.
When you issue a SQL statement, you can include one or more tabs, carriage returns, spaces, or comments anywhere a space occurs within the definition of the statement. Thus, Oracle evaluates the following two statements in the same manner:
SELECT last_name,salary*12,MONTHS_BETWEEN(hire_date, SYSDATE)
FROM employees;
SELECT last_name,
salary * 12,
MONTHS_BETWEEN( hire_date, SYSDATE )
FROM employees;
Case is insignificant in reserved words, keywords, identifiers and parameters. However, case is significant in text literals and quoted names.
When you issue a SQL statement, you can include one or more tabs, carriage returns, spaces, or comments anywhere a space occurs within the definition of the statement. Thus, Oracle evaluates the following two statements in the same manner:
SELECT last_name,salary*12,MONTHS_BETWEEN(hire_date, SYSDATE)
FROM employees;
SELECT last_name,
salary * 12,
MONTHS_BETWEEN( hire_date, SYSDATE )
FROM employees;
Case is insignificant in reserved words, keywords, identifiers and parameters. However, case is significant in text literals and quoted names.
DataTypes
Each value
manipulated by Oracle has a datatype.
A value's datatype associates a fixed set of properties with the value. These
properties cause Oracle to treat values of one datatype differently from values
of another. For example, you can add values of
NUMBER
datatype, but not values of RAW
datatype.
When
you create a table or cluster, you must specify a datatype for each of its
columns. When you create a procedure or stored function, you must specify a
datatype for each of its arguments. These datatypes define the domain of values
that each column can contain or each argument can have. For example,
DATE
columns cannot accept the value February 29 (except for a
leap year) or the values 2 or 'SHOE'. Each value subsequently placed in a
column assumes the column's datatype. For example, if you insert '01-JAN-98'
into a DATE
column, Oracle treats the
'01-JAN-98' character string as a DATE
value after verifying that it
translates to a valid date.
Oracle
provides a number of built-in datatypes as well as several categories for
user-defined types. The syntax of Oracle datatypes appears in the diagrams that
follow. The text of this section is divided into the following sections:
Oracle Built-in Datatypes
Table 2-1 Built-In Datatype Summary
Character Datatypes
Character
datatypes store character (alphanumeric) data, which are words and free-form
text, in the database character set or national character set. They are less
restrictive than other datatypes and consequently have fewer properties. For
example, character columns can store all alphanumeric values, but
NUMBER
columns can store only numeric values.
Character
data is stored in strings with byte values corresponding to one of the
character sets, such as 7-bit ASCII or EBCDIC, specified when the database was
created. Oracle supports both single-byte and multibyte character sets.
CHAR Datatype
The
CHAR
datatype specifies a fixed-length
character string. Oracle subsequently ensures that all values stored in that
column have the length specified by size
.
If you insert a value that is shorter than the column length, Oracle blank-pads
the value to column length. If you try to insert a value that is too long for
the column, Oracle returns an error.
The
default length for a
CHAR
column is 1 byte and the maximum
allowed is 2000 bytes. A 1-byte string can be inserted into a CHAR(10)
column, but the string is blank-padded to 10 bytes before
it is stored.
When
you create a table with a
CHAR
column, by default you supply the
column length in bytes. The BYTE
qualifier is the same as the
default. If you use the CHAR
qualifier, for example CHAR
(10
CHAR
), you supply the column length in characters. A character
is technically a codepoint of the database character set. Its size can range
from 1 byte to 4 bytes, depending on the database character set. The BYTE
and CHAR
qualifiers override the semantics
specified by the NLS_LENGTH_SEMANTICS
parameter, which has a default of
byte semantics. VARCHAR2 Datatype
The
VARCHAR2
datatype specifies a
variable-length character string. When you create a VARCHAR2
column, you supply the maximum number of bytes or
characters of data that it can hold. Oracle subsequently stores each value in
the column exactly as you specify it, provided the value does not exceed the
column's maximum length. If you try to insert a value that exceeds the
specified length, Oracle returns an error.
You
must specify a maximum length for a
VARCHAR2
column. This maximum must be at
least 1 byte, although the actual length of the string stored is permitted to
be zero. Oracle treats zero-length strings as nulls. You can use the CHAR
qualifier, for example VARCHAR2
(10
CHAR
), to give the maximum length in characters instead of
bytes. A character is technically a codepoint of the database character set. CHAR
and BYTE
qualifiers override the setting of
the NLS_LENGTH_SEMANTICS
parameter, which has a default of
bytes. The maximum length of VARCHAR2
data is 4000 bytes. Oracle
compares VARCHAR2
values using nonpadded comparison
semantics. VARCHAR Datatype
The
VARCHAR
datatype is currently synonymous
with the VARCHAR2
datatype. Oracle recommends that
you use VARCHAR2
rather than VARCHAR
. In the future, VARCHAR
might be defined as a separate
datatype used for variable-length character strings compared with different
comparison semantics. NUMBER Datatype
The
NUMBER
datatype stores zero, positive,
and negative fixed and floating-point numbers with magnitudes between 1.0 x 10-130
and 9.9...9 x 10125 (38 nines followed by 88 zeroes) with 38 digits
of precision. If you specify an arithmetic expression whose value has a
magnitude greater than or equal to 1.0 x 10126, Oracle returns an
error. NUMBER(p,s)
p
is the precision, or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.s
is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.
NUMBER(p)
NUMBER
The
absence of precision and scale designators specifies the maximum range and
precision for an Oracle number.
Scale and Precision
Specify
the scale and precision of a fixed-point number column for extra integrity
checking on input. Specifying scale and precision does not force all values to
a fixed length. If a value exceeds the precision, Oracle returns an error. If a
value exceeds the scale, Oracle rounds it.
Negative Scale
If
the scale is negative, the actual data is rounded to the specified number of
places to the left of the decimal point. For example, a specification of
(10,-2) means to round to hundreds.
Scale Greater than Precision
You
can specify a scale that is greater than precision, although it is uncommon. In
this case, the precision specifies the maximum number of digits to the right of
the decimal point. As with all number datatypes, if the value exceeds the
precision, Oracle returns an error message. If the value exceeds the scale,
Oracle rounds the value. For example, a column defined as
NUMBER(4,5)
requires a zero for the first digit after the decimal
point and rounds all values past the fifth digit after the decimal point. The
following examples show the effects of a scale greater than precision: Floating-Point Numbers
Oracle
lets you specify floating-point numbers, which can have a decimal point
anywhere from the first to the last digit or can have no decimal point at all.
An exponent may optionally be used following the number to increase the range
(e.g. 1.777 e-20). A scale value is not applicable to floating-point
numbers, because the number of digits that can appear after the decimal point
is not restricted.
You
can specify floating-point numbers with the range of values discussed in
"NUMBER Datatype".
The format is defined in "Number Literals.
Oracle also supports the ANSI datatype
FLOAT
.
You can specify this datatype using one of these syntactic forms: FLOAT
specifies a floating-point number with decimal precision 38 or binary precision 126.FLOAT(b)
specifies a floating-point number with binary precisionb
. The precisionb
can range from 1 to 126. To convert from binary to decimal precision, multiplyb
by 0.30103. To convert from decimal to binary precision, multiply the decimal precision by 3.32193. The maximum of 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision.
LONG Datatype
LONG
columns store variable-length
character strings containing up to 2 gigabytes, or 231-1 bytes. LONG
columns have many of the characteristics of VARCHAR2
columns. You can use LONG
columns to store long text strings. The length of LONG
values may be limited by the memory available on your
computer. - A table can contain only one
LONG
column. - You cannot create an object
type with a
LONG
attribute. LONG
columns cannot appear inWHERE
clauses or in integrity constraints (except that they can appear inNULL
andNOT
NULL
constraints).LONG
columns cannot be indexed.- A stored function cannot
return a
LONG
value. - You can declare a variable or
argument of a PL/SQL program unit using the
LONG
datatype. However, you cannot then call the program unit from SQL. - Within a single SQL statement,
all
LONG
columns, updated tables, and locked tables must be located on the same database. LONG
andLONG
RAW
columns cannot be used in distributed SQL statements and cannot be replicated.- If a table has both
LONG
and LOB columns, you cannot bind more than 4000 bytes of data to both theLONG
and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either theLONG
or the LOB column. - A table with
LONG
columns cannot be stored in a tablespace with automatic segment-space management.
GROUP
BY
clauses,ORDER
BY
clauses, orCONNECT
BY
clauses or with theDISTINCT
operator inSELECT
statements- The
UNIQUE
operator of aSELECT
statement - The column list of a
CREATE
CLUSTER
statement - The
CLUSTER
clause of aCREATE
MATERIALIZED
VIEW
statement - SQL built-in functions, expressions, or conditions
SELECT
lists of queries containingGROUP
BY
clausesSELECT
lists of subqueries or queries combined by theUNION
,INTERSECT
, orMINUS
set operatorsSELECT
lists ofCREATE
TABLE
...AS
SELECT
statementsALTER
TABLE
...MOVE
statementsSELECT
lists in subqueries inINSERT
statements
- A SQL statement within a
trigger can insert data into a
LONG
column. - If data from a
LONG
column can be converted to a constrained datatype (such asCHAR
andVARCHAR2
), aLONG
column can be referenced in a SQL statement within a trigger. - Variables in triggers cannot
be declared using the
LONG
datatype. - :
NEW
and :OLD
cannot be used withLONG
columns.
You
can use the Oracle Call Interface functions to retrieve a portion of a
LONG
value from the database. DATE Datatype
The
DATE
datatype stores date and time
information. Although date and time information can be represented in both
character and number datatypes, the DATE
datatype has special associated
properties. For each DATE
value, Oracle stores the following
information: century, year, month, date, hour, minute, and second.
You
can specify a date value as a literal, or you can convert a character or
numeric value to a date value with the
TO_DATE
function. To specify a date as a literal, you must use the Gregorian calendar.
You can specify an ANSI date literal, as shown in this example: DATE '1998-12-25'
The
ANSI date literal contains no time portion, and must be specified in exactly
this format ('YYYY-MM-DD'). Alternatively you can specify an Oracle date
literal, as in the following example:
TO_DATE('98-DEC-25:17:30','YY-MON-DD:HH24:MI')
The
default date format for an Oracle date literal is specified by the
initialization parameter
NLS_DATE_FORMAT
. This example date format includes
a two-digit number for the day of the month, an abbreviation of the month name,
the last two digits of the year, and a 24-hour time designation.
Oracle
automatically converts character values that are in the default date format
into date values when they are used in date expressions.
If
you specify a date value without a time component, the default time is 12:00:00
am (midnight). If you specify a date
value without a date, the default date is the first day of the current month.
Oracle
DATE
columns always contain both the
date and time fields. If your queries use a date format without a time portion,
you must ensure that the time fields in the DATE
column are set to zero (that is, midnight). Otherwise,
Oracle may not return the query results you expect. Here are some examples that
assume a table my_table
with a number column row_num
and a DATE
column datecol
: INSERT INTO my_table VALUES (1, SYSDATE);
INSERT INTO my_table VALUES (2, TRUNC(SYSDATE));
SELECT * FROM my_table;
ROW_NUM DATECOL
---------- ---------
1 04-OCT-00
2 04-OCT-00
SELECT * FROM my_table
WHERE datecol = TO_DATE('04-OCT-00','DD-MON-YY');
ROW_NUM DATECOL
---------- ---------
2 04-OCT-00
If
you know that the time fields of your
DATE
column are set to zero, then you
can query your DATE
column as shown in the second
example above, or by using the DATE
literal: SELECT * FROM my_table WHERE datecol = DATE '2000-10-04';
However,
if the
DATE
column contains nonzero time
fields, then you must filter out the time fields in the query to get the
correct result. For example: SELECT * FROM my_table WHERE TRUNC(datecol) = DATE'2000-10-04';
Oracle
applies the
TRUNC
function to each row in the query,
so performance is better if you ensure the zero value of the time fields in
your data. To ensure that the time fields are set to zero, use one of the
following methods during inserts and updates: · INSERT INTO my_table VALUES
· (3, TO_DATE('4-APR-2000','DD-MON-YYYY'));
·
· INSERT INTO my_table VALUES (4, '04-OCT-00');
·
· INSERT INTO my_table VALUES (5, TRUNC(SYSDATE));
·
The
date function
SYSDATE
returns the current system date
and time. The function CURRENT_DATE
returns the current session date. Date Arithmetic
You
can add and subtract number constants as well as other dates from dates. Oracle
interprets number constants in arithmetic date expressions as numbers of days.
For example,
SYSDATE
+ 1 is tomorrow. SYSDATE
- 7 is one week ago. SYSDATE
+ (10/1440) is ten minutes from now. Subtracting the hiredate
column of the sample table employees
from SYSDATE
returns the number of days since
each employee was hired. You cannot multiply or divide DATE
values.
Oracle
provides functions for many common date operations. For example, the
ADD_MONTHS
function lets you add or subtract months from a date. The MONTHS_BETWEEN
function returns the number of months between two
dates. The fractional portion of the result represents that portion of a 31-day
month.
Because
each date contains a time component, most results of date operations include a
fraction. This fraction means a portion of one day. For example, 1.5 days is 36
hours.
Using Julian Dates
A
Julian date is the number of days since January 1, 4712 bc. Julian dates allow continuous dating from a common
reference. You can use the date format model "J" with date functions
TO_DATE
and TO_CHAR
to convert between Oracle DATE
values and their Julian equivalents. Example
SELECT TO_CHAR(TO_DATE('01-01-1997', 'MM-DD-YYYY'),'J')
FROM DUAL;
TO_CHAR
--------
2450450
TIMESTAMP Datatype
The
TIMESTAMP
datatype is an extension of the DATE
datatype. It stores the year, month, and day of the DATE
datatype, plus hour, minute, and second values. Specify
the TIMESTAMP
datatype as follows: TIMESTAMP [ (fractional_seconds_precision)]
where
fractional_seconds_precision
optionally specifies the number of
digits in the fractional part of the SECOND
datetime field and can be a number
in the range 0 to 9. The default is 6. For example, you specify TIMESTAMP
as a literal as follows: TIMESTAMP'1997-01-31 09:26:50.124'
TIMESTAMP WITH TIME ZONE Datatype
TIMESTAMP
WITH
TIME
ZONE
is a variant of TIMESTAMP
that includes a time zone displacement in its value.
The time zone displacement is the difference (in hours and minutes) between
local time and UTC (Coordinated Universal Time--formerly Greenwich Mean Time).
Specify the TIMESTAMP
WITH
TIME
ZONE
datatype as follows: TIMESTAMP [ (fractional_seconds_precision) ] WITH TIME ZONE
where
fractional_seconds_precision
optionally specifies the number of
digits in the fractional part of the SECOND
datetime field and can be a number
in the range 0 to 9. The default is 6. For example, you specify TIMESTAMP
WITH
TIME
ZONE
as a literal as follows: TIMESTAMP '1997-01-31 09:26:56.66 +02:00'
Two
TIMESTAMP
WITH
TIME
ZONE
values are considered identical if they represent the same
instant in UTC, regardless of the TIME
ZONE
offsets stored in the data. For example, TIMESTAMP '1999-04-15 8:00:00 -8:00'
TIMESTAMP '1999-04-15 11:00:00 -5:00'
You
can replace the UTC offset with the
TZR
(time zone region) format element.
For example, the following example has the same value as the preceding example:
TIMESTAMP '1999-04-15 8:00:00 US/Pacific'
To
eliminate the ambiguity of boundary cases when the daylight savings time
switches, use both the
TZR
and a corresponding TZD
format element. The following example ensures that the
preceding example will return a daylight savings time value: TIMESTAMP '1999-10-31 01:30:00 US/Pacific PDT'
If
you do not add the
TZD
format element, and the datetime
value is ambiguous, then Oracle returns an error if you have the ERROR_ON_OVERLAP_TIME
session parameter set to TRUE
. If that parameter is set to FALSE
, then Oracle interprets the ambiguous datetime as standard
time. TIMESTAMP WITH LOCAL TIME ZONE Datatype
TIMESTAMP
WITH
LOCAL
TIME
ZONE
is another variant of TIMESTAMP
that includes a time
zone displacement in its value. It differs from TIMESTAMP
WITH
TIME
ZONE
in that data stored in the
database is normalized to the database time zone, and the time zone
displacement is not stored as part of the column data. When users retrieve the
data, Oracle returns it in the users' local session time zone. The time zone
displacement is the difference (in hours and minutes) between local time and
UTC (Coordinated Universal Time--formerly Greenwich Mean Time). Specify the TIMESTAMP
WITH
LOCAL
TIME
ZONE
datatype as follows: TIMESTAMP [ (fractional_seconds_precision) ] WITH LOCAL TIME ZONE
where
fractional_seconds_precision
optionally specifies the number of
digits in the fractional part of the SECOND
datetime field and can be a number
in the range 0 to 9. The default is 6. ROWID Datatype
Each
row in the database has an address. You can examine a row's address by querying
the pseudocolumn
ROWID
. Values of this pseudocolumn are
hexadecimal strings representing the address of each row. These strings have
the datatype ROWID
. You can also create tables and
clusters that contain actual columns having the ROWID
datatype. Oracle does not guarantee that the values of
such columns are valid rowids. Restricted Rowids
Beginning
with Oracle8, Oracle SQL incorporated an extended format for rowids to
efficiently support partitioned tables and indexes and tablespace-relative data
block addresses (DBAs) without ambiguity.
Character
values representing rowids in Oracle7 and earlier releases are called restricted rowids. Their format is
as follows:
block.row.file
block
is a hexadecimal string identifying the data block of the datafile containing the row. The length of this string depends on your operating system.row
is a four-digit hexadecimal string identifying the row in the data block. The first row of the block has a digit of 0.file
is a hexadecimal string identifying the database file containing the row. The first datafile has the number 1. The length of this string depends on your operating system.
Extended Rowids
The
extended
ROWID
datatype stored in a user column includes the data in the
restricted rowid plus a data object
number. The data object number is an identification number
assigned to every database segment. You can retrieve the data object number
from the data dictionary views USER_OBJECTS
, DBA_OBJECTS
, and ALL_OBJECTS
. Objects that share the same
segment (clustered tables in the same cluster, for example) have the same
object number.
Extended
rowids are stored as base 64 values that can contain the characters A-Z, a-z,
0-9, as well as the plus sign (+) and forward slash (/). Extended rowids are
not available directly. You can use a supplied package,
DBMS_ROWID
, to interpret extended rowid contents. The package functions
extract and provide information that would be available directly from a
restricted rowid as well as information specific to extended rowids. Compatibility and Migration
The
restricted form of a rowid is still supported in Oracle9i for backward compatibility, but all tables return rowids
in the extended format.
UROWID Datatype
Each
row in a database has an address. However, the rows of some tables have
addresses that are not physical or permanent or were not generated by Oracle.
For example, the row addresses of index-organized tables are stored in index
leaves, which can move. Rowids of foreign tables (such as DB2 tables accessed
through a gateway) are not standard Oracle rowids.
Oracle
uses "universal rowids" (urowids)
to store the addresses of index-organized and foreign tables. Index-organized
tables have logical urowids and foreign tables have foreign urowids. Both types
of urowid are stored in the
ROWID
pseudocolumn (as are the physical
rowids of heap-organized tables).
Oracle
creates logical rowids based on a table's primary key. The logical rowids do
not change as long as the primary key does not change. The
ROWID
pseudocolumn of an index-organized table has a datatype of
UROWID
. You can access this pseudocolumn
as you would the ROWID
pseudocolumn of a heap-organized
table (that is, using the SELECT
ROWID
statement). If you wish to
store the rowids of an index-organized table, you can
define a column of type UROWID
for the table and retrieve the
value of the ROWID
pseudocolumn into that column. Literals
The
terms literal and constant value are synonymous and refer to a fixed data
value. For example, 'JACK', 'BLUE
ISLAND', and '101' are
all character literals; 5001 is a numeric literal. Character literals are
enclosed in single quotation marks, which enable Oracle to distinguish them
from schema object names.
This section contains these topics:
o Text Literals
o Integer Literals
o Number Literals
o Interval Literals
Many sql statements and functions require you to specify character and numeric literal values. You can also specify literals as part of expressions and conditions. You can specify character literals with the 'text' notation, national character literals with the N'text' notation, and numeric literals with the integer or number notation, depending on the context of the literal. The syntactic forms of these notations appear in the sections that follow.
To specify a datetime or interval datatype as a literal, you must take into account any optional precisions included in the datatypes. Examples of specifying datetime and interval datatypes as literals are provided in the relevant sections of "Datatypes".
This section contains these topics:
o Text Literals
o Integer Literals
o Number Literals
o Interval Literals
Many sql statements and functions require you to specify character and numeric literal values. You can also specify literals as part of expressions and conditions. You can specify character literals with the 'text' notation, national character literals with the N'text' notation, and numeric literals with the integer or number notation, depending on the context of the literal. The syntactic forms of these notations appear in the sections that follow.
To specify a datetime or interval datatype as a literal, you must take into account any optional precisions included in the datatypes. Examples of specifying datetime and interval datatypes as literals are provided in the relevant sections of "Datatypes".
Text Literals
Text
specifies a text or character literal. You must use this notation to specify
values whenever 'text' or char appear in expressions, conditions, sql
functions, and sql statements in other parts of this reference.
The syntax of text is as follows:
text::=
where
* N specifies representation of the literal using the national character set. Text entered using this notation is translated into the national character set by Oracle when used.
* c is any member of the user's character set, except a single quotation mark (').
* ' ' are two single quotation marks that begin and end text literals. To represent one single quotation mark within a literal, enter two single quotation marks.
A text literal must be enclosed in single quotation marks. This reference uses the terms text literal and character literal interchangeably.
Text literals have properties of both the CHAR and VARCHAR2 datatypes:
* Within expressions and conditions, Oracle treats text literals as though they have the datatype CHAR by comparing them using blank-padded comparison semantics.
* A text literal can have a maximum length of 4000 bytes.
Here are some valid text literals:
'Hello'
'ORACLE.dbs'
'Jackie''s raincoat'
'09-MAR-98'
N'nchar literal'
The syntax of text is as follows:
text::=
where
* N specifies representation of the literal using the national character set. Text entered using this notation is translated into the national character set by Oracle when used.
* c is any member of the user's character set, except a single quotation mark (').
* ' ' are two single quotation marks that begin and end text literals. To represent one single quotation mark within a literal, enter two single quotation marks.
A text literal must be enclosed in single quotation marks. This reference uses the terms text literal and character literal interchangeably.
Text literals have properties of both the CHAR and VARCHAR2 datatypes:
* Within expressions and conditions, Oracle treats text literals as though they have the datatype CHAR by comparing them using blank-padded comparison semantics.
* A text literal can have a maximum length of 4000 bytes.
Here are some valid text literals:
'Hello'
'ORACLE.dbs'
'Jackie''s raincoat'
'09-MAR-98'
N'nchar literal'
Integer Literals
You must use the integer notation to specify an integer
whenever integer appears in expressions, conditions, sql functions, and sql
statements described in other parts of this reference.
The syntax of integer is as follows:
integer::=
where digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.
An integer can store a maximum of 38 digits of precision.
Here are some valid integers:
7
+255
The syntax of integer is as follows:
integer::=
where digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.
An integer can store a maximum of 38 digits of precision.
Here are some valid integers:
7
+255
Number Literals
You must
use the number notation to specify values whenever number appears in
expressions, conditions, sql functions, and sql statements in
other parts of this reference.
The syntax of number is as follows:
number::=
where
* + or - indicates a positive or negative value. If you omit the sign, a positive value is the default.
* digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8 or 9.
* e or E indicates that the number is specified in scientific notation. The digits after the E specify the exponent. The exponent can range from -130 to 125.
A number can store a maximum of 38 digits of precision.
If you have established a decimal character other than a period (.) with the initialization parameter NLS_NUMERIC_CHARACTERS, you must specify numeric literals with 'text' notation. In such cases, Oracle automatically converts the text literal to a numeric value.
For example, if the NLS_NUMERIC_CHARACTERS parameter specifies a decimal character of comma, specify the number 5.123 as follows:
'5,123'
Here are some valid representations of number:
25
+6.34
0.5
25e-03
-1
The syntax of number is as follows:
number::=
where
* + or - indicates a positive or negative value. If you omit the sign, a positive value is the default.
* digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8 or 9.
* e or E indicates that the number is specified in scientific notation. The digits after the E specify the exponent. The exponent can range from -130 to 125.
A number can store a maximum of 38 digits of precision.
If you have established a decimal character other than a period (.) with the initialization parameter NLS_NUMERIC_CHARACTERS, you must specify numeric literals with 'text' notation. In such cases, Oracle automatically converts the text literal to a numeric value.
For example, if the NLS_NUMERIC_CHARACTERS parameter specifies a decimal character of comma, specify the number 5.123 as follows:
'5,123'
Here are some valid representations of number:
25
+6.34
0.5
25e-03
-1
Nulls
If
a column in a row has no value, then the column is said to be null, or to
contain a null. Nulls can appear in columns of any datatype that are not
restricted by NOT NULL or PRIMARY KEY integrity constraints. Use a null when
the actual value is not known or when a value would not be meaningful.
Do not use null to represent a value of zero, because they are not equivalent. (Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.) Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand..
Do not use null to represent a value of zero, because they are not equivalent. (Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.) Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand..
Nulls in SQL Functions
All scalar functions (except REPLACE, NVL, and CONCAT)
return null when given a null argument. You can use the NVL function to return
a value when a null occurs. For example, the expression NVL(COMM,0) returns 0
if COMM is null or the value of COMM if it is not null..
Most aggregate functions ignore nulls. For example, consider a query that averages the five values 1000, null, null, null, and 2000. Such a query ignores the nulls and calculates the average to be (1000+2000)/2 = 1500..
Most aggregate functions ignore nulls. For example, consider a query that averages the five values 1000, null, null, null, and 2000. Such a query ignores the nulls and calculates the average to be (1000+2000)/2 = 1500..
Nulls with Comparison Conditions
To test for nulls, use only the comparison conditions IS
NULL and IS NOT NULL. If you use any other condition with nulls and the result
depends on the value of the null, the result is UNKNOWN. Because null
represents a lack of data, a null cannot be equal or unequal to any value or to
another null. However, Oracle considers two nulls to be equal when evaluating a
DECODE function..
Oracle also considers two nulls to be equal if they appear in compound keys. That is, Oracle considers identical two compound keys containing nulls if all the non-null components of the keys are equal..
Oracle also considers two nulls to be equal if they appear in compound keys. That is, Oracle considers identical two compound keys containing nulls if all the non-null components of the keys are equal..
Nulls in Conditions
A condition that evaluates to UNKNOWN acts almost like
FALSE. For example, a SELECT statement with a condition in the WHERE clause
that evaluates to UNKNOWN returns no rows. However, a condition evaluating to
UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition
evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT
UNKNOWN evaluates to UNKNOWN. .
Following table shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query..
Following table shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query..
If A
is:
|
Condition
|
Evaluates
to:
|
10
|
a IS
NULL
|
FALSE
|
10
|
a IS NOT
NULL
|
TRUE
|
NULL
|
a IS
NULL
|
TRUE
|
NULL
|
a IS NOT
NULL
|
FALSE
|
10
|
a = NULL
|
UNKNOWN
|
10
|
a !=
NULL
|
UNKNOWN
|
NULL
|
a = NULL
|
UNKNOWN
|
NULL
|
a !=
NULL
|
UNKNOWN
|
NULL
|
a = 10
|
UNKNOWN
|
NULL
|
a != 10
|
UNKNOWN
|
Pseudocolumns
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. This section describes these pseudocolumns:
* CURRVAL and NEXTVAL
* LEVEL
* ROWID
* ROWNUM
* CURRVAL and NEXTVAL
* LEVEL
* ROWID
* ROWNUM
CURRVAL and NEXTVAL
A sequence is a schema object that can generate unique
sequential values. These values are often used for primary and unique keys. You
can refer to sequence values in sql statements with these pseudocolumns:
CURRVAL
The CURRVAL pseudocolumn returns the current value of a sequence.
NEXTVAL
The NEXTVAL pseudocolumn increments the sequence and returns the next value.
You must qualify CURRVAL and NEXTVAL with the name of the sequence:
sequence.CURRVAL
sequence.NEXTVAL
To refer to the current or next value of a sequence in the schema of another user, you must have been granted either SELECT object privilege on the sequence or SELECT ANY SEQUENCE system privilege, and you must qualify the sequence with the schema containing it:
schema.sequence.CURRVAL
schema.sequence.NEXTVAL
To refer to the value of a sequence on a remote database, you must qualify the sequence with a complete or partial name of a database link:
schema.sequence.CURRVAL@dblink
schema.sequence.NEXTVAL@dblink
Where to Use Sequence Values
You can use CURRVAL and NEXTVAL in:
* The SELECT list of a SELECT statement that is not contained in a subquery, materialized view, or view
* The SELECT list of a subquery in an INSERT statement
* The VALUES clause of an INSERT statement
* The SET clause of an UPDATE statement
Restrictions: You cannot use CURRVAL and NEXTVAL:
* A subquery in a DELETE, SELECT, or UPDATE statement
* A query of a view or of a materialized view
* A SELECT statement with the DISTINCT operator
* A SELECT statement with a GROUP BY clause or ORDER BY clause
* A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
* The WHERE clause of a SELECT statement
* DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
* The condition of a CHECK constraint
Also, within a single sql statement that uses CURRVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.
CURRVAL
The CURRVAL pseudocolumn returns the current value of a sequence.
NEXTVAL
The NEXTVAL pseudocolumn increments the sequence and returns the next value.
You must qualify CURRVAL and NEXTVAL with the name of the sequence:
sequence.CURRVAL
sequence.NEXTVAL
To refer to the current or next value of a sequence in the schema of another user, you must have been granted either SELECT object privilege on the sequence or SELECT ANY SEQUENCE system privilege, and you must qualify the sequence with the schema containing it:
schema.sequence.CURRVAL
schema.sequence.NEXTVAL
To refer to the value of a sequence on a remote database, you must qualify the sequence with a complete or partial name of a database link:
schema.sequence.CURRVAL@dblink
schema.sequence.NEXTVAL@dblink
Where to Use Sequence Values
You can use CURRVAL and NEXTVAL in:
* The SELECT list of a SELECT statement that is not contained in a subquery, materialized view, or view
* The SELECT list of a subquery in an INSERT statement
* The VALUES clause of an INSERT statement
* The SET clause of an UPDATE statement
Restrictions: You cannot use CURRVAL and NEXTVAL:
* A subquery in a DELETE, SELECT, or UPDATE statement
* A query of a view or of a materialized view
* A SELECT statement with the DISTINCT operator
* A SELECT statement with a GROUP BY clause or ORDER BY clause
* A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
* The WHERE clause of a SELECT statement
* DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
* The condition of a CHECK constraint
Also, within a single sql statement that uses CURRVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.
How to Use Sequence Values
When you create a sequence, you can define its initial
value and the increment between its values. The first reference to NEXTVAL
returns the sequence's initial value. Subsequent references to NEXTVAL
increment the sequence value by the defined increment and return the new value.
Any reference to CURRVAL always returns the sequence's current value, which is
the value returned by the last reference to NEXTVAL. Note that before you use
CURRVAL for a sequence in your session, you must first initialize the sequence
with NEXTVAL.
Within a single sql statement, Oracle will increment the sequence only once for each row. If a statement contains more than one reference to NEXTVAL for a sequence, Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL. If a statement contains references to both CURRVAL and NEXTVAL, Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL regardless of their order within the statement.
A sequence can be accessed by many users concurrently with no waiting or locking.
Finding the current value of a sequence: Example
This example selects the current value of the employee sequence in the sample schema hr:
SELECT employees_seq.currval
FROM DUAL;
Within a single sql statement, Oracle will increment the sequence only once for each row. If a statement contains more than one reference to NEXTVAL for a sequence, Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL. If a statement contains references to both CURRVAL and NEXTVAL, Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL regardless of their order within the statement.
A sequence can be accessed by many users concurrently with no waiting or locking.
Finding the current value of a sequence: Example
This example selects the current value of the employee sequence in the sample schema hr:
SELECT employees_seq.currval
FROM DUAL;
Inserting sequence values into a table: Example
This example increments the employee sequence and uses its
value for a new employee inserted into the sample table hr.employees:
INSERT INTO employees
VALUES (employees_seq.nextval, 'John', 'Doe', 'jdoe',
'555-1212', TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null,
30);
INSERT INTO employees
VALUES (employees_seq.nextval, 'John', 'Doe', 'jdoe',
'555-1212', TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null,
30);
Reusing the current value of a sequence: Example
This example adds a new order with the next order number to
the master order table. It then adds suborders with this number to the detail
order table:
INSERT INTO orders (order_id, order_date, customer_id)
VALUES (orders_seq.nextval, TO_DATE(SYSDATE), 106);
INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 1, 2359);
INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 2, 3290);
INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 3, 2381);
INSERT INTO orders (order_id, order_date, customer_id)
VALUES (orders_seq.nextval, TO_DATE(SYSDATE), 106);
INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 1, 2359);
INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 2, 3290);
INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 3, 2381);
LEVEL
For each row returned by a hierarchical query, the LEVEL
pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. A
root row is the highest row within an inverted tree. A child row is any nonroot
row. A parent row is any row that has children. A leaf row is any row without
children. Figure 2-1 shows the nodes of an inverted tree with their LEVEL
values.
To define a hierarchical relationship in a query, you must use the START WITH and CONNECT BY clauses.
To define a hierarchical relationship in a query, you must use the START WITH and CONNECT BY clauses.
ROWID
For each
row in the database, the ROWID pseudocolumn returns a row's address. Oracle9i
rowid values contain information necessary to locate a row:
* The data object number of the object
* Which data block in the datafile
* Which row in the data block (first row is 0)
* Which datafile (first file is 1). The file number is relative to the tablespace.
Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.
Values of the ROWID pseudocolumn have the datatype ROWID or UROWID.
Rowid values have several important uses:
* They are the fastest way to access a single row.
* They can show you how a table's rows are stored.
* They are unique identifiers for rows in a table.
You should not use ROWID as a table's primary key. If you delete and reinsert a row with the Import and Export utilities, for example, its rowid may change. If you delete a row, Oracle may reassign its rowid to a new row inserted later.
Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudocolumn.
Example
This statement selects the address of all rows that contain data for employees in department 20:
SELECT ROWID, last_name
FROM employees
WHERE department_id = 20;
ROWNUM
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
You can use ROWNUM to limit the number of rows returned by a query, as in this example:
SELECT * FROM employees WHERE ROWNUM < 10;
If an ORDER BY clause follows ROWNUM in the same query, the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index to access the data, Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:
SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;
If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the 10 smallest employee numbers. This is sometimes referred to as a "top-N query":
SELECT * FROM
(SELECT * FROM employees ORDER BY employee_id)
WHERE ROWNUM < 11;
In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered by employee_id in the subquery.
Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:
SELECT * FROM employees
WHERE ROWNUM > 1;
The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.
You can also use ROWNUM to assign unique values to each row of a table, as in this example:
UPDATE my_table
SET column1 = ROWNUM;
* The data object number of the object
* Which data block in the datafile
* Which row in the data block (first row is 0)
* Which datafile (first file is 1). The file number is relative to the tablespace.
Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.
Values of the ROWID pseudocolumn have the datatype ROWID or UROWID.
Rowid values have several important uses:
* They are the fastest way to access a single row.
* They can show you how a table's rows are stored.
* They are unique identifiers for rows in a table.
You should not use ROWID as a table's primary key. If you delete and reinsert a row with the Import and Export utilities, for example, its rowid may change. If you delete a row, Oracle may reassign its rowid to a new row inserted later.
Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudocolumn.
Example
This statement selects the address of all rows that contain data for employees in department 20:
SELECT ROWID, last_name
FROM employees
WHERE department_id = 20;
ROWNUM
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
You can use ROWNUM to limit the number of rows returned by a query, as in this example:
SELECT * FROM employees WHERE ROWNUM < 10;
If an ORDER BY clause follows ROWNUM in the same query, the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index to access the data, Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:
SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;
If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the 10 smallest employee numbers. This is sometimes referred to as a "top-N query":
SELECT * FROM
(SELECT * FROM employees ORDER BY employee_id)
WHERE ROWNUM < 11;
In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered by employee_id in the subquery.
Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:
SELECT * FROM employees
WHERE ROWNUM > 1;
The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.
You can also use ROWNUM to assign unique values to each row of a table, as in this example:
UPDATE my_table
SET column1 = ROWNUM;
SQL - Comments
You
can associate comments with sql statements and schema objects.
Comments Within SQL Statements
Comments
within sql statements do not affect the statement execution, but they
may make your application easier for you to read and maintain. You may want to
include a comment in a statement that describes the statement's purpose within
your application.
A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement using either of these means:
* Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. This text can span multiple lines. End the comment with an asterisk and a slash (*/). The opening and terminating characters need not be separated from the text by a space or a line break.
* Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.
A sql statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.
Example
These statements contain many comments:
SELECT last_name, salary + NVL(commission_pct, 0),
job_id, e.department_id
/* Select all employees whose compensation is
greater than that of Pataballa.*/
FROM employees e, departments d
/*The DEPARTMENTS table is used to get the department name.*/
WHERE e.department_id = d.department_id
AND salary + NVL(commission_pct,0) > /* Subquery: */
(SELECT salary + NVL(commission_pct,0)
/* total compensation is salar + commission_pct */
FROM employees
WHERE last_name = 'Pataballa');
SELECT last_name, -- select the name
salary + NVL(commission_pct, 0),-- total compensation
job_id, -- job
e.department_id -- and department
FROM employees e, -- of all employees
departments d
WHERE e.department_id = d.department_id
AND salary + NVL(commission_pct, 0) > -- whose compensation
-- is greater than
(SELECT salary + NVL(commission_pct,0) -- the compensation
FROM employees
WHERE last_name = 'Pataballa') -- of Pataballa.
;
A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement using either of these means:
* Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. This text can span multiple lines. End the comment with an asterisk and a slash (*/). The opening and terminating characters need not be separated from the text by a space or a line break.
* Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.
A sql statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.
Example
These statements contain many comments:
SELECT last_name, salary + NVL(commission_pct, 0),
job_id, e.department_id
/* Select all employees whose compensation is
greater than that of Pataballa.*/
FROM employees e, departments d
/*The DEPARTMENTS table is used to get the department name.*/
WHERE e.department_id = d.department_id
AND salary + NVL(commission_pct,0) > /* Subquery: */
(SELECT salary + NVL(commission_pct,0)
/* total compensation is salar + commission_pct */
FROM employees
WHERE last_name = 'Pataballa');
SELECT last_name, -- select the name
salary + NVL(commission_pct, 0),-- total compensation
job_id, -- job
e.department_id -- and department
FROM employees e, -- of all employees
departments d
WHERE e.department_id = d.department_id
AND salary + NVL(commission_pct, 0) > -- whose compensation
-- is greater than
(SELECT salary + NVL(commission_pct,0) -- the compensation
FROM employees
WHERE last_name = 'Pataballa') -- of Pataballa.
;
Comments on Schema Objects
You can associate a comment with a table, view,
materialized view, or column using the COMMENT command. Comments associated
with schema objects are stored in the data dictionary.
Hints
You can
use comments in a sql statement to pass instructions, or hints, to the
Oracle optimizer. The optimizer uses these hints as suggestions for choosing an
execution plan for the statement.
A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, or DELETE keyword. The syntax below shows hints contained in both styles of comments that Oracle supports within a statement block.
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
or
{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...
where:
* DELETE, INSERT, SELECT, or UPDATE is a DELETE, INSERT, SELECT, or UPDATE keyword that begins a statement block. Comments containing hints can appear only after these keywords.
* + is a plus sign that causes Oracle to interpret the comment as a list of hints. The plus sign must follow immediately after the comment delimiter (no space is permitted).
* hint is one of the hints discussed in this section. The space between the plus sign and the hint is optional. If the comment contains multiple hints, separate the hints by at least one space.
* text is other commenting text that can be interspersed with the hints.
all_rows_hint::=
The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).
and_equal_hint::=
The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes.
append_hint::=
The APPEND hint lets you enable direct-path INSERT if your database is running in serial mode. (Your database is in serial mode if you are not using Enterprise Edition. Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode).
In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. In addition, direct-path INSERT bypasses the buffer cache and ignores integrity constraints. As a result, direct-path INSERT can be considerably faster than conventional INSERT.
cache_hint::=
The CACHE hint specifies that the blocks retrieved for the table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.
choose_hint::=
The CHOOSE hint causes the optimizer to choose between the rule-based and cost-based approaches for a sql statement. The optimizer bases its selection on the presence of statistics for the tables accessed by the statement. If the data dictionary has statistics for at least one of these tables, then the optimizer uses the cost-based approach and optimizes with the goal of best throughput. If the data dictionary does not have statistics for these tables, then it uses the rule-based approach.
cluster_hint::=
The CLUSTER hint explicitly chooses a cluster scan to access the specified table. It applies only to clustered objects.
cursor_sharing_exact_hint::=
Oracle can replace literals in sql statements with bind variables if it is safe to do so. This is controlled with the CURSOR_SHARING startup parameter. The CURSOR_SHARING_EXACT hint causes this behavior to be switched off. In other words, Oracle executes the sql statement without any attempt to replace literals by bind variables.
driving_site_hint::=
The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization.
fact_hint::=
The FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should be considered as a fact table.
first_rows_hint::=
The hints FIRST_ROWS(n) (where n is any positive integer) or FIRST_ROWS instruct Oracle to optimize an individual sql statement for fast response. FIRST_ROWS(n) affords greater precision, because it instructs Oracle to choose the plan that returns the first n rows most efficiently. The FIRST_ROWS hint, which optimizes for the best plan to return the first single row, is retained for backward compatibility and plan stability.
full_hint::=
The FULL hint explicitly chooses a full table scan for the specified table.
hash_hint::=
The HASH hint explicitly chooses a hash scan to access the specified table. It applies only to tables stored in a cluster.
hash_aj_hint::=
For a specific query, place the HASH_SJ, MERGE_SJ, or NL_SJ hint into the EXISTS subquery. HASH_SJ uses a hash semi-join, MERGE_SJ uses a sort merge semi-join, and NL_SJ uses a nested loop semi-join.
hash_sj_hint::=
For a specific query, place the HASH_SJ, MERGE_SJ, or NL_SJ hint into the EXISTS subquery. HASH_SJ uses a hash semi-join, MERGE_SJ uses a sort merge semi-join, and NL_SJ uses a nested loop semi-join.
index_hint::=
The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B-tree, bitmap, and bitmap join indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for bitmap indexes, because it is a more versatile hint.
index_asc_hint::=
The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values.
index_combine_hint::=
The INDEX_COMBINE hint explicitly chooses a bitmap access path for the table. If no indexes are given as arguments for the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate for the table. If certain indexes are given as arguments, then the optimizer tries to use some Boolean combination of those particular bitmap indexes.
index_desc_hint::=
The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition.
index_ffs_hint::=
The INDEX_FFS hint causes a fast full index scan to be performed rather than a full table scan.
leading_hint::=
The LEADING hint causes Oracle to use the specified table as the first table in the join order.
If you specify two or more LEADING hints on different tables, then all of them are ignored. If you specify the ORDERED hint, then it overrides all LEADING hints.
merge_hint::=
The MERGE hint lets you merge a view on a per-query basis.
If a view's query contains a GROUP BY clause or DISTINCT operator in the SELECT list, then the optimizer can merge the view's query into the accessing statement only if complex view merging is enabled. Complex merging can also be used to merge an IN subquery into the accessing statement if the subquery is uncorrelated.
Complex merging is not cost-based--that is, the accessing query block must include the MERGE hint. Without this hint, the optimizer uses another approach.
merge_aj_hint::=
See HASH_AJ hint.
merge_sj_hint::=
See HASH_SJ hint.
nl_aj_hint::=
See HASH_AJ hint.
nl_sj_hint::=
See HASH_SJ hint.
noappend_hint::=
The NOAPPEND hint enables conventional INSERT by disabling parallel mode for the duration of the INSERT statement. (Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode).
nocache_hint::=
The NOCACHE hint specifies that the blocks retrieved for the table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache.
no_expand_hint::=
The NO_EXPAND hint prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it.
no_fact_hint::=
The NO_FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should not be considered as a fact table.
no_index_hint::=
The NO_INDEX hint explicitly disallows a set of indexes for the specified table.
no_merge_hint::=
The NO_MERGE hint causes Oracle not to merge mergeable views.
noparallel_hint::=
The NOPARALLEL hint overrides a PARALLEL specification in the table clause. In general, hints take precedence over table clauses.
Restriction: You cannot parallelize a query involving a nested table.
noparallel_index_hint::=
The NOPARALLEL_INDEX hint overrides a PARALLEL attribute setting on an index to avoid a parallel index scan operation.
no_push_pred_hint::=
The NO_PUSH_PRED hint prevents pushing of a join predicate into the view.
norewrite_hint::=
The NOREWRITE hint disables query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED. Use the NOREWRITE hint on any query block of a request.
no_unnest_hint::=
If you enabled subquery unnesting with the UNNEST_SUBQUERY parameter, then the NO_UNNEST hint turns it off for specific subquery blocks.
ordered_hint::=
The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.
If you omit the ORDERED hint from a sql statement performing a join, then the optimizer chooses the order in which to join the tables. You might want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information lets you choose an inner and outer table better than the optimizer could.
ordered_predicates_hint::=
The ORDERED_PREDICATES hint forces the optimizer to preserve the order of predicate evaluation, except for predicates used as index keys. Use this hint in the WHERE clause of SELECT statements.
If you do not use the ORDERED_PREDICATES hint, then Oracle evaluates all predicates in the order specified by the following rules. Predicates:
* Without user-defined functions, type methods, or subqueries are evaluated first, in the order specified in the WHERE clause.
* With user-defined functions and type methods that have user-computed costs are evaluated next, in increasing order of their cost.
* With user-defined functions and type methods without user-computed costs are evaluated next, in the order specified in the WHERE clause.
* Not specified in the WHERE clause (for example, predicates transitively generated by the optimizer) are evaluated next.
* With subqueries are evaluated last in the order specified in the WHERE clause. parallel_hint::=
The PARALLEL hint lets you specify the desired number of concurrent servers that can be used for a parallel operation. The hint applies to the INSERT, UPDATE, and DELETE portions of a statement as well as to the table scan portion.
parallel_index_hint::=
The PARALLEL_INDEX hint specifies the desired number of concurrent servers that can be used to parallelize index range scans for partitioned indexes.
pq_distribute_hint::=
The PQ_DISTRIBUTE hint improves parallel join operation performance. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers. Using this hint overrides decisions the optimizer would normally make.
Use the EXPLAIN PLAN statement to identify the distribution chosen by the optimizer. The optimizer ignores the distribution hint if both tables are serial.
push_pred_hint::=
The PUSH_PRED hint forces pushing of a join predicate into the view.
push_subq_hint::=
The PUSH_SUBQ hint causes non-merged subqueries to be evaluated at the earliest possible place in the execution plan. Generally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then it improves performance to evaluate the subquery earlier.
This hint has no effect if the subquery is applied to a remote table or one that is joined using a merge join.
rewrite_hint::=
The REWRITE hint forces the cost-based optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. Use the REWRITE hint with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, then Oracle uses that view regardless of its cost.
Oracle does not consider views outside of the list. If you do not specify a view list, then Oracle searches for an eligible materialized view and always uses it regardless of its cost.
rowid_hint::=
The ROWID hint explicitly chooses a table scan by rowid for the specified table.
rule_hint::=
The RULE hint explicitly chooses rule-based optimization for a statement block. It also makes the optimizer ignore other hints specified for the statement block.
star_hint::=
The STAR hint forces a star query plan to be used, if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least three tables, the large table's concatenated index has at least three columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.
star_transformation_hint::=
The STAR_TRANSFORMATION hint makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a cost-based decision to use the best plan generated without the transformation, instead of the best plan for the transformed query.
Even if the hint is given, there is no guarantee that the transformation will take place. The optimizer only generates the subqueries if it seems reasonable to do so. If no subqueries are generated, then there is no transformed query, and the best plan for the untransformed query is used, regardless of the hint.
unnest_hint::=
If the UNNEST_SUBQUERY parameter is set to true, then the UNNEST hint checks the subquery block for validity only. If it is valid, then subquery unnesting is enabled without Oracle checking the heuristics.
use_concat_hint::=
The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Generally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.
The USE_CONCAT hint turns off IN-list processing and OR-expands all disjunctions, including IN-lists.
use_hash_hint::=
The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join.
use_merge_hint::=
The USE_MERGE hint causes Oracle to join each specified table with another row source with a sort-merge join.
use_nl_hint::=
The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table.
A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, or DELETE keyword. The syntax below shows hints contained in both styles of comments that Oracle supports within a statement block.
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
or
{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...
where:
* DELETE, INSERT, SELECT, or UPDATE is a DELETE, INSERT, SELECT, or UPDATE keyword that begins a statement block. Comments containing hints can appear only after these keywords.
* + is a plus sign that causes Oracle to interpret the comment as a list of hints. The plus sign must follow immediately after the comment delimiter (no space is permitted).
* hint is one of the hints discussed in this section. The space between the plus sign and the hint is optional. If the comment contains multiple hints, separate the hints by at least one space.
* text is other commenting text that can be interspersed with the hints.
all_rows_hint::=
The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).
and_equal_hint::=
The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes.
append_hint::=
The APPEND hint lets you enable direct-path INSERT if your database is running in serial mode. (Your database is in serial mode if you are not using Enterprise Edition. Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode).
In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. In addition, direct-path INSERT bypasses the buffer cache and ignores integrity constraints. As a result, direct-path INSERT can be considerably faster than conventional INSERT.
cache_hint::=
The CACHE hint specifies that the blocks retrieved for the table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.
choose_hint::=
The CHOOSE hint causes the optimizer to choose between the rule-based and cost-based approaches for a sql statement. The optimizer bases its selection on the presence of statistics for the tables accessed by the statement. If the data dictionary has statistics for at least one of these tables, then the optimizer uses the cost-based approach and optimizes with the goal of best throughput. If the data dictionary does not have statistics for these tables, then it uses the rule-based approach.
cluster_hint::=
The CLUSTER hint explicitly chooses a cluster scan to access the specified table. It applies only to clustered objects.
cursor_sharing_exact_hint::=
Oracle can replace literals in sql statements with bind variables if it is safe to do so. This is controlled with the CURSOR_SHARING startup parameter. The CURSOR_SHARING_EXACT hint causes this behavior to be switched off. In other words, Oracle executes the sql statement without any attempt to replace literals by bind variables.
driving_site_hint::=
The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization.
fact_hint::=
The FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should be considered as a fact table.
first_rows_hint::=
The hints FIRST_ROWS(n) (where n is any positive integer) or FIRST_ROWS instruct Oracle to optimize an individual sql statement for fast response. FIRST_ROWS(n) affords greater precision, because it instructs Oracle to choose the plan that returns the first n rows most efficiently. The FIRST_ROWS hint, which optimizes for the best plan to return the first single row, is retained for backward compatibility and plan stability.
full_hint::=
The FULL hint explicitly chooses a full table scan for the specified table.
hash_hint::=
The HASH hint explicitly chooses a hash scan to access the specified table. It applies only to tables stored in a cluster.
hash_aj_hint::=
For a specific query, place the HASH_SJ, MERGE_SJ, or NL_SJ hint into the EXISTS subquery. HASH_SJ uses a hash semi-join, MERGE_SJ uses a sort merge semi-join, and NL_SJ uses a nested loop semi-join.
hash_sj_hint::=
For a specific query, place the HASH_SJ, MERGE_SJ, or NL_SJ hint into the EXISTS subquery. HASH_SJ uses a hash semi-join, MERGE_SJ uses a sort merge semi-join, and NL_SJ uses a nested loop semi-join.
index_hint::=
The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B-tree, bitmap, and bitmap join indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for bitmap indexes, because it is a more versatile hint.
index_asc_hint::=
The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values.
index_combine_hint::=
The INDEX_COMBINE hint explicitly chooses a bitmap access path for the table. If no indexes are given as arguments for the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate for the table. If certain indexes are given as arguments, then the optimizer tries to use some Boolean combination of those particular bitmap indexes.
index_desc_hint::=
The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition.
index_ffs_hint::=
The INDEX_FFS hint causes a fast full index scan to be performed rather than a full table scan.
leading_hint::=
The LEADING hint causes Oracle to use the specified table as the first table in the join order.
If you specify two or more LEADING hints on different tables, then all of them are ignored. If you specify the ORDERED hint, then it overrides all LEADING hints.
merge_hint::=
The MERGE hint lets you merge a view on a per-query basis.
If a view's query contains a GROUP BY clause or DISTINCT operator in the SELECT list, then the optimizer can merge the view's query into the accessing statement only if complex view merging is enabled. Complex merging can also be used to merge an IN subquery into the accessing statement if the subquery is uncorrelated.
Complex merging is not cost-based--that is, the accessing query block must include the MERGE hint. Without this hint, the optimizer uses another approach.
merge_aj_hint::=
See HASH_AJ hint.
merge_sj_hint::=
See HASH_SJ hint.
nl_aj_hint::=
See HASH_AJ hint.
nl_sj_hint::=
See HASH_SJ hint.
noappend_hint::=
The NOAPPEND hint enables conventional INSERT by disabling parallel mode for the duration of the INSERT statement. (Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode).
nocache_hint::=
The NOCACHE hint specifies that the blocks retrieved for the table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache.
no_expand_hint::=
The NO_EXPAND hint prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it.
no_fact_hint::=
The NO_FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should not be considered as a fact table.
no_index_hint::=
The NO_INDEX hint explicitly disallows a set of indexes for the specified table.
no_merge_hint::=
The NO_MERGE hint causes Oracle not to merge mergeable views.
noparallel_hint::=
The NOPARALLEL hint overrides a PARALLEL specification in the table clause. In general, hints take precedence over table clauses.
Restriction: You cannot parallelize a query involving a nested table.
noparallel_index_hint::=
The NOPARALLEL_INDEX hint overrides a PARALLEL attribute setting on an index to avoid a parallel index scan operation.
no_push_pred_hint::=
The NO_PUSH_PRED hint prevents pushing of a join predicate into the view.
norewrite_hint::=
The NOREWRITE hint disables query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED. Use the NOREWRITE hint on any query block of a request.
no_unnest_hint::=
If you enabled subquery unnesting with the UNNEST_SUBQUERY parameter, then the NO_UNNEST hint turns it off for specific subquery blocks.
ordered_hint::=
The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.
If you omit the ORDERED hint from a sql statement performing a join, then the optimizer chooses the order in which to join the tables. You might want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information lets you choose an inner and outer table better than the optimizer could.
ordered_predicates_hint::=
The ORDERED_PREDICATES hint forces the optimizer to preserve the order of predicate evaluation, except for predicates used as index keys. Use this hint in the WHERE clause of SELECT statements.
If you do not use the ORDERED_PREDICATES hint, then Oracle evaluates all predicates in the order specified by the following rules. Predicates:
* Without user-defined functions, type methods, or subqueries are evaluated first, in the order specified in the WHERE clause.
* With user-defined functions and type methods that have user-computed costs are evaluated next, in increasing order of their cost.
* With user-defined functions and type methods without user-computed costs are evaluated next, in the order specified in the WHERE clause.
* Not specified in the WHERE clause (for example, predicates transitively generated by the optimizer) are evaluated next.
* With subqueries are evaluated last in the order specified in the WHERE clause. parallel_hint::=
The PARALLEL hint lets you specify the desired number of concurrent servers that can be used for a parallel operation. The hint applies to the INSERT, UPDATE, and DELETE portions of a statement as well as to the table scan portion.
parallel_index_hint::=
The PARALLEL_INDEX hint specifies the desired number of concurrent servers that can be used to parallelize index range scans for partitioned indexes.
pq_distribute_hint::=
The PQ_DISTRIBUTE hint improves parallel join operation performance. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers. Using this hint overrides decisions the optimizer would normally make.
Use the EXPLAIN PLAN statement to identify the distribution chosen by the optimizer. The optimizer ignores the distribution hint if both tables are serial.
push_pred_hint::=
The PUSH_PRED hint forces pushing of a join predicate into the view.
push_subq_hint::=
The PUSH_SUBQ hint causes non-merged subqueries to be evaluated at the earliest possible place in the execution plan. Generally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then it improves performance to evaluate the subquery earlier.
This hint has no effect if the subquery is applied to a remote table or one that is joined using a merge join.
rewrite_hint::=
The REWRITE hint forces the cost-based optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. Use the REWRITE hint with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, then Oracle uses that view regardless of its cost.
Oracle does not consider views outside of the list. If you do not specify a view list, then Oracle searches for an eligible materialized view and always uses it regardless of its cost.
rowid_hint::=
The ROWID hint explicitly chooses a table scan by rowid for the specified table.
rule_hint::=
The RULE hint explicitly chooses rule-based optimization for a statement block. It also makes the optimizer ignore other hints specified for the statement block.
star_hint::=
The STAR hint forces a star query plan to be used, if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least three tables, the large table's concatenated index has at least three columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.
star_transformation_hint::=
The STAR_TRANSFORMATION hint makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a cost-based decision to use the best plan generated without the transformation, instead of the best plan for the transformed query.
Even if the hint is given, there is no guarantee that the transformation will take place. The optimizer only generates the subqueries if it seems reasonable to do so. If no subqueries are generated, then there is no transformed query, and the best plan for the untransformed query is used, regardless of the hint.
unnest_hint::=
If the UNNEST_SUBQUERY parameter is set to true, then the UNNEST hint checks the subquery block for validity only. If it is valid, then subquery unnesting is enabled without Oracle checking the heuristics.
use_concat_hint::=
The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Generally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.
The USE_CONCAT hint turns off IN-list processing and OR-expands all disjunctions, including IN-lists.
use_hash_hint::=
The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join.
use_merge_hint::=
The USE_MERGE hint causes Oracle to join each specified table with another row source with a sort-merge join.
use_nl_hint::=
The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table.
SQL - Database Objects
Oracle recognizes objects that are associated with a
particular schema and objects that are not associated with a particular schema,
as described in the sections that follow.
Schema Objects
A schema is a collection of logical structures of data, or
schema objects. A schema is owned by a database user and has the same name as
that user. Each user owns a single schema. Schema objects can be created and
manipulated with SQL and include the following types of objects:
* Clusters
* Constraints
* Database links
* Database triggers
* Dimensions
* External procedure libraries
* Index-organized tables
* Indexes
* Indextypes
* Java classes, Java resources, Java sources
* Materialized views
* Materialized view logs
* Object tables
* Object types
* Object views
* Operators
* Packages
* Sequences
* Stored functions, stored procedures
* Synonyms
* Tables
* Views
* Clusters
* Constraints
* Database links
* Database triggers
* Dimensions
* External procedure libraries
* Index-organized tables
* Indexes
* Indextypes
* Java classes, Java resources, Java sources
* Materialized views
* Materialized view logs
* Object tables
* Object types
* Object views
* Operators
* Packages
* Sequences
* Stored functions, stored procedures
* Synonyms
* Tables
* Views
Nonschema Objects
Other
types of objects are also stored in the database and can be created and
manipulated with SQL but are not contained in a schema:
* Contexts
* Directories
* Parameter files (PFILEs) and server parameter files (SPFILEs)
* Profiles
* Roles
* Rollback segments
* Tablespaces
* Users
* Contexts
* Directories
* Parameter files (PFILEs) and server parameter files (SPFILEs)
* Profiles
* Roles
* Rollback segments
* Tablespaces
* Users
Parts of Schema Objects
Some schema objects are made up of parts that you can or
must name, such as:
* Columns in a table or view
* Index and table partitions and subpartitions
* Integrity constraints on a table
* Packaged procedures, packaged stored functions, and other objects stored within a package
* Columns in a table or view
* Index and table partitions and subpartitions
* Integrity constraints on a table
* Packaged procedures, packaged stored functions, and other objects stored within a package
Partitioned Tables and Indexes
Tables and indexes can be partitioned. When partitioned,
these schema objects consist of a number of parts called partitions, all of
which have the same logical attributes. For example, all partitions in a table
share the same column and constraint definitions, and all partitions in an
index share the same index columns.
When you partition a table or index using the range method, you specify a maximum value for the partitioning key column(s) for each partition. When you partition a table or index using the list method, you specify actual values for the partitioning key column(s) for each partition. When you partition a table or index using the hash method, you instruct Oracle to distribute the rows of the table into partitions based on a system-defined hash function on the partitioning key column(s). When you partition a table or index using the composite-partitioning method, you specify ranges for the partitions, and Oracle distributes the rows in each partition into one or more hash subpartitions based on a hash function. Each subpartition of a table or index partitioned using the composite method has the same logical attributes.
When you partition a table or index using the range method, you specify a maximum value for the partitioning key column(s) for each partition. When you partition a table or index using the list method, you specify actual values for the partitioning key column(s) for each partition. When you partition a table or index using the hash method, you instruct Oracle to distribute the rows of the table into partitions based on a system-defined hash function on the partitioning key column(s). When you partition a table or index using the composite-partitioning method, you specify ranges for the partitions, and Oracle distributes the rows in each partition into one or more hash subpartitions based on a hash function. Each subpartition of a table or index partitioned using the composite method has the same logical attributes.
Partition-Extended and Subpartition-Extended Names
Partition-extended and subpartition-extended names let you
perform some partition-level and subpartition-level operations, such as
deleting all rows from a partition or subpartition, on only one partition or
subpartition. Without extended names, such operations would require that you
specify a predicate (WHERE clause). For range- and list-partitioned tables,
trying to phrase a partition-level operation with a predicate can be
cumbersome, especially when the range partitioning key uses more than one column.
For hash partitions and subpartitions, using a predicate is more difficult
still, because these partitions and subpartitions are based on a system-defined
hash function.
Partition-extended names let you use partitions as if they were tables. An advantage of this method, which is most useful for range-partitioned tables, is that you can build partition-level access control mechanisms by granting (or revoking) privileges on these views to (or from) other users or roles.To use a partition as a table, create a view by selecting data from a single partition, and then use the view as a table.
You can specify partition-extended or subpartition-extended table names for the following DML statements:
* DELETE
* INSERT
* LOCK TABLE
* SELECT
* UPDATE
Syntax
The basic syntax for using partition-extended and subpartition-extended table names is:
partition_extended_name::=
Partition-extended names let you use partitions as if they were tables. An advantage of this method, which is most useful for range-partitioned tables, is that you can build partition-level access control mechanisms by granting (or revoking) privileges on these views to (or from) other users or roles.To use a partition as a table, create a view by selecting data from a single partition, and then use the view as a table.
You can specify partition-extended or subpartition-extended table names for the following DML statements:
* DELETE
* INSERT
* LOCK TABLE
* SELECT
* UPDATE
Syntax
The basic syntax for using partition-extended and subpartition-extended table names is:
partition_extended_name::=
Restrictions
Currently,
the use of partition-extended and subpartition-extended table names has the
following restrictions:
* No remote tables: A partition-extended or subpartition-extended table name cannot contain a database link (dblink) or a synonym that translates to a table with a dblink. To use remote partitions and subpartitions, create a view at the remote site that uses the extended table name syntax and then refer to the remote view.
* No synonyms: A partition or subpartition extension must be specified with a base table. You cannot use synonyms, views, or any other objects.
Example
In the following statement, sales is a partitioned table with partition jan97. You can create a view of the single partition jan97, and then use it as if it were a table. This example deletes rows from the partition.
CREATE VIEW sales_Q1_2000 AS
SELECT * FROM sales PARTITION (Q1_2000);
DELETE FROM sales_Q1_2000 WHERE amount < 0;
* No remote tables: A partition-extended or subpartition-extended table name cannot contain a database link (dblink) or a synonym that translates to a table with a dblink. To use remote partitions and subpartitions, create a view at the remote site that uses the extended table name syntax and then refer to the remote view.
* No synonyms: A partition or subpartition extension must be specified with a base table. You cannot use synonyms, views, or any other objects.
Example
In the following statement, sales is a partitioned table with partition jan97. You can create a view of the single partition jan97, and then use it as if it were a table. This example deletes rows from the partition.
CREATE VIEW sales_Q1_2000 AS
SELECT * FROM sales PARTITION (Q1_2000);
DELETE FROM sales_Q1_2000 WHERE amount < 0;
SQL - Schema Object Names and Qualifiers
This
section provides:
* Rules for naming schema objects and schema object location qualifiers
* Guidelines for naming schema objects and qualifiers
SQL - Schema Object Naming Rules
The following rules apply when naming schema objects:
1. Names must be from 1 to 30 bytes long with these exceptions:
* Names of databases are limited to 8 bytes.
* Names of database links can be as long as 128 bytes.
2. Names cannot contain quotation marks.
3. Names are not case sensitive.
4. A name must begin with an alphabetic character from your database character set unless surrounded by double quotation marks.
5. Names can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Oracle strongly discourages you from using $ and #. Names of database links can also contain periods (.) and "at" signs (@).
If your database character set contains multibyte characters, Oracle recommends that each name for a user or a role contain at least one single-byte character.
6. A name cannot be an Oracle reserved word.
Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words.
7. Do not use the word DUAL as a name for an object or part. DUAL is the name of a dummy table.
8. The Oracle SQL language contains other words that have special meanings. These words include datatypes, function names, and keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE, DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally. Therefore, if you use these words as names for objects and object parts, your SQL statements may be more difficult to read and may lead to unpredictable results.
In particular, do not use words beginning with "SYS_" as schema object names, and do not use the names of SQL built-in functions for the names of schema objects or user-defined functions.
9. Within a namespace, no two objects can have the same name.
The following schema objects share one namespace:
* Tables
* Views
* Sequences
* Private synonyms
* Stand-alone procedures
* Stand-alone stored functions
* Packages
* Materialized views
* User-defined types
Each of the following schema objects has its own namespace:
* Indexes
* Constraints
* Clusters
* Database triggers
* Private database links
* Dimensions
Because tables and views are in the same namespace, a table and a view in the same schema cannot have the same name. However, tables and indexes are in different namespaces. Therefore, a table and an index in the same schema can have the same name.
Each schema in the database has its own namespaces for the objects it contains. This means, for example, that two tables in different schemas are in different namespaces and can have the same name.
Each of the following nonschema objects also has its own namespace:
* User roles
* Public synonyms
* Public database links
* Tablespaces
* Rollback segments
* Profiles
* Parameter files (PFILEs) and server parameter files (SPFILEs)
9. Because the objects in
these namespaces are not contained in schemas, these namespaces span the entire
database.* Rules for naming schema objects and schema object location qualifiers
* Guidelines for naming schema objects and qualifiers
SQL - Schema Object Naming Rules
The following rules apply when naming schema objects:
1. Names must be from 1 to 30 bytes long with these exceptions:
* Names of databases are limited to 8 bytes.
* Names of database links can be as long as 128 bytes.
2. Names cannot contain quotation marks.
3. Names are not case sensitive.
4. A name must begin with an alphabetic character from your database character set unless surrounded by double quotation marks.
5. Names can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Oracle strongly discourages you from using $ and #. Names of database links can also contain periods (.) and "at" signs (@).
If your database character set contains multibyte characters, Oracle recommends that each name for a user or a role contain at least one single-byte character.
6. A name cannot be an Oracle reserved word.
Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words.
7. Do not use the word DUAL as a name for an object or part. DUAL is the name of a dummy table.
8. The Oracle SQL language contains other words that have special meanings. These words include datatypes, function names, and keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE, DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally. Therefore, if you use these words as names for objects and object parts, your SQL statements may be more difficult to read and may lead to unpredictable results.
In particular, do not use words beginning with "SYS_" as schema object names, and do not use the names of SQL built-in functions for the names of schema objects or user-defined functions.
9. Within a namespace, no two objects can have the same name.
The following schema objects share one namespace:
* Tables
* Views
* Sequences
* Private synonyms
* Stand-alone procedures
* Stand-alone stored functions
* Packages
* Materialized views
* User-defined types
Each of the following schema objects has its own namespace:
* Indexes
* Constraints
* Clusters
* Database triggers
* Private database links
* Dimensions
Because tables and views are in the same namespace, a table and a view in the same schema cannot have the same name. However, tables and indexes are in different namespaces. Therefore, a table and an index in the same schema can have the same name.
Each schema in the database has its own namespaces for the objects it contains. This means, for example, that two tables in different schemas are in different namespaces and can have the same name.
Each of the following nonschema objects also has its own namespace:
* User roles
* Public synonyms
* Public database links
* Tablespaces
* Rollback segments
* Profiles
* Parameter files (PFILEs) and server parameter files (SPFILEs)
10. Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name.
11. Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.
12. A name can be enclosed in double quotation marks. Such names can contain any combination of characters, including spaces, ignoring rules 3 through 7 in this list. This exception is allowed for portability, but Oracle recommends that you do not break rules 3 through 7. If you give a schema object a name enclosed in double quotation marks, you must use double quotation marks whenever you refer to the object.
Enclosing a name in double quotes enables it to:
* Contain spaces
* Be case sensitive
* Begin with a character other than an alphabetic character, such as a numeric character
* Contain characters other than alphanumeric characters and _, $, and #
* Be a reserved word
By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace:
emp
"emp"
"Emp"
"EMP "
Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace:
emp
EMP
"EMP"
If you give a user or password a quoted name, the name cannot contain lowercase letters.
Database link names cannot be quoted.
SQL - Schema Object Naming Examples
The following examples are valid schema object names:
ename
horse
scott.hiredate
"EVEN THIS & THAT!"
a_very_long_and_valid_name
Although column aliases, table aliases, usernames, and passwords are not objects or parts of objects, they must also follow these naming rules with these exceptions:
* Column aliases and table aliases exist only for the execution of a single SQL statement and are not stored in the database, so rule 12 does not apply to them.
* Passwords do not have namespaces, so rule 9 does not apply to them.
* Do not use quotation marks to make usernames and passwords case sensitive.
SQL - Schema Object Naming Guidelines
Here are several helpful guidelines for naming objects and their parts:
* Use full, descriptive, pronounceable names (or well-known abbreviations).
* Use consistent naming rules.
* Use the same name to describe the same entity or attribute across tables.
When naming objects, balance the objective of keeping names short and easy to use with the objective of making names as descriptive as possible. When in doubt, choose the more descriptive name, because the objects in the database may be used by many people over a period of time. Your counterpart ten years from now may have difficulty understanding a database with a name like pmdd instead of payment_due_date.
Using consistent naming rules helps users understand the part that each table plays in your application. One such rule might be to begin the names of all tables belonging to the FINANCE application with fin_.
Use the same names to describe the same things across tables. For example, the department number columns of the sample employees and departments tables are both named deptno.
SQL - Queries and Subqueries
A query is an operation that retrieves data from one or
more tables or views. In this reference, a top-level SELECT statement is called
a query, and a query nested within another SQL statement is called a subquery.
This section describes some types of queries and subqueries and how to use them..
This section describes some types of queries and subqueries and how to use them..
Creating Simple Queries
The list of expressions that appears after the SELECT
keyword and before the FROM clause is called the select list. Within the select
list, you specify one or more columns in the set of rows you want Oracle to
return from one or more tables, views, or materialized views. The number of
columns, as well as their datatype and length, are determined by the elements
of the select list..
If two or more tables have some column names in common, you must qualify column names with names of tables. Otherwise, fully qualified column names are optional. However, it is always a good idea to qualify table and column references explicitly. Oracle often does less work with fully qualified table and column names..
You can use a column alias, c_alias, to label the preceding expression in the select list so that the column is displayed with a new heading. The alias effectively renames the select list item for the duration of the query. The alias can be used in the ORDER BY clause, but not other clauses in the query..
You can use comments in a SELECT statement to pass instructions, or hints, to the Oracle optimizer. The optimizer uses hints to choose an execution plan for the statement..
If two or more tables have some column names in common, you must qualify column names with names of tables. Otherwise, fully qualified column names are optional. However, it is always a good idea to qualify table and column references explicitly. Oracle often does less work with fully qualified table and column names..
You can use a column alias, c_alias, to label the preceding expression in the select list so that the column is displayed with a new heading. The alias effectively renames the select list item for the duration of the query. The alias can be used in the ORDER BY clause, but not other clauses in the query..
You can use comments in a SELECT statement to pass instructions, or hints, to the Oracle optimizer. The optimizer uses hints to choose an execution plan for the statement..
Hierarchical Queries
If a table
contains hierarchical data, you can select rows in a hierarchical order using
the hierarchical query clause: .
* START WITH specifies the root row(s) of the hierarchy..
* CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. Some part of the connect_by_condition must use the PRIOR operator to refer to the parent row..
* PRIOR evaluates the connect_by_condition for the parent row of the current row in a hierarchical query. PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators..
The manner in which Oracle processes a WHERE clause (if any) in a hierarchical query depends on whether the WHERE clause contains a join:.
* If the WHERE predicate contains a join, Oracle applies the join predicates before doing the CONNECT BY processing..
* Oracle applies any non-join predicates (that is, all predicates if the WHERE clause does not contain a join) after doing the CONNECT BY processing without affecting the other rows of the hierarchy..
Oracle uses the information from the hierarchical query clause to form the hierarchy using the following steps:.
1. Oracle processes the WHERE clause either before or after the CONNECT BY clause depending on whether the WHERE clause contains any join predicates (as described in the preceding bullet list)..
2. Oracle selects the root row(s) of the hierarchy--those rows that satisfy the START WITH condition..
3. Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows..
4. Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 3, and then the children of those children, and so on. Oracle always selects children by evaluating the CONNECT BY condition with respect to a current parent row..
5. If the query contains a WHERE clause without a join, Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition. .
To find the children of a parent row, Oracle evaluates the PRIOR expression of the CONNECT BY condition for the parent row and the other expression for each row in the table. Rows for which the condition is true are the children of the parent. The CONNECT BY condition can contain other conditions to further filter the rows selected by the query. The CONNECT BY condition cannot contain a subquery..
* START WITH specifies the root row(s) of the hierarchy..
* CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. Some part of the connect_by_condition must use the PRIOR operator to refer to the parent row..
* PRIOR evaluates the connect_by_condition for the parent row of the current row in a hierarchical query. PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators..
The manner in which Oracle processes a WHERE clause (if any) in a hierarchical query depends on whether the WHERE clause contains a join:.
* If the WHERE predicate contains a join, Oracle applies the join predicates before doing the CONNECT BY processing..
* Oracle applies any non-join predicates (that is, all predicates if the WHERE clause does not contain a join) after doing the CONNECT BY processing without affecting the other rows of the hierarchy..
Oracle uses the information from the hierarchical query clause to form the hierarchy using the following steps:.
1. Oracle processes the WHERE clause either before or after the CONNECT BY clause depending on whether the WHERE clause contains any join predicates (as described in the preceding bullet list)..
2. Oracle selects the root row(s) of the hierarchy--those rows that satisfy the START WITH condition..
3. Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows..
4. Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 3, and then the children of those children, and so on. Oracle always selects children by evaluating the CONNECT BY condition with respect to a current parent row..
5. If the query contains a WHERE clause without a join, Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition. .
To find the children of a parent row, Oracle evaluates the PRIOR expression of the CONNECT BY condition for the parent row and the other expression for each row in the table. Rows for which the condition is true are the children of the parent. The CONNECT BY condition can contain other conditions to further filter the rows selected by the query. The CONNECT BY condition cannot contain a subquery..
If
the CONNECT BY condition results in a loop in the hierarchy, Oracle returns an
error. A loop occurs if one row is both the parent (or grandparent or direct
ancestor) and a child (or a grandchild or a direct descendent) of another row..
Examples.
The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers:.
SELECT employee_id, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;.
Examples.
The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers:.
SELECT employee_id, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;.
EMPLOYEE_ID LAST_NAME MANAGER_ID
----------- ------------------------- ----------
101 Kochhar 100
108 Greenberg 101
109 Faviet 108
110 Chen 108
111 Sciarra 108
112 Urman 108
113 Popp 108
200 Whalen 101
.
.
.
The next example is similar to the preceding example, but
uses the LEVEL pseudocolumn to show parent and child rows:.
SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id;.
SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id;.
EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL
----------- ------------------------- ---------- ----------
101 Kochhar 100 1
108 Greenberg 101 2
109 Faviet 108 3
110 Chen 108 3
111 Sciarra 108 3
112 Urman 108 3
113 Popp 108 3
.
.
.
The UNION [ALL], INTERSECT, MINUS Operators
You can combine multiple queries using the set operators
UNION, UNION ALL, INTERSECT, and MINUS. All
set operators have equal precedence. If a SQL statement contains multiple set
operators, Oracle evaluates them from the left to right if no parentheses
explicitly specify another order..
The corresponding expressions in the select lists of the component queries of a compound query must match in number and datatype. If component queries select character data, the datatype of the return values are determined as follows:.
* If both queries select values of datatype CHAR, the returned values have datatype CHAR..
* If either or both of the queries select values of datatype VARCHAR2, the returned values have datatype VARCHAR2..
The corresponding expressions in the select lists of the component queries of a compound query must match in number and datatype. If component queries select character data, the datatype of the return values are determined as follows:.
* If both queries select values of datatype CHAR, the returned values have datatype CHAR..
* If either or both of the queries select values of datatype VARCHAR2, the returned values have datatype VARCHAR2..
Restrictions on set operators:
* The set operators are not valid on columns of type BLOB,
CLOB, BFILE, varray, or nested table..
* The UNION, INTERSECT, and MINUS operators are not valid on LONG columns..
* To reference a column, you must use an alias to name the column..
* You cannot also specify the for_update_clause with these set operators..
* You cannot specify the order_by_clause in the subquery of these operators..
* You cannot use these operators in SELECT statements containing TABLE collection expressions. .
The following examples combine the two query results with each of the set operators..
* The UNION, INTERSECT, and MINUS operators are not valid on LONG columns..
* To reference a column, you must use an alias to name the column..
* You cannot also specify the for_update_clause with these set operators..
* You cannot specify the order_by_clause in the subquery of these operators..
* You cannot use these operators in SELECT statements containing TABLE collection expressions. .
The following examples combine the two query results with each of the set operators..
UNION Example
The following statement combines the results with the UNION
operator, which eliminates duplicate selected rows. This statement shows that
you must match datatype (using the TO_CHAR function) when columns do not exist
in one or the other table:.
SELECT location_id, department_name "Department",
TO_CHAR(NULL) "Warehouse" FROM departments
UNION
SELECT location_id, TO_CHAR(NULL) "Department", warehouse_name
FROM warehouses;.
LOCATION_ID Department Warehouse ----------- --------------------- -------------------------- 1400 IT 1400 Southlake, Texas 1500 Shipping 1500 San Francisco 1600 New Jersey 1700 Accounting 1700 Administration 1700 Benefits 1700 Construction . . ..
SELECT location_id, department_name "Department",
TO_CHAR(NULL) "Warehouse" FROM departments
UNION
SELECT location_id, TO_CHAR(NULL) "Department", warehouse_name
FROM warehouses;.
LOCATION_ID Department Warehouse ----------- --------------------- -------------------------- 1400 IT 1400 Southlake, Texas 1500 Shipping 1500 San Francisco 1600 New Jersey 1700 Accounting 1700 Administration 1700 Benefits 1700 Construction . . ..
UNION ALL Example
The UNION operator returns only distinct rows that appear
in either result, while the UNION ALL operator returns all rows. The UNION ALL
operator does not eliminate duplicate selected rows:.
SELECT product_id FROM order_items
UNION
SELECT product_id FROM inventories;.
SELECT location_id FROM locations
UNION ALL
SELECT location_id FROM departments;.
A location_id value that appears multiple times in either or both queries (such as '1700') is returned only once by the UNION operator, but multiple times by the UNION ALL operator..
SELECT product_id FROM order_items
UNION
SELECT product_id FROM inventories;.
SELECT location_id FROM locations
UNION ALL
SELECT location_id FROM departments;.
A location_id value that appears multiple times in either or both queries (such as '1700') is returned only once by the UNION operator, but multiple times by the UNION ALL operator..
INTERSECT Example
The following statement combines the results with the
INTERSECT operator, which returns only those rows returned by both queries:.
SELECT product_id FROM inventories
INTERSECT
SELECT product_id FROM order_items;.
SELECT product_id FROM inventories
INTERSECT
SELECT product_id FROM order_items;.
MINUS Example
The following statement combines results with the MINUS
operator, which returns only rows returned by the first query but not by the
second:.
SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items;.
SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items;.
Sorting Query Results
Use the ORDER BY clause to order the rows selected by a
query. Sorting by position is useful in the following cases:.
* To order by a lengthy select list expression, you can specify its position, rather than duplicate the entire expression, in the ORDER BY clause..
* For compound queries (containing set operators UNION, INTERSECT, MINUS, or UNION ALL), the ORDER BY clause must use positions, rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query..
The mechanism by which Oracle sorts values for the ORDER BY clause is specified either explicitly by the NLS_SORT initialization parameter or implicitly by the NLS_LANGUAGE initialization parameter. You can change the sort mechanism dynamically from one linguistic sort sequence to another using the ALTER SESSION statement. You can also specify a specific sort sequence for a single query by using the NLSSORT function with the NLS_SORT parameter in the ORDER BY clause. .
* To order by a lengthy select list expression, you can specify its position, rather than duplicate the entire expression, in the ORDER BY clause..
* For compound queries (containing set operators UNION, INTERSECT, MINUS, or UNION ALL), the ORDER BY clause must use positions, rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query..
The mechanism by which Oracle sorts values for the ORDER BY clause is specified either explicitly by the NLS_SORT initialization parameter or implicitly by the NLS_LANGUAGE initialization parameter. You can change the sort mechanism dynamically from one linguistic sort sequence to another using the ALTER SESSION statement. You can also specify a specific sort sequence for a single query by using the NLSSORT function with the NLS_SORT parameter in the ORDER BY clause. .
Joins
A join is a query that combines rows from two or more
tables, views, or materialized views. Oracle performs a join whenever multiple
tables appear in the query's FROM clause. The query's select list can select
any columns from any of these tables. If any two of these tables have a column
name in common, you must qualify all references to these columns throughout the
query with table names to avoid ambiguity. .
Join Conditions
Most join queries contain WHERE clause conditions that
compare two columns, each from a different table. Such a condition is called a
join condition. To execute a join, Oracle combines pairs of rows, each
containing one row from each table, for which the join condition evaluates to
TRUE. The columns in the join conditions need not also appear in the select
list..
To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result. The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, in the case of the cost-based optimization approach, statistics for the tables..
In addition to join conditions, the WHERE clause of a join query can also contain other conditions that refer to columns of only one table. These conditions can further restrict the rows returned by the join query. .
To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result. The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, in the case of the cost-based optimization approach, statistics for the tables..
In addition to join conditions, the WHERE clause of a join query can also contain other conditions that refer to columns of only one table. These conditions can further restrict the rows returned by the join query. .
Equijoins
An equijoin is a join with a join condition containing an
equality operator. An equijoin combines rows that have equivalent values for
the specified columns. Depending on the internal algorithm the optimizer
chooses to execute the join, the total size of the columns in the equijoin
condition in a single table may be limited to the size of a data block minus
some overhead. The size of a data block is specified by the initialization
parameter DB_BLOCK_SIZE. .
Self Joins
A self join is a join of a table to itself. This table
appears twice in the FROM clause and is followed by table aliases that qualify
column names in the join condition. To perform a self join, Oracle combines and
returns rows of the table that satisfy the join condition. .
Cartesian Products
If two tables in a join query have no join condition, Oracle
returns their Cartesian product. Oracle combines each row of one table with
each row of the other. A Cartesian product always generates many rows and is
rarely useful. For example, the Cartesian product of two tables, each with 100
rows, has 10,000 rows. Always include a join condition unless you specifically
need a Cartesian product. If a query joins three or more tables and you do not
specify a join condition for a specific pair, the optimizer may choose a join
order that avoids producing an intermediate Cartesian product..
Inner Joins
An inner join (sometimes called a "simple join")
is a join of two or more tables that returns only those rows that satisfy the
join condition..
Outer Joins
An outer join extends the result of a simple join. An outer
join returns all rows that satisfy the join condition and also returns some or
all of those rows from one table for which no rows from the other satisfy the
join condition..
* To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the ANSI LEFT [OUTER] JOIN syntax, or apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B..
* To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the ANSI RIGHT [OUTER] syntax, or apply the outer join operator (+) to all columns of A in the join condition. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A..
* To write a query that performs an outer join and and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the ANSI FULL [OUTER] JOIN syntax..
Oracle Corporation recommends that you use the ANSI OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the ANSI syntax:.
* You cannot specify the (+) operator in a query block that also contains ANSI JOIN syntax..
* The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (that is, when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view..
* If A and B are joined by multiple join conditions, you must use the (+) operator in all of these conditions. If you do not, Oracle will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join..
* The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain a column marked with the (+) operator..
* A condition containing the (+) operator cannot be combined with another condition using the OR logical operator..
* A condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression..
* A condition cannot compare any column marked with the (+) operator with a subquery..
If the WHERE clause contains a condition that compares a column from table B with a constant, the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated NULLs for this column. Otherwise Oracle will return only the results of a simple join..
In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C. .
* To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the ANSI LEFT [OUTER] JOIN syntax, or apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B..
* To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the ANSI RIGHT [OUTER] syntax, or apply the outer join operator (+) to all columns of A in the join condition. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A..
* To write a query that performs an outer join and and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the ANSI FULL [OUTER] JOIN syntax..
Oracle Corporation recommends that you use the ANSI OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the ANSI syntax:.
* You cannot specify the (+) operator in a query block that also contains ANSI JOIN syntax..
* The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (that is, when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view..
* If A and B are joined by multiple join conditions, you must use the (+) operator in all of these conditions. If you do not, Oracle will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join..
* The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain a column marked with the (+) operator..
* A condition containing the (+) operator cannot be combined with another condition using the OR logical operator..
* A condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression..
* A condition cannot compare any column marked with the (+) operator with a subquery..
If the WHERE clause contains a condition that compares a column from table B with a constant, the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated NULLs for this column. Otherwise Oracle will return only the results of a simple join..
In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C. .
Using Subqueries
A subquery answers multiple-part questions. For example, to
determine who works in Taylor's department, you
can first use a subquery to determine the department in which Taylor works. You can then answer the
original question with the parent SELECT statement. A subquery in the FROM
clause of a SELECT statement is also called an inline view. A subquery in the
WHERE clause of a SELECT statement is also called a nested subquery..
A subquery can contain another subquery. Oracle imposes no limit on the number of subquery levels in the FROM clause of the top-level query. You can nest up to 255 levels of subqueries in the WHERE clause..
If columns in a subquery have the same name as columns in the containing statement, you must prefix any reference to the column of the table from the containing statement with the table name or alias. To make your statements easier for you to read, always qualify the columns in a subquery with the name or alias of the table, view, or materialized view..
Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement..
A correlated subquery answers a multiple-part question whose answer depends on the value in each row processed by the parent statement. For example, you can use a correlated subquery to determine which employees earn more than the average salaries for their departments. In this case, the correlated subquery specifically computes the average salary for each department. .
Use subqueries for the following purposes:.
* To define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement.
* To define the set of rows to be included in a view or materialized view in a CREATE VIEW or CREATE MATERIALIZED VIEW statement.
* To define one or more values to be assigned to existing rows in an UPDATE statement.
* To provide values for conditions in a WHERE clause, HAVING clause, or START WITH clause of SELECT, UPDATE, and DELETE statements.
* To define a table to be operated on by a containing query..
You do this by placing the subquery in the FROM clause of the containing query as you would a table name. You may use subqueries in place of tables in this way as well in INSERT, UPDATE, and DELETE statements..
Subqueries so used can employ correlation variables, but only those defined within the subquery itself, not outer references. Outer references ("left-correlated subqueries") are allowed only in the FROM clause of a SELECT statement. .
Scalar subqueries, which return a single column value from a single row, are a valid form of expression. You can use scalar subquery expressions in most of the places where expr is called for in syntax. .
A subquery can contain another subquery. Oracle imposes no limit on the number of subquery levels in the FROM clause of the top-level query. You can nest up to 255 levels of subqueries in the WHERE clause..
If columns in a subquery have the same name as columns in the containing statement, you must prefix any reference to the column of the table from the containing statement with the table name or alias. To make your statements easier for you to read, always qualify the columns in a subquery with the name or alias of the table, view, or materialized view..
Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement..
A correlated subquery answers a multiple-part question whose answer depends on the value in each row processed by the parent statement. For example, you can use a correlated subquery to determine which employees earn more than the average salaries for their departments. In this case, the correlated subquery specifically computes the average salary for each department. .
Use subqueries for the following purposes:.
* To define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement.
* To define the set of rows to be included in a view or materialized view in a CREATE VIEW or CREATE MATERIALIZED VIEW statement.
* To define one or more values to be assigned to existing rows in an UPDATE statement.
* To provide values for conditions in a WHERE clause, HAVING clause, or START WITH clause of SELECT, UPDATE, and DELETE statements.
* To define a table to be operated on by a containing query..
You do this by placing the subquery in the FROM clause of the containing query as you would a table name. You may use subqueries in place of tables in this way as well in INSERT, UPDATE, and DELETE statements..
Subqueries so used can employ correlation variables, but only those defined within the subquery itself, not outer references. Outer references ("left-correlated subqueries") are allowed only in the FROM clause of a SELECT statement. .
Scalar subqueries, which return a single column value from a single row, are a valid form of expression. You can use scalar subquery expressions in most of the places where expr is called for in syntax. .
Unnesting of Nested Subqueries
Subqueries are "nested" when they appear in the
WHERE clause of the parent statement. When Oracle evaluates a statement with a
nested subquery, it must evaluate the subquery portion multiple times and may
overlook some efficient access paths or joins..
Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins. The optimizer can unnest most subqueries, with some exceptions. Those exceptions include hierarchical subqueries and subqueries that contain a ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or a correlated reference to a query block that is not the subquery's immediate outer query block..
Assuming no restrictions exist, the optimizer automatically unnests some (but not all) of the following nested subqueries:.
* Uncorrelated IN subqueries.
* IN and EXISTS correlated subqueries as long, as they do not contain aggregate functions or a GROUP BY clause.
You can enable extended subquery unnesting by instructing the optimizer to unnest additional types of subqueries:.
* You can unnest an uncorrelated NOT IN subquery by specifying the HASH_AJ or MERGE_AJ hint in the subquery..
* You can unnest other subqueries by specifying the UNNEST hint in the subquery .
Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins. The optimizer can unnest most subqueries, with some exceptions. Those exceptions include hierarchical subqueries and subqueries that contain a ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or a correlated reference to a query block that is not the subquery's immediate outer query block..
Assuming no restrictions exist, the optimizer automatically unnests some (but not all) of the following nested subqueries:.
* Uncorrelated IN subqueries.
* IN and EXISTS correlated subqueries as long, as they do not contain aggregate functions or a GROUP BY clause.
You can enable extended subquery unnesting by instructing the optimizer to unnest additional types of subqueries:.
* You can unnest an uncorrelated NOT IN subquery by specifying the HASH_AJ or MERGE_AJ hint in the subquery..
* You can unnest other subqueries by specifying the UNNEST hint in the subquery .
Selecting from the DUAL Table
DUAL is a table automatically created by Oracle along with
the data dictionary. DUAL is in the schema of the user SYS, but is accessible
by the name DUAL to all users. It has one column, DUMMY, defined to be
VARCHAR2(1), and contains one row with a value 'X'. Selecting from the DUAL
table is useful for computing a constant expression with the SELECT statement.
Because DUAL has only one row, the constant is returned only once.
Alternatively, you can select a constant, pseudocolumn, or expression from any
table, but the value will be returned as many times as there are rows in the table.
.
Distributed Queries
Oracle's distributed database management system
architecture lets you access data in remote databases using Oracle Net and an
Oracle server. You can identify a remote table, view, or materialized view by
appending @dblink to the end of its name. The dblink must be a complete or
partial name for a database link to the database containing the remote table,
view, or materialized view. .
Restrictions on Distributed Queries
Distributed
queries are currently subject to the restriction that all tables locked by a
FOR UPDATE clause and all tables with LONG columns selected by the query must
be located on the same database. For example, the following statement will
raise an error:.
SELECT employees_ny.*
FROM employees_ny@ny, departments
WHERE employees_ny.department_id = departments.department_id
AND departments.department_name = 'ACCOUNTING'
FOR UPDATE OF employees_ny.salary; .
The following statement fails because it selects long_column, a LONG value, from the employees_review table on the ny database and locks the employees table on the local database:.
SELECT employees.employee_id, review.long_column, employees.salary
FROM employees, employees_review@ny review
WHERE employees.employee_id = employees_review.employee_id
FOR UPDATE OF employees.salary;.
In addition, Oracle currently does not support distributed queries that select user-defined types or object REFs on remote tables.
SELECT employees_ny.*
FROM employees_ny@ny, departments
WHERE employees_ny.department_id = departments.department_id
AND departments.department_name = 'ACCOUNTING'
FOR UPDATE OF employees_ny.salary; .
The following statement fails because it selects long_column, a LONG value, from the employees_review table on the ny database and locks the employees table on the local database:.
SELECT employees.employee_id, review.long_column, employees.salary
FROM employees, employees_review@ny review
WHERE employees.employee_id = employees_review.employee_id
FOR UPDATE OF employees.salary;.
In addition, Oracle currently does not support distributed queries that select user-defined types or object REFs on remote tables.
SQL - Types of SQL Statements
The tables in the following sections provide a functional
summary of SQL statements and are divided into these categories:
* Data Definition Language (DDL) Statements
* Data Manipulation Language (DML) Statements
* Transaction Control Statements
* Session Control Statements
* System Control Statements
* Data Definition Language (DDL) Statements
* Data Manipulation Language (DML) Statements
* Transaction Control Statements
* Session Control Statements
* System Control Statements
Data Definition Language (DDL) Statements
Data
definition language (DDL) statements enable you to perform these tasks:
* Create, alter, and drop schema objects
* Grant and revoke privileges and roles
* Analyze information on a table, index, or cluster
* Establish auditing options
* Add comments to the data dictionary
The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table.
The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.
Oracle implicitly commits the current transaction before and after every DDL statement.
Many DDL statements may cause Oracle to recompile or reauthorize schema objects. For information on how Oracle recompiles and reauthorizes schema objects and the circumstances under which a DDL statement would cause this
DDL statements are supported by PL/SQL with the use of the DBMS_SQL package.
* Create, alter, and drop schema objects
* Grant and revoke privileges and roles
* Analyze information on a table, index, or cluster
* Establish auditing options
* Add comments to the data dictionary
The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table.
The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.
Oracle implicitly commits the current transaction before and after every DDL statement.
Many DDL statements may cause Oracle to recompile or reauthorize schema objects. For information on how Oracle recompiles and reauthorizes schema objects and the circumstances under which a DDL statement would cause this
DDL statements are supported by PL/SQL with the use of the DBMS_SQL package.
Data Definition Language Statements
ALTER
CLUSTER
ALTER DATABASE
ALTER DIMENSION
ALTER FUNCTION
ALTER INDEX
ALTER MATERIALIZED VIEW
ALTER MATERIALIZED VIEW
ALTER PACKAGE
ALTER PROCEDURE
ALTER PROFILE
ALTER RESOURCE COST
ALTER ROLE
ALTER ROLLBACK SEGMENT
ALTER SEQUENCE
ALTER TABLE
ALTER TABLESPACE
ALTER TRIGGER
ALTER TYPE
ALTER USER
ALTER VIEW
ANALYZE
ASSOCIATE STATISTICS
AUDIT
COMMENT
CREATE CLUSTER
CREATE CONTEXT
CREATE CONTROLFILE
CREATE DATABASE
CREATE DATABASE LINK
CREATE DIMENSION
CREATE DIRECTORY
CREATE FUNCTION
CREATE INDEX
CREATE INDEXTYPE
CREATE LIBRARY
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW
CREATE OPERATOR
CREATE PACKAGE
CREATE PACKAGE BODY
CREATE PFILE
CREATE PROCEDURE
CREATE PROFILE
CREATE ROLE
CREATE ROLLBACK SEGMENT
CREATE SCHEMA
CREATE SEQUENCE
CREATE SYNONYM
CREATE SPFILE
CREATE TABLE
CREATE TABLESPACE
CREATE TEMPORARY TABLESPACE
CREATE TRIGGER
CREATE TYPE
CREATE USER
CREATE VIEW
DISASSOCIATE STATISTICS
DROP CLUSTER
DROP CONTEXT
DROP DATABASE LINK
DROP DIMENSION
DROP DIRECTORY
DROP FUNCTION
DROP INDEX
DROP INDEXTYPE
DROP LIBRARY
DROP MATERIALIZED VIEW
DROP MATERIALIZED VIEW
DROP OPERATOR
DROP PACKAGE
DROP PROCEDURE
DROP PROFILE
DROP ROLE
DROP ROLLBACK SEGMENT
DROP SEQUENCE
DROP SYNONYM
DROP TABLE
DROP TABLESPACE
DROP TRIGGER
DROP TYPE
DROP USER
DROP VIEW
GRANT
NOAUDIT
RENAME
REVOKE
TRUNCATE
ALTER DATABASE
ALTER DIMENSION
ALTER FUNCTION
ALTER INDEX
ALTER MATERIALIZED VIEW
ALTER MATERIALIZED VIEW
ALTER PACKAGE
ALTER PROCEDURE
ALTER PROFILE
ALTER RESOURCE COST
ALTER ROLE
ALTER ROLLBACK SEGMENT
ALTER SEQUENCE
ALTER TABLE
ALTER TABLESPACE
ALTER TRIGGER
ALTER TYPE
ALTER USER
ALTER VIEW
ANALYZE
ASSOCIATE STATISTICS
AUDIT
COMMENT
CREATE CLUSTER
CREATE CONTEXT
CREATE CONTROLFILE
CREATE DATABASE
CREATE DATABASE LINK
CREATE DIMENSION
CREATE DIRECTORY
CREATE FUNCTION
CREATE INDEX
CREATE INDEXTYPE
CREATE LIBRARY
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW
CREATE OPERATOR
CREATE PACKAGE
CREATE PACKAGE BODY
CREATE PFILE
CREATE PROCEDURE
CREATE PROFILE
CREATE ROLE
CREATE ROLLBACK SEGMENT
CREATE SCHEMA
CREATE SEQUENCE
CREATE SYNONYM
CREATE SPFILE
CREATE TABLE
CREATE TABLESPACE
CREATE TEMPORARY TABLESPACE
CREATE TRIGGER
CREATE TYPE
CREATE USER
CREATE VIEW
DISASSOCIATE STATISTICS
DROP CLUSTER
DROP CONTEXT
DROP DATABASE LINK
DROP DIMENSION
DROP DIRECTORY
DROP FUNCTION
DROP INDEX
DROP INDEXTYPE
DROP LIBRARY
DROP MATERIALIZED VIEW
DROP MATERIALIZED VIEW
DROP OPERATOR
DROP PACKAGE
DROP PROCEDURE
DROP PROFILE
DROP ROLE
DROP ROLLBACK SEGMENT
DROP SEQUENCE
DROP SYNONYM
DROP TABLE
DROP TABLESPACE
DROP TRIGGER
DROP TYPE
DROP USER
DROP VIEW
GRANT
NOAUDIT
RENAME
REVOKE
TRUNCATE
Data Manipulation Language (DML) Statements
Data manipulation language (DML) statements query and
manipulate data in existing schema objects. These statements do not implicitly
commit the current transaction.
CALL
DELETE
EXPLAIN PLAN
INSERT
LOCK TABLE
MERGE
SELECT
UPDATE
The CALL and EXPLAIN PLAN statements are supported in PL/SQL only when executed dynamically. All other DML statements are fully supported in PL/SQL.
Transaction Control Statements
Transaction control statements manage changes made by DML statements.
CALL
DELETE
EXPLAIN PLAN
INSERT
LOCK TABLE
MERGE
SELECT
UPDATE
The CALL and EXPLAIN PLAN statements are supported in PL/SQL only when executed dynamically. All other DML statements are fully supported in PL/SQL.
Transaction Control Statements
Transaction control statements manage changes made by DML statements.
Transaction Control Statements
Statement
COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTION
All transaction control statements except certain forms of the COMMIT and ROLLBACK commands are supported in PL/SQL. For information on the restrictions, see COMMIT and ROLLBACK.
COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTION
All transaction control statements except certain forms of the COMMIT and ROLLBACK commands are supported in PL/SQL. For information on the restrictions, see COMMIT and ROLLBACK.
Session Control Statements
Session control statements dynamically manage the
properties of a user session. These statements do not implicitly commit the
current transaction.
PL/SQL does not support session control statements.
Session Control Statements
Statement
ALTER SESSION
SET ROLE
PL/SQL does not support session control statements.
Session Control Statements
Statement
ALTER SESSION
SET ROLE
System Control Statement
The single system control statement dynamically manages the
properties of an Oracle instance. This statement does not implicitly commit the
current transaction.
ALTER SYSTEM is not supported in PL/SQL.
SQL - SAVEPOINT to UPDATE
SAVEPOINT
Purpose
Use the SAVEPOINT statement to identify a point in a transaction to which you can later roll back.
Keywords and Parameters
savepoint
Specify the name of the savepoint to be created.
Savepoint names must be distinct within a given transaction. If you create a second savepoint with the same identifier as an earlier savepoint, the earlier savepoint is erased. After a savepoint has been created, you can either continue processing, commit your work, roll back the entire transaction, or roll back to the savepoint.
Example
To update Banda's and Greene's salary in the demo table hr.employees, check that the total department salary does not exceed 314,000, then reenter Greene's salary, enter:
UPDATE employees
SET salary = 7000
WHERE last_name = 'Banda';
SAVEPOINT banda_sal;
UPDATE employees
SET salary = 12000
WHERE last_name = 'Greene';
SAVEPOINT greene_sal;
SELECT SUM(salary) FROM employees;
ROLLBACK TO SAVEPOINT banda_sal;
UPDATE employees
SET salary = 11000
WHERE last_name = 'Greene';
COMMIT;
Use the SAVEPOINT statement to identify a point in a transaction to which you can later roll back.
Keywords and Parameters
savepoint
Specify the name of the savepoint to be created.
Savepoint names must be distinct within a given transaction. If you create a second savepoint with the same identifier as an earlier savepoint, the earlier savepoint is erased. After a savepoint has been created, you can either continue processing, commit your work, roll back the entire transaction, or roll back to the savepoint.
Example
To update Banda's and Greene's salary in the demo table hr.employees, check that the total department salary does not exceed 314,000, then reenter Greene's salary, enter:
UPDATE employees
SET salary = 7000
WHERE last_name = 'Banda';
SAVEPOINT banda_sal;
UPDATE employees
SET salary = 12000
WHERE last_name = 'Greene';
SAVEPOINT greene_sal;
SELECT SUM(salary) FROM employees;
ROLLBACK TO SAVEPOINT banda_sal;
UPDATE employees
SET salary = 11000
WHERE last_name = 'Greene';
COMMIT;
SET CONSTRAINT[S]
Purpose
Use the SET CONSTRAINTS statement to specify, for a particular transaction, whether a deferrable constraint is checked following each DML statement or when the transaction is committed.
Prerequisites
To specify when a deferrable constraint is checked, you must have SELECT privilege on the table to which the constraint is applied unless the table is in your schema.
Keywords and Parameters
constraint
Specify the name of one or more integrity constraints.
ALL
Specify ALL to set all deferrable constraints for this transaction.
IMMEDIATE
Specify IMMEDIATE to indicate that the conditions specified by the deferrable constraint are checked immediately after each DML statement.
DEFERRED
Specify DEFERRED to indicate that the conditions specified by the deferrable constraint are checked when the transaction is committed.
Examples
Setting Constraints Examples
The following statement sets all deferrable constraints in this transaction to be checked immediately following each DML statement:
SET CONSTRAINTS ALL IMMEDIATE;
The following statement checks three deferred constraints when the transaction is committed:
SET CONSTRAINTS emp_job_nn, emp_salary_min,
hr.emp_job_fk@houston DEFERRED;
Use the SET CONSTRAINTS statement to specify, for a particular transaction, whether a deferrable constraint is checked following each DML statement or when the transaction is committed.
Prerequisites
To specify when a deferrable constraint is checked, you must have SELECT privilege on the table to which the constraint is applied unless the table is in your schema.
Keywords and Parameters
constraint
Specify the name of one or more integrity constraints.
ALL
Specify ALL to set all deferrable constraints for this transaction.
IMMEDIATE
Specify IMMEDIATE to indicate that the conditions specified by the deferrable constraint are checked immediately after each DML statement.
DEFERRED
Specify DEFERRED to indicate that the conditions specified by the deferrable constraint are checked when the transaction is committed.
Examples
Setting Constraints Examples
The following statement sets all deferrable constraints in this transaction to be checked immediately following each DML statement:
SET CONSTRAINTS ALL IMMEDIATE;
The following statement checks three deferred constraints when the transaction is committed:
SET CONSTRAINTS emp_job_nn, emp_salary_min,
hr.emp_job_fk@houston DEFERRED;
SET ROLE
Purpose
Use the SET ROLE statement to enable and disable roles for your current session.
When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the user's default roles. During the session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. The number of roles that can be concurrently enabled is limited by the initialization parameter MAX_ENABLED_ROLES.
You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view.
Prerequisites
You must already have been granted the roles that you name in the SET ROLE statement.
Keywords and Parameters
role
Specify a role to be enabled for the current session. Any roles not listed and not already enabled are disabled for the current session.
In the IDENTIFIED BY password clause, specify the password for a role. If the role has a password, you must specify the password to enable the role.
Restriction: You cannot specify a role unless it was granted to you either directly or through other roles.
ALL
Specify ALL to enable all roles granted to you for the current session except those optionally listed in the EXCEPT clause.
Roles listed in the EXCEPT clause must be roles granted directly to you. They cannot be roles granted to you through other roles.
If you list a role in the EXCEPT clause that has been granted to you both directly and through another role, the role remains enabled by virtue of the role to which it has been granted.
Restriction: You cannot use this clause to enable roles with passwords that have been granted directly to you.
NONE
Specify NONE to disable all roles for the current session, including the DEFAULT role.
Examples
Setting Roles Examples
To enable the role gardener identified by the password marigolds for your current session, issue the following statement:
SET ROLE gardener IDENTIFIED BY marigolds;
To enable all roles granted to you for the current session, issue the following statement:
SET ROLE ALL;
To enable all roles granted to you except dw_manager, issue the following statement:
SET ROLE ALL EXCEPT dw_manager;
To disable all roles granted to you for the current session, issue the following statement:
SET ROLE NONE;
Use the SET ROLE statement to enable and disable roles for your current session.
When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the user's default roles. During the session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. The number of roles that can be concurrently enabled is limited by the initialization parameter MAX_ENABLED_ROLES.
You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view.
Prerequisites
You must already have been granted the roles that you name in the SET ROLE statement.
Keywords and Parameters
role
Specify a role to be enabled for the current session. Any roles not listed and not already enabled are disabled for the current session.
In the IDENTIFIED BY password clause, specify the password for a role. If the role has a password, you must specify the password to enable the role.
Restriction: You cannot specify a role unless it was granted to you either directly or through other roles.
ALL
Specify ALL to enable all roles granted to you for the current session except those optionally listed in the EXCEPT clause.
Roles listed in the EXCEPT clause must be roles granted directly to you. They cannot be roles granted to you through other roles.
If you list a role in the EXCEPT clause that has been granted to you both directly and through another role, the role remains enabled by virtue of the role to which it has been granted.
Restriction: You cannot use this clause to enable roles with passwords that have been granted directly to you.
NONE
Specify NONE to disable all roles for the current session, including the DEFAULT role.
Examples
Setting Roles Examples
To enable the role gardener identified by the password marigolds for your current session, issue the following statement:
SET ROLE gardener IDENTIFIED BY marigolds;
To enable all roles granted to you for the current session, issue the following statement:
SET ROLE ALL;
To enable all roles granted to you except dw_manager, issue the following statement:
SET ROLE ALL EXCEPT dw_manager;
To disable all roles granted to you for the current session, issue the following statement:
SET ROLE NONE;
SET TRANSACTION
Purpose
Use the SET TRANSACTION statement to establish the current transaction as read only or read write, establish its isolation level, or assign it to a specified rollback segment.
The operations performed by a SET TRANSACTION statement affect only your current transaction, not other users or other transactions. Your transaction ends whenever you issue a COMMIT or ROLLBACK statement. Oracle implicitly commits the current transaction before and after executing a data definition language (DDL) statement.
Prerequisites
If you use a SET TRANSACTION statement, it must be the first statement in your transaction. However, a transaction need not have a SET TRANSACTION statement.
Keywords and Parameters
READ ONLY
The READ ONLY clause establishes the current transaction as a read-only transaction. This clause established transaction-level read consistency.
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
Specify READ WRITE to establish the current transaction as a read/write transaction. This clause establishes 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 Clause
Use the ISOLATION LEVEL clause to specify how transactions containing database modifications are handled.
* The SERIALIAZBLE setting specifies serializable transaction isolation mode as defined in the SQL92 standard. 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.
* The READ COMMITTED setting 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 Clause
Specify USE ROLLBACK SEGMENT to assign 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.
NAME Clause
Use the NAME clause to assign a name to the current transaction. This clause is especially useful in distributed database environments when you must identify and resolve in-doubt transactions. The text string is limited to 255 bytes.
If you specify a name for a distributed transaction, when the transaction commits, the name becomes the commit comment, overriding any comment specified explicitly in the COMMIT statement.
Examples
The following statements could be run at midnight of the last day of every month to count the products and quantities on hand in the Toronto warehouse in the sample Order Entry (oe) schema. This report would not be affected by any other user who might be adding or removing inventory to a different warehouse.
COMMIT;
SET TRANSACTION READ ONLY NAME 'Toronto';
SELECT product_id, quantity_on_hand FROM inventories
WHERE warehouse_id = 5;
COMMIT;
The first COMMIT statement ensures that SET TRANSACTION is the first statement in the transaction. The last COMMIT statement does not actually make permanent any changes to the database. It simply ends the read-only transaction.
The following statement assigns your current transaction to the rollback segment rs_1:
SET TRANSACTION USE ROLLBACK SEGMENT rs_1;
Use the SET TRANSACTION statement to establish the current transaction as read only or read write, establish its isolation level, or assign it to a specified rollback segment.
The operations performed by a SET TRANSACTION statement affect only your current transaction, not other users or other transactions. Your transaction ends whenever you issue a COMMIT or ROLLBACK statement. Oracle implicitly commits the current transaction before and after executing a data definition language (DDL) statement.
Prerequisites
If you use a SET TRANSACTION statement, it must be the first statement in your transaction. However, a transaction need not have a SET TRANSACTION statement.
Keywords and Parameters
READ ONLY
The READ ONLY clause establishes the current transaction as a read-only transaction. This clause established transaction-level read consistency.
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
Specify READ WRITE to establish the current transaction as a read/write transaction. This clause establishes 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 Clause
Use the ISOLATION LEVEL clause to specify how transactions containing database modifications are handled.
* The SERIALIAZBLE setting specifies serializable transaction isolation mode as defined in the SQL92 standard. 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.
* The READ COMMITTED setting 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 Clause
Specify USE ROLLBACK SEGMENT to assign 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.
NAME Clause
Use the NAME clause to assign a name to the current transaction. This clause is especially useful in distributed database environments when you must identify and resolve in-doubt transactions. The text string is limited to 255 bytes.
If you specify a name for a distributed transaction, when the transaction commits, the name becomes the commit comment, overriding any comment specified explicitly in the COMMIT statement.
Examples
The following statements could be run at midnight of the last day of every month to count the products and quantities on hand in the Toronto warehouse in the sample Order Entry (oe) schema. This report would not be affected by any other user who might be adding or removing inventory to a different warehouse.
COMMIT;
SET TRANSACTION READ ONLY NAME 'Toronto';
SELECT product_id, quantity_on_hand FROM inventories
WHERE warehouse_id = 5;
COMMIT;
The first COMMIT statement ensures that SET TRANSACTION is the first statement in the transaction. The last COMMIT statement does not actually make permanent any changes to the database. It simply ends the read-only transaction.
The following statement assigns your current transaction to the rollback segment rs_1:
SET TRANSACTION USE ROLLBACK SEGMENT rs_1;
TRUNCATE
Caution:
You cannot roll back a TRUNCATE statement.
Purpose
Use the TRUNCATE statement to remove all rows from a table or cluster and reset the STORAGE parameters to the values when the table or cluster was created.
Removing rows with the TRUNCATE statement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table invalidates the table's dependent objects, requires you to regrant object privileges on the table, and requires you to re-create the table's indexes, integrity constraints, and triggers and respecify its storage parameters. Truncating has none of these effects.
Prerequisites
To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege.
Keywords and Parameters
TABLE Clause
Specify 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 removed.
* Oracle changes the NEXT storage parameter of table 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.
# If table (whether it is a regular or index-organized table) contains LOB columns, all LOB data and LOB index segments are truncated.
# If table is partitioned, all partitions or subpartitions, as well as the LOB data and LOB index segments for each partition or subpartition, are truncated.
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.)
* If table belongs to a hierarchy, it must be the root of the hierarchy.
* If a domain index is defined on table, neither the index nor any index partitions can be marked IN_PROGRESS.
MATERIALIZED VIEW LOG Clause
The MATERIALIZED VIEW LOG clause lets you specify whether a materialized view log defined on the table is to be preserved or purged when the table is truncated. This clause permits materialized view master tables to be reorganized through export/import without affecting the ability of primary-key materialized views defined on the master to be fast refreshed. To support continued fast refresh of primary-key materialized views, the materialized view log must record primary-key information.
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.)
* If table belongs to a hierarchy, it must be the root of the hierarchy.
* If a domain index is defined on table, neither the index nor any index partitions can be marked IN_PROGRESS.
MATERIALIZED VIEW LOG Clause
The MATERIALIZED VIEW LOG clause lets you specify whether a materialized view log defined on the table is to be preserved or purged when the table is truncated. This clause permits materialized view master tables to be reorganized through export/import without affecting the ability of primary-key materialized views defined on the master to be fast refreshed. To support continued fast refresh of primary-key materialized views, the materialized view log must record primary-key information.
CLUSTER Clause
Specify 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 cluster is in your own schema.
When you truncate a cluster, Oracle also automatically deletes all data in the indexes of the cluster tables.
STORAGE Clauses
The STORAGE clauses let you determine what happens to the space freed by the truncated rows. The DROP STORAGE clause and REUSE STORAGE clause also apply to the space freed by the data deleted from associated indexes.
DROP STORAGE
Specify DROP STORAGE to deallocate all space from the deleted rows from the table or cluster except the space allocated by the MINEXTENTS parameter of the table or cluster. This space can subsequently be used by other objects in the tablespace. This is the default. REUSE STORAGE
Specify REUSE STORAGE to retain 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 insert or update operations.
Examples
Simple TRUNCATE Example
The following statement removes all rows from the employees table and returns the freed space to the tablespace containing employees:
TRUNCATE TABLE employees;
The above statement also removes all data from all indexes on employees and returns the freed space to the tablespaces containing them.
Retaining Free Space After Truncate Example
The following statement removes all rows from all tables in the personnel cluster, but leaves the freed space allocated to the tables:
TRUNCATE CLUSTER personnel REUSE STORAGE
The above statement also removes all data from all indexes on the tables in the personnel cluster.
Preserving Materialized View Logs After Truncate Example
The following statements are examples of truncate statements that preserve materialized view logs:
TRUNCATE TABLE sales PRESERVE MATERIALIZED VIEW LOG;
TRUNCATE TABLE orders;
UPDATE
Purpose
Use the UPDATE statement to change existing values in a table or in a view's base table
Prerequisites
For you to update values in a table, the table must be in your own schema or you must have UPDATE privilege on the table.
For you to update values in the base table of a view:
* You must have UPDATE privilege on the view, and
* Whoever owns the schema containing the view must have UPDATE privilege on the base table.
If the SQL92_SECURITY initialization parameter is set to TRUE, then you must have SELECT privilege on the table whose column values you are referencing (such as the columns in a where_clause) to perform an UPDATE.
The UPDATE ANY TABLE system privilege also allows you to update values in any table or any view's base table.
Examples
Simple Examples
The following statement gives null commissions to all employees with the job sa_clerk:
UPDATE employees
SET commission_pct = NULL
WHERE job = 'SA_CLERK';
The following statement promotes Douglas Grant to manager of Department 20 with a $1,000 raise:
UPDATE employees SET
job_id = 'SA_MAN', salary = salary + 1000, department_id = 120
WHERE first_name||' '||last_name = 'Douglas Grant';
The following statement increases the balance of bank account number 5001 in the accounts table on a remote database accessible through the database link boston:
UPDATE accounts@boston
SET balance = balance + 500
WHERE acc_no = 5001;
PARTITION Example
The following example updates values in a single partition of the sales table:
UPDATE sales PARTITION (sales_q1_1999) s
SET s.promo_id = 494;
Complex Example
The next example shows the following syntactic constructs of the UPDATE statement:
* Both forms of the update_set_clause together in a single statement
* A correlated subquery
* A where_clause to limit the updated rows
UPDATE employees a
SET department_id =
(SELECT department_id
FROM departments
WHERE location_id = '2100'),
(salary, commission_pct) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)
FROM employees b
WHERE a.department_id = b.department_id)
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700);
The above UPDATE statement performs the following operations:
* Updates only those employees who work in Geneva or Munich (locations 2900 and 2700)
* Sets department_id for these employees to the department_id corresponding to Bombay (location_id 2100)
* Sets each employee's salary to 1.1 times the average salary of their department
* Sets each employee's commission to 1.5 times the average commission of their department
SET VALUE Example
The following statement updates a row of object table table1 by selecting a row from another object table table2:
UPDATE table1 p SET VALUE(p) =
(SELECT VALUE(q) FROM table2 q WHERE p.id = q.id)
WHERE p.id = 10;
The subquery uses the value object reference function in its expression.
Correlated Update Example
The following example updates particular rows of the projs nested table corresponding to the department whose department equals 123:
UPDATE TABLE(SELECT projs
FROM dept d WHERE d.dno = 123) p
SET p.budgets = p.budgets + 1
WHERE p.pno IN (123, 456);
RETURNING Clause Example
The following example returns values from the updated row and stores the result in PL/SQL variables bnd1, bnd2, bnd3:
UPDATE employees
SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140
WHERE last_name = 'Jones'
RETURNING salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd3;
Use the UPDATE statement to change existing values in a table or in a view's base table
Prerequisites
For you to update values in a table, the table must be in your own schema or you must have UPDATE privilege on the table.
For you to update values in the base table of a view:
* You must have UPDATE privilege on the view, and
* Whoever owns the schema containing the view must have UPDATE privilege on the base table.
If the SQL92_SECURITY initialization parameter is set to TRUE, then you must have SELECT privilege on the table whose column values you are referencing (such as the columns in a where_clause) to perform an UPDATE.
The UPDATE ANY TABLE system privilege also allows you to update values in any table or any view's base table.
Examples
Simple Examples
The following statement gives null commissions to all employees with the job sa_clerk:
UPDATE employees
SET commission_pct = NULL
WHERE job = 'SA_CLERK';
The following statement promotes Douglas Grant to manager of Department 20 with a $1,000 raise:
UPDATE employees SET
job_id = 'SA_MAN', salary = salary + 1000, department_id = 120
WHERE first_name||' '||last_name = 'Douglas Grant';
The following statement increases the balance of bank account number 5001 in the accounts table on a remote database accessible through the database link boston:
UPDATE accounts@boston
SET balance = balance + 500
WHERE acc_no = 5001;
PARTITION Example
The following example updates values in a single partition of the sales table:
UPDATE sales PARTITION (sales_q1_1999) s
SET s.promo_id = 494;
Complex Example
The next example shows the following syntactic constructs of the UPDATE statement:
* Both forms of the update_set_clause together in a single statement
* A correlated subquery
* A where_clause to limit the updated rows
UPDATE employees a
SET department_id =
(SELECT department_id
FROM departments
WHERE location_id = '2100'),
(salary, commission_pct) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)
FROM employees b
WHERE a.department_id = b.department_id)
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700);
The above UPDATE statement performs the following operations:
* Updates only those employees who work in Geneva or Munich (locations 2900 and 2700)
* Sets department_id for these employees to the department_id corresponding to Bombay (location_id 2100)
* Sets each employee's salary to 1.1 times the average salary of their department
* Sets each employee's commission to 1.5 times the average commission of their department
SET VALUE Example
The following statement updates a row of object table table1 by selecting a row from another object table table2:
UPDATE table1 p SET VALUE(p) =
(SELECT VALUE(q) FROM table2 q WHERE p.id = q.id)
WHERE p.id = 10;
The subquery uses the value object reference function in its expression.
Correlated Update Example
The following example updates particular rows of the projs nested table corresponding to the department whose department equals 123:
UPDATE TABLE(SELECT projs
FROM dept d WHERE d.dno = 123) p
SET p.budgets = p.budgets + 1
WHERE p.pno IN (123, 456);
RETURNING Clause Example
The following example returns values from the updated row and stores the result in PL/SQL variables bnd1, bnd2, bnd3:
UPDATE employees
SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140
WHERE last_name = 'Jones'
RETURNING salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd3;
No comments:
Post a Comment