30 methods of SQL statement optimization (1/4)

1./*+all_rows*/

It is shown that the cost-based optimization method is chosen for the statement block, and the optimal throughput is achieved to minimize resource consumption.
For example:

Select/*+all+_rows*/emp_no,emp_nam,dat_in from Bsempms where emp_no= ' Scott ';

2./*+first_rows*/
It is shown that the cost-based optimization method is chosen for the statement block, and the optimal response time is obtained to minimize the resource consumption.
For example:

Select/*+first_rows*/emp_no,emp_nam,dat_in from Bsempms where emp_no= ' Scott ';

3./*+choose*/
Indicates that if the data dictionary has access to the statistical information of the table, it will be based on the cost optimization method and obtain the best throughput;
Indicates that if there is no statistical information in the data dictionary to access the table, an optimization method based on rule cost is proposed.
For example:

Select/*+choose*/emp_no,emp_nam,dat_in from Bsempms where emp_no= ' Scott ';

4./*+rule*/
It shows that the rule-based optimization method is chosen for the statement block.
For example:

Select/*+ Rule * * emp_no,emp_nam,dat_in from Bsempms where emp_no= ' Scott ';

5./*+full (table) * *
Indicates a method for selecting a global scan on a table.
For example:

Select/*+full (A) * * * Emp_no,emp_nam from Bsempms a where emp_no= ' Scott ';

6./*+rowid (table) * *
The prompt explicitly indicates that the specified table is accessed according to ROWID.
For example:

Select/*+rowid (BSEMPMS) * * from Bsempms where rowid>= ' aaaaaaaaaaaaaa '
and emp_no= ' Scott ';

7./*+cluster (table) * *
Tip explicitly indicates the access method that selects a cluster scan for a specified table, which is only valid for a cluster object.
For example:

Select/*+cluster */bsempms.emp_no,dpt_no from BSEMPMS,BSDPTMS
where dpt_no= ' tec304 ' and bsempms.dpt_no=bsdptms.dpt_no;

8./*+index (Table index_name) * *
Indicates the scanning method for selecting indexes on a table.
For example:

Select/*+index (Bsempms sex_index) use sex_index because there are fewmale the Bsempms/from Bsempms where sex= ' m ';

9./*+INDEX_ASC (Table index_name) * *
Indicates a scan method that selects index ascending for the table.
For example:

Select/*+INDEX_ASC (Bsempms pk_bsempms) */from Bsempms where dpt_no= ' Scott ';

Home 1 2 3 4 last page