|Oracle® Database SQL Language Reference
11g Release 1 (11.1)
|PDF · Mobi · ePub|
If part or all of the result of a
SELECT statement is equivalent to an existing materialized view, then Oracle Database may use the materialized view in place of one or more tables specified in the
SELECT statement. This substitution is called query rewrite. It takes place only if cost optimization is enabled and the
QUERY_REWRITE_ENABLED parameter is set to
TRUE. To determine whether query write has occurred, use the
For you to select data from a table or materialized view, the table or materialized view must be in your own schema or you must have the
SELECT privilege on the table or materialized view.
For you to select rows from the base tables of a view:
You must have the
SELECT privilege on the view, and
Whoever owns the schema containing the view must have the
SELECT privilege on the base tables.
TABLE system privilege also allows you to select data from any table or any materialized view or the base table of any view.
To issue an Oracle Flashback Query using the
flashback_query_clause, you must have the
SELECT privilege on the objects in the select list. In addition, either you must have
FLASHBACK object privilege on the objects in the select list, or you must have
TABLE system privilege.
query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle Database optimizes the query by treating the query name as either an inline view or as a temporary table.
You can specify this clause in any top-level
SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries except the subquery that defines the query name itself.
You can specify only one
subquery_factoring_clause in a single SQL statement. You cannot specify a
query_name in its own subquery. However, any
query_name defined in the
subquery_factoring_clause can be used in any subsequent named query block in the
In a compound query with set operators, you cannot use the
query_name for any of the component queries, but you can use the
query_name in the
FROM clause of any of the component queries.
Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.
See Also:"Using Hints" for the syntax and description of hints
UNIQUE if you want the database to return only one copy of each set of duplicate rows selected. These two keywords are synonymous. Duplicate rows are those with matching values for each expression in the select list.
When you specify
UNIQUE, the total number of bytes in all select list expressions is limited to the size of a data block minus some overhead. This size is specified by the initialization parameter
You cannot specify
DISTINCT if the
select_list contains LOB columns.
ALL if you want the database to return all rows selected, including all copies of duplicates. The default is
Specify the all-column wildcard (asterisk) to select all columns, excluding pseudocolumns, from all tables, views, or materialized views listed in the
FROM clause. The columns are returned in the order indicated by the
COLUMN_ID column of the
*_TAB_COLUMNS data dictionary view for the table, view, or materialized view.
If you are selecting from a table rather than from a view or a materialized view, then columns that have been marked as
UNUSED by the
UNUSED statement are not selected.
select_list lets you specify the columns you want to retrieve from the database.
query_name, specify a name already specified in the
subquery_factoring_clause. You must have specified the
subquery_factoring_clause in order to specify
query_name in the
select_list. If you specify
query_name in the
select_list, then you also must specify
query_name in the
Specify the object name followed by a period and the asterisk to select all columns from the specified table, view, or materialized view. Oracle Database returns a set of columns in the order in which the columns were specified when the object was created. A query that selects rows from two or more tables, views, or materialized views is a join.
You can use the schema qualifier to select from a table, view, or materialized view in a schema other than your own. If you omit
schema, then the database assumes the table, view, or materialized view is in your own schema.
Specify an expression representing the information you want to select. A column name in this list can be qualified with
schema only if the table, view, or materialized view containing the column is qualified with
schema in the
FROM clause. If you specify a member method of an object type, then you must follow the method name with parentheses even if the method takes no arguments.
See Also:"Selecting Sequence Values: Examples"
c_alias Specify an alias for the column expression. Oracle Database will use this alias in the column heading of the result set. The
AS keyword is optional. 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.
If you also specify a group_by_clause in this statement, then this select list can contain only the following types of expressions:
Aggregate functions and the functions
Expressions identical to those in the
group_by_clause. If the
group_by_clause is in a subquery, then the
BY columns of the subquery must match the select list of the outer query. Any columns in the select list of the subquery that are not needed by the
BY operation are ignored without error.
Expressions involving the preceding expressions that evaluate to the same value for all rows in a group
You can select a rowid from a join view only if the join has one and only one key-preserved table. The rowid of that table becomes the rowid of the view.
See Also:Oracle Database Administrator's Guide for information on key-preserved tables
If two or more tables have some column names in common, and if you are specifying a join in the
FROM clause, then you must qualify column names with names of tables or table aliases.
FROM clause lets you specify the objects from which data is selected.
query_table_expression clause to identify a table, view, materialized view, partition, or subpartition, or to specify a subquery that identifies the objects.
See Also:"Using Subqueries: Examples"
flashback_query_clause to retrieve past data from a table, view, or materialized view.
This clause implements SQL-driven Flashback, which lets you specify a different system change number or timestamp for each object in the select list. You can also implement session-level Flashback using the
A Flashback Query lets you retrieve a history of changes made to a row. You can retrieve the corresponding identifier of the transaction that made the change using the
VERSIONS_XID pseudocolumn. You can also retrieve information about the transaction that resulted in a particular row version by issuing an Oracle Flashback Transaction Query. You do this by querying the
FLASHBACK_TRANSACTION_QUERY data dictionary view for a particular transaction ID.
AS OF Specify
OF to retrieve the single version of the rows returned by the query at a particular change number (SCN) or timestamp. If you specify
expr must evaluate to a number. If you specify
expr must evaluate to a timestamp value. Oracle Database returns rows as they existed at the specified system change number or time.
VERSIONS to retrieve multiple versions of the rows returned by the query. Oracle Database returns all committed versions of the rows that existed between two SCNs or between two timestamp values. The rows returned include deleted and subsequently reinserted versions of the rows.
SCN ... to retrieve the versions of the row that existed between two SCNs. Both expressions must evaluate to a number.
MAXVALUE resolve to the SCN of the oldest and most recent data available, respectively.
TIMESTAMP ... to retrieve the versions of the row that existed between two timestamps. Both expressions must evaluate to a timestamp value.
MAXVALUE resolve to the timestamp of the oldest and most recent data available, respectively.
Oracle Database provides a group of version query pseudocolumns that let you retrieve additional information about the various row versions. Refer to "Version Query Pseudocolumns" for more information.
When both clauses are used together, the
OF clause determines the SCN or moment in time from which the database issues the query. The
VERSIONS clause determines the versions of the rows as seen from the
OF point. The database returns null for a row version if the transaction started before the first
BETWEEN value or ended after the
You cannot specify a subquery in the expression of the
You cannot use the
VERSIONS clause in flashback queries to temporary or external tables, or tables that are part of a cluster.
You cannot use the
VERSIONS clause in flashback queries to views. However, you can use the
VERSIONS syntax in the defining query of a view.
You cannot specify this clause if you have specified
query_name in the
Oracle Database Advanced Application Developer's Guide for more information on Oracle Flashback Query
Oracle Database Advanced Application Developer's Guide and Oracle Database PL/SQL Packages and Types Reference for information about session-level Flashback using the
For range- and list-partitioned data, as an alternative to this clause, you can specify a condition in the
WHERE clause that restricts the retrieval to one or more partitions of
table. Oracle Database will interpret the condition and fetch data from only those partitions. It is not possible to formulate such a
WHERE condition for hash-partitioned data.
If you omit
dblink, then the database assumes that the table, view, or materialized view is on the local database.
You cannot query a user-defined type or an object
REF on a remote table.
You cannot query columns of type
ANYDATASET from remote tables.
sample_clause lets you instruct the database to select from a random sample of data from the table, rather than from the entire table.
See Also:"Selecting a Sample: Examples"
Block sampling is possible only during full table scans or index fast full scans. If a more efficient execution path exists, then Oracle Database does not perform block sampling. If you want to guarantee block sampling for a particular table or index, then use the
sample_percent, specify the percentage of the total row or block count to be included in the sample. The value must be in the range .000001 to, but not including, 100. This percentage indicates the probability of each row, or each cluster of rows in the case of block sampling, being selected as part of the sample. It does not mean that the database will retrieve exactly
sample_percent of the rows of
The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results.
SEED seed_value Specify this clause to instruct the database to attempt to return the same sample from one execution to the next. The
seed_value must be an integer between 0 and 4294967295. If you omit this clause, then the resulting sample will change from one execution to the next.
Restriction on Sampling During Queries When sampling from a view, you must ensure that the view is key preserved. One way to do this is to use a
CREATE TABLE ...
subquery statement to materialize the result of an arbitrary query and then perform sampling on the resulting query.
WITH CHECK OPTION Specify
WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the
FROM clause but not in subquery in the
CONSTRAINT constraint Specify the name of the
CHECK OPTION constraint. If you omit this identifier, then Oracle automatically assigns the constraint a name of the form
n, where n is an integer that makes the constraint name unique within the database.
table_collection_expression lets you inform Oracle that the value of
collection_expression should be treated as a table for purposes of query and DML operations. The
collection_expression can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value—that is, a value whose type is nested table or varray. This process of extracting the elements of a collection is called collection unnesting.
The optional plus (+) is relevant if you are joining the
TABLE expression with the parent table. The + creates an outer join of the two, so that the query returns rows from the outer table even if the collection expression is null.
Note:In earlier releases of Oracle, when
collection_expressionwas a subquery,
table_collection_expressionwas expressed as
subquery. That usage is now deprecated.
collection_expression can reference columns of tables defined to its left in the
FROM clause. This is called left correlation. Left correlation can occur only in
table_collection_expression. Other subqueries cannot contains references to columns defined outside the subquery.
(+) lets you specify that
table_collection_expression should return a row with all fields set to null if the collection is null or empty. The
(+) is valid only if
collection_expression uses left correlation. The result is similar to that of an outer join.
When you use the
(+) syntax in the
WHERE clause of a subquery in an
DELETE operation, you must specify two tables in the
FROM clause of the subquery. Oracle Database ignores the outer join syntax unless there is a join in the subquery itself.
Specify a correlation name, which is alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods. Correlation names are most often used in a correlated query. Other references to the table, view, or materialized view throughout the query must refer to this alias.
See Also:"Using Correlated Subqueries: Examples"
pivot_clause lets you write cross-tabulation queries that rotate rows into columns, aggregating data in the process of the rotation. The output of a pivot operation typically includes more columns and fewer rows than the starting data set. The
pivot_clause performs the following steps:
pivot_clause computes the aggregation functions specified at the beginning of the clause. Aggregation functions must specify a
BY clause to return multiple values, yet the
pivot_clause does not contain an explicit
BY clause. Instead, the
pivot_clause performs an implicit
BY. The implicit grouping is based on all the columns not referred to in the
pivot_clause, along with the set of values specified in the
The grouping columns and aggregated values calculated in Step 1 are configured to produce the following cross-tabular output:
All the implicit grouping columns not referred to in the
pivot_clause, followed by
New columns corresponding to values in the
pivot_in_clause Each aggregated value is transposed to the appropriate new column in the cross-tabulation. If you specify the
XML keyword, then the result is a single new column that expresses the data as an XML string.
The subclauses of the
pivot_clause have the following semantics:
XML The optional
XML keyword generates XML output for the query. The
XML keyword permits the
pivot_in_clause to contain either a subquery or the wildcard keyword
ANY. Subqueries and
ANY wildcards are useful when the
pivot_in_clause values are not known in advance. With XML output, the values of the pivot column are evaluated at execution time. You cannot specify
XML when you specify explicit pivot values using expressions in the
When XML output is generated, the aggregate function is applied to each distinct pivot value, and the database returns a column of
XMLType containing an XML string for all value and measure pairs.
expr, specify an expression that evaluates to a constant value of a pivot column. You can optionally provide an alias for each pivot column value. If there is no alias, the column heading becomes a quoted identifier.
subquery A subquery is used only in conjunction with the
XML keyword. When you specify a subquery, all values found by the subquery are used for pivoting. The output is not the same cross-tabular format returned by non-XML pivot queries. Instead of multiple columns specified in the
pivot_in_clause, the subquery produces a single XML string column. The XML string for each row holds aggregated data corresponding to the implicit
BY value of that row. The XML string for each output row includes all pivot values found by the subquery, even if there are no corresponding rows in the input data.
The subquery must return a list of unique values at the execution time of the pivot query. If the subquery does not return a unique value, then Oracle Database raises a run-time error. Use the
DISTINCT keyword in the subquery if you are not sure the query will return unique values.
ANY keyword is used only in conjunction with the
XML keyword. The
ANY keyword acts as a wildcard and is similar in effect to
subquery. The output is not the same cross-tabular format returned by non-XML pivot queries. Instead of multiple columns specified in the
ANY keyword produces a single XML string column. The XML string for each row holds aggregated data corresponding to the implicit
BY value of that row. However, in contrast to the behavior when you specify
ANY wildcard produces an XML string for each output row that includes only the pivot values found in the input data corresponding to that row.
See Also:Oracle Database Data Warehousing Guide for more information about
UNPIVOTand "Using PIVOT and UNPIVOT: Examples"
unpivot_clause rotates columns into rows.
NULLS clause gives you the option of including or excluding null-valued rows.
NULLS causes the unpivot operation to include null-valued rows;
NULLS eliminates null-values rows from the return set. If you omit this clause, then the unpivot operation excludes nulls.
column, specify a name for each output column that will hold measure values, such as
pivot_for_clause, specify a name for each output column that will hold descriptor values, such as quarter or product.
unpivot_in_clause, specify the input data columns whose names will become values in the output columns of the
pivot_for_clause. These input data columns have names specifying a category value, such as Q1, Q2, Q3, Q4. The optional
alias lets you map the column name to any desired value.
The unpivot operation turns a set of value columns into one column. Therefore, the datatypes of all the value columns must be in the same datatype group, such as numeric or character.
If all the value columns are
CHAR, then the unpivoted column is
CHAR. If any value column is
VARCHAR2, then the unpivoted column is
If all the value columns are
NUMBER, then the unpivoted column is
NUMBER. If any value column is
BINARY_DOUBLE, then the unpivoted column is
BINARY_DOUBLE. If no value column is
BINARY_DOUBLE but any value column is
BINARY_FLOAT, then the unpivoted column is
Use the appropriate
join_clause syntax to identify tables that are part of a join from which to select data. The
inner_cross_join_clause lets you specify an inner or cross join. The
outer_join_clause lets you specify an outer join.
When you join more than two row sources, you can use parentheses to override default precedence. For example, the following syntax:
SELECT ... FROM a JOIN (b JOIN c) ...
results in a join of
c, and then a join of that result set with
See Also:"Joins" for more information on joins, "Using Join Queries: Examples", "Using Self Joins: Example", and "Using Outer Joins: Examples"
Inner joins return only those rows that satisfy the join condition.
JOIN keyword explicitly states that a join is being performed. You can use this syntax to replace the comma-delimited table expressions used in
WHERE clause joins with
FROM clause join syntax.
USING (column) When you are specifying an equijoin of columns that have the same name in both tables, the
column clause indicates the columns to be used. You can use this clause only if the join columns in both tables have the same name. Within this clause, do not qualify the column name with a table name or table alias.
CROSS keyword indicates that a cross join is being performed. A cross join produces the cross-product of two relations and is essentially the same as the comma-delimited Oracle Database notation.
Outer joins return 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. You can specify two types of outer joins: a conventional outer join using the
table_reference syntax on both sides of the join, or a partitioned outer join using the
query_partition_clause on one side or the other. A partitioned outer join is similar to a conventional outer join except that the join takes place between the outer table and each partition of the inner table. This type of join lets you selectively make sparse data more dense along the dimensions of interest. This process is called data densification.
RIGHT to indicate a right outer join.
LEFT to indicate a left outer join.
FULL to indicate a full or two-sided outer join. In addition to the inner join, rows from both tables that have not been returned in the result of the inner join will be preserved and extended with nulls.
You can specify the optional
OUTER keyword following
FULL to explicitly clarify that an outer join is being performed.
query_partition_clause lets you define a partitioned outer join. Such a join extends the conventional outer join syntax by applying the outer join to partitions returned by the query. Oracle Database creates a partition of rows for each expression you specify in the
BY clause. The rows in each query partition have same value for the
query_partition_clause can be on either side of the outer join. The result of a partitioned outer join is a
UNION of the outer joins of each of the partitions in the partitioned result set and the table on the other side of the join. This type of result is useful for filling gaps in sparse data, which simplifies analytic calculations.
If you omit this clause, then the database treats the entire table expression—everything specified in
table_reference—as a single partition, resulting in a conventional outer join.
To use the
query_partition_clause in an analytic function, use the upper branch of the syntax (without parentheses). To use this clause in a model query (in the
model_column_clauses) or a partitioned outer join (in the
outer_join_clause), use the lower branch of the syntax (with parentheses).
You can specify the
query_partition_clause on either the right or left side of the join, but not both.
You cannot specify a
FULL partitioned outer join.
If you specify the
query_partition_clause in an outer join with an
ON clause, then you cannot specify a subquery in the
COALESCE (a, b) = a if a NOT NULL, else b.
A left outer join returns all the common column values from the left table in the
A right outer join returns all the common column values from the right table in the
A full outer join returns all the common column values from both joined tables.
Within this clause, do not qualify the column name with a table name or table alias.
You cannot specify a LOB column or a collection column in the
You cannot specify this clause with a
NATURAL outer join.
See Also:"Using Outer Joins: Examples"
NATURAL JOIN The
NATURAL keyword indicates that a natural join is being performed. A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias.
On occasion, the table pairings in natural or cross joins may be ambiguous. For example, consider the following join syntax:
a NATURAL LEFT JOIN b LEFT JOIN c ON b.c1 = c.c1
This example can be interpreted in either of the following ways:
a NATURAL LEFT JOIN (b LEFT JOIN c ON b.c1 = c.c1) (a NATURAL LEFT JOIN b) LEFT JOIN c ON b.c1 = c.c1
To avoid this ambiguity, you can use parentheses to specify the pairings of joined tables. In the absence of such parentheses, the database uses left associativity, pairing the tables from left to right.
WHERE condition lets you restrict the rows selected to those that satisfy one or more conditions. For
condition, specify any valid SQL condition.
If you omit this clause, then the database returns all rows from the tables, views, or materialized views in the
Note:If this clause refers to a
DATEcolumn of a partitioned table or index, then the database performs partition pruning only if:
You created the table or index partitions by fully specifying the year using the
TO_DATE function with a 4-digit format mask, and
You specify the date in the
where_clause of the query using the
TO_DATE function and either a 2- or 4-digit format mask.
hierarchical_query_clause lets you select rows in a hierarchical order.
SELECT statements that contain hierarchical queries can contain the
LEVEL pseudocolumn in the select list.
LEVEL returns the value 1 for a root node, 2 for a child node of a root node, 3 for a grandchild, and so on. The number of levels returned by a hierarchical query may be limited by available user memory.
Oracle processes hierarchical queries as follows:
A join, if present, is evaluated first, whether the join is specified in the
FROM clause or with
WHERE clause predicates.
BY condition is evaluated.
WHERE clause predicates are evaluated.
If you specify this clause, then do not specify either
BY, because they will destroy the hierarchical order of the
BY results. If you want to order rows of siblings of the same parent, then use the
See Also:"Hierarchical Queries" for a discussion of hierarchical queries and "Using the LEVEL Pseudocolumn: Examples"
Specify a condition that identifies the row(s) to be used as the root(s) of a hierarchical query. Oracle Database uses as root(s) all rows that satisfy this condition. If you omit this clause, then the database uses all rows in the table as root rows.
Specify a condition that identifies the relationship between parent rows and child rows of the hierarchy. The
connect_by_condition can be any condition as described in Chapter 7, "Conditions". However, it must use the
PRIOR operator to refer to the parent row.
BY clause if you want the database to group the selected rows based on the value of
expr(s) for each row and return a single row of summary information for each group. If this clause contains
ROLLUP extensions, then the database produces superaggregate groupings in addition to the regular groupings.
Expressions in the
BY clause can contain any columns of the tables, views, or materialized views in the
FROM clause, regardless of whether the columns appear in the select list.
BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the
ROLLUP operation in the
simple_grouping_clause groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions in the
BY specification, and returns a single row of summary for each group. You can use the
ROLLUP operation to produce subtotal values by using it with the
SUM function. When used with
ROLLUP generates subtotals from the most detailed level to the grand total. Aggregate functions such as
COUNT can be used to produce other kinds of superaggregates.
For example, given three expressions (n=3) in the
ROLLUP clause of the
simple_grouping_clause, the operation results in n+1 = 3+1 = 4 groupings.
Rows grouped on the values of the first
n expressions are called regular rows, and the others are called superaggregate rows.
See Also:Oracle Database Data Warehousing Guide for information on using
ROLLUPwith materialized views
CUBE operation in the
simple_grouping_clause groups the selected rows based on the values of all possible combinations of expressions in the specification. It returns a single row of summary information for each group. You can use the
CUBE operation to produce cross-tabulation values.
For example, given three expressions (n=3) in the
CUBE clause of the
simple_grouping_clause, the operation results in 2n = 23 = 8 groupings. Rows grouped on the values of
n expressions are called regular rows, and the rest are called superaggregate rows.
SETS are a further extension of the
BY clause that let you specify multiple groupings of data. Doing so facilitates efficient aggregation by pruning the aggregates you do not need. You specify just the desired groups, and the database does not need to perform the full set of aggregations generated by
ROLLUP. Oracle Database computes all groupings specified in the
SETS clause and combines the results of individual groupings with a
ALL operation. The
ALL means that the result set can include duplicate rows.
To specify composite columns, group columns within parentheses so that the database treats them as a unit while computing
To specify concatenated grouping sets, separate multiple grouping sets,
CUBE operations with commas so that the database combines them into a single
BY clause. The result is a cross-product of groupings from each grouping set.
HAVING clause to restrict the groups of returned rows to those groups for which the specified
TRUE. If you omit this clause, then the database returns summary rows for all groups.
HAVING after the
hierarchical_query_clause. If you specify both
HAVING, then they can appear in either order.
See Also:"Using the HAVING Condition: Example"
You cannot specify LOB columns, nested tables, or varrays as part of
The expressions can be of any form except scalar subquery expressions.
group_by_clause references any object type columns, then the query will not be parallelized.
model_clause lets you view selected rows as a multidimensional array and randomly access cells within that array. Using the
model_clause, you can specify a series of cell assignments, referred to as rules, that invoke calculations on individual cells and ranges of cells. These rules operate on the results of a query and do not update any database tables.
When using the
model_clause in a query, the
BY clauses must refer only to those columns defined in the
main_model clause defines how the selected rows will be viewed in a multidimensional array and what rules will operate on which cells in that array.
model_column_clauses define and classify the columns of a query into three groups: partition columns, dimension columns, and measure columns. If the
expr in these three subclauses is a model column, then the column alias (
c_alias) is optional. If the
expr is not a model column, then the column alias is required.
DIMENSION BY The
BY clause specifies the columns that will identify a row within a partition. The values of the dimension columns, along with those of the partition columns, serve as array indexes to the measure columns within a row.
MEASURES clause identifies the columns on which the calculations can be performed. Measure columns in individual rows are treated like cells that you can reference, by specifying the values for the partition and dimension columns, and update.
model_column identifies a column to be used in defining the model. A column alias is required if
expr is not a column name. Refer to "Model Expressions" for information on model expressions.
cell_reference_options clause to specify how null and absent values are treated in rules and how column uniqueness is constrained.
Zero for numeric datatypes
01-JAN-2000 for datetime datatypes
An empty string for character datatypes
Null for all other datatypes
model_rules_clause to specify the cells to be updated, the rules for updating those cells, and optionally, how the rules are to be applied and processed.
Each rule represents an assignment and consists of a left-hand side and right-hand side. The left-hand side of the rule identifies the cells to be updated by the right-hand side of the rule. The right-hand side of the rule evaluates to the values to be assigned to the cells specified on the left-hand side of the rule.
UPSERT behavior for a rule with both positional and symbolic references on the left-hand side of the rule. When evaluating an
ALL rule, Oracle performs the following steps to create a list of cell references to be upserted:
Find the existing cells that satisfy all the symbolic predicates of the cell reference.
Using just the dimensions that have symbolic references, find the distinct dimension value combinations of these cells.
Perform a cross product of these value combinations with the dimension values specified by way of positional references.
Please refer to Oracle Database Data Warehousing Guide for more information on the semantics of
UPSERT When you specify
UPSERT, the database applies the rules to those cells referenced on the left-hand side of the rule that exist in the multidimensional array, and inserts new rows for those that do not exist.
UPSERT behavior applies only when positional referencing is used on the left-hand side and a single cell is referenced.
UPSERT is the default. Refer to cell_assignment for more information on positional referencing and single-cell references.
UPSERT can be specified for individual rules as well. When either
UPSERT is specified for a specific rule, it takes precedence over the option specified in the
Notes on UPSERT [ALL] and UPDATE:If an
UPDATErule does not contain the appropriate predicates, then the database may implicitly convert it to a different type of rule:
UPSERT rule contains an existential predicate, then the rule is treated as an
ALL rule must have at least one existential predicate and one qualified predicate on its left side.
If it has no existential predicate, then it is treated as an
If it has no qualified predicate, then it is treated as an
UPDATE When you specify
UPDATE, the database applies the rules to those cells referenced on the left-hand side of the rule that exist in the multidimensional array. If the cells do not exist, then the assignment is ignored.
SEQUENTIAL ORDER When you specify
ORDER, the database evaluates the rules in the order they appear. In this case, a cell can be assigned a value more than once.
ORDER is the default.
ITERATE ... [
UNTIL] to specify the number of times to cycle through the rules and, optionally, an early termination condition. The parentheses around the
UNTIL condition are optional.
When you specify
ITERATE ... [
UNTIL], rules are evaluated in the order in which they appear. Oracle Database returns an error if both
[UNTIL] are specified in the
cell_assignment clause, which is the left-hand side of the rule, specifies one or more cells to be updated. When a
cell_assignment references a single cell, it is called a single-cell reference. When more than one cell is referenced, it is called a multiple-cell reference.
All dimension columns defined in the
model_clause must be qualified in the
cell_assignment clause. A dimension can be qualified using either symbolic or positional referencing.
A symbolic reference qualifies a single dimension column using a Boolean condition like
constant. A positional reference is one where the dimension column is implied by its position in the
BY clause. The only difference between symbolic references and positional references is in the treatment of nulls.
Using a single-cell symbolic reference such as
a[x=null,y=2000], no cells qualify because
x=null evaluates to
FALSE. However, using a single-cell positional reference such as
a[null,2000], a cell where
x is null and
y is 2000 qualifies because null = null evaluates to
TRUE. With single-cell positional referencing, you can reference, update, and insert cells where dimension columns are null.
You can specify a condition or an expression representing a dimension column value using either symbolic or positional referencing.
condition cannot contain aggregate functions or the
CV function, and
condition must reference a single dimension column.
expr cannot contain a subquery. Refer to "Model Expressions" for information on model expressions.
single_column_for_loop clause lets you specify a range of cells to be updated within a single dimension column.
IN clause lets you specify the values of the dimension column as either a list of values or as a subquery. When using
subquery, it cannot:
Be a correlated query
Return more than 10,000 rows
Be a query defined in the
FROM clause lets you specify a range of values for a dimension column with discrete increments within the range. The
FROM clause can only be used for those columns with a datatype for which addition and subtraction is supported. The
DECREMENT values must be positive.
Optionally, you can specify the
LIKE clause within the
FROM clause. In the
pattern is a character string containing a single pattern-matching character
%. This character is replaced during execution with the current incremented or decremented value in the
If all dimensions other than those used by a
FOR loop involve a single-cell reference, then the expressions can insert new rows. The number of dimension value combinations generated by
FOR loops is counted as part of the 10,000 row limit of the
multi_column_for_loop clause lets you specify a range of cells to be updated across multiple dimension columns. The
IN clause lets you specify the values of the dimension columns as either multiple lists of values or as a subquery. When using
subquery, it cannot:
Be a correlated query
Return more than 10,000 rows
Be a query defined in the
If all dimensions other than those used by a
FOR loop involve a single-cell reference, then the expressions can insert new rows. The number of dimension value combinations generated by
FOR loops is counted as part of the 10,000 row limit of the
See Also:Oracle Database Data Warehousing Guide for more information about using
FORloops in the
BY clause to specify the order in which cells on the left-hand side of the rule are to be evaluated. The
expr must resolve to a dimension or measure column. If the
BY clause is not specified, then the order defaults to the order of the columns as specified in the
BY clause. See order_by_clause for more information.
You cannot specify
c_alias in the
order_by_clause of the
You cannot specify this clause on the left-hand side of the model rule and also specify a
FOR loop on the right-hand side of the rule.
Specify an expression representing the value or values of the cell or cells specified on the right-hand side of the rule.
expr cannot contain a subquery. Refer to "Model Expressions" for information on model expressions.
return_rows_clause lets you specify whether to return all rows selected or only those rows updated by the model rules.
ALL is the default.
reference_model clause when you need to access multiple arrays from inside the
model_clause. This clause defines a read-only multidimensional array based on the results of a query.
BY columns cannot be specified for reference models.
The subquery of the reference model cannot refer to columns in an outer subquery.
The set operators combine the rows returned by two
SELECT statements into a single result. The number and datatypes of the columns selected by each component query must be the same, but the column lengths can be different. The names of the columns in the result set are the names of the expressions in the select list preceding the set operator.
If you combine more than two queries with set operators, then the database evaluates adjacent queries from left to right. The parentheses around the subquery are optional. You can use them to specify a different order of evaluation.
Refer to "The UNION [ALL], INTERSECT, MINUS Operators" for information on these operators, including restrictions on their use.
BY clause to order rows returned by the statement. Without an
order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.
SIBLINGS keyword is valid only if you also specify the
BY preserves any ordering specified in the hierarchical query clause and then applies the
order_by_clause to the siblings of the hierarchy.
You can specify multiple expressions in the
order_by_clause. Oracle Database first sorts rows based on their values for the first expression. Rows with the same value for the first expression are then sorted based on their values for the second expression, and so on. The database sorts nulls following all others in ascending order and preceding all others in descending order. Refer to "Sorting Query Results" for a discussion of ordering query results.
LAST is the default for ascending order, and
FIRST is the default for descending order.
If you have specified the
DISTINCT operator in this statement, then this clause cannot refer to columns unless they appear in the select list.
order_by_clause can contain no more than 255 expressions.
You cannot order by a LOB,
RAW column, nested table, or varray.
If you specify a group_by_clause in the same statement, then this
order_by_clause is restricted to the following expressions:
Expressions identical to those in the
Expressions comprising the preceding expressions that evaluate to the same value for all rows in a group
See Also:"Using the ORDER BY Clause: Examples"
UPDATE clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction. You can specify this clause only in a top-level
SELECT statement, not in subqueries.
Note:Prior to updating a LOB value, you must lock the row containing the LOB. One way to lock the row is with an embedded
UPDATEstatement. You can do this using one of the programmatic languages or
DBMS_LOBpackage. For more information on lock rows before writing to a LOB, see Oracle Database SecureFiles and Large Objects Developer's Guide.
Nested table rows are not locked as a result of locking the parent table rows. If you want the nested table rows to be locked, then you must lock them explicitly.
You cannot specify this clause with the following other constructs: the
CURSOR expression, set operators,
group_by_clause, or aggregate functions.
The tables locked by this clause must all be located on the same database and on the same database as any
LONG columns and sequences referenced in the same statement.
See Also:"Using the FOR UPDATE Clause: Examples"
column clause to lock the select rows only for a particular table or view in a join. The columns in the
OF clause only indicate which table or view rows are locked. The specific columns that you specify are not significant. However, you must specify an actual column name, not a column alias. If you omit this clause, then the database locks the selected rows from all the tables in the query.
WAIT clauses let you tell the database how to proceed if the
SELECT statement attempts to lock a row that is locked by another user.
NOWAIT to return control to you immediately if a lock exists.
WAIT to instruct the database to wait
integer seconds for the row to become available and then return control to you.
If you specify neither
NOWAIT, then the database waits until the row is available and then returns the results of the
LOCKED is an alternative way to handle a contending transaction that is locking some rows of interest. Specify
LOCKED to instruct the database to attempt to lock the rows specified by the
WHERE clause and to skip any rows that are found to be already locked by another transaction. This feature is designed for use in multiconsumer queue environments, such as Oracle Streams Advanced Queuing. It enables queue consumers to skip rows that are locked by other consumers and obtain unlocked rows without waiting for the other consumers to finish. Oracle recommends that you use the Oracle Streams Advanced Queuing APIs instead of directly using the
LOCKED functionality. Refer to Oracle Streams Advanced Queuing User's Guide for more information.
If you specify
LOCKED and the table is locked in exclusive mode, then the database will not return the results of the
SELECT statement until the lock on the table is released. In the case of
UPDATE clause is blocked regardless of the wait time specified.
Subquery Factoring: Example The following statement creates the query names
avg_cost for the initial query block containing a join, and then uses the query names in the body of the main query.
WITH dept_costs AS ( SELECT department_name, SUM(salary) dept_total FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY department_name), avg_cost AS ( SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs) SELECT * FROM dept_costs WHERE dept_total > (SELECT avg FROM avg_cost) ORDER BY department_name; DEPARTMENT_NAME DEPT_TOTAL ------------------------------ ---------- Sales 313800 Shipping 156400
SELECT * FROM employees WHERE department_id = 30 ORDER BY last_name;
The following statement selects the name, job, salary and department number of all employees except purchasing clerks from department number 30:
SELECT last_name, job_id, salary, department_id FROM employees WHERE NOT (job_id = 'PU_CLERK' AND department_id = 30) ORDER BY last_name;
The following statement selects from subqueries in the
FROM clause and for each department returns the total employees and salaries as a decimal value of all the departments:
SELECT a.department_id "Department", a.num_emp/b.total_count "%_Employees", a.sal_sum/b.total_sal "%_Salary" FROM (SELECT department_id, COUNT(*) num_emp, SUM(salary) sal_sum FROM employees GROUP BY department_id) a, (SELECT COUNT(*) total_count, SUM(salary) total_sal FROM employees) b ORDER BY a.department_id;
Selecting from a Partition: Example You can select rows from a single partition of a partitioned table by specifying the keyword
PARTITION in the
FROM clause. This SQL statement assigns an alias for and retrieves rows from the
sales_q2_2000 partition of the sample table
SELECT * FROM sales PARTITION (sales_q2_2000) s WHERE s.amount_sold > 1500 ORDER BY cust_id, time_id, channel_id;
The following example selects rows from the
oe.orders table for orders earlier than a specified date:
SELECT * FROM orders WHERE order_date < TO_DATE('2000-06-15', 'YYYY-MM-DD');
SELECT COUNT(*) * 10 FROM orders SAMPLE (10); COUNT(*)*10 ----------- 70
Because the query returns an estimate, the actual return value may differ from one query to the next.
SELECT COUNT(*) * 10 FROM orders SAMPLE (10); COUNT(*)*10 ----------- 80
The following query adds a seed value to the preceding query. Oracle Database always returns the same estimate given the same seed value:
SELECT COUNT(*) * 10 FROM orders SAMPLE(10) SEED (1); COUNT(*)*10 ----------- 110 SELECT COUNT(*) * 10 FROM orders SAMPLE(10) SEED(4); COUNT(*)*10 ----------- 120 SELECT COUNT(*) * 10 FROM orders SAMPLE(10) SEED (1); COUNT(*)*10 ----------- 110
Using Flashback Queries: Example The following statements show a current value from the sample table
hr.employees and then change the value. The intervals used in these examples are very short for demonstration purposes. Time intervals in your own environment are likely to be larger.
SELECT salary FROM employees WHERE last_name = 'Chung'; SALARY ---------- 3800 UPDATE employees SET salary = 4000 WHERE last_name = 'Chung'; 1 row updated. SELECT salary FROM employees WHERE last_name = 'Chung'; SALARY ---------- 4000
To learn what the value was before the update, you can use the following Flashback Query:
SELECT salary FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE) WHERE last_name = 'Chung'; SALARY ---------- 3800
To learn what the values were during a particular time period, you can use a version Flashback Query:
SELECT salary FROM employees VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE AND SYSTIMESTAMP - INTERVAL '1' MINUTE WHERE last_name = 'Chung';
To revert to the earlier value, use the Flashback Query as the subquery of another
UPDATE employees SET salary = (SELECT salary FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' MINUTE) WHERE last_name = 'Chung') WHERE last_name = 'Chung'; 1 row updated. SELECT salary FROM employees WHERE last_name = 'Chung'; SALARY ---------- 3800
SELECT department_id, MIN(salary), MAX (salary) FROM employees GROUP BY department_id ORDER BY department_id;
To return the minimum and maximum salaries for the clerks in each department, issue the following statement:
SELECT department_id, MIN(salary), MAX (salary) FROM employees WHERE job_id = 'PU_CLERK' GROUP BY department_id ORDER BY department_id;
Using the GROUP BY CUBE Clause: Example To return the number of employees and their average yearly salary across all possible combinations of department and job category, issue the following query on the sample tables
SELECT DECODE(GROUPING(department_name), 1, 'All Departments', department_name) AS department_name, DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job_id, COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal" FROM employees e, departments d WHERE d.department_id = e.department_id GROUP BY CUBE (department_name, job_id) ORDER BY department_name, job_id; DEPARTMENT_NAME JOB_ID Total Empl Average Sal ------------------------------ ---------- ---------- ----------- Accounting AC_ACCOUNT 1 99600 Accounting AC_MGR 1 144000 Accounting All Jobs 2 121800 Administration AD_ASST 1 52800 . . . All Departments ST_MAN 5 87360 All Departments All Jobs 107 77798.1308
(channel_desc, calendar_month_desc, country_id)
SETS syntax, you would have to write less efficient queries with more complicated SQL. For example, you could run three separate queries and
UNION them, or run a query with a
CUBE(channel_desc, calendar_month_desc, country_id) operation and filter out five of the eight groups it would generate.
SELECT channel_desc, calendar_month_desc, co.country_id, TO_CHAR(sum(amount_sold) , '9,999,999,999') SALES$ FROM sales, customers, times, channels, countries co WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id AND sales.channel_id= channels.channel_id AND customers.country_id = co.country_id AND channels.channel_desc IN ('Direct Sales', 'Internet') AND times.calendar_month_desc IN ('2000-09', '2000-10') AND co.country_iso_code IN ('UK', 'US') GROUP BY GROUPING SETS( (channel_desc, calendar_month_desc, co.country_id), (channel_desc, co.country_id), (calendar_month_desc, co.country_id) ); CHANNEL_DESC CALENDAR CO SALES$ -------------------- -------- -- -------------- Direct Sales 2000-09 UK 1,378,126 Direct Sales 2000-10 UK 1,388,051 Direct Sales 2000-09 US 2,835,557 Direct Sales 2000-10 US 2,908,706 Internet 2000-09 UK 911,739 Internet 2000-10 UK 876,571 Internet 2000-09 US 1,732,240 Internet 2000-10 US 1,893,753 Direct Sales UK 2,766,177 Direct Sales US 5,744,263 Internet UK 1,788,310 Internet US 3,625,993 2000-09 UK 2,289,865 2000-09 US 4,567,797 2000-10 UK 2,264,622 2000-10 US 4,802,459
Hierarchical Query Examples The following query with a
BY clause defines a hierarchical relationship in which the
employee_id value of the parent row is equal to the
manager_id value of the child row:
SELECT last_name, employee_id, manager_id FROM employees CONNECT BY employee_id = manager_id ORDER BY last_name;
In the following
BY clause, the
PRIOR operator applies only to the
employee_id value. To evaluate this condition, the database evaluates
employee_id values for the parent row and
commission_pct values for the child row:
SELECT last_name, employee_id, manager_id FROM employees CONNECT BY PRIOR employee_id = manager_id AND salary > commission_pct ORDER BY last_name;
To qualify as a child row, a row must have a
manager_id value equal to the
employee_id value of the parent row and it must have a
salary value greater than its
SELECT department_id, MIN(salary), MAX (salary) FROM employees GROUP BY department_id HAVING MIN(salary) < 5000 ORDER BY department_id; DEPARTMENT_ID MIN(SALARY) MAX(SALARY) ------------- ----------- ----------- 10 4400 4400 30 2500 11000 50 2100 8200 60 4200 9000
The following example uses a correlated subquery in a
HAVING clause that eliminates from the result set any departments without managers and managers without departments:
SELECT department_id, manager_id FROM employees GROUP BY department_id, manager_id HAVING (department_id, manager_id) IN (SELECT department_id, manager_id FROM employees x WHERE x.department_id = employees.department_id) ORDER BY department_id;
SELECT * FROM employees WHERE job_id = 'PU_CLERK' ORDER BY salary DESC;
To select information from
employees ordered first by ascending department number and then by descending salary, issue the following statement:
SELECT last_name, department_id, salary FROM employees ORDER BY department_id ASC, salary DESC, last_name;
To select the same information as the previous
SELECT and use the positional
BY notation, issue the following statement, which orders by ascending
department_id, then descending
salary, and finally alphabetically by
SELECT last_name, department_id, salary FROM employees ORDER BY 2 ASC, 3 DESC, 1;
CREATE OR REPLACE VIEW sales_view_ref AS SELECT country_name country, prod_name prod, calendar_year year, SUM(amount_sold) sale, COUNT(amount_sold) cnt FROM sales,times,customers,countries,products WHERE sales.time_id = times.time_id AND sales.prod_id = products.prod_id AND sales.cust_id = customers.cust_id AND customers.country_id = countries.country_id AND ( customers.country_id = 52779 OR customers.country_id = 52776 ) AND ( prod_name = 'Standard Mouse' OR prod_name = 'Mouse Pad' ) GROUP BY country_name,prod_name,calendar_year; SELECT country, prod, year, sale FROM sales_view_ref ORDER BY country, prod, year; COUNTRY PROD YEAR SALE ---------- ----------------------------------- -------- --------- France Mouse Pad 1998 2509.42 France Mouse Pad 1999 3678.69 France Mouse Pad 2000 3000.72 France Mouse Pad 2001 3269.09 France Standard Mouse 1998 2390.83 France Standard Mouse 1999 2280.45 France Standard Mouse 2000 1274.31 France Standard Mouse 2001 2164.54 Germany Mouse Pad 1998 5827.87 Germany Mouse Pad 1999 8346.44 Germany Mouse Pad 2000 7375.46 Germany Mouse Pad 2001 9535.08 Germany Standard Mouse 1998 7116.11 Germany Standard Mouse 1999 6263.14 Germany Standard Mouse 2000 2637.31 Germany Standard Mouse 2001 6456.13 16 rows selected.
The next example creates a multidimensional array from
sales_view_ref with columns containing country, product, year, and sales. It also:
Assigns the sum of the sales of the Mouse Pad for years 1999 and 2000 to the sales of the Mouse Pad for year 2001, if a row containing sales of the Mouse Pad for year 2001 exists.
Assigns the value of sales of the Standard Mouse for year 2001 to sales of the Standard Mouse for year 2002, creating a new row if a row containing sales of the Standard Mouse for year 2002 does not exist.
SELECT country,prod,year,s FROM sales_view_ref MODEL PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale s) IGNORE NAV UNIQUE DIMENSION RULES UPSERT SEQUENTIAL ORDER ( s[prod='Mouse Pad', year=2001] = s['Mouse Pad', 1999] + s['Mouse Pad', 2000], s['Standard Mouse', 2002] = s['Standard Mouse', 2001] ) ORDER BY country, prod, year; COUNTRY PROD YEAR SALE ---------- ----------------------------------- -------- --------- France Mouse Pad 1998 2509.42 France Mouse Pad 1999 3678.69 France Mouse Pad 2000 3000.72 France Mouse Pad 2001 6679.41 France Standard Mouse 1998 2390.83 France Standard Mouse 1999 2280.45 France Standard Mouse 2000 1274.31 France Standard Mouse 2001 2164.54 France Standard Mouse 2002 2164.54 Germany Mouse Pad 1998 5827.87 Germany Mouse Pad 1999 8346.44 Germany Mouse Pad 2000 7375.46 Germany Mouse Pad 2001 15721.9 Germany Standard Mouse 1998 7116.11 Germany Standard Mouse 1999 6263.14 Germany Standard Mouse 2000 2637.31 Germany Standard Mouse 2001 6456.13 Germany Standard Mouse 2002 6456.13 18 rows selected.
The first rule uses
UPDATE behavior because symbolic referencing is used on the left-hand side of the rule. The rows represented by the left-hand side of the rule exist, so the measure columns are updated. If the rows did not exist, then no action would have been taken.
The second rule uses
UPSERT behavior because positional referencing is used on the left-hand side and a single cell is referenced. The rows do not exist, so new rows are inserted and the related measure columns are updated. If the rows did exist, then the measure columns would have been updated.
See Also:Oracle Database Data Warehousing Guide for an expanded discussion and examples
The next example uses the same
sales_view_ref view and the analytic function
SUM to calculate a cumulative sum (
csum) of sales per country and per year.
SELECT country, year, sale, csum FROM (SELECT country, year, SUM(sale) sale FROM sales_view_ref GROUP BY country, year ) MODEL DIMENSION BY (country, year) MEASURES (sale, 0 csum) RULES (csum[any, any]= SUM(sale) OVER (PARTITION BY country ORDER BY year ROWS UNBOUNDED PRECEDING) ) ORDER BY country, year; COUNTRY YEAR SALE CSUM --------------- ---------- ---------- ---------- France 1998 4900.25 4900.25 France 1999 5959.14 10859.39 France 2000 4275.03 15134.42 France 2001 5433.63 20568.05 Germany 1998 12943.98 12943.98 Germany 1999 14609.58 27553.56 Germany 2000 10012.77 37566.33 Germany 2001 15991.21 53557.54 8 rows selected.
Using the FOR UPDATE Clause: Examples The following statement locks rows in the
employees table with purchasing clerks located in Oxford, which has
location_id 2500, and locks rows in the
departments table with departments in Oxford that have purchasing clerks:
SELECT e.employee_id, e.salary, e.commission_pct FROM employees e, departments d WHERE job_id = 'SA_REP' AND e.department_id = d.department_id AND location_id = 2500 FOR UPDATE ORDER BY e.employee_id;
The following statement locks only those rows in the
employees table with purchasing clerks located in Oxford. No rows are locked in the
SELECT e.employee_id, e.salary, e.commission_pct FROM employees e JOIN departments d USING (department_id) WHERE job_id = 'SA_REP' AND location_id = 2500 FOR UPDATE OF e.salary ORDER BY e.employee_id;
INSERT INTO (SELECT department_id, department_name, location_id FROM departments WHERE location_id < 2000) VALUES (9999, 'Entertainment', 2500);
However, the following statement is illegal because it contains the
INSERT INTO (SELECT department_id, department_name, location_id FROM departments WHERE location_id < 2000 WITH CHECK OPTION) VALUES (9999, 'Entertainment', 2500); * ERROR at line 2: ORA-01402: view WITH CHECK OPTION where-clause violation
Using PIVOT and UNPIVOT: Examples The
oe.orders table contains information about when an order was placed (
order_date), how it was place (
order_mode), and the total amount of the order (
order_total), as well as other information. The following example shows how to use the
PIVOT clause to pivot
order_mode values into columns, aggregating
order_total data in the process, to get yearly totals by order mode:
CREATE TABLE pivot_table AS SELECT * FROM (SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total FROM orders) PIVOT (SUM(order_total) FOR order_mode IN ('direct' AS Store, 'online' AS Internet)); SELECT * FROM pivot_table ORDER BY year; YEAR STORE INTERNET ---------- ---------- ---------- 1990 61655.7 1996 5546.6 1997 310 1998 309929.8 100056.6 1999 1274078.8 1271019.5 2000 252108.3 393349.4 6 rows selected.
UNPIVOT clause lets you rotate specified columns so that the input column headings are output as values of one or more descriptor columns, and the input column values are output as values of one or more measures columns. The first query that follows shows that nulls are excluded by default. The second query shows that you can include nulls using the
SELECT * FROM pivot_table UNPIVOT (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online')) ORDER BY year, order_mode; YEAR ORDER_ YEARLY_TOTAL ---------- ------ ------------ 1990 direct 61655.7 1996 direct 5546.6 1997 direct 310 1998 direct 309929.8 1998 online 100056.6 1999 direct 1274078.8 1999 online 1271019.5 2000 direct 252108.3 2000 online 393349.4 9 rows selected. SELECT * FROM pivot_table UNPIVOT INCLUDE NULLS (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online')) ORDER BY year, order_mode; YEAR ORDER_ YEARLY_TOTAL ---------- ------ ------------ 1990 direct 61655.7 1990 online 1996 direct 5546.6 1996 online 1997 direct 310 1997 online 1998 direct 309929.8 1998 online 100056.6 1999 direct 1274078.8 1999 online 1271019.5 2000 direct 252108.3 2000 online 393349.4 12 rows selected.
Using Join Queries: Examples The following examples show various ways of joining tables in a query. In the first example, an equijoin returns the name and job of each employee and the number and name of the department in which the employee works:
SELECT last_name, job_id, departments.department_id, department_name FROM employees, departments WHERE employees.department_id = departments.department_id ORDER BY last_name, job_id; LAST_NAME JOB_ID DEPARTMENT_ID DEPARTMENT_NAME ------------------- ---------- ------------- ---------------------- . . . Sciarra FI_ACCOUNT 100 Finance Urman FI_ACCOUNT 100 Finance Popp FI_ACCOUNT 100 Finance . . .
You must use a join to return this data because employee names and jobs are stored in a different table than department names. Oracle Database combines rows of the two tables according to this join condition:
employees.department_id = departments.department_id
The following equijoin returns the name, job, department number, and department name of all sales managers:
SELECT last_name, job_id, departments.department_id, department_name FROM employees, departments WHERE employees.department_id = departments.department_id AND job_id = 'SA_MAN' ORDER BY last_name; LAST_NAME JOB_ID DEPARTMENT_ID DEPARTMENT_NAME ------------------- ---------- ------------- ----------------------- Russell SA_MAN 80 Sales Partners SA_MAN 80 Sales Errazuriz SA_MAN 80 Sales Cambrault SA_MAN 80 Sales Zlotkey SA_MAN 80 Sales
This query is identical to the preceding example, except that it uses an additional
where_clause condition to return only rows with a
job value of '
SELECT last_name, department_id FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE last_name = 'Lorentz') ORDER BY last_name, department_id;
To give all employees in the
employees table a 10% raise if they have changed jobs—if they appear in the
job_history table—issue the following statement:
UPDATE employees SET salary = salary * 1.1 WHERE employee_id IN (SELECT employee_id FROM job_history);
To create a second version of the
new_departments, with only three of the columns of the original table, issue the following statement:
CREATE TABLE new_departments (department_id, department_name, location_id) AS SELECT department_id, department_name, location_id FROM departments;
SELECT e1.last_name||' works for '||e2.last_name "Employees and Their Managers" FROM employees e1, employees e2 WHERE e1.manager_id = e2.employee_id AND e1.last_name LIKE 'R%' ORDER BY e1.last_name; Employees and Their Managers ------------------------------- Rajs works for Mourgos Raphaely works for King Rogers works for Kaufling Russell works for King
The join condition for this query uses the aliases
e2 for the sample table
e1.manager_id = e2.employee_id
Using Outer Joins: Examples The following example shows how a partitioned outer join fills data gaps in rows to facilitate analytic function specification and reliable report formatting. The example first creates a small data table to be used in the join:
SELECT d.department_id, e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name;
Users familiar with the traditional Oracle Database outer joins syntax will recognize the same query in this form:
SELECT d.department_id, e.last_name FROM departments d, employees e WHERE d.department_id = e.department_id(+) ORDER BY d.department_id, e.last_name;
Oracle strongly recommends that you use the more flexible
FROM clause join syntax shown in the former example.
The left outer join returns all departments, including those without any employees. The same statement with a right outer join returns all employees, including those not yet assigned to a department:
Note:The employee Zeuss was added to the employees table for these examples, and is not part of the sample data.
SELECT d.department_id, e.last_name FROM departments d RIGHT OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name; DEPARTMENT_ID LAST_NAME ------------- ------------------------- . . . 110 Higgins 110 Gietz Grant Zeuss
It is not clear from this result whether employees Grant and Zeuss have
NULL, or whether their
department_id is not in the
departments table. To determine this requires a full outer join:
SELECT d.department_id as d_dept_id, e.department_id as e_dept_id, e.last_name FROM departments d FULL OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name; D_DEPT_ID E_DEPT_ID LAST_NAME ---------- ---------- ------------------------- . . . 110 110 Gietz 110 110 Higgins . . . 260 270 999 Zeuss Grant
Because the column names in this example are the same in both tables in the join, you can also use the common column feature by specifying the
USING clause of the join syntax. The output is the same as for the preceding example except that the
USING clause coalesces the two matching columns
department_id into a single column output:
SELECT department_id AS d_e_dept_id, e.last_name FROM departments d FULL OUTER JOIN employees e USING (department_id) ORDER BY department_id, e.last_name; D_E_DEPT_ID LAST_NAME ----------- ------------------------- . . . 110 Higgins 110 Gietz . . . 260 270 999 Zeuss Grant
Using Partitioned Outer Joins: Examples The following example shows how a partitioned outer join fills in gaps in rows to facilitate analytic calculation specification and reliable report formatting. The example first creates and populates a simple table to be used in the join:
CREATE TABLE inventory (time_id DATE, product VARCHAR2(10), quantity NUMBER); INSERT INTO inventory VALUES (TO_DATE('01/04/01', 'DD/MM/YY'), 'bottle', 10); INSERT INTO inventory VALUES (TO_DATE('06/04/01', 'DD/MM/YY'), 'bottle', 10); INSERT INTO inventory VALUES (TO_DATE('01/04/01', 'DD/MM/YY'), 'can', 10); INSERT INTO inventory VALUES (TO_DATE('04/04/01', 'DD/MM/YY'), 'can', 10); SELECT times.time_id, product, quantity FROM inventory PARTITION BY (product) RIGHT OUTER JOIN times ON (times.time_id = inventory.time_id) WHERE times.time_id BETWEEN TO_DATE('01/04/01', 'DD/MM/YY') AND TO_DATE('06/04/01', 'DD/MM/YY') ORDER BY 2,1; TIME_ID PRODUCT QUANTITY --------- ---------- ---------- 01-APR-01 bottle 10 02-APR-01 bottle 03-APR-01 bottle 04-APR-01 bottle 05-APR-01 bottle 06-APR-01 bottle 10 06-APR-01 bottle 8 01-APR-01 can 10 01-APR-01 can 15 02-APR-01 can 03-APR-01 can 04-APR-01 can 10 04-APR-01 can 11 05-APR-01 can 06-APR-01 can 15 rows selected.
The data is now more dense along the time dimension for each partition of the product dimension. However, each of the newly added rows within each partition is null in the quantity column. It is more useful to see the nulls replaced by the preceding non-
NULL value in time order. You can achieve this by applying the analytic function
LAST_VALUE on top of the query result:
SELECT time_id, product, LAST_VALUE(quantity IGNORE NULLS) OVER (PARTITION BY product ORDER BY time_id) quantity FROM ( SELECT times.time_id, product, quantity FROM inventory PARTITION BY (product) RIGHT OUTER JOIN times ON (times.time_id = inventory.time_id) WHERE times.time_id BETWEEN TO_DATE('01/04/01', 'DD/MM/YY') AND TO_DATE('06/04/01', 'DD/MM/YY')) ORDER BY 2,1; TIME_ID PRODUCT QUANTITY --------- ---------- ---------- 01-APR-01 bottle 10 02-APR-01 bottle 10 03-APR-01 bottle 10 04-APR-01 bottle 10 05-APR-01 bottle 10 06-APR-01 bottle 8 06-APR-01 bottle 8 01-APR-01 can 15 01-APR-01 can 15 02-APR-01 can 15 03-APR-01 can 15 04-APR-01 can 11 04-APR-01 can 11 05-APR-01 can 11 06-APR-01 can 11 15 rows selected.
See Also:Oracle Database Data Warehousing Guide for an expanded discussion on filling gaps in time series calculations and examples of usage
SELECT * FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments WHERE location_id = 1700) ORDER BY last_name;
Using Semijoins: Example In the following example, only one row needs to be returned from the
departments table, even though many rows in the
employees table might match the subquery. If no index has been defined on the
salary column in
employees, then a semijoin can be used to improve query performance.
SELECT * FROM departments WHERE EXISTS (SELECT * FROM employees WHERE departments.department_id = employees.department_id AND employees.salary > 2500) ORDER BY department_name;
Table Collections: Examples You can perform DML operations on nested tables only if they are defined as columns of a table. Therefore, when the
query_table_expr_clause of an
UPDATE statement is a
table_collection_expression, the collection expression must be a subquery that uses the
TABLE function to select the nested table column of the table. The examples that follow are based on the following scenario:
Suppose the database contains a table
hr_info with columns
manager_id, and a column of nested table type
people which has
salary columns for all the employees of each respective manager:
CREATE TYPE people_typ AS OBJECT ( last_name VARCHAR2(25), department_id NUMBER(4), salary NUMBER(8,2)); / CREATE TYPE people_tab_typ AS TABLE OF people_typ; / CREATE TABLE hr_info ( department_id NUMBER(4), location_id NUMBER(4), manager_id NUMBER(6), people people_tab_typ) NESTED TABLE people STORE AS people_stor_tab; INSERT INTO hr_info VALUES (280, 1800, 999, people_tab_typ());
The following example inserts into the
people nested table column of the
hr_info table for department 280:
INSERT INTO TABLE(SELECT h.people FROM hr_info h WHERE h.department_id = 280) VALUES ('Smith', 280, 1750);
The next example updates the department 280
people nested table:
UPDATE TABLE(SELECT h.people FROM hr_info h WHERE h.department_id = 280) p SET p.salary = p.salary + 100;
The next example deletes from the department 280
people nested table:
DELETE TABLE(SELECT h.people FROM hr_info h WHERE h.department_id = 280) p WHERE p.salary > 1700;
You could get all the rows from
hr_info, which was created in the preceding example, and all the rows from the
people nested table column of
hr_info using the following statement:
SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(t1.people) t2 WHERE t2.department_id = t1.department_id;
Now suppose that
people is not a nested table column of
hr_info, but is instead a separate table with columns
salary. You can extract the same rows as in the preceding example with this statement:
SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(CAST(MULTISET( SELECT t3.last_name, t3.department_id, t3.salary FROM people t3 WHERE t3.department_id = t1.department_id) AS people_tab_typ)) t2;
Finally, suppose that
people is neither a nested table column of table
hr_info nor a table itself. Instead, you have created a function
people_func that extracts from various sources the name, department, and salary of all employees. You can get the same information as in the preceding examples with the following query:
SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(CAST (people_func( ... ) AS people_tab_typ)) t2;
See Also:Oracle Database Object-Relational Developer's Guide for more examples of collection unnesting.
Using the LEVEL Pseudocolumn: Examples The following statement returns all employees in hierarchical order. The root row is defined to be the employee whose job is
AD_VP. The child rows of a parent row are defined to be those who have the employee number of the parent row as their manager number.
SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart, employee_id, manager_id, job_id FROM employees START WITH job_id = 'AD_VP' CONNECT BY PRIOR employee_id = manager_id; ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID ------------------ ----------- ---------- ---------- Kochhar 101 100 AD_VP Greenberg 108 101 FI_MGR Faviet 109 108 FI_ACCOUNT Chen 110 108 FI_ACCOUNT Sciarra 111 108 FI_ACCOUNT Urman 112 108 FI_ACCOUNT Popp 113 108 FI_ACCOUNT Whalen 200 101 AD_ASST Mavris 203 101 HR_REP Baer 204 101 PR_REP Higgins 205 101 AC_MGR Gietz 206 205 AC_ACCOUNT De Haan 102 100 AD_VP Hunold 103 102 IT_PROG Ernst 104 103 IT_PROG Austin 105 103 IT_PROG Pataballa 106 103 IT_PROG Lorentz 107 103 IT_PROG
The following statement is similar to the previous one, except that it does not select employees with the job
SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart, employee_id, manager_id, job_id FROM employees WHERE job_id != 'FI_MGR' START WITH job_id = 'AD_VP' CONNECT BY PRIOR employee_id = manager_id; ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID ------------------ ----------- ---------- ---------- Kochhar 101 100 AD_VP Faviet 109 108 FI_ACCOUNT Chen 110 108 FI_ACCOUNT Sciarra 111 108 FI_ACCOUNT Urman 112 108 FI_ACCOUNT Popp 113 108 FI_ACCOUNT Whalen 200 101 AD_ASST Mavris 203 101 HR_REP Baer 204 101 PR_REP Higgins 205 101 AC_MGR Gietz 206 205 AC_ACCOUNT De Haan 102 100 AD_VP Hunold 103 102 IT_PROG Ernst 104 103 IT_PROG Austin 105 103 IT_PROG Pataballa 106 103 IT_PROG Lorentz 107 103 IT_PROG
Oracle Database does not return the manager
Greenberg, although it does return employees who are managed by
The following statement is similar to the first one, except that it uses the
LEVEL pseudocolumn to select only the first two levels of the management hierarchy:
SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart, employee_id, manager_id, job_id FROM employees START WITH job_id = 'AD_PRES' CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 2; ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID ------------------ ----------- ---------- ---------- King 100 AD_PRES Kochhar 101 100 AD_VP De Haan 102 100 AD_VP Raphaely 114 100 PU_MAN Weiss 120 100 ST_MAN Fripp 121 100 ST_MAN Kaufling 122 100 ST_MAN Vollman 123 100 ST_MAN Mourgos 124 100 ST_MAN Russell 145 100 SA_MAN Partners 146 100 SA_MAN Errazuriz 147 100 SA_MAN Cambrault 148 100 SA_MAN Zlotkey 149 100 SA_MAN Hartstein 201 100 MK_MAN
SELECT last_name, department_name FROM employees@remote, departments WHERE employees.department_id = departments.department_id;
SELECT select_list FROM table1 t_alias1 WHERE expr operator (SELECT column_list FROM table2 t_alias2 WHERE t_alias1.column operator t_alias2.column); UPDATE table1 t_alias1 SET column = (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column = t_alias2.column); DELETE FROM table1 t_alias1 WHERE column operator (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column = t_alias2.column);
The following statement returns data about employees whose salaries exceed their department average. The following statement assigns an alias to
employees, the table containing the salary information, and then uses the alias in a correlated subquery:
SELECT department_id, last_name, salary FROM employees x WHERE salary > (SELECT AVG(salary) FROM employees WHERE x.department_id = department_id) ORDER BY department_id;
For each row of the
employees table, the parent query uses the correlated subquery to compute the average salary for members of the same department. The correlated subquery performs the following steps for each row of the
department_id of the row is determined.
department_id is then used to evaluate the parent query.
If the salary in that row is greater than the average salary of the departments of that row, then the row is returned.
The subquery is evaluated once for each row of the
SELECT SYSDATE FROM DUAL;
You could select
SYSDATE from the
employees table, but the database would return 14 rows of the same
SYSDATE, one for every row of the
employees table. Selecting from
DUAL is more convenient.
SELECT employees_seq.nextval FROM DUAL;
The following statement selects the current value of
SELECT employees_seq.currval FROM DUAL;