Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Basic Elements of Oracle SQL, 7 of 10
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 ename, sal + NVL(comm, 0), job, loc /* Select all employees whose compensation is greater than that of Jones.*/ FROM emp, dept /*The DEPT table is used to get the department name.*/ WHERE emp.deptno = dept.deptno AND sal + NVL(comm,0) > /* Subquery: */ (SELECT sal + NLV(comm,0) /* total compensation is sal + comm */ FROM emp WHERE ename = 'JONES'); SELECT ename, -- select the name sal + NVL(comm, 0), -- total compensation job, -- job loc -- and city containing the office FROM emp, -- of all employees dept WHERE emp.deptno = dept.deptno AND sal + NVL(comm, 0) > -- whose compensation -- is greater than (SELECT sal + NVL(comm,0) -- the compensation FROM emp WHERE ename = 'JONES'); -- of Jones.
You can associate a comment with a table, view, materialized view, or column using the COMMENT
command described in Chapter 7, "SQL Statements". Comments associated with schema objects are stored in the data dictionary.
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
UPDATE |
is a |
+ |
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 and in Oracle8i Designing and Tuning for Performance. 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-15 lists hint syntax and descriptions.
See Also:
Oracle8i Designing and Tuning for Performance and Oracle8i Concepts for more information on hints. |
Hint Syntax | Description |
---|---|
Optimization Approaches and Goals |
|
|
Explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption). |
|
Causes the optimizer to choose between the rule-based approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement. |
|
Explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row). |
|
Explicitly chooses rule-based optimization for a statement block. |
Access Methods | |
|
Explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes. |
|
Explicitly chooses a cluster scan to access the specified table. |
|
Explicitly chooses a full table scan for the specified table. |
|
Explicitly chooses a hash scan to access the specified table. |
|
Transforms a |
|
Transforms a |
|
Explicitly chooses an index scan for the specified table. |
|
Explicitly chooses an ascending-range index scan for the specified table. |
|
If no indexes are given as arguments for the |
|
Explicitly chooses a descending-range index scan for the specified table. |
|
Causes a fast full index scan to be performed rather than a full table scan. |
|
Transforms a |
|
Transforms a correlated |
|
Prevents the optimizer from considering |
|
Instructs the optimizer not to consider a scan on the specified index or indexes. If no indexes are specified, the optimizer does not consider a scan on any index defined on the table. |
|
Disables query rewrite for the query block, overriding a |
|
Prohibits the unnesting of the subquery. The hints |
|
Forces the optimizer to preserve the order of predicate evaluation (except predicates used in index keys), as specified in the |
|
Enforces query rewrite. If you specify a view list and the list contains an eligible materialized view, Oracle will use that view regardless of the cost. No views outside of the list are considered. If you do not specify a view list, Oracle will search for an eligible materialized view and always use it regardless of the cost. |
|
Explicitly chooses a table scan by rowid for the specified table. |
|
Forces the unnesting of the subquery if possible. |
|
Forces combined |
Join Orders |
|
|
Causes Oracle to join tables in the order in which they appear in the |
|
Forces the large table to be joined last using a nested-loops join on the index. |
Join Operations |
|
|
Forces query execution to be done at a different site from that selected by Oracle. |
/*+ LEADING (table) */ |
Causes Oracle to use the specified table as the first table in the join order. The |
|
Causes Oracle to join each specified table with another row source with a hash join. |
|
Causes Oracle to join each specified table with another row source with a sort-merge join. |
|
Causes Oracle to join each specified table to another row source with a nested-loops join using the specified table as the inner table. |
Parallel Execution |
|
Note: Oracle ignores parallel hints on a temporary table. For more information on temporary tables, see "CREATE TABLE" and Oracle8i Concepts. |
|
|
Specifies that data is simply appended (or not) to a table; existing free space is not used. Use these hints only following the |
|
Disables parallel scanning of a table, even if the table was created with a Restriction: You cannot parallelize a query involving a nested table. |
|
Lets you specify parallel execution of DML and queries on the table; integer specifies the desired degree of parallelism, which is the number of parallel threads that can be used for the operation. Each parallel thread may use one or two parallel execution servers. If you do not specify integer, Oracle computes a value using the
|
|
Allows you to parallelize fast full index scans for partitioned and nonpartitioned indexes that have the |
|
Specifies how rows of joined tables should be distributed between producer and consumer query servers. The four possible distribution methods are |
|
Overrides a |
Other Hints |
|
|
Specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. |
|
Specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. |
|
Causes Oracle to evaluate complex views or subqueries before the surrounding query. |
|
Causes Oracle not to merge mergeable views. |
|
Causes the optimizer to evaluate, on a cost basis, whether to push individual join predicates into the view. |
|
Prevents pushing of a join predicate into the view. |
|
Causes nonmerged subqueries to be evaluated at the earliest possible place in the execution plan. |
|
Makes the optimizer use the best plan in which the transformation has been used. |
|
Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|