Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

Comments

You can associate comments with SQL statements and schema objects.

Comments Within SQL Statements

Comments within SQL statements do not affect the statement execution, but they may make your application easier for you to read and maintain. You may want to include a comment in a statement that describes the statement's purpose within your application.

A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement using either of these means:

A SQL statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.

Example

These statements contain many comments:

SELECT last_name, salary + NVL(commission_pct, 0), 
   job_id, e.department_id
/* Select all employees whose compensation is
greater than that of Pataballa.*/
  FROM employees e, departments d
       /*The DEPARTMENTS table is used to get the department name.*/
  WHERE e.department_id = d.department_id
    AND salary + NVL(commission_pct,0) >   /* Subquery:       */
   (SELECT salary + NVL(commission_pct,0)
                 /* total compensation is salar + commission_pct */
      FROM employees 
      WHERE last_name = 'Pataballa');

SELECT last_name,                    -- select the name
    salary + NVL(commission_pct, 0),-- total compensation
    job_id,                         -- job
    e.department_id                 -- and department
  FROM employees e,                 -- of all employees
       departments d
  WHERE e.department_id = d.department_id
    AND salary + NVL(commission_pct, 0) >  -- whose compensation 
                                           -- is greater than
      (SELECT salary + NVL(commission_pct,0)  -- the compensation
    FROM employees 
    WHERE last_name = 'Pataballa')        -- of Pataballa.
;

Comments on Schema Objects

You can associate a comment with a table, view, materialized view, or column using the COMMENT command. Comments associated with schema objects are stored in the data dictionary.

See Also:

COMMENT for a description of comments

Hints

You can use comments in a SQL statement to pass instructions, or hints, to the Oracle optimizer. The optimizer uses these hints as suggestions for choosing an execution plan for the statement.

A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, or DELETE keyword. The following syntax shows hints contained in both styles of comments that Oracle supports within a statement block.

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

or

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

where:

Table 2-22 lists the hints by functional category. An alphabetical listing of the hints, including the syntax and a brief description of each hint, follow the table.


Note:

Oracle treats misspelled hints as regular comments and does not return an error.


See Also:

Oracle9i Database Performance Tuning Guide and Reference and Oracle9i Database Concepts for more information on hints

Table 2-21  Hints by Functional Category
Category Hint

Optimization Goals and Approaches

ALL_ROWS and FIRST_ROWS

CHOOSE

RULE

Access Method Hints

AND_EQUAL

CLUSTER

FULL

HASH

INDEX and NO_INDEX

INDEX_ASC and INDEX_DESC

INDEX_COMBINE

INDEX_FFS

ROWID

Join Order Hints

ORDERED

STAR

Join Operation Hints

DRIVING_SITE

HASH_SJ, MERGE_SJ, and NL_SJ

LEADING

USE_HASH and USE_MERGE

USE_NL

Parallel Execution Hints

PARALLEL and NOPARALLEL

PARALLEL_INDEX

PQ_DISTRIBUTE

NOPARALLEL_INDEX

Query Transformation Hints

EXPAND_GSET_TO_UNION

FACT and NOFACT

MERGE

NO_EXPAND

NO_MERGE

REWRITE and NOREWRITE

STAR_TRANSFORMATION

USE_CONCAT

Other Hints

APPEND and NOAPPEND

CACHE and NOCACHE

CURSOR_SHARING_EXACT

DYNAMIC_SAMPLING

NESTED_TABLE_GET_REFS

UNNEST and NO_UNNEST

ORDERED_PREDICATES

PUSH_PRED and NO_PUSH_PRED

PUSH_SUBQ and NO_PUSH_SUBQ

all_rows_hint::=

Text description of sql_elements61a.gif follows
Text description of all_rows_hint


The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).

and_equal_hint::=

Text description of sql_elements65a.gif follows
Text description of and_equal_hint


The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes.

append_hint::=

Text description of sql_elements77b.gif follows
Text description of append_hint


The APPEND hint lets you enable direct-path INSERT if your database is running in serial mode. Your database is in serial mode if you are not using Enterprise Edition. Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode.

In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT.

cache_hint::=

Text description of sql_elements60a.gif follows
Text description of cache_hint


The CACHE hint specifies that the blocks retrieved for the table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.

choose_hint::=

Text description of sql_elements76.gif follows
Text description of choose_hint


The CHOOSE hint causes the optimizer to choose between the rule-based and cost-based approaches for a SQL statement. The optimizer bases its selection on the presence of statistics for the tables accessed by the statement. If the data dictionary has statistics for at least one of these tables, then the optimizer uses the cost-based approach and optimizes with the goal of best throughput. If the data dictionary does not have statistics for these tables, then it uses the rule-based approach.

