Common hint (i) ____oracle in Oracle

Source: Internet
Author: User
Tags oracle database

Hint in Oracle can be used to adjust SQL's execution plan and improve SQL execution efficiency. The following categories describe the common hint in Oracle databases. It is described here that the hint in each version of the common Hint,oracle database in ORACLE11GR2 are different, so the hint described here may not be applicable to earlier versions of Oracle.

Hint associated with the optimizer pattern

1, All_rows

The all_rows is hint for the entire target SQL, meaning that the optimizer enables the CBO and chooses those throughput best execution paths when it gets the execution plan for the target SQL. The "Best throughput" here is the smallest amount of resource consumption (i.e., consumption of hardware resources, such as I/O, CPUs), that is, when all_rows hint is in effect, the optimizer enables the CBO and calculates their respective costs based on the resource consumption of each execution path.

The format of the all_rows hint is as follows:

/*+ all_rows * *

Use Example:

1 2 3 Select/*+ all_rows * * empno,ename,sal,job from EMP where empno=7396;

Starting with oracle10g, All_rows is the default optimizer mode, and the CBO is enabled.

1 2 3 4 5 Scott@test>show parameter Optimizer_mode NAME TYPE VALUE-----------------------                   ----------------------------------------------------------------------------Optimizer_mode string All_rows

The optimizer prioritizes all_rows if there are other hint associated with execution paths and table joins in the target SQL, in addition to All_rows.

2, First_rows (n)

First_rows (n) is the hint of the entire target SQL, meaning that the optimizer enables the CBO mode, and selects the execution path that returns the first N records in the quickest response time when the execution plan for the target SQL is obtained, that is, in First_rows (n) In the case of hint, the optimizer enables the CBO and determines the execution plan of the target SQL based on the response time of the first N records returned.

The First_rows (n) format is as follows:

/*+ first_rows (N) * *

Usage examples

1 2
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.