Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
You can associate comments with SQL statements and schema objects.
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.
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. ;
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 |
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:
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.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, then separate the hints by at least one space.text
is other commenting text that can be interspersed with the hints.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.
See Also:
Oracle9i Database Performance Tuning Guide and Reference and Oracle9i Database Concepts for more information on hints |
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::=
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::=
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::=
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::=
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::=
The CLUSTER
hint explicitly chooses a cluster scan to access the specified table. It applies only to clustered objects.
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::=
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::=
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::=
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::=
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::=
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::=
The FULL
hint explicitly chooses a full table scan for the specified table.
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::=
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::=
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::=
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::=
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::=
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::=
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::=
The INDEX_FFS
hint causes a fast full index scan to be performed rather than a full table scan.
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::=
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::=
See HASH_AJ
hint.
merge_sj_hint::=
See HASH_SJ
hint.
nl_aj_hint::=
See HASH_AJ
hint.
nl_sj_hint::=
See HASH_SJ
hint.
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::=
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::=
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::=
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::=
The NO_INDEX
hint explicitly disallows a set of indexes for the specified table.
no_merge_hint::=
The NO_MERGE
hint causes Oracle not to merge mergeable views.
noparallel_hint::=
The NOPARALLEL
hint overrides a PARALLEL
specification in the table clause. In general, hints take precedence over table clauses.
You cannot parallelize a query involving a nested table.
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::=
The NO_PUSH_PRED
hint prevents pushing of a join predicate into the view.
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::=
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::=
Use of the NO_UNNEST
hint turns off unnesting for specific subquery blocks.
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::=
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:
WHERE
clause.WHERE
clause.WHERE
clause (for example, predicates transitively generated by the optimizer) are evaluated next.WHERE
clause.
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 |
If any parallel restrictions are violated, then the hint is ignored.
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::=
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::=
The PUSH_PRED
hint forces pushing of a join predicate into the view.
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::=
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::=
The ROWID
hint explicitly chooses a table scan by rowid for the specified table.
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::=
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::=
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::=
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::=
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::=
The USE_HASH
hint causes Oracle to join each specified table with another row source, using a hash join.
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::=
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.