SELECT 选择
Purpose 目的
Use a SELECT
statement or subquery to retrieve data from one or more tables, object tables, views, object views, materialized views, analytic views, or hierarchies.
使用 SELECT
语句或子查询从一个或多个表、对象表、视图、对象视图、具体化视图、分析视图或层次结构中检索数据。
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 rewrite has occurred, use the EXPLAIN
PLAN
statement.
如果 SELECT
语句的部分或全部结果相当于现有的物化视图,则 Oracle 数据库可以使用该物化视图来代替 SELECT
中指定的一个或多个表陈述。这种替换称为查询重写。仅当启用成本优化且 QUERY_REWRITE_ENABLED
参数设置为 TRUE
时才会发生。要确定是否发生查询重写,请使用 EXPLAIN
PLAN
语句。
See Also: 也可以看看:
-
SQL Queries and Subqueries for general information on queries and subqueries
SQL 查询和子查询有关查询和子查询的一般信息 -
Oracle Database Data Warehousing Guide for more information on materialized views, query rewrite, and analytic views and hierarchies
Oracle 数据库数据仓库指南,了解有关物化视图、查询重写以及分析视图和层次结构的更多信息 -
If you are querying JSON data see Query JSON Data
如果您要查询 JSON 数据,请参阅查询 JSON 数据 -
If you are querying XML data see Querying XML Content Stored in Oracle XML DB
如果您要查询 XML 数据,请参阅查询 Oracle XML DB 中存储的 XML 内容
Prerequisites 先决条件
For you to select data from a table, materialized view, analytic view, or hierarchy, the object must be in your own schema or you must have the READ
or SELECT
privilege on the table, materialized view, analytic view, or hierarchy.
要从表、物化视图、分析视图或层次结构中选择数据,对象必须位于您自己的架构中,或者您必须拥有表的 READ
或 SELECT
权限、物化视图、分析视图或层次结构。
For you to select rows from the base tables of a view:
供您从视图的基表中选择行:
-
The object must be in your own schema or you must have the
READ
orSELECT
privilege on it, and
该对象必须位于您自己的架构中,或者您必须对其具有READ
或SELECT
权限,并且 -
Whoever owns the schema containing the object must have the
READ
orSELECT
privilege on the base tables.
拥有包含该对象的架构的人必须拥有基表的READ
或SELECT
权限。
The READ
ANY
TABLE
or SELECT
ANY
TABLE
system privilege also allows you to select data from any table, materialized view, analytic view, or hierarchy, or the base table of any materialized view, analytic view, or hierarchy.
READ
ANY
TABLE
或 SELECT
ANY
TABLE
系统权限还允许您从任何表、物化视图、分析视图或层次结构,或者任何物化视图、分析视图或层次结构的基表中选择数据。
To specify the FOR
UPDATE
clause, the preceding prerequisites apply with the following exception: The READ
and READ
ANY
TABLE
privileges, where mentioned, do not allow you to specify the FOR
UPDATE
clause.
要指定 FOR
UPDATE
子句,上述先决条件适用,但以下例外: READ
和 READ
ANY
TABLE
权限(如提及)不允许指定 FOR
UPDATE
子句。
To issue an Oracle Flashback Query using the flashback_query_clause
, you must have the READ
or 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 FLASHBACK
ANY
TABLE
system privilege.
要使用 flashback_query_clause
发出 Oracle 闪回查询,您必须对选择列表中的对象具有 READ
或 SELECT
权限。此外,您必须对选择列表中的对象具有 FLASHBACK
对象权限,或者您必须具有 FLASHBACK
ANY
TABLE
系统特权。
Syntax 句法
select::= 选择::=
(subquery::=, for_update_clause::=)
(子查询::=,for_update_clause::=)
subquery::= 子查询::=
(query_block::=, order_by_clause::=, row_limiting_clause::=)
(query_block::=、order_by_clause::=、row_limiting_clause::=)
query_block::= 查询块::=
(with_clause::=, select_list::=, table_reference::=, join_clause::=, inline_analytic_view, where_clause::=, hierarchical_query_clause::=, group_by_clause::=, model_clause::=)
(with_clause::=、select_list::=、table_reference::=、join_clause::=、inline_analytic_view、where_clause::=、hierarchical_query_clause::=、group_by_clause::=、model_clause::=)
with_clause::=
Note:
You cannot specify only the WITH
keyword. You must specify at least one of the clauses plsql_declarations
, subquery_factoring_clause
, or subav_factoring_clause
.
注意:您不能仅指定
WITH
关键字。您必须至少指定 plsql_declarations
、 subquery_factoring_clause
或 subav_factoring_clause
子句之一。
plsql_declarations::= plsql_声明::=
subquery_factoring_clause::=
子查询分解子句::=
search_clause::= 搜索子句::=
cycle_clause::= 循环子句::=
subav_factoring_clause::=
subav_clause::= subav_子句::=
hierarchies_clause::= 层次结构子句::=
filter_clauses::= 过滤子句::=
filter_clause::= 过滤子句::=
add_calcs_clause::=
calc_meas_clause::=
select_list::= 选择列表::=
table_reference::=
(query_table_expression::=, flashback_query_clause::=, pivot_clause::=, unpivot_clause::=, row_pattern_clause::=, containers_clause::=)
flashback_query_clause::=
query_table_expression::=
(analytic_view, hierarchy, subquery_restriction_clause::=, table_collection_expression::=)
(分析视图、层次结构、子查询限制子句::=、表集合表达式::=)
inline_external_table::=
内联外部表::=
inline_external_table_properties::=
内联外部表属性::=
modified_external_table::=
修改的外部表::=
modify_external_table_properties::=
修改外部表属性::=
pivot_for_clause::= 枢轴_for_子句::=
pivot_in_clause::= 子句中的枢轴::=
unpivot_in_clause::=
partition_extension_clause::=
分区扩展子句::=
table_collection_expression::=
表集合表达式::=
containers_clause::= 容器子句::=
shards_clause::= 分片子句::=
join_clause::=
(inner_cross_join_clause::=, outer_join_clause::=, cross_outer_apply_clause::=)
(inner_cross_join_clause::=、outer_join_clause::=、cross_outer_apply_clause::=)
inner_cross_join_clause::=
内部交叉连接子句::=
(table_reference::=) (表参考::=)
outer_join_clause::= 外部连接子句::=
(query_partition_clause::=, outer_join_type::=, table_reference::=)
(query_partition_clause::=、outer_join_type::=、table_reference::=)
query_partition_clause::=
查询分区子句::=
outer_join_type::= 外部连接类型::=
cross_outer_apply_clause::=
(table_reference::=, query_partition_clause::=)
(表引用::=,查询分区子句::=)
inline_analytic_view 内联分析视图
where_clause::= where_子句::=
hierarchical_query_clause::=
分层查询子句::=
(condition
can be any condition as described in Conditions)
( condition
可以是条件中描述的任何条件)
rollup_cube_clause::=
(grouping_expression_list::=)
(分组表达式列表::=)
grouping_sets_clause::= 分组集子句::=
(rollup_cube_clause::=, grouping_expression_list::=)
(rollup_cube_clause::=,grouping_expression_list::=)
grouping_expression_list::=
分组表达式列表::=
expression_list::= 表达式列表::=
model_clause::= 模型子句::=
(cell_reference_options::=, return_rows_clause::=, reference_model::=, main_model::=)
(cell_reference_options::=、return_rows_clause::=、reference_model::=、main_model::=)
cell_reference_options::=
单元格引用选项::=
return_rows_clause::=
reference_model::= 参考模型::=
main_model::= 主模型::=
(model_column_clauses::=, cell_reference_options::=, model_rules_clause::=)
( model_column_clauses::=、cell_reference_options::=、model_rules_clause::=)
model_column_clauses::=
model_rules_clause::=
(model_iterate_clause::=, cell_assignment::=, order_by_clause::=)
( model_iterate_clause::=、cell_assignment::=、order_by_clause::=)
model_iterate_clause::=
cell_assignment::= 单元格分配::=
single_column_for_loop::=
multi_column_for_loop::=
多列for_循环::=
row_limiting_clause::= 行限制子句::=
row_pattern_clause::= 行模式子句::=
(row_pattern_partition_by::=, row_pattern_order_by::=, row_pattern_measures::=, row_pattern_rows_per_match::=, row_pattern_skip_to::=, row_pattern::=, row_pattern_subset_clause::=, row_pattern_definition_list::=)
(row_pattern_partition_by::=、row_pattern_order_by::=、row_pattern_measures::=、row_pattern_rows_per_match::=、row_pattern_skip_to::=、row_pattern::=、row_pattern_subset_clause::=、row_pattern_definition_list::=)
row_pattern_partition_by::=
row_pattern_order_by::=
row_pattern_measures::= 行模式测量::=
row_pattern_measure_column::=
行_模式_测量_列::=
row_pattern_rows_per_match::=
row_pattern_skip_to::=
row_pattern::= 行模式::=
row_pattern_term::=
row_pattern_factor::= 行模式因子::=
row_pattern_primary::=
row_pattern_permute::=
row_pattern_quantifier::=
行模式量词::=
row_pattern_subset_clause::=
row_pattern_subset_item::=
row_pattern_definition_list::=
行模式定义列表::=
row_pattern_definition::=
行模式定义::=
row_pattern_rec_func::=
(row_pattern_classifier_func::=, row_pattern_match_num_func::=, row_pattern_navigation_func::=, row_pattern_aggregate_func::=)
(row_pattern_classifier_func::=、row_pattern_match_num_func::=、row_pattern_navigation_func::=、row_pattern_aggregate_func::=)
row_pattern_classifier_func::=
row_pattern_match_num_func::=
row_pattern_navigation_func::=
(row_pattern_nav_logical::=, row_pattern_nav_physical::=, row_pattern_nav_compound::=)
(row_pattern_nav_logic::=、row_pattern_nav_physical::=、row_pattern_nav_compound::=)
row_pattern_nav_logical::=
row_pattern_nav_逻辑::=
row_pattern_nav_physical::=
row_pattern_nav_compound::=
row_pattern_aggregate_func::=
Semantics 语义学
with_clause with_子句
Use the with_clause
to define the following:
使用 with_clause
定义以下内容:
-
PL/SQL procedures and functions (using the
plsql_declarations
clause)
PL/SQL 过程和函数(使用plsql_declarations
子句) -
Subquery blocks (using
subquery_factoring_clause
orsubav_factoring_clause
, or both)
子查询块(使用subquery_factoring_clause
或subav_factoring_clause
或两者)
plsql_declarations plsql_声明
The plsql_declarations
clause lets you declare and define PL/SQL functions and procedures. You can then reference the PL/SQL functions in the query in which you specify this clause, as well as its subqueries, if any. For the purposes of name resolution, these function names have precedence over schema-level stored functions.
plsql_declarations
子句允许您声明和定义 PL/SQL 函数和过程。然后,您可以在指定此子句的查询中引用 PL/SQL 函数及其子查询(如果有)。出于名称解析的目的,这些函数名称优先于架构级存储函数。
If the query in which you specify this clause is not a top-level SELECT
statement, then the following rules apply to the top-level SQL statement that contains the query:
如果指定此子句的查询不是顶级 SELECT
语句,则以下规则适用于包含该查询的顶级 SQL 语句:
-
If the top-level statement is a
SELECT
statement, then it must have either aWITH
plsql_declarations
clause or theWITH_PLSQL
hint.
如果顶级语句是SELECT
语句,则它必须具有WITH
plsql_declarations
子句或WITH_PLSQL
提示。 -
If the top-level statement is a
DELETE
,MERGE
,INSERT
, orUPDATE
statement, then it must have theWITH_PLSQL
hint.
如果顶级语句是DELETE
、MERGE
、INSERT
或UPDATE
语句,则它必须具有WITH_PLSQL
提示。
The WITH_PLSQL
hint only enables you to specify the WITH
plsql_declarations
clause within the statement. It is not an optimizer hint.
WITH_PLSQL
提示仅允许您在语句中指定 WITH
plsql_declarations
子句。它不是优化器提示。
See Also: 也可以看看:
-
Oracle Database PL/SQL Language Reference for syntax and restrictions for
function_declaration
andprocedure_declaration
.
Oracle 数据库 PL/SQL 语言参考,了解function_declaration
和procedure_declaration
的语法和限制。 -
"Using a PL/SQL Function in the WITH Clause: Examples"
“在WITH子句中使用PL/SQL函数:示例”
subquery_factoring_clause
子查询分解子句
The subquery_factoring_clause
lets you assign a name (query_name
) to a subquery block. You can then reference the subquery block multiple places in the query by specifying query_name
. Oracle Database optimizes the query by treating the query_name
as either an inline view or as a temporary table. The query_name
is subject to the same naming conventions and restrictions as database schema objects. Refer to "Database Object Naming Rules" for information on database object names.
subquery_factoring_clause
允许您为子查询块分配名称 ( query_name
)。然后,您可以通过指定 query_name
在查询中的多个位置引用子查询块。 Oracle 数据库通过将 query_name
视为内联视图或临时表来优化查询。 query_name
遵循与数据库模式对象相同的命名约定和限制。有关数据库对象名称的信息,请参阅“数据库对象命名规则”。
The column aliases following the query_name
and the set operators separating multiple subqueries in the AS
clause are valid and required for recursive subquery factoring. The search_clause
and cycle_clause
are valid only for recursive subquery factoring but are not required. See "Recursive Subquery Factoring".
query_name
后面的列别名和 AS
子句中分隔多个子查询的集合运算符是有效的,并且是递归子查询分解所必需的。 search_clause
和 cycle_clause
仅对递归子查询分解有效,但不是必需的。请参阅“递归子查询因式分解”。
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. For recursive subquery factoring, the query name is even visible to the subquery that defines the query name itself.
您可以在任何顶级 SELECT
语句和大多数类型的子查询中指定此子句。查询名称对于主查询和所有后续子查询都是可见的。对于递归子查询分解,查询名称甚至对于定义查询名称本身的子查询也是可见的。
Recursive Subquery Factoring
递归子查询因式分解
If a subquery_factoring_clause
refers to its own query_name
in the subquery that defines it, then the subquery_factoring_clause
is said to be recursive. A recursive subquery_factoring_clause
must contain two query blocks: the first is the anchor member and the second is the recursive member. The anchor member must appear before the recursive member, and it cannot reference query_name
. The anchor member can be composed of one or more query blocks combined by the set operators: UNION
ALL
, UNION
, INTERSECT
or MINUS
. The recursive member must follow the anchor member and must reference query_name
exactly once. You must combine the recursive member with the anchor member using the UNION
ALL
set operator.
如果 subquery_factoring_clause
在定义它的子查询中引用它自己的 query_name
,则 subquery_factoring_clause
被认为是递归的。递归 subquery_factoring_clause
必须包含两个查询块:第一个是锚成员,第二个是递归成员。锚成员必须出现在递归成员之前,并且不能引用 query_name
。锚成员可以由一个或多个由集合运算符组合的查询块组成: UNION
ALL
、 UNION
、 INTERSECT
或 < b9> 。递归成员必须跟随锚成员,并且必须恰好引用 query_name
一次。您必须使用 UNION
ALL
集合运算符将递归成员与锚定成员组合起来。
The number of column aliases following WITH
query_name
and the number of columns in the SELECT
lists of the anchor and recursive query blocks must be the same.
WITH
query_name
后面的列别名数量与锚点和递归查询块的 SELECT
列表中的列数量必须相同。
The recursive member cannot contain any of the following elements:
递归成员不能包含以下任何元素:
-
The
DISTINCT
keyword or aGROUP
BY
clause
DISTINCT
关键字或GROUP
BY
子句 -
The
model_clause
model_clause
-
An aggregate function. However, analytic functions are permitted in the select list.
聚合函数。但是,选择列表中允许使用分析函数。 -
Subqueries that refer to
query_name
.
引用query_name
的子查询。 -
Outer joins that refer to
query_name
as the right table.
将query_name
引用为右表的外连接。
In previous releases of Oracle Database, the recursive member of a recursive WITH
clause ran serially regardless of the parallelism of the entire query (also known as the top-level SELECT
statement). Beginning with Oracle Database 12c Release 2 (12.2), the recursive member runs in parallel if the optimizer determines that the top-level SELECT
statement can be executed in parallel.
在 Oracle 数据库的早期版本中,无论整个查询(也称为顶级 SELECT
语句)的并行性如何,递归 WITH
子句的递归成员都会串行运行。从 Oracle Database 12c 第 2 版 (12.2) 开始,如果优化器确定顶级 SELECT
语句可以并行执行,则递归成员将并行运行。
search_clause 搜索子句
Use the SEARCH
clause to specify an ordering for the rows.
使用 SEARCH
子句指定行的顺序。
-
Specify
BREADTH
FIRST
BY
if you want sibling rows returned before any child rows are returned.
如果您希望在返回任何子行之前返回同级行,请指定BREADTH
FIRST
BY
。 -
Specify
DEPTH
FIRST
BY
if you want child rows returned before any siblings rows are returned.
如果您希望在返回任何同级行之前返回子行,请指定DEPTH
FIRST
BY
。 -
Sibling rows are ordered by the columns listed after the
BY
keyword.
同级行按BY
关键字后列出的列排序。 -
The
c_alias
list following theSEARCH
keyword must contain column names from the column alias list forquery_name
.
SEARCH
关键字后面的c_alias
列表必须包含query_name
的列别名列表中的列名称。 -
The
ordering_column
is automatically added to the column list for the query name. The query that selects fromquery_name
can include anORDER
BY
onordering_column
to return the rows in the order that was specified by theSEARCH
clause.
ordering_column
会自动添加到查询名称的列列表中。从query_name
中选择的查询可以在ordering_column
上包含ORDER
BY
,以按 < 指定的顺序返回行。 b5> 子句。
cycle_clause 循环子句
Use the CYCLE
clause to mark cycles in the recursion.
使用 CYCLE
子句标记递归中的循环。
-
The
c_alias
list following theCYCLE
keyword must contain column names from the column alias list forquery_name
. Oracle Database uses these columns to detect a cycle.
CYCLE
关键字后面的c_alias
列表必须包含query_name
的列别名列表中的列名称。 Oracle 数据库使用这些列来检测循环。 -
cycle_value
andno_cycle_value
should be character strings of length 1.
cycle_value
和no_cycle_value
应该是长度为1的字符串。 -
If a cycle is detected, then the cycle mark column specified by
cycle_mark_c_alias
for the row causing the cycle is set to the value specified forcycle_value
. The recursion will then stop for this row. That is, it will not look for child rows for the offending row, but it will continue for other noncyclic rows.
如果检测到循环,则由cycle_mark_c_alias
为导致循环的行指定的循环标记列将设置为为cycle_value
指定的值。然后递归将针对该行停止。也就是说,它不会查找违规行的子行,但会继续查找其他非循环行。 -
If no cycles are found, then the cycle mark column is set to the default value specified for
no_cycle_value
.
如果未找到循环,则循环标记列将设置为为no_cycle_value
指定的默认值。 -
The cycle mark column is automatically added to the column list for the
query_name
.
循环标记列会自动添加到query_name
的列列表中。 -
A row is considered to form a cycle if one of its ancestor rows has the same values for the cycle columns.
如果某行的祖先行具有相同的循环列值,则该行被视为形成循环。
If you omit the CYCLE
clause, then the recursive WITH
clause returns an error if cycles are discovered. In this case, a row forms a cycle if one of its ancestor rows has the same values for all the columns in the column alias list for query_name
that are referenced in the WHERE
clause of the recursive member.
如果省略 CYCLE
子句,则在发现循环时递归 WITH
子句将返回错误。在这种情况下,如果一行的祖先行之一对于 query_name
的列别名列表中的所有列具有相同的值(在 WHERE
子句中引用),则该行形成一个循环。递归成员。
Restrictions on Subquery Factoring
子查询分解的限制
This clause is subject to the following restrictions:
本条款受以下限制:
-
You can specify only one
subquery_factoring_clause
in a single SQL statement. Anyquery_name
defined in thesubquery_factoring_clause
can be used in any subsequent named query block in thesubquery_factoring_clause
.
在一条 SQL 语句中只能指定一个subquery_factoring_clause
。subquery_factoring_clause
中定义的任何query_name
都可以在subquery_factoring_clause
中的任何后续命名查询块中使用。 -
In a compound query with set operators, you cannot use the
query_name
for any of the component queries, but you can use thequery_name
in theFROM
clause of any of the component queries.
在具有集合运算符的复合查询中,您不能对任何组件查询使用query_name
,但可以在任何组件查询的FROM
子句中使用query_name
。的组件查询。 -
You cannot specify duplicate names in the column alias list for
query_name
.
您不能在query_name
的列别名列表中指定重复的名称。 -
The name used for the
ordering_column
has to be different from the name used forcycle_mark_c_alias
.
ordering_column
使用的名称必须与cycle_mark_c_alias
使用的名称不同。 -
The
ordering_column
and cycle mark column names cannot already be in the column alias list forquery_name
.
ordering_column
和循环标记列名称不能已位于query_name
的列别名列表中。
See Also: 也可以看看:
-
Oracle Database Concepts for information about inline views
Oracle 数据库概念,了解有关内联视图的信息 -
"Subquery Factoring: Example"
“子查询因式分解:示例” -
"Recursive Subquery Factoring: Examples"
“递归子查询分解:示例”
subav_factoring_clause
With the subav_factoring_clause
, you can define a transitory analytic view that filters fact data prior to aggregation or adds calculated measures to a query of an analytic view. The subav_name
argument assigns a name to the transitory analytic view. You can then reference the transitory analytic view multiple places in the query by specifying subav_name
. The subav_name
is subject to the same naming conventions and restrictions as database schema objects. Refer to "Database Object Naming Rules" for information on database object names.
使用 subav_factoring_clause
,您可以定义一个临时分析视图,该视图在聚合之前过滤事实数据或将计算的度量添加到分析视图的查询中。 subav_name
参数为瞬态分析视图指定一个名称。然后,您可以通过指定 subav_name
在查询中的多个位置引用临时分析视图。 subav_name
遵循与数据库模式对象相同的命名约定和限制。有关数据库对象名称的信息,请参阅“数据库对象命名规则”。
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.
您可以在任何顶级 SELECT
语句和大多数类型的子查询中指定此子句。查询名称对于主查询和所有后续子查询都是可见的。
The subav_clause
argument defines a transitory analytic view.
subav_clause
参数定义瞬态分析视图。
subav_clause 子av_子句
With the USING
keyword, specify the name of an analytic view, which may be a transitory analytic view previously defined in the WITH
clause or it may be a persistent analytic view. A persistent analytic view is defined in a CREATE
ANALYTIC
VIEW
statement. If the analytic view is a persistent one, then the current user must have select access on it.
使用 USING
关键字指定分析视图的名称,该名称可以是先前在 WITH
子句中定义的瞬态分析视图,也可以是持久分析视图。持久分析视图在 CREATE
ANALYTIC
VIEW
语句中定义。如果分析视图是持久视图,则当前用户必须对其具有选择访问权限。
See Also: 也可以看看:
Analytic Views: Examples分析视图:示例
hierarchies_clause 层次结构子句
The hierarchies_clause
specifies the hierarchies of the base analytic view that the results of the transitory analytic view are dimensioned by. With the HIERARCHIES
keyword, specify the alias of one or more hierarchies of the base analytic view.
hierarchies_clause
指定基本分析视图的层次结构,瞬态分析视图的结果是根据该层次结构来确定尺寸的。使用 HIERARCHIES
关键字指定基本分析视图的一个或多个层次结构的别名。
If you do not specify a HIERARCHIES
clause, then the default hierarchies of the base analytic view are used.
如果不指定 HIERARCHIES
子句,则使用基本分析视图的默认层次结构。
filter_clauses 过滤子句
You may specify a given hier_alias
in at most one filter_clause
.
您最多可以在一个 filter_clause
中指定给定的 hier_alias
。
filter_clause 过滤子句
The filter clause applies the specified predicate condition to the fact table, which reduces the number of rows returned from the table before aggregation of the measure values. The predicate may contain any SQL row function or operation. The predicate may refer to any attribute of the specified hierarchy or it may refer to a measure of the analytic view if you specify the MEASURES
keyword.
过滤子句将指定的谓词条件应用于事实表,这会减少在聚合度量值之前从表返回的行数。谓词可以包含任何 SQL 行函数或操作。谓词可以引用指定层次结构的任何属性,或者如果您指定 MEASURES
关键字,则它可以引用分析视图的度量。
For example, the following clause restricts the aggregation of measure values to those for the first and second quarters of every year of a time hierarchy.
例如,以下子句将度量值的聚合限制为时间层次结构每年第一季度和第二季度的度量值。
FILTER FACT (time_hier TO quarter_of_year IN (1,2))
If you then select from the transitory analytic view the sales for the years 2000 and 2001, the values returned are the aggregated values of the first and second quarters only.
如果您随后从临时分析视图中选择 2000 年和 2001 年的销售额,则返回的值仅为第一季度和第二季度的合计值。
An example of specifying a predicate for a measure in the filter clause is the following.
下面是在过滤子句中为度量指定谓词的示例。
FILTER FACT (MEASURES TO sales BETWEEN 100 AND 200)
attr_dim_alias
The alias of an attribute dimension in the base analytic view. The USER_ANALYTIC_VIEW_DIMENSIONS
view contains the aliases of the attribute dimensions in an analytic view.
基础分析视图中属性维度的别名。 USER_ANALYTIC_VIEW_DIMENSIONS
视图包含分析视图中属性维度的别名。
hier_alias 层次别名
The alias of a hierarchy in the base analytic view. The USER_ANALYTIC_VIEW_HIERS
view contains the aliases of the hierarchies in an analytic view.
基础分析视图中层次结构的别名。 USER_ANALYTIC_VIEW_HIERS
视图包含分析视图中层次结构的别名。
add_calcs_clause 添加计算子句
With the ADD
MEASURES
keywords, you may add calculated measures to the transitory analytic view.
使用 ADD
MEASURES
关键字,您可以将计算的度量添加到瞬态分析视图中。
calc_meas_clause 计算测量子句
Specify a name for the calculated measure and an analytic view expression that specifies values for the calculated measure. The analytic view expression can be any valid calc_meas_expression
as described in Analytic View Expressions. For example, the following adds a calculated measure named “share_sales.”
指定计算度量的名称以及指定计算度量值的分析视图表达式。分析视图表达式可以是任何有效的 calc_meas_expression
,如分析视图表达式中所述。例如,以下添加了一个名为“share_sales”的计算度量。
ADD MEASURES (share_sales AS (SHARE_OF(sales HIERARCHY time_hier PARENT)))
hint 暗示
Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.
指定一条注释,将指令传递给优化器以选择语句的执行计划。
See Also: 也可以看看:
"Hints" for the syntax and description of hints
“Hints”用于提示的语法和描述
DISTINCT | UNIQUE 独特|独特的
Specify DISTINCT
or 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.
如果您希望数据库仅返回所选的每组重复行的一个副本,请指定 DISTINCT
或 UNIQUE
。这两个关键字是同义词。重复行是指选择列表中每个表达式的值都匹配的行。
Restrictions on DISTINCT and UNIQUE Queries
对 DISTINCT 和 UNIQUE 查询的限制
These types of queries are subject to the following restrictions:
这些类型的查询受到以下限制:
-
When you specify
DISTINCT
orUNIQUE
, 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 parameterDB_BLOCK_SIZE
.
当您指定DISTINCT
或UNIQUE
时,所有选择列表表达式中的字节总数限制为数据块的大小减去一些开销。该大小由初始化参数DB_BLOCK_SIZE
指定。 -
You cannot specify
DISTINCT
if theselect_list
contains LOB columns.
如果select_list
包含 LOB 列,则无法指定DISTINCT
。
ALL
Specify ALL
if you want the database to return all rows selected, including all copies of duplicates. The default is ALL
.
如果您希望数据库返回所有选定的行(包括所有重复项的副本),请指定 ALL
。默认为 ALL
。
select_list 选择列表
The select_list
lets you specify the columns you want to retrieve from the database.
select_list
允许您指定要从数据库检索的列。
* (all-column wildcard) *(全列通配符)
Specify the all-column wildcard (asterisk) to select all columns, excluding pseudocolumns and INVISIBLE
columns, 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.
指定全列通配符(星号)以从 FROM
子句中列出的所有表、视图或具体化视图中选择所有列(不包括伪列和 INVISIBLE
列)。这些列按照表、视图或物化视图的 *_TAB_COLUMNS
数据字典视图的 COLUMN_ID
列指示的顺序返回。
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 ALTER
TABLE
SET
UNUSED
statement are not selected.
如果您从表中而不是从视图或物化视图中进行选择,则由 ALTER
TABLE
SET
的列 UNUSED
语句没有被选中。
See Also: 也可以看看:
ALTER TABLE, "Simple Query Examples", and "Selecting from the DUAL Table: Example"
ALTER TABLE、“简单查询示例”和“从 DUAL 表中选择:示例”
query_name.* 查询名称.*
Specify query_name
followed by a period and the asterisk to select all columns from the specified subquery block. For query_name
, specify a subquery block 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 query_table_expression
(FROM
clause).
指定 query_name
后跟句点和星号以选择指定子查询块中的所有列。对于 query_name
,指定已在 subquery_factoring_clause
中指定的子查询块名称。您必须指定 subquery_factoring_clause
才能在 select_list
中指定 query_name
。如果您在 select_list
中指定 query_name
,那么您还必须在 query_table_expression
( FROM
子句中指定 query_name
)。
table.* | view.* | materialized view.*
表。*|查看。*|物化视图。*
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.
指定对象名称,后跟句点和星号,以从指定的表、视图或物化视图中选择所有列。 Oracle 数据库按照创建对象时指定列的顺序返回一组列。从两个或多个表、视图或具体化视图中选择行的查询是联接。
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.
您可以使用模式限定符从您自己的模式以外的模式中的表、视图或物化视图中进行选择。如果省略 schema
,则数据库假定表、视图或物化视图位于您自己的架构中。
See Also: 也可以看看:
"Joins" “加入”
t_alias .* t_别名.*
Specify a correlation name (alias) followed by a period and the asterisk to select all columns from the object with that correlation name specified in the FROM
clause of the same subquery. The object can be a table, view, materialized view, or subquery. 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 objects is a join.
指定相关名称(别名),后跟句点和星号,以从具有在同一子查询的 FROM
子句中指定的相关名称的对象中选择所有列。该对象可以是表、视图、物化视图或子查询。 Oracle 数据库按照创建对象时指定列的顺序返回一组列。从两个或多个对象中选择行的查询是联接。
expr 表达式
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.
指定代表您要选择的信息的表达式。仅当包含该列的表、视图或物化视图在 FROM
子句中用 schema
限定时,此列表中的列名称才能用 schema
限定。如果指定对象类型的成员方法,则即使该方法不带参数,也必须在方法名称后面加上括号。
The expression can also hold a scalar value that can be return values of PL/SQLfunctions, subqueries that return a single value per row, and SQL macros.
该表达式还可以保存标量值,该标量值可以是 PL/SQL 函数的返回值、每行返回单个值的子查询以及 SQL 宏。
c_alias c_别名
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.
指定列表达式的别名。 Oracle 数据库将在结果集的列标题中使用此别名。 AS
关键字是可选的。别名在查询期间有效地重命名选择列表项。别名可以在 order_by_clause
中使用,但不能在查询中的其他子句中使用。
See Also: 也可以看看:
-
Oracle Database Data Warehousing Guide for information on using the
expr
AS
c_alias
syntax with theUNION
ALL
operator in queries of multiple materialized views
Oracle 数据库数据仓库指南 有关在多个物化视图的查询 -
"About SQL Expressions" for the syntax of
expr
expr
语法的“关于 SQL 表达式”
Restrictions on the Select List
选择列表的限制
The select list is subject to the following restrictions:
选择列表受以下限制:
-
If you also specify a group_by_clause in this statement, then this select list can contain only the following types of expressions:
如果您在此语句中还指定了 group_by_clause,则此选择列表只能包含以下类型的表达式:-
Constants 常数
-
Aggregate functions and the functions
USER
,UID
, andSYSDATE
聚合函数和函数USER
、UID
和SYSDATE
-
Expressions identical to those in the
group_by_clause
. If thegroup_by_clause
is in a subquery, then all columns in the select list of the subquery must match theGROUP
BY
columns in the subquery. If the select list andGROUP
BY
columns of a top-level query or of a subquery do not match, then the statement results in ORA-00979.
表达式与group_by_clause
中的表达式相同。如果group_by_clause
在子查询中,则子查询的选择列表中的所有列都必须与子查询中的GROUP
BY
列匹配。如果顶级查询或子查询的选择列表与GROUP
BY
列不匹配,则该语句将导致 ORA-00979。 -
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.
仅当连接有且仅有一个键保留表时,您才可以从连接视图中选择 rowid。该表的 rowid 成为视图的 rowid。See Also: 也可以看看:
Oracle Database Administrator's Guide for information on key-preserved tables
Oracle 数据库管理员指南 有关密钥保留表的信息 -
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
子句中指定联接,则必须使用表名或表别名来限定列名。
FROM Clause FROM 子句
The FROM
clause lets you specify the objects from which data is selected.
FROM
子句允许您指定从中选择数据的对象。
You can invoke a polymorphic table function (PTF) in the query block of the FROM
clause like other existing table functions. A PTF is a table function whose operands can have more than one type. .
您可以像其他现有表函数一样在 FROM
子句的查询块中调用多态表函数 (PTF)。 PTF 是一种表函数,其操作数可以有多种类型。 。
Starting with Oracle Database Release 19c, version 19.7, you can write table valued macros and use them inside the FROM
clause, where it would be legal to call a PL/SQL function. SQL table macros are expressions, typically used in a FROM
clause, to act like a kind of polymorphic (parameterized) views. You must define these macro functions in PL/SQL and call them from SQL for them to function as macros.
从 Oracle 数据库版本 19c 版本 19.7 开始,您可以编写表值宏并在 FROM
子句中使用它们,在该子句中调用 PL/SQL 函数是合法的。 SQL 表宏是表达式,通常在 FROM
子句中使用,其作用类似于一种多态(参数化)视图。您必须在 PL/SQL 中定义这些宏函数,并从 SQL 中调用它们,以便它们发挥宏的作用。
See Also: 也可以看看:
ONLY 仅有的
The ONLY
clause applies only to views. Specify ONLY
if the view in the FROM
clause is a view belonging to a hierarchy and you do not want to include rows from any of its subviews.
ONLY
子句仅适用于视图。如果 FROM
子句中的视图是属于层次结构的视图,并且您不想包含其任何子视图中的行,请指定 ONLY
。
query_table_expression 查询表表达式
Use the query_table_expression
clause to identify a subquery block, table, view, materialized view, analytic view, hierarchy, partition, or subpartition, or to specify a subquery that identifies the objects. In order to specify a subquery block, you must have specified the subquery block name (query_name
in the subquery_factoring_clause
or subav_name
in the subav_factoring_clause
).
使用 query_table_expression
子句来标识子查询块、表、视图、物化视图、分析视图、层次结构、分区或子分区,或者指定标识对象的子查询。为了指定子查询块,您必须指定子查询块名称( subquery_factoring_clause
中的 query_name
或 subav_factoring_clause
中的 subav_name
)。
The analytic view in the expression may be a transitory analytic view defined in the with_clause
or a persistent analytic view.
表达式中的分析视图可以是 with_clause
中定义的瞬时分析视图或持久分析视图。
See Also: 也可以看看:
"Using Subqueries: Examples"
“使用子查询:示例”
Specify LATERAL
to designate subquery
as a lateral inline view. Within a lateral inline view, you can specify tables that appear to the left of the lateral inline view in the FROM
clause of a query. You can specify this left correlation anywhere within subquery
(such as the SELECT
, FROM
, and WHERE
clauses) and at any nesting level.
指定 LATERAL
将 subquery
指定为横向内联视图。在横向内联视图中,您可以在查询的 FROM
子句中指定显示在横向内联视图左侧的表。您可以在 subquery
内的任何位置(例如 SELECT
、 FROM
和 WHERE
子句)和任何嵌套级别指定此左相关性。
Restrictions on LATERAL 横向限制
Lateral inline views are subject to the following restrictions:
横向内联视图受到以下限制:
-
If you specify
LATERAL
, then you cannot specify thepivot_clause
, theunpivot_clause
, or a pattern in thetable_reference
clause.
如果指定LATERAL
,则无法在table_reference
子句中指定pivot_clause
、unpivot_clause
或模式。 -
If a lateral inline view contains the
query_partition_clause
, and it is the right side of a join clause, then it cannot contain a left correlation to the left table in the join clause. However, it can contain a left correlation to a table to its left in theFROM
clause that is not the left table.
如果横向内联视图包含query_partition_clause
,并且它是 join 子句的右侧,则它不能包含与 join 子句中的左表的左关联。但是,它可以包含与FROM
子句中左侧表(不是左表)的左相关性。 -
A lateral inline view cannot contain a left correlation to the first table in a right outer join or full outer join.
横向内联视图不能在右外连接或完全外连接中包含与第一个表的左关联。
See Also: 也可以看看:
"Using Lateral Inline Views: Example"
“使用横向内联视图:示例”
inline_external_table 内联外部表
Specify this clause to inline an external table in a query. You must specify the table columns and properties for the external table that will be inlined in the query.
指定此子句可在查询中内联外部表。您必须指定将在查询中内联的外部表的表列和属性。
inline_external_table_properties
内联外部表属性
This clause extends the external_table_data_props
with the REJECT LIMIT
and access_driver_type
options. Use this clause to specify the properties of the external table.
此子句使用 REJECT LIMIT
和 access_driver_type
选项扩展 external_table_data_props
。使用此子句指定外部表的属性。
In addition to supporting external data residing in operating file systems and Big Data sources and formats such as HDFS and Hive, Oracle supports external data residing in objects.
除了支持驻留在操作系统文件系统和大数据源以及 HDFS 和 Hive 等格式中的外部数据外,Oracle 还支持驻留在对象中的外部数据。
modified_external_table 修改的外部表
You can use this clause to override some external table properties specified by the CREATE TABLE
or ALTER TABLE
statements from within a query.
您可以使用此子句覆盖查询中 CREATE TABLE
或 ALTER TABLE
语句指定的某些外部表属性。
You can override external table parameters at runtime.
您可以在运行时覆盖外部表参数。
Restrictions 限制
-
You must specify the key words
EXTERNAL MODIFY
in the query. If you do not specify the keywords, you will see aMissing or invalid option
error.
您必须在查询中指定关键字EXTERNAL MODIFY
。如果不指定关键字,您将看到Missing or invalid option
错误。 -
You must reference an external table in the query. If you do not, you will see an error.
您必须在查询中引用外部表。如果不这样做,您将看到错误。 -
You must specify at least one property in the query. One of
DEFAULT DIRECTORY
,LOCATION
,ACCESS PARAMETERS
, orREJECT LIMIT
.
您必须在查询中至少指定一个属性。DEFAULT DIRECTORY
、LOCATION
、ACCESS PARAMETERS
或REJECT LIMIT
之一。 -
If you specify more than one external table properties, they must be listed in order. First the
DEFAULT DIRECTORY
must be specified, followed by theACCESS PARAMETERS
,LOCATION
andREJECT LIMIT
. Otherwise an error will be raised.
如果指定多个外部表属性,则必须按顺序列出它们。首先必须指定DEFAULT DIRECTORY
,然后是ACCESS PARAMETERS
、LOCATION
和REJECT LIMIT
。否则会引发错误。 -
In the
DEFAULT DIRECTORY
clause, you must specify only one proper default directory. Otherwise aMissing DEFAULT keyword
error will occur.
在DEFAULT DIRECTORY
子句中,您必须仅指定一个适当的默认目录。否则会出现Missing DEFAULT keyword
错误。 -
You must enclose a filename in the
LOCATION
clause within quotes. Otherwise aMissing keyword
error will occur. Note that the access driver will decide whether or not to allow aLOCATION
clause in the query. If the clause is disallowed for a particular access driver, an error will be raised.
您必须将文件名括在LOCATION
子句中并用引号括起来。否则会出现Missing keyword
错误。请注意,访问驱动程序将决定是否允许查询中使用LOCATION
子句。如果特定访问驱动程序不允许使用该子句,则会引发错误。 -
For
ORACLE_LOADER
andORACLE_DATAPUMP
access drivers, the external file location in theLOCATION
clause must be specified in the following format: directory: location, i.e, the directory and location must be separated by a colon. Multiple locations in the clause must be separated by a comma. Otherwise, aMissing keyword
error will occur.
对于ORACLE_LOADER
和ORACLE_DATAPUMP
访问驱动程序,LOCATION
子句中的外部文件位置必须按以下格式指定:目录:位置,即目录和位置必须用冒号分隔。子句中的多个位置必须用逗号分隔。否则,将会出现Missing keyword
错误。 -
Note that
LOCATION
will be made optional inCREATE TABLE
, and must be specified either when creating or querying the external table. Otherwise an error will be raised in the access driver.
请注意,LOCATION
在CREATE TABLE
中将变为可选,并且必须在创建或查询外部表时指定。否则,访问驱动程序中将会出现错误。 -
When populating external data using
ORACLE DATAPUMP
viaCTAS
, the external file location must be specified. This will be the only case whereLOCATION
clause is mandatory inCREATE TABLE
.
当通过CTAS
使用ORACLE DATAPUMP
填充外部数据时,必须指定外部文件位置。这将是LOCATION
子句在CREATE TABLE
中是强制性的唯一情况。 -
When overriding access parameters, a proper access parameter list must be provided in the
ACCESS PARAMETERS
clause, with enclosing parentheses.
当覆盖访问参数时,必须在ACCESS PARAMETERS
子句中提供正确的访问参数列表,并用括号括起来。Note that the syntax and allowable values for the access parameters in the
modified_external_table
clause are the same as for the external table DDL for each access driver. For more see Oracle Database Utilitiesfor additional details regarding syntax and permissible values.
请注意,modified_external_table
子句中访问参数的语法和允许值与每个访问驱动程序的外部表 DDL 相同。有关语法和允许值的更多详细信息,请参阅 Oracle 数据库实用程序。 -
If you specify the
REJECT LIMIT
, then it must either beUNLIMITED
or some valid value that is within range. Otherwise aReject limit out of range
error will be raised.
如果您指定REJECT LIMIT
,则它必须是UNLIMITED
或范围内的某个有效值。否则将引发Reject limit out of range
错误。
modify_external_table_properties
修改外部表属性
You can specify the external table properties that you want to modify at run time using this clause. The parameters that you can modify are DEFAULT DIRECTORY
, LOCATION
, ACCESS PARAMETERS (BADFILE, LOGFILE, DISCARDFILE)
and REJECT LIMIT
.
您可以使用此子句指定要在运行时修改的外部表属性。您可以修改的参数是 DEFAULT DIRECTORY
、 LOCATION
、 ACCESS PARAMETERS (BADFILE, LOGFILE, DISCARDFILE)
和 REJECT LIMIT
。
Example: Overriding External Table Parameters in a Query
示例:覆盖查询中的外部表参数
SELECT * FROM sales_external EXTERNAL MODIFY (LOCATION 'sales_9.csv’ REJECT LIMIT UNLIMITED);
flashback_query_clause 闪回查询子句
Use the flashback_query_clause
to retrieve data from a table, view, or materialized view based on time dimensions associated with the data.
使用 flashback_query_clause
根据与数据关联的时间维度从表、视图或物化视图中检索数据。
This clause implements SQL-driven Flashback, which lets you specify the following:
该子句实现 SQL 驱动的闪回,它允许您指定以下内容:
-
A different system change number or timestamp for each object in the select list, using the clauses
VERSIONS
BETWEEN
{
SCN
|
TIMESTAMP
}
orVERSIONS
AS
OF
{
SCN
|
TIMESTAMP
}
. You can also implement session-level Flashback using theDBMS_FLASHBACK
package.
使用子句VERSIONS
BETWEEN
{
SCN
|
}
或VERSIONS
AS
OF
{
SCN
TIMESTAMP
}
。您还可以使用DBMS_FLASHBACK
包实现会话级闪回。 -
A valid time period for each object in the select list, using the clauses
VERSIONS
PERIOD
FOR
orAS
OF
PERIOD
FOR
. You can also implement valid-time session-level Flashback using theDBMS_FLASHBACK_ARCHIVE
package.
选择列表中每个对象的有效时间段,使用子句VERSIONS
PERIOD
FOR
或AS
OF
PERIOD
FOR
。您还可以使用DBMS_FLASHBACK_ARCHIVE
包实现有效时间会话级闪回。
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.
闪回查询允许您检索对行所做的更改的历史记录。您可以使用 VERSIONS_XID
伪列检索进行更改的事务的相应标识符。您还可以通过发出 Oracle 闪回事务查询来检索有关导致特定行版本的事务的信息。您可以通过查询 FLASHBACK_TRANSACTION_QUERY
数据字典视图中的特定事务 ID 来完成此操作。
VERSIONS BETWEEN { SCN | TIMESTAMP }
{ SCN | 之间的版本时间戳}
Specify VERSIONS
BETWEEN
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 first specified SCN or timestamp must be earlier than the second specified SCN or timestamp. The rows returned include deleted and subsequently reinserted versions of the rows.
指定 VERSIONS
BETWEEN
以检索查询返回的行的多个版本。 Oracle 数据库返回两个 SCN 之间或两个时间戳值之间存在的行的所有已提交版本。第一个指定的 SCN 或时间戳必须早于第二个指定的 SCN 或时间戳。返回的行包括行的已删除版本和随后重新插入的版本。
-
Specify
VERSIONS
BETWEEN
SCN
... to retrieve the versions of the row that existed between two SCNs. Both expressions must evaluate to a number and cannot evaluate to NULL.MINVALUE
andMAXVALUE
resolve to the SCN of the oldest and most recent data available, respectively.
指定VERSIONS
BETWEEN
SCN
... 以检索两个 SCN 之间存在的行的版本。两个表达式的计算结果都必须为数字,并且不能计算为 NULL。MINVALUE
和MAXVALUE
分别解析为最旧和最新可用数据的 SCN。 -
Specify
VERSIONS
BETWEEN
TIMESTAMP
... to retrieve the versions of the row that existed between two timestamps. Both expressions must evaluate to a timestamp value and cannot evaluate to NULL.MINVALUE
andMAXVALUE
resolve to the timestamp of the oldest and most recent data available, respectively.
指定VERSIONS
BETWEEN
TIMESTAMP
... 以检索两个时间戳之间存在的行的版本。两个表达式的计算结果都必须为时间戳值,并且不能计算为 NULL。MINVALUE
和MAXVALUE
分别解析为最旧和最新可用数据的时间戳。
AS OF { SCN | TIMESTAMP }
截至 {SCN |时间戳}
Specify AS
OF
to retrieve the single version of the rows returned by the query at a particular change number (SCN) or timestamp. If you specify SCN
, then expr
must evaluate to a number. If you specify TIMESTAMP
, then expr
must evaluate to a timestamp value. In either case, expr
cannot evaluate to NULL. Oracle Database returns rows as they existed at the specified system change number or time.
指定 AS
OF
以检索查询在特定更改号 (SCN) 或时间戳处返回的行的单一版本。如果您指定 SCN
,则 expr
的计算结果必须为数字。如果您指定 TIMESTAMP
,则 expr
必须计算为时间戳值。无论哪种情况, expr
都不能计算为 NULL。 Oracle 数据库返回在指定的系统更改编号或时间存在的行。
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.
Oracle 数据库提供了一组版本查询伪列,可让您检索有关各个行版本的附加信息。有关详细信息,请参阅“版本查询伪列”。
When both clauses are used together, the AS
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 AS
OF
point. The database returns null for a row version if the transaction started before the first BETWEEN
value or ended after the AS
OF
point.
当两个子句一起使用时, AS
OF
子句确定数据库发出查询的 SCN 或时刻。 VERSIONS
子句确定从 AS
OF
点看到的行的版本。如果事务在第一个 BETWEEN
值之前开始或在 AS
OF
点之后结束,则数据库会为行版本返回 null。
VERSIONS PERIOD FOR 版本期限
Specify VERSIONS
PERIOD
FOR
to retrieve rows from table
based on whether they are considered valid during the specified time period. In order to use this clause, table
must support Temporal Validity.
指定 VERSIONS
PERIOD
FOR
以根据 table
中的行在指定时间段内是否被视为有效来检索这些行。为了使用此子句, table
必须支持时间有效性。
-
For
valid_time_column
, specify the name of the valid time dimension column fortable
.
对于valid_time_column
,指定table
的有效时间维度列的名称。 -
Use the
BETWEEN
clause to specify the time period during which rows are considered valid. Both expressions must evaluate to a timestamp value and cannot evaluate to NULL.MINVALUE
resolves to the earliest date or timestamp in the start time column oftable
.MAXVALUE
resolves to latest date or timestamp in the end time column oftable
.
使用BETWEEN
子句指定行被视为有效的时间段。两个表达式的计算结果都必须为时间戳值,并且不能计算为 NULL。MINVALUE
解析为table
开始时间列中的最早日期或时间戳。MAXVALUE
解析为table
结束时间列中的最新日期或时间戳。
AS OF PERIOD FOR 截至期间
Specify AS
OF
PERIOD
FOR
to retrieve rows from table
based on whether they are considered valid as of the specified time. In order to use this clause, table
must support Temporal Validity.
指定 AS
OF
PERIOD
FOR
来根据 table
中的行是否被视为有效来检索行指定时间。为了使用此子句, table
必须支持时间有效性。
-
For
valid_time_column
, specify the name of the valid time dimension column fortable
.
对于valid_time_column
,指定table
的有效时间维度列的名称。 -
Use
expr
to specify the time as of which rows are considered valid. The expression must evaluate to a timestamp value and cannot evaluate to NULL.
使用expr
指定行被视为有效的时间。该表达式的计算结果必须为时间戳值,并且不能计算为 NULL。
See Also: 也可以看看:
-
Oracle Database Development Guide for more information on Temporal Validity
Oracle 数据库开发指南 了解有关时间有效性的更多信息 -
CREATE
TABLE
period_definition to learn how to configure a table to support Temporal Validity and for information about thevalid_time_column
, start time column, and end time column
CREATE
TABLE
period_definition 了解如何配置表以支持时间有效性以及有关valid_time_column
、开始时间列和结束时间列的信息
Note on Flashback Queries
关于闪回查询的注意事项
When performing a flashback query, Oracle Database might not use query optimizations that it would use for other types of queries, which could have a negative impact on performance. In particular, this occurs when you specify multiple flashback queries in a hierarchical query.
执行闪回查询时,Oracle 数据库可能不会使用用于其他类型查询的查询优化,这可能会对性能产生负面影响。特别是,当您在分层查询中指定多个闪回查询时,会发生这种情况。
Restrictions on Flashback Queries
闪回查询的限制
These queries are subject to the following restrictions:
这些查询受到以下限制:
-
You cannot specify a column expression or a subquery in the expression of the
AS
OF
clause.
您不能在AS
OF
子句的表达式中指定列表达式或子查询。 -
You cannot specify the
AS
OF
clause if you have specified thefor_update_clause
.
如果您已指定for_update_clause
,则无法指定AS
OF
子句。 -
You cannot use the
AS
OF
clause in the defining query of a materialized view.
您不能在物化视图的定义查询中使用AS
OF
子句。 -
You cannot use the
VERSIONS
clause in flashback queries to temporary or external tables, or tables that are part of a cluster.
您不能在对临时表、外部表或属于集群的表的闪回查询中使用VERSIONS
子句。 -
You cannot use the
VERSIONS
clause in flashback queries to views. However, you can use theVERSIONS
syntax in the defining query of a view.
您不能在视图的闪回查询中使用VERSIONS
子句。但是,您可以在定义视图查询时使用VERSIONS
语法。 -
You cannot specify the
flashback_query_clause
if you have specifiedquery_name
in thequery_table_expression
.
如果您在query_table_expression
中指定了query_name
,则无法指定flashback_query_clause
。
See Also: 也可以看看:
-
Oracle Database Development Guide for more information on Oracle Flashback Query
Oracle 数据库开发指南 了解有关 Oracle 闪回查询的更多信息 -
"Using Flashback Queries: Example"
“使用闪回查询:示例” -
Oracle Database Development Guide and Oracle Database PL/SQL Packages and Types Reference for information about session-level Flashback using the
DBMS_FLASHBACK
package
Oracle 数据库开发指南和 Oracle 数据库 PL/SQL 包和类型参考,了解有关使用DBMS_FLASHBACK
包的会话级闪回的信息 -
Oracle Database Administrator's Guide and to the description of
FLASHBACK_TRANSACTION_QUERY
in the Oracle Database Reference for more information about transaction history
Oracle 数据库管理员指南以及 Oracle 数据库参考中FLASHBACK_TRANSACTION_QUERY
的描述,了解有关事务历史记录的更多信息
partition_extension_clause
分区扩展子句
For PARTITION
or SUBPARTITION
, specify the name or key value of the partition or subpartition within table
from which you want to retrieve data.
对于 PARTITION
或 SUBPARTITION
,指定 table
中要从中检索数据的分区或子分区的名称或键值。
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.
对于范围分区和列表分区数据,作为此子句的替代方案,您可以在 WHERE
子句中指定一个条件,将检索限制为 table
的一个或多个分区。 Oracle 数据库将解释条件并仅从这些分区获取数据。不可能为散列分区数据制定这样的 WHERE
条件。
See Also: 也可以看看:
"References to Partitioned Tables and Indexes" and "Selecting from a Partition: Example"
“对分区表和索引的引用”和“从分区中选择:示例”
dblink 数据库链接
For dblink
, specify the complete or partial name for a database link to a remote database where the table, view, or materialized view is located. This database need not be an Oracle Database.
对于 dblink
,指定表、视图或物化视图所在的远程数据库的数据库链接的完整或部分名称。该数据库不必是 Oracle 数据库。
See Also: 也可以看看:
-
"References to Objects in Remote Databases" for more information on referring to database links
有关引用数据库链接的更多信息,请参阅“对远程数据库中对象的引用” -
"Distributed Queries" for more information about distributed queries and "Using Distributed Queries: Example"
“分布式查询”了解有关分布式查询的更多信息和“使用分布式查询:示例”
If you omit dblink
, then the database assumes that the table, view, or materialized view is on the local database.
如果省略 dblink
,则数据库假定表、视图或物化视图位于本地数据库上。
Restrictions on Database Links
数据库链接的限制
Database links are subject to the following restrictions:
数据库链接受到以下限制:
-
You cannot query a user-defined type or an object
REF
on a remote table.
您无法查询远程表上的用户定义类型或对象REF
。 -
You cannot query columns of type
ANYTYPE
,ANYDATA
, orANYDATASET
from remote tables.
您无法从远程表查询ANYTYPE
、ANYDATA
或ANYDATASET
类型的列。
table | view | materialized_view | analytic_view | hierarchy
表|查看 |物化视图 |分析视图 |等级制度
Specify the name of a table, view, materialized view, analytic view, or hierarchy from which data is selected.
指定从中选择数据的表、视图、物化视图、分析视图或层次结构的名称。
analytic_view 分析视图
A persistent analytic view defined with the CREATE
ANALYTIC
VIEW
statement or a transitory analytic view defined in a WITH
clause.
使用 CREATE
ANALYTIC
VIEW
语句定义的持久分析视图或在 WITH
子句中定义的暂时分析视图。
See Also: 也可以看看:
Analytic Views: Examples分析视图:示例
hierarchy 等级制度
A hierarchy defined with the CREATE
HIERARCHY
statement.
使用 CREATE
HIERARCHY
语句定义的层次结构。
sample_clause 样本子句
The sample_clause
lets you instruct the database to select from a random sample of data from the table, rather than from the entire table.
sample_clause
允许您指示数据库从表中的随机数据样本中进行选择,而不是从整个表中进行选择。