cluster_hint::=

Text description of sql_elements63a.gif follows
Text description of cluster_hint


The CLUSTER hint explicitly chooses a cluster scan to access the specified table. It applies only to clustered objects.

cursor_sharing_exact_hint::=

Text description of sql_elements62a.gif follows
Text description of cursor_sharing_exact_hint


Oracle can replace literals in SQL statements with bind variables, if it is safe to do so. This is controlled with the CURSOR_SHARING startup parameter. The CURSOR_SHARING_EXACT hint causes this behavior to be switched off. In other words, Oracle executes the SQL statement without any attempt to replace literals by bind variables.

driving_site_hint::=

Text description of sql_elements64a.gif follows
Text description of driving_site_hint


The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization.

dynamic_sampling_hint::=

Text description of sql_elements74a.gif follows
Text description of dynamic_sampling_hint


The DYNAMIC_SAMPLING hint lets you control dynamic sampling to improve server performance by determining more accurate selectivity and cardinality estimates. You can set the value of DYNAMIC_SAMPLING to a value from 0 to 10. The higher the level, the more effort the compiler puts into dynamic sampling and the more broadly it is applied. Sampling defaults to cursor level unless you specify a table.

expand_gset_to_union_hint::=

Text description of sql_elements67.gif follows
Text description of expand_gset_to_union_hint


The EXPAND_GSET_TO_UNION hint is used for queries containing grouping sets (such as queries with GROUP BY GROUPING SET or GROUP BY ROLLUP). The hint forces a query to be transformed into a corresponding query with UNION ALL of individual groupings.

fact_hint::=

Text description of sql_elements75a.gif follows
Text description of fact_hint


The FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should be considered as a fact table.

first_rows_hint::=

Text description of sql_elements77.gif follows
Text description of first_rows_hint


The hints FIRST_ROWS(n) (where n is any positive integer) or FIRST_ROWS instruct Oracle to optimize an individual SQL statement for fast response. FIRST_ROWS(n) affords greater precision, because it instructs Oracle to choose the plan that returns the first n rows most efficiently. The FIRST_ROWS hint, which optimizes for the best plan to return the first single row, is retained for backward compatibility and plan stability.

full_hint::=

Text description of sql_elements78.gif follows
Text description of full_hint


The FULL hint explicitly chooses a full table scan for the specified table.

hash_hint::=

Text description of sql_elements68.gif follows
Text description of hash_hint


The HASH hint explicitly chooses a hash scan to access the specified table. It applies only to tables stored in a cluster.

hash_aj_hint::=

Text description of sql_elements69a.gif follows
Text description of hash_aj_hint


For a specific query, place the HASH_SJ, MERGE_SJ, or NL_SJ hint into the EXISTS subquery. HASH_SJ uses a hash semi-join, MERGE_SJ uses a sort merge semi-join, and NL_SJ uses a nested loop semi-join.

hash_sj_hint::=

Text description of sql_elements70a.gif follows
Text description of hash_sj_hint


For a specific query, place the HASH_SJ, MERGE_SJ, or NL_SJ hint into the EXISTS subquery. HASH_SJ uses a hash semi-join, MERGE_SJ uses a sort merge semi-join, and NL_SJ uses a nested loop semi-join.

index_hint::=

Text description of sql_elements45.gif follows
Text description of index_hint


The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B-tree, bitmap, and bitmap join indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for bitmap indexes, because it is a more versatile hint.

index_asc_hint::=

Text description of sql_elements46.gif follows
Text description of index_asc_hint


The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values.

index_combine_hint::=

Text description of sql_elements49.gif follows
Text description of index_combine_hint


The INDEX_COMBINE hint explicitly chooses a bitmap access path for the table. If no indexes are given as arguments for the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate for the table. If certain indexes are given as arguments, then the optimizer tries to use some Boolean combination of those particular bitmap indexes.

index_desc_hint::=

Text description of sql_elements50a.gif follows
Text description of index_desc_hint


The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition.

index_ffs_hint::=

Text description of sql_elements53.gif follows
Text description of index_ffs_hint


The INDEX_FFS hint causes a fast full index scan to be performed rather than a full table scan.

leading_hint::=

Text description of sql_elements61.gif follows
Text description of leading_hint


The LEADING hint causes Oracle to use the specified table as the first table in the join order.

If you specify two or more LEADING hints on different tables, then all of them are ignored. If you specify the ORDERED hint, then it overrides all LEADING hints.

