SELECT

SELECT — retrieve rows from a table or view

Syntax

SELECT  [ 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 UPDATE [ NOWAIT | SKIP LOCKED | WAIT <lock_timeout_in_sec> ] ]

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 }

Description

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

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

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

  2. SELECT DISTINCT eliminates duplicate rows from the result set.

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

  4. All elements in the FROM list are computed. (Each element in the FROM list 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 SELECT 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 SELECT itself (see UNION below). NuoDB uses a common type algorithm (Data Type Conversion) to decide the type, precision and scale of each column in the result of a query with UNION branches and coerces the columns in each branch to the common type. The common type is computed from the top down through the branches and is not commutative.

  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 below). Use the keyword COLLATE to change the character classification behavior of the sort order.

  9. If the LIMIT, OFFSET or FETCH clause is specified, the SELECT 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).

Results of queries to the Transaction Engine (TE) will be buffered back to the client in rows that fit into less than 100 KB. Once the client iterates through a set of fetched results, it will go back to the TE to get more results.

You must have SELECT privilege on all tables and/or views to access them in a SELECT command. The use of FOR UPDATE requires UPDATE privilege on the table being updated. Views cannot be updated.
The use of a column alias cannot be used in the WHERE, GROUP BY, or HAVING clause. For example, "SELECT field as F FROM table WHERE F > 0" is not supported. Allowing such a condition can result in ambiguous name resolution and therefore is not allowed.

Parameters

SELECT list

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

optimizer_hint

An optimizer hint is a mechanism for you to tell the optimizer how to execute the query. For more information see Optimizer Hints below.

ALL | DISTINCT

