| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Need: a description of hints for SQL statements tuning
Cant help you out with 7.3 but how about oracle8?
ALL_ROWS 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). For example, the optimizer uses the cost-based approach to optimize this statement for best throughput:
SELECT /*+ ALL_ROWS */ empno, ename, sal, job
FROM emp
WHERE empno = 7566;
FIRST_ROWS The FIRST_ROWS hint 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).
This hint causes the optimizer to make these choices:
If an index scan is available, the optimizer may choose it over a full table scan. If an index scan is available, the optimizer may choose a nested loops join over a sort-merge join whenever the associated table is the potential inner table of the nested loops. If an index scan is made available by an ORDER BY clause, the optimizer may choose it to avoid a sort operation.
For example, the optimizer uses the cost-based approach to optimize this statement for best response time:
SELECT /*+ FIRST_ROWS */ empno, ename, sal, job
FROM emp
WHERE empno = 7566;
The optimizer ignores this hint in DELETE and UPDATE statement blocks and in SELECT statement blocks that contain any of the following syntax:
set operators (UNION, INTERSECT, MINUS, UNION ALL)
GROUP BY clause
FOR UPDATE clause
group functions
DISTINCT operator
These statements cannot be optimized for best response time because Oracle must retrieve all rows accessed by the statement before returning the first row. If you specify this hint in any of these statements, the optimizer uses the cost-based approach and optimizes for best throughput.
If you specify either the ALL_ROWS or FIRST_ROWS hint in a SQL statement and the data dictionary contains no statistics about any of the tables accessed by the statement, the optimizer uses default statistical values (such as allocated storage for such tables) to estimate the missing statistics and subsequently to choose an execution plan. Since these estimates may not be as accurate as those generated by the ANALYZE command, you should use the ANALYZE command to generate statistics for all tables accessed by statements that use cost-based optimization. If you specify hints for access paths or join operations along with either the ALL_ROWS or FIRST_ROWS hint, the optimizer gives precedence to the access paths and join operations specified by the hints.
CHOOSE The CHOOSE hint 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. If the data dictionary contains statistics for at least one of these tables, the optimizer uses the cost-based approach and optimizes with the goal of best throughput. If the data dictionary contains no statistics for any of these tables, the optimizer uses the rule-based approach.
SELECT /*+ CHOOSE */ empno, ename, sal, job
FROM emp
WHERE empno = 7566;
RULE The RULE hint explicitly chooses rule-based optimization for a statement block. This hint also causes the optimizer to ignore any other hints specified for the statement block. For example, the optimizer uses the rule-based approach for this statement:
SELECT --+ RULE
empno, ename, sal, job
FROM emp
WHERE empno = 7566;
The RULE hint, along with the rule-based approach, may not be supported in future versions of Oracle.
Hints for Access Methods
Each hint described in this section suggests an access method for a table.
FULL
ROWID
CLUSTER
HASH
HASH_AJ
INDEX
INDEX_ASC
INDEX_COMBINE
INDEX_DESC
INDEX_FFS
MERGE_AJ
AND_EQUAL
USE_CONCAT
Specifying one of these hints causes the optimizer to choose the specified access path only if the access path is available based on the existence of an index or cluster and the syntactic constructs of the SQL statement. If a hint specifies an unavailable access path, the optimizer ignores it.
You must specify the table to be accessed exactly as it appears in the statement. If the statement uses an alias for the table, you must use the alias, rather than the table name, in the hint. The table name within the hint should not include the schema name, if the schema name is present in the statement.
FULL The FULL hint explicitly chooses a full table scan for the specified table. The syntax of the FULL hint is
FULL(table)
where table specifies the name or alias of the table on which the full table scan is to be performed.
For example, Oracle performs a full table scan on the ACCOUNTS table to execute this statement, even if there is an index on the ACCNO column that is made available by the condition in the WHERE clause:
SELECT /*+ FULL(a) Don't use the index on ACCNO */ accno, bal
FROM accounts a
WHERE accno = 7086854;
Note: Because the ACCOUNTS table has an alias, A, the hint must refer to the table by its alias, rather than by its name. Also, do not specify schema names in the hint, even if they are specified in the FROM clause.
ROWID The ROWID hint explicitly chooses a table scan by ROWID for the specified table. The syntax of the ROWID hint is
ROWID(table)
where table specifies the name or alias of the table on which the table access by ROWID is to be performed.
CLUSTER The CLUSTER hint explicitly chooses a cluster scan to access the specified table. It applies only to clustered objects. The syntax of the CLUSTER hint is
CLUSTER(table)
where table specifies the name or alias of the table to be accessed by a cluster scan.
The following example illustrates the use of the CLUSTER hint.
SELECT --+ CLUSTER emp
ename, deptno
FROM emp, dept
WHERE deptno = 10 AND
emp.deptno = dept.deptno;
HASH The HASH hint explicitly chooses a hash scan to access the specified table. It only applies to tables stored in a cluster. The syntax of the HASH hint is
HASH(table)
where table specifies the name or alias of the table to be accessed by a hash scan.
HASH_AJ
The HASH_AJ hint transforms a NOT IN subquery into a hash anti-join to access
the specified table. The syntax of the
HASH_AJ hint is
HASH_AJ(table)
where table specifies the name or alias of the table to be accessed.
INDEX The INDEX hint explicitly chooses an index scan for the specified table. The syntax of the INDEX hint is
where:
table Specifies the name or alias of the table associated with the index to be scanned. index Specifies an index on which an index scan is to be performed.
This hint may optionally specify one or more indexes:
If this hint specifies a single available index, the optimizer performs a scan on this index. The optimizer does not consider a full table scan or a scan on another index on the table. If this hint specifies a list of available indexes, the optimizer considers the cost of a scan on each index in the list and then performs the index scan with the lowest cost. The optimizer may also choose to scan multiple indexes from this list and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan or a scan on an index not listed in the hint. If this hint specifies no indexes, the optimizer considers the cost of a scan on each available index on the table and then performs the index scan with the lowest cost. The optimizer may also choose to scan multiple indexes and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan.
For example, consider this query, which selects the name, height, and weight of all male patients in a hospital:
SELECT name, height, weight
FROM patients
WHERE sex = 'M';
Assume that there is an index on the SEX column and that this column contains the values M and F. If there are equal numbers of male and female patients in the hospital, the query returns a relatively large percentage of the table's rows and a full table scan is likely to be faster than an index scan. However, if a very small percentage of the hospital's patients are male, the query returns a relatively small percentage of the table's rows and an index scan is likely to be faster than a full table scan.
The number of occurrences of each distinct column value is not available to the optimizer. The cost-based approach assumes that each value has an equal probability of appearing in each row. For a column having only two distinct values, the optimizer assumes each value appears in 50% of the rows, so the cost-based approach is likely to choose a full table scan rather than an index scan.
If you know that the value in the WHERE clause of your query appears in a very small percentage of the rows, you can use the INDEX hint to force the optimizer to choose an index scan. In this statement, the INDEX hint explicitly chooses an index scan on the SEX_INDEX, the index on the SEX column:
SELECT /*+ INDEX(patients sex_index) Use SEX_INDEX, since there
are few male patients */
name, height, weight
FROM patients
WHERE sex = 'M';
The INDEX hint applies to inlist predicates; it forces the optimizer to use the hinted index, if possible, for an inlist predicate. Multi-column inlists will not use an index.
INDEX_ASC The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in ascending order of their indexed values. The syntax of the INDEX_ASC hint is
Each parameter serves the same purpose as in the INDEX hint.
Because Oracle's default behavior for a range scan is to scan index entries in ascending order of their indexed values, this hint does not currently specify anything more than the INDEX hint. However, since Oracle Corporation does not guarantee that the default behavior for an index range scan will remain the same in future versions of Oracle, you may want to use the INDEX_ASC hint to specify ascending range scans explicitly, should the default behavior change.
INDEX_COMBINE If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer will use on the table whatever boolean combination of bitmap indexes has the best cost estimate. If certain indexes are given as arguments, the optimizer will try to use some boolean combination of those particular bitmap indexes. The syntax of INDEX_COMBINE is
INDEX_DESC The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in descending order of their indexed values. Syntax of the INDEX_DESC hint is
Each parameter serves the same purpose as in the INDEX hint. This hint has no effect on SQL statements that access more than one table. Such statements always perform range scans in ascending order of the indexed values.
INDEX_FFS This hint causes a fast full index scan to be performed rather than a full table scan. The syntax of INDEX_FFS is
MERGE_AJ The MERGE_AJ hint transforms a NOT IN subquery into a merge anti-join to access the specified table. The syntax of the MERGE_AJ hint is
MERGE_AJ(table)
where table specifies the name or alias of the table to be accessed.
AND_EQUAL The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes. The syntax of the AND_EQUAL hint is:
where:
table Specifies the name or alias of the table associated with the indexes to be merged. index Specifies an index on which an index scan is to be performed. You must specify at least two indexes. You cannot specify more than five.
USE_CONCAT 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. Normally, 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 inlist processing and OR-expands all disjunctions, including inlists.
Hints for Join Orders
The hints in this section suggest join orders:
ORDERED
STAR
STAR_TRANSFORMATION
ORDERED The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause. For example, this statement joins table TAB1 to table TAB2 and then joins the result to table TAB3:
SELECT /*+ ORDERED */ tab1.col1, tab2.col2, tab3.col3
FROM tab1, tab2, tab3
WHERE tab1.col1 = tab2.col1
AND tab2.col1 = tab3.col1;
If you omit the ORDERED hint from a SQL statement performing a join, the optimizer chooses the order in which to join the tables. You may 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 would allow you to choose an inner and outer table better than the optimizer could.
STAR 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 3 tables, the large table's concatenated index has at least 3 columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.
Usually, if you analyze the tables the optimizer will choose an efficient star plan. You can also use hints to improve the plan. The most precise method is to order the tables in the FROM clause in the order of the keys in the index, with the large table last. Then use the following hints:
/*+ ORDERED USE_NL(facts) INDEX(facts fact_concat) */
A more general method is to use the STAR hint /*+ STAR */.
See Also: Oracle8 Server Concepts for background information about star plans.
STAR_TRANSFORMATION 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.
Note that even if the hint is given, there is no guarantee that the transformation will take place. The optimizer will only generate the subqueries if it seems reasonable to do so. If no subqueries are generated, there is no transformed query, and the best plan for the untransformed query will be used regardless of the hint.
Syntax of this hint is: /*+ STAR_TRANSFORMATION */.
See Also: Oracle8 Server Concepts for a full discussion of star transformation.
Oracle8 Server Reference Manual describes the STAR_TRANSFORMATION_ENABLED
parameter, which causes the
optimizer to consider performing a star transformation.
Hints for Join Operations
Each hint described in this section suggests a join operation for a table.
USE_NL
USE_MERGE
NO_MERGE
USE_HASH
DRIVING_SITE
You must specify a table to be joined exactly as it appears in the statement. If the statement uses an alias for the table, you must use the alias rather than the table name in the hint. The table name within the hint should not include the schema name, if the schema name is present in the statement.
Use of the USE_NL and USE_MERGE hints is recommended with the ORDERED hint. Oracle uses these hints when the referenced table is forced to be the inner table of a join, and they are ignored if the referenced table is the outer table.
USE_NL 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. The syntax of the USE_NL hint is
where table is the name or alias of a table to be used as the inner table of a nested loops join.
For example, consider this statement, which joins the ACCOUNTS and CUSTOMERS
tables. Assume that these tables are
not stored together in a cluster:
SELECT accounts.balance, customers.last_name, customers.first_name
FROM accounts, customers
WHERE accounts.custno = customers.custno;
Since the default goal of the cost-based approach is best throughput, the optimizer will choose either a nested loops operation or a sort-merge operation to join these tables, depending on which is likely to return all the rows selected by the query more quickly.
However, you may want to optimize the statement for best response time, or the
minimal elapsed time necessary to return the
first row selected by the query, rather than best throughput. If so, you can
force the optimizer to choose a nested loops join by
using the USE_NL hint. In this statement, the USE_NL hint explicitly chooses a
nested loops join with the CUSTOMERS
table as the inner table:
SELECT /*+ ORDERED USE_NL(customers) Use N-L to get first row
faster */
accounts.balance, customers.last_name, customers.first_name
FROM accounts, customers
WHERE accounts.custno = customers.custno;
In many cases, a nested loops join returns the first row faster than a sort-merge join. A nested loops join can return the first row after reading the first selected row from one table and the first matching row from the other and combining them, while a sort-merge join cannot return the first row until after reading and sorting all selected rows of both tables and then combining the first rows of each sorted row source.
USE_MERGE The USE_MERGE hint causes Oracle to join each specified table with another row source with a sort-merge join. The syntax of the USE_MERGE hint is
where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a sort-merge join.
NO_MERGE The NO_MERGE hint causes Oracle not to merge mergeable views. The syntax of the NO_MERGE hint is:
This hint allows the user to have more influence over the way in which the view will be accessed. This hint has no arguments. For example,
SELECT * FROM t1, (SELECT /*+ NO_MERGE */ * from t2) v ...
causes view v not to be merged.
USE_HASH The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join. The syntax of the USE_HASH hint is
where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a hash join.
DRIVING_SITE The DRIVING_SITE hint can be used to force 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. The format of the hint is
DRIVING_SITE(table)
where table is the name or alias for the table at which site the execution should take place.
Example:
SELECT /*+DRIVING_SITE(dept)*/ * FROM emp, dept_at_rsite
WHERE emp.deptno = dept.deptno;
If this query is executed without the hint, rows from dept will be sent to the local site and the join will be executed there. With the hint, the rows from emp will be sent to the remote site and the query will be executed there, returning the result to the local site.
Hints for Parallel Execution
The hints described in this section determine how statements are parallelized or not parallelized when using parallel execution.
PARALLEL
NOPARALLEL
APPEND
NOAPPEND
PARALLEL_INDEX
See Also: Chapter 19, "Tuning Parallel Execution"
PARALLEL The PARALLEL hint allows you to specify the desired number of concurrent servers that can be used for a parallel operation. The hint now applies to the INSERT, UPDATE, and DELETE portions of a statement as well as to the table scan portion. Previously, the hint only applied to the scan operation. If any of the parallel restrictions are violated, the hint is ignored. The syntax is:
The PARALLEL hint must use the table alias if an alias is specified in the query. The hint can then take two values separated by commas after the table name. The first value specifies the degree of parallelism for the given table, the second value specifies how the table is to be split among the instances of a parallel server. Specifying DEFAULT or no value signifies the query coordinator should examine the settings of the initialization parameters (described in a later section) to determine the default degree of parallelism.
In the following example, the PARALLEL hint overrides the degree of parallelism specified in the EMP table definition:
SELECT /*+ FULL(scott_emp) PARALLEL(scott_emp, 5) */
ename
FROM scott.emp scott_emp;
In the next example, the PARALLEL hint overrides the degree of parallelism specified in the EMP table definition and tells the optimizer to use the default degree of parallelism determined by the initialization parameters. This hint also specifies that the table should be split among all of the available instances, with the default degree of parallelism on each instance.
SELECT /*+ FULL(scott_emp) PARALLEL(scott_emp, DEFAULT,DEFAULT) */
ename
FROM scott.emp scott_emp;
NOPARALLEL You can use the NOPARALLEL hint to override a PARALLEL specification in the table clause. Note, in general, that hints take precedence over table clauses.
The following example illustrates the NOPARALLEL hint:
SELECT /*+ NOPARALLEL(scott_emp) */
ename
FROM scott.emp scott_emp;
The NOPARALLEL hint is equivalent to specifying the hint /*+ PARALLEL(table,1,1) */
APPEND When you use the APPEND hint for INSERT, data is simply appended to a table. Existing free space in the block is not used.
If INSERT is parallelized using the PARALLEL hint or clause, append mode will be used by default. You can use NOAPPEND to override append mode. Note that the APPEND hint applies to both serial and parallel insert.
The append operation is performed in LOGGING or NOLOGGING mode, depending on
whether the [NO]LOGGING
option is set for the table in question. Use the ALTER TABLE [NO]LOGGING
statement to set the appropriate value.
Certain restrictions apply to the APPEND hint; these are detailed in Oracle8 Server Concepts. If any of these restrictions are violated, the hint will be ignored.
NOAPPEND You can use NOAPPEND to override append mode.
PARALLEL_INDEX Use the PARALLEL_INDEX hint to specify the desired number of concurrent servers that can be used to parallelize index range scans for partitioned indexes. The syntax of the PARALLEL_INDEX hint is:
where:
table Specifies the name or alias of the table associated with the index to be scanned. index Specifies an index on which an index scan is to be performed (optional).
The hint can take two values separated by commas after the table name. The first value specifies the degree of parallelism for the given table, the second value specifies how the table is to be split among the instances of a parallel server. Specifying DEFAULT or no value signifies the query coordinator should examine the settings of the initialization parameters (described in a later section) to determine the default degree of parallelism.
For example:
SELECT /*+ PARALLEL_INDEX(table1 index1, 3, 2) +/;
In this example there are 3 parallel server processes to be used on each of 2 instances.
Additional Hints
Three additional hints are included in this section:
CACHE
NOCACHE
PUSH_SUBQ
CACHE The CACHE hint 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. This option is useful for small lookup tables. In the following example, the CACHE hint overrides the table's default caching specification:
SELECT /*+ FULL (scott_emp) CACHE(scott_emp) */
ename
FROM scott.emp scott_emp;
NOCACHE The NOCACHE hint 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. This is the normal behavior of blocks in the buffer cache. The following example illustrates the NOCACHE hint:
SELECT /*+ FULL(scott_emp) NOCACHE(scott_emp) */
ename
FROM scott.emp scott_emp;
PUSH_SUBQ
The PUSH_SUBQ hint causes nonmerged subqueries to be evaluated at the earliest
possible place in the execution plan.
Normally, 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, it will improve
performance to evaluate the subquery earlier.
The hint will have no effect if the subquery is applied to a remote table or one that is joined using a merge join.
In article <6udsdp$966_at_eureka.vl.net.ua>,
"Andrew Trubin" <taa_at_eureka.vl.net.ua> wrote:
> Hi All!
>
> Nowadays we have a great need in the knowledge of hints for
> ORACLE 7.3.3 SQL statements tuning, the most interesting thing is
> the detailed description of all hints of 7.3.3. If somebody has
> the information, please send that for us. We'll really appreciate
> your help!
>
> Thanks in advance.
>
> P.S. Please, reply to the newsgroup, not to the private e-mail address...
>
>
-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Fri Sep 25 1998 - 07:55:57 CDT
 |
 |