merge_hint::=

Text description of sql_elements63.gif follows
Text description of merge_hint


The MERGE hint lets you merge a view for each query.

If a view's query contains a GROUP BY clause or DISTINCT operator in the SELECT list, then the optimizer can merge the view's query into the accessing statement only if complex view merging is enabled. Complex merging can also be used to merge an IN subquery into the accessing statement if the subquery is uncorrelated.

Complex merging is not cost-based; that is, the accessing query block must include the MERGE hint. Without this hint, the optimizer uses another approach.

merge_aj_hint::=

Text description of sql_elements17.gif follows
Text description of merge_aj_hint


See HASH_AJ hint.

merge_sj_hint::=

Text description of sql_elements71a.gif follows
Text description of merge_sj_hint


See HASH_SJ hint.

nl_aj_hint::=

Text description of sql_elements31.gif follows
Text description of nl_aj_hint


See HASH_AJ hint.

nl_sj_hint::=

Text description of sql_elements33.gif follows
Text description of nl_sj_hint


See HASH_SJ hint.

noappend_hint::=

Text description of sql_elements35.gif follows
Text description of noappend_hint


The NOAPPEND hint enables conventional INSERT by disabling parallel mode for the duration of the INSERT statement. (Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode).

nocache_hint::=

Text description of sql_elementsa.gif follows
Text description of nocache_hint


The NOCACHE hint specifies that the blocks retrieved for the table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache.

no_expand_hint::=

Text description of sql_elements74.gif follows
Text description of no_expand_hint


The NO_EXPAND hint prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it.

no_fact_hint::=

Text description of sql_elements16.gif follows
Text description of no_fact_hint


The NO_FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should not be considered as a fact table.

no_index_hint::=

Text description of sql_elements48.gif follows
Text description of no_index_hint


The NO_INDEX hint explicitly disallows a set of indexes for the specified table.

no_merge_hint::=

Text description of sql_elements62.gif follows
Text description of no_merge_hint


The NO_MERGE hint causes Oracle not to merge mergeable views.

noparallel_hint::=

Text description of sql_elements73a.gif follows
Text description of noparallel_hint


The NOPARALLEL hint overrides a PARALLEL specification in the table clause. In general, hints take precedence over table clauses.

Restriction on NOPARALLEL

You cannot parallelize a query involving a nested table.

noparallel_index_hint::=

Text description of sql_elements72.gif follows
Text description of noparallel_index_hint


The NOPARALLEL_INDEX hint overrides a PARALLEL attribute setting on an index to avoid a parallel index scan operation.

no_push_pred_hint::=

Text description of sql_elements5.gif follows
Text description of no_push_pred_hint


The NO_PUSH_PRED hint prevents pushing of a join predicate into the view.

no_push_subq_hint::=

Text description of no_push_subq_hint.gif follows
Text description of no_push_subq_hint


The NO_PUSH_SUBQ hint causes non-merged subqueries to be evaluated as the last step in the execution plan. If the subquery is relatively expensive or does not reduce the number of rows significantly, then it improves performance to evaluate the subquery last.

norewrite_hint::=

Text description of sql_elements47.gif follows
Text description of norewrite_hint


The NOREWRITE hint disables query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED. Use the NOREWRITE hint on any query block of a request.


Note:

The NOREWRITE hint disables the use of function-based indexes.


no_unnest_hint::=

Text description of sql_elements60.gif follows
Text description of no_unnest_hint


Use of the NO_UNNEST hint turns off unnesting for specific subquery blocks.

ordered_hint::=

Text description of sql_elements52.gif follows
Text description of ordered_hint


The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.

If you omit the ORDERED hint from a SQL statement performing a join, then the optimizer chooses the order in which to join the tables. You might want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information lets you choose an inner and outer table better than the optimizer could.

ordered_predicates_hint::=

Text description of sql_elements70.gif follows
Text description of ordered_predicates_hint


The ORDERED_PREDICATES hint forces the optimizer to preserve the order of predicate evaluation, except for predicates used as index keys. Use this hint in the WHERE clause of SELECT statements.

If you do not use the ORDERED_PREDICATES hint, then Oracle evaluates all predicates in the following order:

  1. Predicates without user-defined functions, type methods, or subqueries are evaluated first, in the order specified in the WHERE clause.
  2. Predicates with user-defined functions and type methods that have user-computed costs are evaluated next, in increasing order of their cost.
  3. Predicates with user-defined functions and type methods without user-computed costs are evaluated next, in the order specified in the WHERE clause.
  4. Predicates not specified in the WHERE clause (for example, predicates transitively generated by the optimizer) are evaluated next.
  5. Predicates with subqueries are evaluated last, in the order specified in the WHERE clause.


    Note:

    Remember, you cannot use the ORDERED_PREDICATES hint to preserve the order of predicate evaluation on index keys.