If SELECT DISTINCT is specified, all duplicate rows are removed from the result set (one row is kept from each group of duplicates). SELECT ALL specifies the opposite: all rows are kept; this is the default (see Example 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.* or 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()

This is a NuoDB SQL system function that will return a unique identifier for each row relative to its position in the result set. In other words, it is related to the row’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 or GROUP BY clause changes this sequential ordering. See Example 17. For more details and limitations, see ROWNUM() function.

expr

Specify an expression representing the information you want to select. This can be in the form of a function or mathematical operator (see CREATE FUNCTION and SQL Functions and Operations) or an expression. A column name in this list can be optionally qualified with a table or view name and a schema name (see Example 2). Additional examples of using user defined functions as an item in a select list can be found with the CREATE FUNCTION documentation.

query

This is a valid SELECT statement, in parentheses, called a scalar subquery. This SELECT statement MUST retrieve exactly one row per each row selected in the surrounding SELECT statement. It can only select one column. If during execution, the subquery returns no rows, there is no error; the scalar result is taken to be NULL. The subquery can refer to variables from the surrounding query, which act as constants during any one evaluation of the scalar subquery (see Example 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:

  • A User Defined function (UDF) (see CREATE FUNCTION)

  • A NuoDB defined psuedo function (see SQL System Information Functions)

  • A NuoDB SQL function (see SQL Functions and Operations)

    AS output_name

    Just as in a table, every output column of a SELECT has a name. In a simple SELECT this name is just used to label the column for display (see Example 2), but when the SELECT 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 Example 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 or double-quote the output_name.)

    If you do not specify a column name, a name is chosen automatically. If the column’s expression is a simple column reference then the chosen name is the same as the column’s name. In more complex cases, the function name may be used (see SQL Functions and Operations), or NuoDB may leave the column heading blank, as in the case with a subquery (see Example 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 SELECT list:

    If you specify a GROUP BY clause in a SELECT statement, then the SELECT list can only contain the following types of expressions:

  • Constants

  • Aggregate functions

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

  • Expressions involving the preceding expressions that evaluate to the same value of all rows in a group.

    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 SELECT. If the SELECT 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

The name (optionally schema-qualified) of an existing table or view.

( select)

A sub-SELECT can appear in the FROM clause. This acts as though its output were created as a temporary table for the duration of this single SELECT statement and is called a derived table. Note that the sub-SELECT must be surrounded by parentheses, and an alias must be provided for it (see Example 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-SELECT statements (derived tables) in the FROM clause must be given an alias.

join_type

For the INNER and OUTER join types, a join condition (ON or 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 FROM and WHERE (see Example 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 Example 6).

  • RIGHT [OUTER] JOIN

    RIGHT OUTER JOIN returns combined records from each table specified in the FROM clause, matched by the condition(s) in the ON clause. Additionally, it includes a copy of all the rows from the right-hand FROM item for which there was no left-hand row that passed the JOIN condition. This right-hand row is extended to the full width of the joined result set by inserting null values for the left-hand columns.

    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 is a shorthand notation and accepts one or more column names, separated by a comma, which the joined tables must have in common, and forms a join condition specifying equality of each of these pairs of columns.

    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 Example 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. Columns unique to the first table, in the order in which they appear in that table.

    3. Columns unique to the second table, in the order in which they appear in that table.

      table_function | scalar function

      A user defined function an be used to select from. See CREATE FUNCTION. This user defined function can be defined as:

  • 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 Functions and Operations). Any row that does not satisfy this condition is eliminated from the output. A row satisfies the condition if it returns true when the actual row values are substituted for any variable reference. This expression can also compare the returned value from a scalar function.

{ EXISTS | NOT EXISTS } ( SELECT )

A parenthesized subquery SELECT 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 SELECT statement returns zero rows, the result of EXISTS is "false" and the result of NOT EXISTS is "true".

The SELECT statement can refer to items in the FROM list, regardless of whether they are also items in the SELECT list (see Example 7).

expression { IN | NOT IN } ( SELECT )

The right-hand side is a parenthesized subquery SELECT 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).

Note 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 Example 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 SELECT list.

Aggregate functions, if any are used, are computed across all rows making up each group, producing a separate value for each group.

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 SELECT list 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 Example 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 Example 9).

UNION ALL|DISTINCT select

The select item is any valid SELECT statement.

The UNION clause is used to combine the result set returned by multiple SELECT statements into one result set. If a row is returned in the result set of at least one of the SELECT statements in the UNION, then the row is returned in the combined result set. The SELECT list for all SELECT 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, OFFSET and FETCH clauses cannot be applied separately to each SELECT statement in a UNION statement (see Note below for work-around). These clauses can only apply to the combined result set of the UNION and must be specified at the end of the UNION statement, after all of the SELECT statements for the UNION(see Example 11).

It is possible to sort the result set of each individual SELECT statement of a UNION separately. This is done by replacing each SELECT statement of the UNION with a select from a derived table which is created using each SELECT statement (see Example 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 SELECT 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 Example 10).

ORDER BY order_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 order_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 order_list that do not appear in the SELECT list. The exception to this is a SELECT statement using a UNION for the combined result set. In this case, only items being returned in the SELECT list can be referred to in the ORDER BY order_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 SELECT 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 order_list (see Example 11).

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

COLLATE collation_type

A collation type can be defined to change the character classification behavior of the sort order of a result set (see Example 12). Supported collation types include: case_insensitive and "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 order_list in the ORDER BY clause. If not specified, ASC is the default.

LIMIT, FETCH, OFFSET

count

Specifies the maximum number of rows to return for the result set. If zero, the result set returns zero number of rows.

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 count and start 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 count and start cannot be the results of a scalar subquery.

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

LIMIT and FETCH are different parameters that achieve the same result. When either LIMIT or 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 count and start 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

    See Example 13 for examples of using LIMIT, FETCH and OFFSET.

When using LIMIT or 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 rows. For example, you might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? You don’t know what ordering unless you specify ORDER BY. This is because NuoDB performs the ORDER BY before it does the LIMIT or 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 or derived table, unless a LIMIT or OFFSET is also supplied, along with the ORDER BY. Let’s say you have defined a VIEW with an ORDER BY and now you want to SELECT from the VIEW and 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 START TRANSACTION for how to create a session level transaction. See Supported Transaction Isolation Levels, for an illustration of how SELECT…​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 the NOWAIT option is specified, and the record that is going to be locked is already locked by another transaction, an exception is thrown immediately instead of waiting for the other transaction to release the lock.

If the SKIP LOCKED option is specified, the rows locked by another transaction are skipped.

If the WAIT option is specified and the record that is going to be locked is already locked by another transaction, the current transaction will wait for the specified time. If the transaction cannot acquire a lock within the specified time, an exception is thrown.

When using FOR UPDATE with the READ COMMITTED isolation level, the query will be internally run twice. The first run will attempt to lock the affected records, then the processing will restart to produce the output from the query.

Accessing Sequences

To access the next value from a sequence use NEXT VALUE FOR sequence_name. This can be part of a longer SELECT statement or to just fetch the next value use:

SELECT NEXT VALUE FOR sequence_name FROM DUAL

The value is always a 64-bit integer, but you can cast it like this:

SELECT cast(NEXT VALUE FOR sequence_name AS SMALLINT) ...

Sequences in NuoDB may be shared by multiple TEs, so when a TE accesses a sequence it gets its own set of values. 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 SELECT NEXT VALUE will be 1. However, if the client connects to the second TE to use the sequence, then SELECT NEXT VALUE will return 101.

As a result the values from SELECT 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 SELECT NEXT VALUE might return 101, 1, 2, 102 …​

See also CREATE SEQUENCE.

Optimizer Hints

Optimizer hints are a means to indicate how the query engine should evaluate specific operations of a query plan statement. For example, you may know, based on the data in a given column, and the predicate(s) applied to this column, that scanning a certain index may be faster than another which was chosen automatically by the optimizer. This may be the case when database statistics are too old or missing. Optimizer hints are embedded in the SQL query, right after the SELECT keyword. The format of optimizer hints is as follows:

/*+ 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 ] )
HASHMATERIALIZE [ ( [schema.]table ) ]
NO_HASHMATERIALIZE [ ( [schema.]table ) ]
NO_MERGE
REOPTIMIZE { ALWAYS | NEVER }

table is the name of a table or its alias, as specified in the AS alias clause of the SELECT statement. The table name may optionally be schema qualified. If a table has an alias then all index hints on that table must reference the alias.

index_list is a list of one or more index specifications separated by commas, where each index specification has the format:

[ ID_ORDERED ] index_name
[ KEY_ORDERED ] index_name

ID_ORDERED and KEY_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 refers to an index scan operation that is reading data in index key order.

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.

The quoted table or index names in index hints must be specified in uppercase. For example, explain select /*+ skip_index(foo, "FOO..PRIMARY_KEY") */ * from foo where a = 4;.

ORDERED Hint

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

Example:

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

USE_INDEX Hint

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

Note: You can use [schema.]table or you may need to use table alias. This is needed if you do a join between the same table more than once.

The USE_INDEX hint tells the optimizer to use an index (or a set of indexes) for a table.

  • If the hint specifies a single index, then the optimizer uses that index. The optimizer does not consider any other index or indexes for the table.

  • If the hint specifies multiple indexes, then the optimizer chooses an optimal index plan based on the specified indexes. The optimizer does not guarantee that all specified indexes are going to be used in the plan.

  • If the hint specifies no indexes, then the optimizer chooses an optimal index plan based on all available indexes over the table. The optimizer does not consider a full scan over the table.

  • If the hint specifies a scan type (ID_ORDERED or 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 refers to a query that is reading data in index key order.
    For example, if the hint specifies ‘KEY_ORDERED foo_a’, then the optimizer will use the foo_a index when it is doing a key ordered scan for ORDER BY or GROUP BY optimization.

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

  • If the index_list specifies both ID_ORDERED and KEY_ORDERED specifications for an index, then the optimizer considers that index for both key ordered and id ordered query plans.
    Examples:
    /+ 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.

See Example 15 below.

SKIP_INDEX Hint

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

Note: You can use [schema.]table or you may need to use table alias. This is needed if you do a join between the same table more than once.

The SKIP_INDEX hint tells the optimizer to not use an index (or a set of indexes) for a table.

  • If the hint specifies a single index, then the optimizer does not consider that index. The optimizer considers the other indexes on the table.

  • If the hint specifies multiple indexes, then the optimizer does not consider any of the specified indexes. The optimizer still considers other indexes that are not specified.

  • If the hint specifies no indexes, the optimizer does not consider any of the indexes on the table. The optimizer will choose a full scan for the table.

  • If the hint specifies a scan type (KEY_ORDERED or 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 refers to an index scan that is reading data in index key order.
    For example, if the hint specifies ‘ID_ORDERED foo_a’, the optimizer still considers foo_a while choosing a plan in the context of ORDER BY or GROUP BY optimization.

  • If the hint does not specify a scan type for an index, then the optimizer does not consider that index for both id ordered and key ordered query plans.
    Example: /*+ SKIP_INDEX(foo, foo_a) */

  • If the hint specifies both KEY_ORDERED and ID_ORDERED for an index, then the optimizer does not consider that index for both id ordered and key ordered query plans.
    Examples:
    /+ 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.

See Example 16 below.

HASHMATERIALIZE and NO_HASHMATERIALIZE Hints

The HASHMATERIALIZE hint is useful for equi-joins when at least one of the tables doesn’t have a suitable index on the join column(s). This could be either a base or an unmerged derived table. The hint instructs the query optimizer to materialize the table and create a hash-index on the join column(s). The resulting join operation is called Hash-join.

Correspondingly, the NO_HASHMATERIALIZE hint disables the hash-join strategy for the table it references.

Example 1

Base tables are referenced as the only argument of the hint inside the SELECT clause of the query.

explain select /*+hashmaterialize(t1)*/ * from t1, t2 where t1.f1 = t2.f2;

 Project F1, F2, F1, F2
  Nested Loop Join (cost: 4903.0, est. rows: 1700)
    Table scan T2 (cost: 1201.0, est. rows: 100)
    Hash lookup on (T1.F1 = T2.F2) (cost: 3602.0, est. rows: 17)
      Materialize stream
        Table scan T1 (cost: 3601.0, est. rows: 300)

Example 2

The hint can be used inside a derived table without an argument. In this case it refers to the derived table itself, so that it is materialized in the parent query.

explain select * from (select /*+hashmaterialize*/ f1, f2 from t1 group by f1, f2) t1, t2 where t1.f1 = t2.f2;

 Project F1, F2, F1, F2
  Nested Loop Join (cost: 4903.0, est. rows: 30000)
    Table scan T2 (cost: 1201.0, est. rows: 100)
    Hash lookup on (T1.F1 = T2.F2) (cost: 3602.0, est. rows: 300)
      Materialize stream
        StreamedGrouping keys { T1.F1, T1.F2 } (cost: 3601.0, est. rows: 300)
          Sort T1.F1 ASC T1.F2 ASC
            Table scan T1 (cost: 3601.0, est. rows: 300)

NO_MERGE Hint

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

Example 1

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

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

Example 2

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

REOPTIMIZE Hint

REOPTIMIZE { ALWAYS | NEVER }

The REOPTIMIZE hint can be used to force the optimizer to perform a full reoptimization on each execution of a query. A full reoptimization may help to improve the query plan generated by the optimizer in cases where user parameters or variables are used in the query and when the value of the parameters or variables can significantly influence the chosen query plan.

The optimizer will automatically reoptimize, without using a hint, when all of the following conditions are true:

  1. A user parameter or variable is used in the query

  2. An index scan is considered by the optimizer

  3. A table scan is chosen instead

This automatic reoptimization can be disabled by using the REOPTIMIZE NEVER hint.

Example

create table t1 (f1 int primary key, f2 string);
insert into t1 values (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five');

explain (analyze on) select /*+ REOPTIMIZE ALWAYS */ * from t1 where f1 < ?;
Enter value: 6
 Project T1.F1, T1.F2 (actual rows: 5, time: 37us, batches: 2)
  Filter (CAST(T1.F1 AS numeric(38,*)) < 6) (actual rows: 5, time: 35us, batches: 2)
    Table scan T1 (cost: 71.0, est. rows: 5, actual rows: 5 in 1 record, 1 data atoms (imported 0 (0KB)), time: 26us, batches: 1)

explain (analyze on) select /*+ REOPTIMIZE NEVER */ * from t1 where f1 < ?;
Enter value: 6
 Project T1.F1, T1.F2 (actual rows: 5, time: 46us, batches: 2)
  Record fetch T1 (cost: 33.4, est. rows: 2, actual rows: 5 in 1 record, 1 data atoms (imported 0 (0KB)), time: 46us, batches: 2)
    Bitmap filter
      Bitmap scan T1..PRIMARY_KEY (F1[-inf:<?:1]) (cost: 4.4, est. keys: 2, actual keys: 5 in 1 record, 1 index atoms (imported 0 (0KB)), time: 29us)

Examples for SELECT

Example 1: Use of SELECT ALL compared with SELECT DISTINCT
SELECT 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

SELECT 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
Example 2: SELECT using functions, operators and expressions and the use of output display names
SELECT 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
Example 3: SELECT expression using a scalar subquery as an expression in the select list and using output display names as compared with no output display name
SELECT s.PLAYERID, s.year, s.stint, s.teamid,
       (SELECT name FROM teams t WHERE t.teamid = s.teamid and 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
...

SELECT s.PLAYERID, s.year, s.stint, s.teamid,
       (SELECT name FROM teams t WHERE t.teamid = s.teamid and 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
...
Example 4: SELECT statement using a subquery in the FROM list
SELECT a.team_year, a.team_teamid, a.team_conf, a.team_div
  FROM (SELECT 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
Example 5: SELECT statement using INNER JOIN and a WHERE clause to filter the combined result set
SELECT 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 and 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
...
Example 6: SELECT statement using LEFT OUTER JOIN and a WHERE clause to filter and order the combined result set
SELECT 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
Example 7: SELECT statement using EXISTS (SELECT…​) to filter result set based on a subquery
SELECT p.playerid, p.firstname, p.lastname FROM players p
 WHERE EXISTS (SELECT '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
...
Example 8: SELECT statement using IN to filter result set based on a subquery that returns a single column
SELECT p.playerid, p.firstname, p.lastname FROM players p
 WHERE p.playerid IN (SELECT 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
...
Example 9: SELECT statement using a GROUP BY and an optional HAVING
SELECT 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
...

SELECT 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
Example 10: SELECT statement showing UNION ALL compared with using UNION DISTINCT

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

SELECT playerid, firstname, lastname, height, weight
  FROM players
  WHERE playerid = 'macinst01'
UNION ALL
SELECT 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


SELECT playerid, firstname, lastname, height, weight
  FROM players
  WHERE playerid = 'macinst01'
UNION DISTINCT
SELECT 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
Example 11: SELECT statement using ORDER BY

Ordering the result set of a SELECT statement.

SELECT 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.

SELECT playerid, firstname, lastname AS lname, height, weight
     FROM players
    WHERE playerid like 'zo%'
UNION ALL
   SELECT 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 SELECT statement in a UNION can be sorted independently. In this case, each SELECT statement selects from a derived table that is created from each individual SELECT statement.

SELECT * FROM
  (SELECT playerid, firstname, lastname AS lname, height, weight
     FROM players
    WHERE playerid like 'zo%'
    ORDER BY firstname) a
UNION
SELECT * FROM
  (SELECT 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
Example 12: Defining a collation type as an ORDER BY option
DROP TABLE tst_table IF EXISTS;
CREATE TABLE tst_table (column1 VARCHAR(12));
INSERT INTO tst_table VALUES ('abc'),('ABC');
INSERT INTO tst_table VALUES ('bcd'),('BCD');
SELECT * FROM tst_table ORDER BY column1;
 COLUMN1
 --------
   ABC
   BCD
   abc
   bcd

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

SELECT * FROM tst_table ORDER BY column1 COLLATE case_insensitive DESC;
 COLUMN1
 --------
   bcd
   BCD
   abc
   ABC
Example 13: SELECT statement using LIMIT, FETCH, OFFSET
SELECT
        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

SELECT
        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

SELECT
        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

SELECT
        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

SELECT
        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
Example 14: Result set column order for a USING join

Using an asterisk (“*”) 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 SELECT playerid,firstname,lastname FROM players;
CREATE VIEW vw_scoring AS SELECT 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 */
SELECT * 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. */
SELECT * 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
...
Example 15: USE_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.
SELECT /*+ USE_INDEX(foo, foo_a) */ *
    FROM foo WHERE a > 100 ORDER BY a;

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

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

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

-- Choose a bitmap index
SELECT /*+ 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
SELECT /*+ 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
SELECT /*+ 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)
SELECT /*+ 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
SELECT /*+ 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)
SELECT /*+ 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);

SELECT /*+ 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);

SELECT /*+ 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)
SELECT /*+ 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)
SELECT /*+ 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;
Example 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'
SELECT /*+ 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.
SELECT /*+ 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
SELECT /*+ SKIP_INDEX(foo, ID_ORDERED foo_a) */ *
    FROM foo WHERE a > 100 ORDER BY a;

-- Equivalent to /*+ SKIP_INDEX(foo foo_a) */
SELECT /*+ 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
SELECT /*+ 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
SELECT /*+ 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
SELECT /*+ 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
SELECT /*+ 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
SELECT /*+ 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;
Example 17: ROWNUM()

With the exception of an ORDER BY or 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 );
INSERT INTO hockey_dup (number,name,position)
    SELECT number,name,position
    FROM hockey WHERE name like 'A%' or name like 'D%';
SELECT 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 */
DELETE FROM hockey_dup WHERE id = 6;
SELECT 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 */
SELECT 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

SELECT 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 */

SELECT 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