Shulou(Shulou.com)05/31 Report--

Oracle commonly used hint is how, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

1.According to + ALL_ROWS * /

It is hoped that the optimizer will give an execution plan that can be fully recorded as soon as possible, with the goal of increasing the throughput of the system.

2.Compact accounts + FIRST_ROWS (n) * /

It is hoped that the optimization program can give an execution plan that can get the first line quickly, and the goal is to reduce the response time of the system.

3.Universe + FULL (TABLE) * /

Indicates the method of selecting a global scan for the table.

4. + INDEX (TABLE INDEX_NAME) * /

Indicates the scanning method for selecting an index on the table.

5.Universe + INDEX_ASC (TABLE INDEX_NAME) * /

Indicates the scanning method for selecting the ascending order of the index on the table.

6. + INDEX_DESC (TABLE INDEX_NAME) * /

It shows the scanning method of selecting the descending order of the index on the table.

7.Universe + INDEX_COMBINE*/

Get the ROWID information from the index, convert it to a bitmap through BITMAP CONVERSION FROM ROWIDS, and then convert multiple bitmap information through BITMAP AND

By combining and matching, the resulting bitmap information is converted into ROWID by BITMAP CONVERSION TO ROWIDS, and then the table is accessed or the data is returned directly.

8.Universe + INDEX_JOIN (TABLE INDEX_NAME1 INDEX_NAME2) * /

How index_join works:

The prerequisite of index_join is that all the queried data can be obtained from the index, and Oracle uses hash index join to compare the index.

The data is processed and returned directly, thus avoiding access to the table. The index used by index_join can be a unique index or a multi-column index.

Limitations of index_join:

1. All the queried data can be obtained in the index.

two。 Predicates with non-index columns cannot exist in predicate conditions

9. / * + INDEX_FFS (TABLE INDEX_NAME) * /

Index fast full scan uses multi-block reads to read index blocks and generates db file scattered reads events, which is efficient but unordered.

Index full scan uses single-block read mode to read index blocks in order to generate db file sequential reads events. When a large number of indexes are scanned in this way, it is inefficient.

10.Universe + INDEX_SS (T T_IND) * /

Since 9i, oracle has introduced this kind of index access. When in a federated index, some predicate conditions are not in the first column of the federated index, the index can be accessed through Index Skip Scan to obtain data. When the number of unique values in the first column of the federated index is small, this method is more efficient than a full table scan.

11. + leading (table_1,table_2) * /

In a multi-table association query, specify which table is the driver table, that is, tell the optimizer which table to access first.

12.Universe + ordered * /

Let Oracle choose the driver table according to the order of the tables behind the from. Oracle recommends using leading, which is more flexible.

13. / * + use_nl (table_1,table_2) * /

In the multi-table association query, specify the use of nest loops for multi-table association.

14. / * + use_hash (table_1,table_2) * /

In the multi-table association query, specify the use of hash join for multi-table association.

15.Plus use_merge (table_1,table_2) * /

In the multi-table association query, specify the use of merge join for multi-table association.

16. + parallel (table_name n) * /

Specify the parallelism of execution in sql, which will override its own parallelism

17.Plus no_parallel (table_name) * /

Specifies that the execution in sql does not use parallelism

18.According to + append * /

Load data into the library by direct loading

19.Placement + dynamic_sampling (table_name n) * /

Sets the level at which sql is dynamically adopted during execution, which is 0: 10

20.Universe + cache (table_name) * /

Place table on the most active side of the LRU list when performing a full table scan, similar to the cache property of table

21. / * + HASH_SJ * / / * + HASH_AJ * /

Commonly used in Exists,in subqueries

twenty-two。 / * + use_concat * / / * + no_expand*/

Expansion and non-expansion of or

23./*+NOWRITE*/

Query rewriting of query blocks is prohibited.

/ * + REWRITE*/

Rewrite the statement according to the materialized view

24. / * + MERGE (TABLE) * / / * + NO_MERGE (TABLE) * /

Can merge the queries of the view accordingly.

25. UNNEST and NO_UNNEST

Force Oracle to merge a subquery with a main query

26.NO_QUERY_TRANSFORMATION

Skip all query transformations, including OR operation transformations, view merging, subquery and main query merging, star conversion, materialized view statement rewriting, and so on.

27./*+push_subq (@ tmp) * / / * + QB_Name (tmp) * /

Then push_subq is to make the subquery join first.

Select / * + push_subq (@ tmp) * / hao1.object_name from

Hao1,hao2,hao4

Where hao1.object_name like'% a%'

And hao1.object_id+hao2.object_id > 50

And hao4.object_type=hao1.object_type

And 11 in

(SELECT / * + QB_Name (tmp) * / hao3.object_id FROM hao3 WHERE hao1.object_id = hao3.object_id)

28./*+push_pred (haoview) * /

Next, we use the hint push_pred here to force the optimizer to merge the predicate into view, and we see "VIEW PUSHED PREDICATE":

Select / * + push_pred (haoview) * / hao3.object_name

From hao3,haoview

Where hao3.object_name=haoview.object_name (+)

And hao3.object_id=999

twenty-nine。 / * + pq_distribute * /

Hints are often used to improve the performance of join operations between partitioned tables in a data warehouse.

thirty。 CURSOR_SHARING_EXACT

Prevents Oracle from replacing constants in SQL statements with bound variables.

31.DRIVING_SITE

Force Oracle to execute on another node initiated by the SQL statement

32.DYNAMIC_SAMPLING

Force Oracle to sample dynamic statistical data, and the sampling rate parameter is 0 to 10. The higher the value, the more dynamic sampling data.

33.RESULT_CACHE NO_RESULT_CACHE

Force Oracle to cache the current query result set in RESULT CACHE

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.