parallel_hint::=

Text description of sql_elements69.gif follows
Text description of parallel_hint


The PARALLEL hint lets you specify the desired number of concurrent servers that can be used for a parallel operation. The hint applies to the SELECT, INSERT, UPDATE, and DELETE portions of a statement, as well as to the table scan portion.


Note:

The number of servers that can be used is twice the value in the PARALLEL hint, if sorting or grouping operations also take place.


If any parallel restrictions are violated, then the hint is ignored.


Note:

Oracle ignores parallel hints on a temporary table.


See Also:

CREATE TABLE and Oracle9i Database Concepts

parallel_index_hint::=

Text description of sql_elements77a.gif follows
Text description of parallel_index_hint


The PARALLEL_INDEX hint specifies the desired number of concurrent servers that can be used to parallelize index range scans for partitioned indexes.

pq_distribute_hint::=

Text description of sql_elements3.gif follows
Text description of pq_distribute_hint


The PQ_DISTRIBUTE hint improves the performance of parallel join operations. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers. Using this hint overrides decisions the optimizer would normally make.

Use the EXPLAIN PLAN statement to identify the distribution chosen by the optimizer. The optimizer ignores the distribution hint, if both tables are serial.

See Also:

Oracle9i Database Performance Tuning Guide and Reference for the permitted combinations of distributions for the outer and inner join tables

push_pred_hint::=

Text description of sql_elements72a.gif follows
Text description of push_pred_hint


The PUSH_PRED hint forces pushing of a join predicate into the view.

push_subq_hint::=

Text description of sql_elements9.gif follows
Text description of push_subq_hint


The PUSH_SUBQ hint causes non-merged subqueries to be evaluated at the earliest possible step in the execution plan. Generally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then it improves performance to evaluate the subquery earlier.

This hint has no effect if the subquery is applied to a remote table or one that is joined using a merge join.

rewrite_hint::=

Text description of sql_elements57.gif follows
Text description of rewrite_hint


The REWRITE hint forces the cost-based optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. Use the REWRITE hint with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, then Oracle uses that view regardless of its cost.

Oracle does not consider views outside of the list. If you do not specify a view list, then Oracle searches for an eligible materialized view and always uses it regardless of its cost.

rowid_hint::=

Text description of sql_elements50.gif follows
Text description of rowid_hint


The ROWID hint explicitly chooses a table scan by rowid for the specified table.

rule_hint::=

Text description of sql_elements38.gif follows
Text description of rule_hint


The RULE hint explicitly chooses rule-based optimization for a statement block. It also makes the optimizer ignore other hints specified for the statement block.

star_hint::=

Text description of sql_elements54.gif follows
Text description of star_hint


The STAR hint forces a star query plan to be used, if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least three tables, the large table's concatenated index has at least three columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.

star_transformation_hint::=

Text description of sql_elements73.gif follows
Text description of star_transformation_hint


The STAR_TRANSFORMATION hint makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a cost-based decision to use the best plan generated without the transformation, instead of the best plan for the transformed query.

Even if the hint is given, there is no guarantee that the transformation will take place. The optimizer only generates the subqueries if it seems reasonable to do so. If no subqueries are generated, then there is no transformed query, and the best plan for the untransformed query is used, regardless of the hint.

unnest_hint::=

Text description of sql_elements71.gif follows
Text description of unnest_hint


The UNNEST hint tells Oracle to check the subquery block for validity only. If the subquery block is valid, then subquery unnesting is enabled without Oracle's checking the heuristics.

use_concat_hint::=

Text description of sql_elements57a.gif follows
Text description of use_concat_hint


The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Generally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.

The USE_CONCAT hint turns off IN-list processing and OR-expands all disjunctions, including IN-lists.

use_hash_hint::=

Text description of sql_elements55.gif follows
Text description of use_hash_hint


The USE_HASH hint causes Oracle to join each specified table with another row source, using a hash join.

use_merge_hint::=

Text description of sql_elements65.gif follows
Text description of use_merge_hint


The USE_MERGE hint causes Oracle to join each specified table with another row source, using a sort-merge join.

use_nl_hint::=

Text description of sql_elements75.gif follows
Text description of use_nl_hint


The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join, using the specified table as the inner table.