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:

• Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. This text can span multiple lines. End the comment with an asterisk and a slash (*/). The opening and terminating characters need not be separated from the text by a space or a line break.

• Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.

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 syntax below 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:

• DELETE, INSERT, SELECT, or UPDATE is a DELETE, INSERT, SELECT, or UPDATE keyword that begins a statement block. Comments containing hints can appear only after these keywords.

• + is a plus sign that causes Oracle to interpret the comment as a list of hints. The plus sign must follow immediately after the comment delimiter (no space is permitted).

• hint is one of the hints discussed in this section. The space between the plus sign and the hint is optional. If the comment contains multiple hints, separate the hints by at least one space.

• text is other commenting text that can be interspersed with the hints.

Table 2-19 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 Guide and Reference and Oracle9i Database Concepts for more information on hints

Table 2-19 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	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 NESTED_TABLE_GET_REFS UNNEST and NO_UNNEST ORDERED_PREDICATES PUSH_PRED and NO_PUSH_PRED PUSH_SUBQ

all_rows_hint::=

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 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 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. In addition, direct-path INSERT bypasses the buffer cache and ignores integrity constraints. As a result, direct-path INSERT can be considerably faster than conventional INSERT.

cache_hint::=

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

fact_hint::=

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 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 full_hint

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

hash_hint::=

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 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 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 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 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 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 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 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 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 merge_hint

The MERGE hint lets you merge a view on a per-query basis.

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 merge_aj_hint

See HASH_AJ hint.

merge_sj_hint::=

Text description of merge_sj_hint

See HASH_SJ hint.

nl_aj_hint::=

Text description of nl_aj_hint

See HASH_AJ hint.

nl_sj_hint::=

Text description of nl_sj_hint

See HASH_SJ hint.

noappend_hint::=

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 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 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 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 no_index_hint

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

no_merge_hint::=

Text description of no_merge_hint

The NO_MERGE hint causes Oracle not to merge mergeable views.

noparallel_hint::=

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: You cannot parallelize a query involving a nested table.

noparallel_index_hint::=

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 no_push_pred_hint

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

norewrite_hint::=

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.

no_unnest_hint::=

Text description of no_unnest_hint

If you enabled subquery unnesting with the UNNEST_SUBQUERY parameter, then the NO_UNNEST hint turns it off for specific subquery blocks.

ordered_hint::=

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 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 order specified by the following rules. Predicates:

• Without user-defined functions, type methods, or subqueries are evaluated first, in the order specified in the WHERE clause.

• With user-defined functions and type methods that have user-computed costs are evaluated next, in increasing order of their cost.

• With user-defined functions and type methods without user-computed costs are evaluated next, in the order specified in the WHERE clause.

• Not specified in the WHERE clause (for example, predicates transitively generated by the optimizer) are evaluated next.

• With subqueries are evaluated last in the order specified in the WHERE clause.

  Note: As mentioned, you cannot use the ORDERED_PREDICATES hint to preserve the order of predicate evaluation on index keys.

parallel_hint::=

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 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 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 pq_distribute_hint

The PQ_DISTRIBUTE hint improves parallel join operation performance. 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 Guide and Reference for the permitted combinations of distributions for the outer and inner join tables

push_pred_hint::=

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 push_subq_hint

The PUSH_SUBQ hint causes non-merged subqueries to be evaluated at the earliest possible place 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 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 rowid_hint

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

rule_hint::=

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 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 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 unnest_hint

If the UNNEST_SUBQUERY parameter is set to true, then the UNNEST hint checks the subquery block for validity only. If it is valid, then subquery unnesting is enabled without Oracle checking the heuristics.

use_concat_hint::=

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 use_hash_hint

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

use_merge_hint::=

Text description of use_merge_hint

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

use_nl_hint::=

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.