New SQL Optimization in Oracle 10 GB

The hints adjustment mechanism used by Oracle has been very complex. Oracle Technical Network has a good overview of the process of using hints to adjust Oracle SQL. According to the introduction of 10 Gb database, more new optimizer hints can be used to control optimization behavior.

Now let's take a quick look at these powerful new hints:

Spread_min_analysis

With this hint, you can ignore the Compilation Time Optimization rules for workbooks such as detailed graph analysis. Other optimizations, such as creating filters to selectively locate the workbook access structure and restrict the revision rules, are available.

Because the number of rules is very large, the workbook analysis will be very long. This prompt helps us reduce the resulting compilation time by hundreds of hours.

Example: SELECT/* + SPREAD_MIN_ANALYSIS */...

Spread_no_analysis

With this hint, No workbook analysis is possible. Similarly, this hint can be used to ignore the revision rules and filter the generation. If a spreadsheet analysis exists, the Compilation Time can be minimized.

Example: SELECT/* + SPREAD_NO_ANALYSIS */...

Use_nl_with_index

This hint allows CBO to add a specific table to another original row through nested loops. A specific table is used as an internal table only in the following cases: if no label is specified, CBO must be able to use some labels, and at least one of these labels can be used as the index key value for judgment; otherwise, CBO must be able to add at least one tag as the index key value to the judgment.

Example: SELECT/* + USE_NL_WITH_INDEX (polrecpolrind )*/...

CARDINALITY

This hint defines the base number returned by the query or query. Note that if no table is defined, the base number is the total number of rows returned by the entire query.

Example: SELECT/* + CARDINALITY ([tablespec] card )*/

Selecti.pdf

This hint defines a selective evaluation of the query or query. If only one table is defined, the row part that satisfies all single table judgments in the defined table is optional. If a series of tables are defined, selectivity refers to the row section in the result after all tables that meet all available judgments in any order are merged.

Example: SELECT/* + selectispec ([tablespec] sel )*/

However, note that if both hints CARDINALITY and SELECTIVITY are defined in the same batch of tables, both are ignored.

No_use_nl

Hint no_use_nl enables CBO to execute loop nesting. By using the specified table as an internal table, each specified table is connected to another original row. With this hint, only hash join and sort-merge joins are considered for the specified table.

Example: SELECT/* + NO_USE_NL (employees )*/...

No_use_merge

This hint allows CBO to reject sort-merge from adding each specified table to another original row by using the specified table as an internal table.

Example: SELECT/* + NO_USE_MERGE (employees dept )*/...

No_use_hash

This hint allows CBO to reject hash joins from adding each specified table to another original row by using the specified table as an internal table.

Example: SELECT/* + NO_USE_HASH (employees dept )*/...

No_index_ffs

This hint denies CBO from performing fast full-index scan on the specified tags in the specified table.

Syntax:/* + NO_INDEX_FFS (tablespecindexspec )*/

No_index_ss

This hint denies CBO from performing skip scan on the specified tags in the specified table.

Syntax:/* + NO_INDEX_SS (tablespecindexspec )*/

No_star_transformation

This hint causes CBO to ignore the star query information.

Syntax:/* + NO_STAR_TRANSFORMATION */

Index_ss

This hint explicitly selects index skip scan for the specified table. If the statement uses index range scan, Oracle will check the index entries in ascending order of its index values. In the partitioned index, the results are sorted in ascending order within each part.

Syntax:/* + INDEX_SS (tablespecindexspec )*/

Index_ss_asc

This hint explicitly selects index skip scan for the specified table. If the statement uses index range scan, Oracle will check the index entries in ascending order of its index values. In the partitioned index, the results are sorted in ascending order within each part.

Syntax:/* + INDEX_SS_ASC (tablespecindexspec )*/

Index_ss_desc

This hint explicitly selects index skip scan for the specified table. If the statement uses index range scan, Oracle will check the index entries in descending order of its index values. In the partitioned index, the results are sorted in descending order within each part.

Syntax:/* + INDEX_SS_DESC (tablespecindexspec )*/

Cpu_costing

This hint enables CPU costing for SQL statements. This is the default evaluation mode for the optimizer. The optimizer evaluates the IO operations, IO operation types, and CPU cycles that the database needs to run when a given query is executed.

Syntax:/* + CPU_COSTING (tablespecindexspec )*/

No_cpu_costing

This hint is an SQL statement that disables CPU costing. CBO then uses the IO cost mode, which ignores CPU consumption and only measures all metrics in single-block reads.

Syntax:/* + NO_CPU_COSTING */

As Oracle optimizer matures, Oracle experts must constantly increase their reserves of tools to adjust SQL statements. Of course, discussing all the complicated New hints of Oracle10g SQL is far beyond the scope of this article. You can get more information about Oracle10g from Mike Ault's New Oracle Database 10g New Features.

(