选择

选择 - 从表或视图中检索行

句法

 选择   [ optimizer_hint ]
        [ ALL | DISTINCT ]
          { select_item [ [AS] output_name ] } [, ...]
           FROM from_item
         [ WHERE condition ]
         [ GROUP BY expression [, ...] [ HAVING condition [, ...] ] ]
         [ UNION [ ALL | DISTINCT ] select ]
         [ ORDER BY { order_list [ COLLATE collation_name ] [ ASC | DESC] } [, ...] ]
         [ LIMIT { count [ OFFSET start ] | start [ , count ] }
         [ OFFSET start [ ROW | ROWS ] [ FETCH {FIRST | NEXT}
           count [ROW | ROWS] [ONLY] ] ]
         [ FETCH {FIRST | NEXT } count [ROW | ROWS] [ONLY] ]
         [ FOR  更新  [NOWAIT] ]

Where select_item can be any one of the following:

* | table.*  |  view.*  |  alias.*
[[schema.]table.]column_name  |  [[schema.]view.]column_name  |  [alias.]column_name
expr
query
scalar_function
NEXT VALUE FOR seq_name

Where from_item can be any one of the following:

[schema.]object_name [ [AS] alias ] [, ...]
( select ) [ [AS] alias ] [, ...]
from_item { join_type from_item
    { ON join_condition [...] | USING (column_name [, ...]) } }
{ scalar_function | table_function }

描述

Use a 选择 statement or subquery to retrieve data from one or more tables or views. Use of 选择 for accessing sequences is discussed separately below.

An abstract version of the processing steps for a 选择 statement is as follows:

  1. The actual output rows are computed using the 选择 output expressions for each selected row (see 选择 list below).

  2. 选择 DISTINCT 从结果集中消除重复行。

  3. 选择 ALL (the default) returns all candidate rows, including duplicates (see ALL|DISTINCT under 选择 list below).

  4. All elements in the FROM 列表 are computed. (Each element in the FROM 列表 is a real, derived, or virtual table (as in DUAL), or a view.) If more than one element is specified in the FROM list, separated by commas, they are joined together (see FROM list below).

  5. If the WHERE clause is specified, all rows that do not satisfy the condition are eliminated from the output (see WHERE below).

  6. If the GROUP BY clause is specified, the output is aggregated into groups of rows that match on one or more values. If the HAVING clause is also present, NuoDB eliminates groups that do not satisfy the given condition (see GROUP BY below).

  7. The UNION operator combines the output of multiple 选择 statements to form a single result set. The UNION operator returns all rows that are in one or both of the result sets. Duplicate rows are eliminated unless UNION ALL is specified. Notice that UNION DISTINCT is the default here, as duplicate rows are eliminated, even though ALL is the default for the 选择 itself (see UNION 以下)。 NOODB使用通用类型算法(数据类型转换)在与Union分支的澳门比分网址结果中确定每个列的类型,精度和比例,并将每个分支中的列胁迫到公共类型。公共类型从分支机构从上下计算并不换向。

  8. If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce (see ORDER BY 以下)。 Use the keyword COLLATE to change the character classification behavior of the sort order.

  9. If the LIMIT, OFFSET 或者 FETCH clause is specified, the 选择 statement returns a subset of the result set (see LIMIT, FETCH, OFFSET below).

  10. The FOR UPDATE clause controls whether the transaction for the current session places a lock on the retrieved records (see FOR UPDATE below).

对事务引擎的澳门比分网址结果将被缓冲回客户端,以拟合小于100 kB。一旦客户端迭代一组获取的结果,它将返回到TE以获得更多结果。

You must have 选择 privilege on all tables and/or views to access them in a 选择 command. The use of FOR UPDATE requires 更新 privilege on the table being updated. Views cannot be updated.
使用列别名不能在where,group by或have子句中使用。例如,“选择字段为f的桌子>不支持0“。允许这样的条件可能导致模糊的名称解析,因此不允许。

参数

选择 list

The 选择 列表 (between the keywords 选择 FROM) lets you specify the columns you want to retrieve from the database. This list forms the output rows of the 选择 statement. A 选择 statement that selects rows from two or more tables and/or views is a join.

optimizer_hint

优化器提示是您告诉优化器如何执行澳门比分网址的机制。有关更多信息,请参阅 优化器提示 below.

ALL | DISTINCT

If 选择 DISTINCT is specified, all duplicate rows are removed from the result set (one row is kept from each group of duplicates). 选择 ALL specifies the opposite: all rows are kept; this is the default (see 例1 )。

* | table.* | view.* | alias.*

An asterisk, "", can be written in the output list as a shorthand for all columns of the selected rows. Also, you can write table. 或者 view.* as a shorthand for the columns coming from just that table or view. In these cases it is not possible to specify new names with AS; the output column names will be the same as the table or view column names. NuoDB returns a set of columns in the order in which the columns were specified when the table or view was created.

[[schema.]table.]column_name | [[schema.]view.]column_name | [alias.]column_name

Specifies one or more columns that exist in tables and or views in the FROM clause. A column_name in this list can be optionally qualified with a table or view name and a schema name.

ROWNUM()

这是一个Nuodb SQL系统函数,它将返回相对于其位置集中的每个行的唯一标识符。换句话说,它与行有关’s position in the result set after all joins and predicates are evaluated. Prior to any sorting or GROUP BY being applied to the result set, this function will always return a sequential unique identifier for each row. Using an ORDER BY 或者 GROUP BY clause changes this sequential ordering. See 例17..

expr

指定表示要选择的信息的表达式。这可以是函数或数学运算符的形式(参见 创建功能SQL函数和操作或表达。此列表中的列名可以选择使用表或查看名称和架构名称(参见 例2. )。 Additional examples of using user defined functions as an item in a select list can be found with the 创建功能 documentation.

query

这是一个有效的SELECT语句,在括号中,称为标量子澳门比分网址。此SELECT语句必须在周围SELECT语句中选择的每一行恰好检索一行。它只能选择一列。如果在执行期间,子澳门比分网址返回任何行,没有错误;标量结果被带到为null。子澳门比分网址可以参考来自周围澳门比分网址的变量,该变量在标量子澳门比分网址的任何一个评估中充当常量(参见示例3)。

scalar_function

This is a function that returns a single column row. The data type of the column is defined by the RETURN clause of the function definition. This can be defined as any type of function, including:

  • 用户定义的函数(UDF)(参见 创建功能)

  • NOODB定义了PSUEDO功能(参见 SQL系统信息功能)

  • NOODB SQL函数(参见 SQL函数和操作)

    AS output_name

    Just as in a table, every output column of a 选择 has a name. In a simple 选择 this name is just used to label the column for display (see 例2.), but when the 选择 is a subquery of a larger query, the name is seen by the larger query as the column name of the virtual table produced by the subquery (see 例3. )。

    To specify the name to use for an output column, write AS output_name after the column’s expression. (You can omit AS, but only if the desired output name does not match any NuoDB keyword. For protection against possible future keyword additions it is recommended that you always either write AS 或者 double-quote the output_name.)

    如果未指定列名称,则会自动选择一个名称。如果是列’S表达式是一个简单的列参考,然后选择的名称与列相同’姓名。在更复杂的情况下,可以使用函数名称(参见 SQL函数和操作)或Nuodb可能会使列标题为空白,如用子澳门比分网址的情况下(参见 例3. )。

    An output_name can be used to refer to the column’s value in the WHERE, ORDER BY, GROUP BY, and HAVING clauses;

    Restrictions on the 选择 list:

    If you specify a GROUP BY clause in a 选择 statement, then the 选择 列表 can only contain the following types of expressions:

  • 常数

  • 汇总函数

  • Expressions identical to those in the GROUP BY clause. If the GROUP BY clause is in a sub-query, then the GROUP BY columns of the sub-query must match the 选择 列表 of the outer query. Any columns in the 选择 列表 of the subquery that are not needed by the GROUP BY operation are ignored without error.

  • 涉及前面表达式的表达式,它评估到组中所有行的相同值。

    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 the names or aliases of the tables and/or views.

FROM list

The FROM clause specifies one or more data sources for the 选择 . If the 选择 statement contains no restrictions in its ON, USING, or WHERE clause, the result is the full cross product (Cartesian join) of all the sources. Typically, qualification conditions are added to restrict the returned rows to a small subset of the Cartesian join.

[schema.]object_name

现有表或视图的名称(可选地模式)。

( select)

A sub- 选择 can appear in the FROM clause. This acts as though its output were created as a temporary table for the duration of this single 选择 statement and is called a derived table. Note that the sub- 选择 must be surrounded by parentheses, and an alias must be provided for it (see 例4. )。

AS alias

A substitute name for the FROM item containing the alias. An alias is used for brevity or to eliminate ambiguity for multiple tables and/or views having the same column names and for self-joins (where the same table is scanned multiple times). All sub- 选择 statements (derived tables) in the FROM clause must be given an alias.

join_type

For the INNEROUTER join types, a join condition (ON 或者 USING) must be specified.

A JOIN clause combines two FROM items.

  • [INNER ] JOIN

    INNER JOIN returns combined records from each FROM item, matched by the condition(s) in the ON clause. This join_type is a notational convenience, since they do nothing you could not do with plain FROMWHERE (see 例5. )。

  • LEFT [OUTER] JOIN

    LEFT OUTER JOIN returns combined records from each FROM item, matched by the condition(s) in the ON clause, plus one copy of each row in the left-hand FROM item for which there was no right-hand row that passed the JOIN condition. This left-hand row is extended to the full width of the joined result set by inserting null values for the right-hand columns (see 例6. )。

    After the result set is returned from a JOIN, additional filtering of the combined result set can be done by defining conditions in a WHERE clause (see 例5. 或者 例6. )。

    ON join_condition

    An expression resulting in a value of type Boolean (similar to a WHERE clause) that specifies which rows in a join are considered to match. The join_condition in the ON clause should include only Boolean values that describe the relationship between the FROM items being joined.

    USING ( column_name [,column_name] )

    USING 是一个简写介绍,并接受一个或多个列名,由逗号分隔,加入的表必须具有通用,并形成指定这些列对中的每个列的平等的连接条件。

    The column list of the result set for a JOIN with USING is different than the column list of the result set for all other JOIN operations (see 例14. )。 This USING result set column list order is based on the following rule:

    1. Coalesced columns of the two joined tables, used in the USING clause, in the order in which they appear in the first table.

    2. 第一个表唯一的列,按顺序显示在该表中。

    3. 第二表唯一的列,按顺序显示在该表中。

      table_function | scalar function

      用户定义的函数用于选择该功能。看 创建功能。该用户定义的函数可以定义为:

  • A table_function, which is a function that returns a result set with multiple columns.

  • A scalar_function, which returns a single column row.

WHERE condition

The optional WHERE clause has several forms:

expression

An expression that evaluates to a result of type Boolean using comparison operations (see SQL函数和操作)。从输出中消除了不满足此条件的任何行。如果在实际行值替换任何可变引用时,则一行满足条件。此表达式还可以将返回值与标量函数进行比较。

{ EXISTS | NOT EXISTS } ( SELECT )

A parenthesized subquery 选择 statement is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is "true" and the result of NOT EXISTS is "false". If the subquery 选择 statement returns zero rows, the result of EXISTS is "false" and the result of NOT EXISTS is "true".

The 选择 statement can refer to items in the FROM list, regardless of whether they are also items in the 选择 列表 (see 例7. )。

expression { IN | NOT IN } ( SELECT )

The right-hand side is a parenthesized subquery 选择 statement, which must select exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result. The result of IN is "true" if any equal subquery row is found. The result is "false" if no equal row is found (including the case where the subquery returns no rows).

笔记 that if the left-hand expression yields NULL, or if there are no equal right-hand values and at least one right-hand row yields NULL, the result of the IN construct will be NULL, not false (see 例8. )。

GROUP BY expression

The optional GROUP BY condenses into a single row all selected rows that share the same values for the grouped expressions. The expression can be a column name, or the alias, or ordinal number of an item in the 选择 list.

汇总函数(如果使用),则在构成每个组的所有行上计算,为每个组产生单独的值。

Without GROUP BY, an aggregate produces a single value computed across all the rows returned in the result set. When GROUP BY is present, the items in the 选择 列表 cannot refer to ungrouped columns, except within aggregate functions. Otherwise there would be more than one possible value to return for an ungrouped column (see 例9. )。

HAVING condition

The optional HAVING eliminates grouped rows that do not satisfy the condition. HAVING is different from WHERE in that WHERE filters individual rows before the application of GROUP BY, while HAVING filters grouped rows created by GROUP BY. GROUP BY is required for using HAVING. Unless the reference appears within an aggregate function, each item referenced in condition must unambiguously reference a grouping column (see 例9. )。

UNION ALL|DISTINCT select

The select item is any valid 选择 statement.

The UNION clause is used to combine the result set returned by multiple 选择 statements into one result set. If a row is returned in the result set of at least one of the 选择 statements in the UNION, then the row is returned in the combined result set. The 选择 列表 for all 选择 statements in a UNION statement must contain the same number of columns, and the corresponding columns must be of compatible data types.

ORDER BY, LIMIT, OFFSETFETCH clauses cannot be applied separately to each 选择 statement in a UNION statement (see Note below for work-around). These clauses can only apply to the combined result set of the UNION 和 must be specified at the end of the UNION statement, after all of the 选择 statements for the UNION(see 例11. )。

It is possible to sort the result set of each individual 选择 statement of a UNION separately. This is done by replacing each 选择 statement of the UNION with a select from a derived table which is created using each 选择 statement (see 例11. )。

FOR UPDATE cannot be specified.

By default, the combined result set of a UNION will not contain duplicate rows. In other words, if a row is returned in the result set of one or more of the 选择 statement in the UNION, that row will only appear once in the combined result set. This can be explicitly written as UNION DISTINCT. Use UNION ALL to include duplicate rows in the combined result set (see 例10. )。

ORDER BY 或者 der_list

The optional ORDER BY causes the rows returned in the result set to be sorted according to the specified expressions in the ORDER BY 或者 der_list. If two rows are equal according to the left most expression, they are compared according to the next expression and so on. If they are equal according to all specified expressions, they are returned in an implementation-dependent order.

Normally, it is possible to use items in the 或者 der_list that do not appear in the 选择 list. The exception to this is a 选择 statement using a UNION for the combined result set. In this case, only items being returned in the 选择 列表 can be referred to in the ORDER BY 或者 der_list. These can be referred to by name or by the position number that the item is returned in the result set.

If an output name is assigned to an item in the 选择 list, this actual item name needs to be referred to in the ORDER BY order list, and not the output name. This is true when using any type of expression as an item in the select list, including user defined functions. The exception to this is in the use of output names used in a UNION. Since that becomes the name of the item in the combined result set, that output name needs to be used to refer to that column in the ORDER BY 或者 der_list (see 例11. )。

Use of the RAND() function in the ORDER BY clause will return the result set in a random order. 选择 statements using UNION also allow ORDER BY RAND().

COLLATE collation_type

可以定义核对类型以更改结果集的排序顺序的字符分类行为(请参阅 例12. )。 Supported collation types include: case_insensitive"8859-1U". Both collation types are synonyms for each other, so COLLATE case_insensitive will produce the same result as COLLATE "8859-1U".

ASC | DESC

One may add the keyword ASC (ascending) or DESC (descending) after any item in the 或者 der_list in the ORDER BY clause. If not specified, ASC is the default.

LIMIT, FETCH, OFFSET

count

指定要为结果集返回的最大行数。如果为零,结果集返回零行数。

start

Specifies the number of rows to skip before starting to return rows for the result set. The default value is 0. A NULL value is converted to 0.

Both countstart can be one of the following: a non-negative integer value, an expression that can be cast to a non-negative integer value, a non-negative decimal value that is cast to an integer value by rounding. Both countstart cannot be the results of a scalar subquery.

The FETCH parameter must include one of the keywords FIRST 或者 NEXT. The returned result set is the same regardless of which keyword is used.

LIMITFETCH are different parameters that achieve the same result. When either LIMIT 或者 FETCH is used with OFFSET, the number of rows specified as the OFFSET is skipped before starting the count for the number of rows to be returned. The countstart expressions cannot be NULL. The OFFSET clause must come before the FETCH clause if both are present. An offset value may be specified to LIMIT without the OFFSET keyword using the syntax start,offset.

Specifically, LIMIT supports the following three syntaxes:

  • LIMIT count

  • LIMIT start, count

  • LIMIT count OFFSET start

    例13. for examples of using LIMIT, FETCHOFFSET.

When using LIMIT 或者 FETCH, it is a good idea to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise, you will get an unpredictable subset of the query’s行。例如,您可能会询问第十四行,但第十到二十次订购?你不 ’t know what ordering unless you specify ORDER BY. This is because NuoDB performs the ORDER BY before it does the LIMIT 或者 FETCH.

It is not good practice to use an ORDER BY in a VIEW definition or a derived table. To improve performance, the NuoDB optimizer will ignore ordering of the result set returned by the VIEW 或者 derived table, unless a LIMIT 或者 OFFSET is also supplied, along with the ORDER BY. Let’s say you have defined a VIEW with an ORDER BY 和 now you want to 选择 from the VIEW 和 order the results differently. The optimizer would require an execution plan that performs two sorts. In NuoDB, this execution plan requires only one sort.
FOR UPDATE

The FOR UPDATE clause will lock those records in the associated tables that satisfy the query and have been fetched to the client. Results of queries to the TE are buffered back to the client in rows that fit into less than 100 KB. Only rows in results that have been fetched back to the client already will be locked. The FOR UPDATE clause requires that a session level transaction be in effect. See 开始交易 for how to create a session level transaction. See 支持的交易隔离级别, for an illustration of how 选择 …​FOR UPDATE behaves across the different transaction isolation levels that NuoDB supports. When the FOR UPDATE query contains a user defined function, no records in tables accessed by the function are locked. If you specify the NOWAIT option, and the record that is going to be locked is already locked by another transaction, an exception is immediately thrown instead of waiting for the other transaction to release the lock.

访问序列

To access the next value from a sequence use NEXT VALUE FOR sequence_name. 这可以是较长SELECT语句的一部分,或者只是获取下一个值的一部分:

 选择  NEXT VALUE FOR sequence_name FROM DUAL

该值始终为64位整数,但您可以像这样投射:

 选择  cast(NEXT VALUE FOR sequence_name AS SMALLINT )。 ..

NuodB中的序列可以由多个TES共享,因此当TE访问序列时,它可以获得自己的一组值。 The default size of that set (the QUANTUM SIZE) is 100. So if a client connects to the first TE to use the sequence, the result of 选择 NEXT VALUE will be 1. However, if the client connects to the second TE to use the sequence, then 选择 NEXT VALUE will return 101.

As a result the values from 选择 NEXT VALUE are guaranteed to be unique but there may be gaps in the values returned and (if a client is using multiple threads against multiple TEs) the values are not necessarily in an increasing order. For example, successive calls to 选择 NEXT VALUE might return 101, 1, 2, 102 …​

也可以看看 创建序列.

优化器提示

优化器提示是指示澳门比分网址引擎如何评估澳门比分网址计划语句的特定操作的方法。 例如,您可以基于给定列中的数据,并且应用于该列的谓词,扫描某个索引的谓词可能比另一种由优化器自动选择的索引更快。 当数据库统计量过于旧时或丢失可能是这种情况。 Optimizer hints are embedded in the SQL query, right after the 选择 keyword. 优化器提示的格式如下:

/*+ hint[,hint]…​ */

Where the following values (case-insensitive) are allowed to substitute for hint:

ORDERED
USE_INDEX ( [schema.]table [ , index_list ] )
SKIP_INDEX ( [schema.]table [ , index_list ] )

table is the name of a table or its alias, as specified in the AS alias clause of the 选择 statement. The table name may optionally be schema qualified. 如果表具有别名,那么该表上的所有索引提示都必须引用别名。

index_list 是由逗号分隔的一个或多个索引规范的列表,其中每个索引规范都有格式:

[ ID_ORDERED ] index_name
[ KEY_ORDERED ] index_name

ID_ORDEREDKEY_ORDERED designate the scan type for the index. ID_ORDERED refers to an index scan operation that is reading data in ROWID order. KEY_ORDERED 指的是索引扫描操作,该操作是读取索引密钥顺序的数据。

If you specify conflicting hints (for example, /*+ USE_INDEX(foo, foo_a), SKIP_INDEX(foo, foo_a) */), where foo is a table and foo_a is an index on a column in table foo, an error will be thrown.

如果在视图或派生表中指定优化器提示,则不会将视图或派生表与外部澳门比分网址结果合并。 例如,如果您有澳门比分网址:

 选择  * FROM ( 选择  * FROM foo WHERE foo.a = 12);

在这种情况下,我们将内部选择澳门比分网址(派生表)的计划与外部选择澳门比分网址的计划合并,然后处理生成的澳门比分网址计划。 如果内部选择澳门比分网址包含以下内容,则赢取澳门比分网址块’t be merged.

 选择  * FROM ( 选择  /*+ USE_INDEX(foo) */ * FROM foo WHERE foo.a = 12);

有或没有合并的处理澳门比分网址之间的差异是它们将具有不同的澳门比分网址计划,并且可能导致不同的运行时间。

ORDERED Hint

The ORDERED hint tells the optimizer to join tables in the order that they are specified in the FROM clause.

例子:

 选择  /*+ ORDERED */ COUNT(*)
    FROM t1, t2, t4, t3 WHERE t1.col = t2.col AND t1.col = t3.col AND t1.col = t4.col;

用 _INDEX Hint

 用 _INDEX ( [schema.]table [ , index_list ] )

笔记: 您可以使用[架构。]表,或者您可能需要使用表别名。如果您多次在同一表之间进行连接,则需要这是必需的。

The 用 _INDEX hint tells the optimizer to 使用表的索引(或一组索引)。

  • 如果提示指定单个索引,则优化程序使用该索引。 优化器不考虑表格的任何其他索引或索引。

  • 如果提示指定多个索引,则优化器根据指定的索引选择最佳索引计划。 优化器不保证将在计划中使用所有指定的索引。

  • 如果提示指定没有索引,则优化程序根据表格上的所有可用索引选择最佳索引计划。 优化器不考虑在表格上完全扫描。

  • If the hint specifies a scan type (ID_ORDERED 或者 KEY_ORDERED) for an index, then the optimizer considers that index for that type of scan only. ID_ORDERED refers to an index scan that is reading data in ROWID order. KEY_ORDERED 指在索引密钥顺序中读取数据的澳门比分网址。
    例如,如果提示指定‘KEY_ORDERED foo_a’, then the optimizer will use the foo_a index when it is doing a key ordered scan for ORDER BY 或者 GROUP BY optimization.

  • If the hint does not specify a scan type (ID_ORDERED 或者 KEY_ORDERED) for an index, then the optimizer considers that index for both key ordered and id ordered query plans,.
    例子: /*+ USE_INDEX(foo, foo_a) */

  • If the index_list specifies both ID_ORDEREDKEY_ORDERED specifications for an index, then the optimizer considers that index for both key ordered and id ordered query plans.
    例子:
    /+ USE_INDEX(foo, ID_ORDERED foo_a, KEY_ORDERED foo_a) */
    /
    + USE_INDEX(foo, ID_ORDERED foo_a), USE_INDEX(foo, KEY_ORDERED foo_a) */

In the examples above, foo is a table and foo_a is an index on a column in table foo.

例15. below.

SKIP_INDEX Hint

SKIP_INDEX ( [schema.]table [ , index_list ] )

笔记: 您可以使用[架构。]表,或者您可能需要使用表别名。 如果您多次在同一表之间进行连接,则需要这是必需的。

The SKIP_INDEX hint tells the optimizer to 不是 使用表的索引(或一组索引)。

  • 如果提示指定单个索引,则优化程序不会考虑该索引。 优化器考虑表上的另一个索引。

  • 如果提示指定多个索引,则优化程序不考虑任何指定的索引。 优化器仍然认为未指定的其他索引。

  • 如果提示指定没有索引,则优化程序不会考虑表上的任何索引。 优化器将为表选择完全扫描。

  • If the hint specifies a scan type (KEY_ORDERED 或者 ID_ORDERED) for an index, then the optimizer does not consider that index for that type of scan only. ID_ORDERED refers to an index scan that is reading data ROWID order. KEY_ORDERED 指的是索引扫描,它是读取索引密钥顺序的数据。
    例如,如果提示指定‘ID_ORDERED foo_a’, the optimizer still considers foo_a while choosing a plan in the context of ORDER BY 或者 GROUP BY optimization.

  • 如果提示未指定索引的扫描类型,则Optimizer不考虑两个ID订购的索引和键订购的澳门比分网址计划。
    例子: /*+ SKIP_INDEX(foo, foo_a) */

  • If the hint specifies both KEY_ORDEREDID_ORDERED for an index, then the optimizer does not consider that index for both id ordered and key ordered query plans.
    例子:
    /+ SKIP_INDEX(foo, KEY_ORDERED foo_a, ID_ORDERED foo_a) */
    /+ SKIP_INDEX(foo, KEY_ORDERED foo_a), SKIP_INDEX(foo, ID_ORDERED foo_a) */ )

In the examples above, foo is a table and foo_a is an index on column a in table foo.

例16. below.

NO_MERGE Hint

The NO_MERGE hint prevents the optimizer from merging derived queries or views in an OUTER JOIN.

例1

CREATE VIEW V1 AS
 选择  /*+ NO_MERGE */
FROM T2
WHERE F3 = FALSE;

 选择  F1, F2
FROM T1 LEFT OUTER JOIN ( 选择  * FROM V1) T2 ON T1.F2 = T2.F2
WHERE T1.F3 = FALSE

例2.

 选择  F1, F2
FROM T1
LEFT OUTER JOIN ( 选择  /*+ NO_MERGE */ * FROM T2 WHERE T2.F3 = FALSE) T2 ON T1.F2 = T2.F2
WHERE T1.F3 = FALSE;

选择的示例

例1: Use of 选择 ALL compared with 选择 DISTINCT
 选择  ALL playerid,year,stint FROM scoring
  WHERE year = 2009 AND playerid LIKE 'boy%' ORDER BY playerid;
PLAYERID  YEAR  STINT
 --------- ----- ------
 boychjo01 2009    1
 boychza01 2009    1
 boyddu01  2009    1
 boyddu01  2009    2
 boyesbr01 2009    1
 boylebr01 2009    1
 boyleda01 2009    1
 boyntni01 2009    1
 boyntni01 2009    2

 选择  DISTINCT playerid,year FROM scoring
  WHERE year = 2009 AND playerid LIKE 'boy%' ORDER BY playerid;
 PLAYERID  YEAR
 --------- -----
 boychjo01 2009
 boychza01 2009
 boyddu01  2009
 boyesbr01 2009
 boylebr01 2009
 boyleda01 2009
 boyntni01 2009
例2.: 选择 using functions, operators and expressions and the use of output display names
 选择  cast(birthmon||'/'||birthday||'/'||birthyear as date) AS BIRTH_DATE,
       CASE birthcountry
           WHEN 'USA' THEN 'United States'
           ELSE 'Other'
       END AS BIRTH_COUNTRY,
       LASTNHL-FIRSTNHL AS YEARS,
       UPPER(FIRSTNAME), UPPER(LASTNAME), WEIGHT
   FROM players WHERE birthday > 0 AND weight > 250;

 BIRTH_DATE  BIRTH_COUNTRY  YEARS  FIRSTNAME  LASTNAME  WEIGHT
 ----------- -------------- ------ ---------- --------- -------
 1983-04-04  Other            4     EVGENY    ARTYUKHIN   254
 1982-06-23  Other            5     DEREK     BOOGAARD    254
 1985-03-27  United States    6     DUSTIN    BYFUGLIEN   265
 1977-03-18  Other            14    ZDENO     CHARA       255
 1967-10-16  Other            1     GERRY     FLEMING     253
 1978-12-28  Other            1     KYLE      FREADRICH   260
 1979-01-01  Other            6     RAITIS    IVANANS     263
 1981-05-21  Other            3     KRISTIAN  KUDROC      255
 1980-08-08  Other            3     STEVE     MACINTYRE   265
 1973-08-21  Other            7     STEVE     MCKENNA     252
 1982-09-26  Other            3     JOHN      SCOTT       258
例3.: 选择 expression using a scalar subquery as an expression in the select list and using output display names as compared with no output display name
 选择  s.PLAYERID, s.year, s.stint, s.teamid,
       ( 选择  name FROM teams t WHERE t.teamid = s.teamid  t.year = s.year)
  FROM scoring s
  WHERE s.YEAR = 2011 AND s.teamid like 'B%';

 PLAYERID  YEAR  STINT  TEAMID
 --------- ----- ------ ------- --------------
 adamlu01  2011    1      BUF   Buffalo Sabres
 bartkma01 2011    1      BOS   Boston Bruins
 bergepa01 2011    1      BOS   Boston Bruins
 boychjo01 2011    1      BOS   Boston Bruins
 boyesbr01 2011    1      BUF   Buffalo Sabres
 brenntj01 2011    1      BUF   Buffalo Sabres
...

 选择  s.PLAYERID, s.year, s.stint, s.teamid,
       ( 选择  name FROM teams t WHERE t.teamid = s.teamid  t.year = s.year) AS TEAM
  FROM scoring s
  WHERE s.YEAR = 2011 AND s.teamid like 'B%';

PLAYERID  YEAR  STINT  TEAMID       TEAM
 --------- ----- ------ ------- --------------
 adamlu01  2011    1      BUF   Buffalo Sabres
 bartkma01 2011    1      BOS   Boston Bruins
 bergepa01 2011    1      BOS   Boston Bruins
 boychjo01 2011    1      BOS   Boston Bruins
...
例4.: 选择 statement using a subquery in the FROM list
 选择  a.team_year, a.team_teamid, a.team_conf, a.team_div
  FROM ( 选择  t.year AS team_year,t.teamid AS team_teamid, t.rank AS team_rank ,
                t.conferenceid AS team_conf, t.divisionid AS team_div
          FROM teams t) a
 WHERE a.team_rank = 1 AND a.team_year = 2011;

 TEAM_YEAR  TEAM_TEAMID  TEAM_CONF  TEAM_DIV
 ---------- ------------ ---------- ---------
    2011        BOS          EC        NE
    2011        FLO          EC        SE
    2011        NYR          EC        AT
    2011        PHO          WC        PC
    2011        STL          WC        CE
    2011        VAN          WC        NW
例5.: 选择 statement using INNER JOIN 和 a WHERE clause to filter the combined result set
 选择  s.playerid, s.teamid, t.name, t.conferenceid, t.divisionid, t.rank
FROM scoring s
  INNER JOIN teams t ON s.year = t.year AND s.teamid = t.teamid  t.rank =1
WHERE s.position = 'G' AND t.year = 2011;

 PLAYERID  TEAMID        NAME        CONFERENCEID  DIVISIONID  RANK
 --------- ------- ----------------- ------------- ----------- -----
 bironma01   NYR   New York Rangers       EC           AT        1
 clemmsc01   FLO   Florida Panthers       EC           SE        1
 elliobr01   STL   St. Louis Blues        WC           CE        1
 fostebr01   FLO   Florida Panthers       EC           SE        1
 halakja01   STL   St. Louis Blues        WC           CE        1
...
例6.: 选择 statement using LEFT OUTER JOIN 和 a WHERE clause to filter and order the combined result set
 选择  p.firstname,p.lastname,p.firstnhl,p.lastnhl,s.teamid,s.stint,gamesplayed
FROM players p
  LEFT OUTER JOIN scoring s ON p.playerid = s.playerid
                           AND p.firstnhl = s.year
                           AND s.position = 'G'
WHERE p.firstnhl = 2011 AND s.gamesplayed IS NOT NULL ORDER BY LASTNAME,FIRSTNAME,TEAMID;

 FIRSTNAME  LASTNAME  FIRSTNHL  LASTNHL  TEAMID  STINT  GAMESPLAYED
 ---------- --------- --------- -------- ------- ------ ------------
   Brian    Foster      2011      2011     FLO     1          1
   Matt     Hackett     2011      2011     MIN     1         12
   Shawn    Hunwick     2011      2011     CBS     1          1
   Leland   Irving      2011      2011     CAL     1          7
   Mike     Murphy      2011      2011     CAR     1          2
   Anders   Nilsson     2011      2011     NYI     1          4
   Jussi    Rynnas      2011      2011     TOR     1          2
   Ben      Scrivens    2011      2011     TOR     1         12
   Iiro     Tarkki      2011      2011     AND     1          1
   Brad     Thiessen    2011      2011     PIT     1          5
   Allen    York        2011      2011     CBS     1         11
例7.: 选择 statement using EXISTS (SELECT…​) to filter result set based on a subquery
 选择  p.playerid, p.firstname, p.lastname FROM players p
 WHERE EXISTS ( 选择  'x' FROM scoring s WHERE s.playerid = p.playerid
                                 AND s.position = 'G' AND s.year = 2011);
PLAYERID    FIRSTNAME         LASTNAME
 --------- -------------- ------------------
 andercr01 Craig          Anderson
 auldal01  Alex           Auld
 bachmri01 Richard        Bachman
 backsni01 Niklas         Backstrom
 bernijo01 Jonathan       Bernier
 bironma01 Martin         Biron
 bishobe01 Ben            Bishop
 bobrose01 Sergei         Bobrovsky
...
例8.: 选择 statement using IN to filter result set based on a subquery that returns a single column
 选择  p.playerid, p.firstname, p.lastname FROM players p
 WHERE p.playerid IN ( 选择  s.playerid FROM scoring s
                         WHERE s.position = 'G' AND s.year = 2011);
 PLAYERID    FIRSTNAME         LASTNAME
 --------- -------------- ------------------
 andercr01 Craig          Anderson
 auldal01  Alex           Auld
 bachmri01 Richard        Bachman
 backsni01 Niklas         Backstrom
 bernijo01 Jonathan       Bernier
 bironma01 Martin         Biron
 bishobe01 Ben            Bishop
 bobrose01 Sergei         Bobrovsky
...
例9.: 选择 statement using a GROUP BY 和 an optional HAVING
 选择  s.year, s.teamid, count(*) AS NUMBER_OF_PLAYERS
   FROM scoring s
  WHERE s.year = 2011
  GROUP BY s.year, s.teamid;
 YEAR  TEAMID  NUMBER_OF_PLAYERS
 ----- ------- ------------------
 2011    MTL           34
 2011    CAL           40
 2011    CBS           42
 2011    PHO           35
 2011    NYI           36
...

 选择  s.year, s.teamid, count(*) AS NUMBER_OF_PLAYERS
   FROM scoring s
  WHERE s.year = 2011
  GROUP BY s.year, s.teamid HAVING count(*) > 40;
 YEAR  TEAMID  NUMBER_OF_PLAYERS
 ----- ------- ------------------
 2011    CBS           42
 2011    MIN           47
 2011    FLO           41
例10.: 选择 statement showing UNION ALL compared with using UNION DISTINCT

The player with playerid = macinst01 is included in the result set of both 选择 statements of the UNION. Therefore, the UNION ALL statement returns this player twice, once for each 选择 statement. The UNION DISTINCT eliminates duplicate rows from the combined result set.

 选择  playerid, firstname, lastname, height, weight
  FROM players
  WHERE playerid = 'macinst01'
UNION ALL
 选择  playerid, firstname, lastname, height, weight
  FROM players
  WHERE weight > 260;

 PLAYERID  FIRSTNAME  LASTNAME  HEIGHT  WEIGHT
 --------- ---------- --------- ------- -------

 macinst01   Steve    MacIntyre   78      265
 byfugdu01   Dustin   Byfuglien   75      265
 ivanara01   Raitis   Ivanans     75      263
 macinst01   Steve    MacIntyre   78      265


 选择  playerid, firstname, lastname, height, weight
  FROM players
  WHERE playerid = 'macinst01'
UNION DISTINCT
 选择  playerid, firstname, lastname, height, weight
  FROM players
  WHERE weight > 260;

 PLAYERID  FIRSTNAME  LASTNAME  HEIGHT  WEIGHT
 --------- ---------- --------- ------- -------

 macinst01   Steve    MacIntyre   78      265
 byfugdu01   Dustin   Byfuglien   75      265
 ivanara01   Raitis   Ivanans     75      263
例11.: 选择 statement using ORDER BY

Ordering the result set of a 选择 statement.

 选择  playerid, firstname, lastname, height, weight
  FROM players
  WHERE weight > 260
 ORDER BY lastname;

 PLAYERID  FIRSTNAME  LASTNAME  HEIGHT  WEIGHT
 --------- ---------- --------- ------- -------

 byfugdu01   Dustin   Byfuglien   75      265
 ivanara01   Raitis   Ivanans     75      263
 macinst01   Steve    MacIntyre   78      265

Ordering the combined result set of a UNION query.

 选择  playerid, firstname, lastname AS lname, height, weight
     FROM players
    WHERE playerid like 'zo%'
UNION ALL
    选择  playerid, firstname, lastname AS lname, height, weight
     FROM players
    WHERE playerid like 'un%'
ORDER BY firstname;

 PLAYERID  FIRSTNAME     LNAME    HEIGHT  WEIGHT
 --------- ---------- ----------- ------- -------

 ungerga01   Garry    Unger         71      170
 zolniha01   Harry    Zolnierczyk   71      160
 zoborma01   Marty    Zoborosky     70      180
 underma01   Matt     Underhill     74      195
 zombori01   Rick     Zombo         73      202

The result set of each individual 选择 statement in a UNION can be sorted independently. In this case, each 选择 statement selects from a derived table that is created from each individual 选择 statement.

 选择  * FROM
  ( 选择  playerid, firstname, lastname AS lname, height, weight
     FROM players
    WHERE playerid like 'zo%'
    ORDER BY firstname) a
UNION
 选择  * FROM
  ( 选择  playerid, firstname, lastname AS lname, height, weight
     FROM players
    WHERE playerid like 'un%'
    ORDER BY firstname) b;

 PLAYERID  FIRSTNAME     LNAME    HEIGHT  WEIGHT
 --------- ---------- ----------- ------- -------

 zolniha01   Harry    Zolnierczyk   71      160
 zoborma01   Marty    Zoborosky     70      180
 zombori01   Rick     Zombo         73      202
 ungerga01   Garry    Unger         71      170
 underma01   Matt     Underhill     74      195
例12.: Defining a collation type as an ORDER BY option
DROP TABLE tst_table IF EXISTS;
CREATE TABLE tst_table (column1 VARCHAR(12));
 INTO tst_table VALUES ('abc'),('ABC');
 INTO tst_table VALUES ('bcd'),('BCD');
 选择  * FROM tst_table ORDER BY column1;
 COLUMN1
 --------
   ABC
   BCD
   abc
   bcd

  选择  * FROM tst_table ORDER BY column1 COLLATE case_insensitive;
 COLUMN1
 --------
   abc
   ABC
   bcd
   BCD

 选择  * FROM tst_table ORDER BY column1 COLLATE case_insensitive DESC;
 COLUMN1
 --------
   bcd
   BCD
   abc
   ABC
例13.: 选择 statement using LIMIT, FETCH, OFFSET
 选择 
        name AS teamname,
        conferenceid,
        divisionid,
        rank,
        wins||'-'||losses||'-'||ties AS RECORD
  FROM teams
  WHERE year = 2009
  ORDER BY rank, conferenceid, divisionid LIMIT 10;
      TEAMNAME       CONFERENCEID  DIVISIONID  RANK  RECORD
 ------------------- ------------- ----------- ----- -------
 New Jersey Devils        EC           AT        1   48-27-0
 Buffalo Sabres           EC           NE        1   45-27-0
 Washington Capitals      EC           SE        1   54-15-0
 Chicago Blackhawks       WC           CE        1   52-22-0
 Vancouver Canucks        WC           NW        1   49-28-0
 San Jose Sharks          WC           PC        1   51-20-0
 Pittsburgh Penguins      EC           AT        2   47-28-0
 Ottawa Senators          EC           NE        2   44-32-0
 Atlanta Thrashers        EC           SE        2   35-34-0
 Detroit Red Wings        WC           CE        2   44-24-0

 选择 
        name AS teamname,
        conferenceid,
        divisionid,
        rank,
        wins||'-'||losses||'-'||ties AS RECORD
  FROM teams
  WHERE year = 2009
  ORDER BY rank, conferenceid, divisionid FETCH FIRST 3;
      TEAMNAME       CONFERENCEID  DIVISIONID  RANK  RECORD
 ------------------- ------------- ----------- ----- -------
 New Jersey Devils        EC           AT        1   48-27-0
 Buffalo Sabres           EC           NE        1   45-27-0
 Washington Capitals      EC           SE        1   54-15-0

 选择 
        name AS teamname,
        conferenceid,
        divisionid,
        rank,
        wins||'-'||losses||'-'||ties AS RECORD
  FROM teams
  WHERE year = 2009
  ORDER BY rank, conferenceid, divisionid LIMIT 10 OFFSET 2;
      TEAMNAME       CONFERENCEID  DIVISIONID  RANK  RECORD
 ------------------- ------------- ----------- ----- -------
 Washington Capitals      EC           SE        1   54-15-0
 Chicago Blackhawks       WC           CE        1   52-22-0
 Vancouver Canucks        WC           NW        1   49-28-0
 San Jose Sharks          WC           PC        1   51-20-0
 Pittsburgh Penguins      EC           AT        2   47-28-0
 Ottawa Senators          EC           NE        2   44-32-0
 Atlanta Thrashers        EC           SE        2   35-34-0
 Detroit Red Wings        WC           CE        2   44-24-0
 Colorado Avalanche       WC           NW        2   43-30-0
 Phoenix Coyotes          WC           PC        2   50-25-0

 选择 
        name AS teamname,
        conferenceid,
        divisionid,
        rank,
        wins||'-'||losses||'-'||ties AS RECORD
  FROM teams
  WHERE year = 2009
  ORDER BY rank, conferenceid, divisionid LIMIT 2,10;
      TEAMNAME       CONFERENCEID  DIVISIONID  RANK  RECORD
 ------------------- ------------- ----------- ----- -------
 Washington Capitals      EC           SE        1   54-15-0
 Chicago Blackhawks       WC           CE        1   52-22-0
 Vancouver Canucks        WC           NW        1   49-28-0
 San Jose Sharks          WC           PC        1   51-20-0
 Pittsburgh Penguins      EC           AT        2   47-28-0
 Ottawa Senators          EC           NE        2   44-32-0
 Atlanta Thrashers        EC           SE        2   35-34-0
 Detroit Red Wings        WC           CE        2   44-24-0
 Colorado Avalanche       WC           NW        2   43-30-0
 Phoenix Coyotes          WC           PC        2   50-25-0

 选择 
        name AS teamname,
        conferenceid,
        divisionid,
        rank,
        wins||'-'||losses||'-'||ties AS RECORD
  FROM teams
  WHERE year = 2009
  ORDER BY rank, conferenceid, divisionid OFFSET 2 FETCH NEXT 4;
      TEAMNAME       CONFERENCEID  DIVISIONID  RANK  RECORD
 ------------------- ------------- ----------- ----- -------
 Washington Capitals      EC           SE        1   54-15-0
 Chicago Blackhawks       WC           CE        1   52-22-0
 Vancouver Canucks        WC           NW        1   49-28-0
 San Jose Sharks          WC           PC        1   51-20-0
例14.: Result set column order for a USING join

使用星号(“*”) for the select column list, the result set returns all columns in both tables (or views, derived tables, etc.) in the order they appear in the table, returning columns from the first table and then the second table. When joined with USING the result set column list is different. To reduce the number of columns in the result set, this example will use views created from tables that are generated by the QuickStart demo.

/* Create the views to be used for this example */
CREATE VIEW vw_players AS  选择  playerid,firstname,lastname FROM players;
CREATE VIEW vw_scoring AS  选择  playerid,year,stint,teamid,position FROM scoring;

/* SELECT using JOIN of the above two views. The first three columns are from */
   vw_players, the next five columns are from vw_scoring */
 选择  * FROM vw_players p JOIN vw_scoring s ON p.playerid = s.playerid
 WHERE s.position = 'G' AND s.year = 1975 ORDER BY p.playerid;
 PLAYERID  FIRSTNAME   LASTNAME   PLAYERID  YEAR  STINT  TEAMID  POSITION
 --------- ---------- ----------- --------- ----- ------ ------- ---------
 abrahch01 Christer   Abrahamsson abrahch01 1975    1      NEW       G
 aubryse01 Serge      Aubry       aubryse01 1975    1      CIN       G
 belanyv01 Yves       Belanger    belanyv01 1975    1      STL       G
 belhumi01 Michel     Belhumeur   belhumi01 1975    1      WAS       G
...

/* Now JOIN with USING on common column playerid. The result set column list is
   reduced by one column, the playerid, which is the column specified as USING. USING
   columns appear only once in the column list of the result set. The remaining columns
   are ordered the same as they appear in the tables. The columns from the first table
   listed in the JOIN appear first. */
 选择  * FROM vw_players p JOIN vw_scoring s USING (playerid)
 WHERE s.position = 'G' AND s.year = 1975 ORDER BY p.playerid;
 PLAYERID  FIRSTNAME   LASTNAME   YEAR  STINT  TEAMID  POSITION
 --------- ---------- ----------- ----- ------ ------- ---------
 abrahch01 Christer   Abrahamsson 1975    1      NEW       G
 aubryse01 Serge      Aubry       1975    1      CIN       G
 belanyv01 Yves       Belanger    1975    1      STL       G
 belhumi01 Michel     Belhumeur   1975    1      WAS       G
...
例15.: 用 _INDEX Optimizer Hint
CREATE TABLE foo (a INTEGER, b INTEGER, c INTEGER, d INTEGER);

CREATE INDEX foo_a ON foo(a);
CREATE INDEX foo_b ON foo(b);
CREATE INDEX foo_c ON foo(c);
CREATE INDEX foo_d ON foo(d);

-- Choose a specified index; the optimizer has the freedom to choose
-- a streamed / bitmap scan over the specified index.
 选择  /*+ USE_INDEX(foo, foo_a) */ *
    FROM foo WHERE a > 100 ORDER BY a;

-- Choose a streamed index
 选择  /*+ USE_INDEX(foo, KEY_ORDERED foo_a) */ *
    FROM foo WHERE a > 100 ORDER BY a;

-- Choose a bitmap index
 选择  /*+ USE_INDEX(foo, ID_ORDERED foo_a) */ *
    FROM foo WHERE a > 100 ORDER BY a;

-- Choose a streamed index
 选择  /*+ USE_INDEX(foo, KEY_ORDERED foo_a) */ *
    FROM foo WHERE a > 100 AND b > 100 ORDER BY a;

-- Choose a bitmap index
 选择  /*+ USE_INDEX (foo, ID_ORDERED foo_b) */ *
    FROM foo WHERE a > 100 AND b > 100 ORDER BY a;

-- Consider: streamed foo_a, bitmap foo_b
 选择  /*+ USE_INDEX(foo, KEY_ORDERED foo_a, ID_ORDERED foo_b) */ *
    FROM foo WHERE a > 100 AND b > 100 ORDER BY a;

-- Consider only streamed scans over foo_a and foo_b
 选择  /*+ USE_INDEX(foo, KEY_ORDERED foo_a, KEY_ORDERED foo_b) */ *
    FROM foo WHERE a > 100 AND b > 100 ORDER BY a;

-- Consider: bitmap foo_a, bitmap foo_b, BitmapAND(bitmap foo_a, bitmap foo_b)
 选择  /*+ USE_INDEX(foo, ID_ORDERED foo_a, ID_ORDERED foo_b) */ *
    FROM foo WHERE a > 100 AND b > 100 ORDER BY a;

-- Let the optimizer choose an optimal IIU plan over foo_a and foo_b
 选择  /*+ USE_INDEX(foo, foo_a, foo_b) */ *
    FROM foo WHERE a > 100 AND b > 100 ORDER BY a;

-- Use a streamed index (even though there is no group by/ORDER BY)
 选择  /*+ USE_INDEX(foo, KEY_ORDERED foo_a) */ *
    FROM foo WHERE a > 100 AND b > 100;

-- In the context of joins
CREATE TABLE bar (a INTEGER, b INTEGER, c INTEGER, d INTEGER);

 选择  /*+ USE_INDEX(foo, foo_a) */ *
    FROM foo, bar WHERE foo.a = bar.b AND foo.a > 100 AND bar.b > 100;

-- In the context of joins (with indexes ON both tables)
CREATE INDEX bar_b ON bar(b);

 选择  /*+ USE_INDEX(foo, foo_a), USE_INDEX(bar, bar_b) */ *
    FROM foo, bar WHERE foo.a = bar.b AND foo.a > 100 AND bar.b > 100;

-- With joins and a group by (specify both ordered and index hints)
 选择  /*+ ordered, USE_INDEX(foo, KEY_ORDERED foo_a) */ foo.a
    FROM foo, bar WHERE foo.a = bar.b AND foo.a > 100 AND bar.b > 100
    GROUP BY foo.a;

-- With joins and an ORDER BY (specify both ordered and index hints)
 选择  /*+ ordered, USE_INDEX (foo, KEY_ORDERED foo_a) */ *
    FROM foo, bar WHERE foo.a = bar.b AND foo.a > 100 AND bar.b > 100
    ORDER BY foo.a;
例16.: SKIP_INDEX Optimizer Hint
CREATE TABLE foo (a INTEGER, b INTEGER, c INTEGER, d INTEGER);

CREATE INDEX foo_a ON foo(a);
CREATE INDEX foo_b ON foo(b);
CREATE INDEX foo_c ON foo(c);
CREATE INDEX foo_d ON foo(d);

-- Do not choose index 'foo_a'
 选择  /*+ SKIP_INDEX(foo, foo_a) */ *
    FROM foo WHERE a > 100 ORDER BY a;

-- Do not choose a streamed scan over foo_a; the optimizer can choose a
-- bitmap scan over foo_a.
 选择  /*+ SKIP_INDEX(foo, KEY_ORDERED foo_a) */ *
    FROM foo WHERE a > 100 ORDER BY a;

-- Do not choose a bitmap scan over foo_a; the optimizer can choose a
-- streamed scan over foo_a
 选择  /*+ SKIP_INDEX(foo, ID_ORDERED foo_a) */ *
    FROM foo WHERE a > 100 ORDER BY a;

-- Equivalent to /*+ SKIP_INDEX(foo foo_a) */
 选择  /*+ SKIP_INDEX(foo, KEY_ORDERED foo_a, ID_ORDERED foo_a) */ *
    FROM foo WHERE a > 100 ORDER BY a;

-- Do not consider streamed scans over foo_a and foo_b
 选择  /*+ SKIP_INDEX(foo, KEY_ORDERED foo_a, ID_ORDERED foo_b) */ *
    FROM foo WHERE a > 100 AND b > 100 ORDER BY a;

-- In the context of joins
 选择  /*+ SKIP_INDEX(foo, foo_a) */ *
    FROM foo, bar WHERE foo.a = bar.b AND foo.a > 100 AND bar.b > 100;

-- In the context of joins; do not use an index only ON the specified instance of foo
 选择  /*+ SKIP_INDEX(foo_inst_a, foo_a)  */ *
    FROM foo as foo_inst_a, foo as foo_inst_b
    WHERE foo_inst_a.a = foo_inst_b.a AND foo_inst_a.a > 50 AND foo_inst_b.a < 100;

-- In the context of joins
 选择  /*+ ordered, SKIP_INDEX(foo, KEY_ORDERED foo_a) */ foo.a
    FROM foo, bar WHERE foo.a = bar.b AND foo.a > 100 AND bar.b > 100
    GROUP BY foo.a;

-- In the context of joins
 选择  /*+ ordered, SKIP_INDEX(foo, foo_a) */ *
    FROM foo, bar WHERE foo.a = bar.b AND foo.a > 100 AND bar.b > 100
    ORDER BY foo.a;
例17:rownum()`

With the exception of an ORDER BY 或者 GROUP BY clause, this unique identifier returned by ROWNUM() will always be sequential.

DROP TABLE IF EXISTS hockey_dup;
CREATE TABLE hockey_dup (
    id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
    number INTEGER,
    name STRING,
    position STRING );
 INTO hockey_dup (number,name,position)
     选择  number,name,position
    FROM hockey WHERE name like 'A%'  或者  name like 'D%';
 选择  id,number,name,position,rownum() FROM hockey_dup;
 ID  NUMBER        NAME        POSITION  ROWNUM
 --- ------- ----------------- --------- -------

  1    46    DAVID KREJCI       Forward     1
  2    20    DANIEL PAILLE      Forward     2
  3    21    ANDREW FERENCE     Defense     3
  4    27    DOUGIE HAMILTON    Defense     4
  5    45    AARON JOHNSON      Defense     5
  6    54    ADAM MCQUAID       Defense     6
  7    44    DENNIS SEIDENBERG  Defense     7
  8    35    ANTON KHUDOBIN     Goalie      8

/* delete a row which will reset what the ROWNUM() function returns */
 删除  FROM hockey_dup WHERE id = 6;
 选择  id,number,name,position,rownum() FROM hockey_dup;
 ID  NUMBER        NAME        POSITION  ROWNUM
 --- ------- ----------------- --------- -------

  1    46    DAVID KREJCI       Forward     1
  2    20    DANIEL PAILLE      Forward     2
  3    21    ANDREW FERENCE     Defense     3
  4    27    DOUGIE HAMILTON    Defense     4
  5    45    AARON JOHNSON      Defense     5
  7    44    DENNIS SEIDENBERG  Defense     6
  8    35    ANTON KHUDOBIN     Goalie      7

/* add a predicate that will change the position of some rows in the result set */
 选择  id,number,name,position,record_number,rownum()
    FROM hockey_dup WHERE name LIKE 'D%';

 ID  NUMBER        NAME        POSITION  RECORD_NUMBER  ROWNUM
 --- ------- ----------------- --------- -------------- -------

  1    46    DAVID KREJCI       Forward        1           1
  2    20    DANIEL PAILLE      Forward        2           2
  4    27    DOUGIE HAMILTON    Defense        4           3
  7    44    DENNIS SEIDENBERG  Defense        7           4

 选择  id,number,name,position,rownum()
    FROM hockey_dup WHERE name LIKE 'D%';
 ID  NUMBER        NAME        POSITION  ROWNUM
 --- ------- ----------------- --------- -------

  1    46    DAVID KREJCI       Forward     1
  2    20    DANIEL PAILLE      Forward     2
  4    27    DOUGIE HAMILTON    Defense     3
  7    44    DENNIS SEIDENBERG  Defense     4

/* since the rownum() function returns its identifier before any ORDER BY,
   the rownum() value is not always sequential */

 选择  id, number,name,position,rownum()
    FROM hockey_dup WHERE name LIKE 'D%' ORDER BY name;
 ID  NUMBER        NAME        POSITION  ROWNUM
 --- ------- ----------------- --------- -------

  2    20    DANIEL PAILLE      Forward     2
  1    46    DAVID KREJCI       Forward     1
  7    44    DENNIS SEIDENBERG  Defense     4
  4    27    DOUGIE HAMILTON    Defense     3