Oracle SQL statement Optimization Methods: 30 examples

--------------------- The following is the Oracle database -----------------------------

SQL statement Optimization Methods: 30 examples

We often use hint in the SQL statement optimization process. Here we summarize the common Oracle hint usage in the SQL optimization process:

1./* + all_rows */
It indicates that the overhead-based optimization method is selected for the statement block, and the optimal throughput is obtained to minimize the resource consumption.
For example:
Select/* + all + _ rows */emp_no, emp_nam, dat_in from bsempms where emp_no = 'Scott ';
2./* + first_rows */
It indicates that the overhead-based optimization method is selected 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 */
It indicates that if the data dictionary contains statistics about the access table, the overhead-based optimization method is used to obtain the optimal throughput;
It indicates that if no statistical information of the Access Table is found in the data dictionary, the rule overhead-based optimization method is used;
For example:
Select/* + choose */emp_no, emp_nam, dat_in from bsempms where emp_no = 'Scott ';

4./* + rule */
Indicates the rule-based Optimization Method for the statement block.
For example:
Select/* + rule */emp_no, emp_nam, dat_in from bsempms where emp_no = 'Scott ';

5./* + full (table )*/
Indicates the method used to globally scan the 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 Based on rowid.
For example:
Select/* + rowid (bsempms) */* From bsempms where rowid> = 'aaaaaaaaaaaaaaa'
And emp_no = 'Scott ';

7./* + Cluster (table )*/
The prompt clearly indicates the access method for the selected cluster scan for the specified table, which is only valid for the 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 method used to scan the selected index on the table.
For example:
Select/* + index (bsempms sex_index) Use sex_index because there are fewmale bsempms */from bsempms where sex = 'M ';

9./* + index_asc (Table index_name )*/
Indicates the method used to scan the index in ascending order.
For example:
Select/* + index_asc (bsempms pk_bsempms) */from bsempms where dpt_no = 'Scott ';

10./* + index_combine */
Select a bitmap access path for the specified table. If index_combine does not provide an index as a parameter, a Boolean combination of the index is selected.
For example:
Select/* + index_combine (bsempms sal_bmi hiredate_bmi) */* From bsempms
Where Sal <5000000 and hiredate <sysdate;

11./* + index_join (Table index_name )*/
It is prompted that the command optimizer uses the index as the access path.
For example:
Select/* + index_join (bsempms sal_hmi hiredate_bmi) */SAL, hiredate
From bsempms where Sal <60000;

12./* + index_desc (Table index_name )*/
Indicates the method used to scan the table in descending order of indexes.
For example:
Select/* + index_desc (bsempms pk_bsempms) */from bsempms where dpt_no = 'Scott ';

13./* + index_ffs (Table index_name )*/
Perform a quick full index scan for the specified table, instead of a full table scan.
For example:
Select/* + index_ffs (bsempms in_empnam) */* From bsempms where dpt_no = 'tec305 ';

14./* + add_equal table index_nam1, index_nam2 ,...*/
The prompt is to clearly select the execution plan and combine the scanning of Several Single Column indexes.
For example:
Select/* + index_ffs (bsempms in_dptno, in_empno, in_sex) */* From bsempms where emp_no = 'Scott 'and dpt_no = 'tdc306 ';

15./* + use_concat */
Convert the or condition following the WHERE clause in the query to the Union all clause.
For example:
Select/* + use_concat */* From bsempms where dpt_no = 'tdc506 'and sex = 'M ';

16./* + no_expand */
For the or in-list query statement after the WHERE clause, no_expand will prevent it from being extended based on the optimizer.
For example:
Select/* + no_expand */* From bsempms where dpt_no = 'tdc506 'and sex = 'M ';

17./* + nowrite */
Query Rewriting of query blocks is prohibited.

18./* + rewrite */
The view can be used as a parameter.

19./* + Merge (table )*/
Merge View queries accordingly.
For example:
Select/* + Merge (v) */a. emp_no, A. emp_nam, B. dpt_no from bsempms A (selet dpt_no
, AVG (SAL) as avg_sal from bsempms B group by dpt_no) V where a. dpt_no = V. dpt_no
And a. SAL> v. avg_sal;

20./* + no_merge (table )*/
Views that can be merged are not merged.
For example:
Select/* + no_merge (v) */. emp_no,. emp_nam, B. dpt_no from bsempms A (select dpt_no, AVG (SAL) as avg_sal from bsempms B group by dpt_no) V where. dpt_no = v. dpt_no and. SAL> v. avg_sal;

21./* + ordered */
According to the order in which the table appears in from, ordered connects Oracle to the table in this order.
For example:
Select/* + ordered */. col1, B. col2, C. col3 from Table1 A, Table2 B, table3 c Where. col1 = B. col1 and B. col1 = C. col1;

22./* + use_nl (table )*/
Concatenates the specified table with the nested connected row source and uses the specified table as the internal table.
For example:
Select/* + ordered use_nl (bsempms) */bsdptms. Release, bsempms. emp_no, bsempms. emp_nam from bsempms, bsdptms where bsempms. dpt_no = bsdptms. release;

23./* + use_merge (table )*/
The specified table is connected to other row sources by means of merged sort connections.
For example:
Select/* + use_merge (bsempms, bsdptms) */* From bsempms, bsdptms where bsempms. dpt_no = bsdptms. dpt_no;

24./* + use_hash (table )*/
Connects the specified table to other row sources by means of hash connections.
For example:
Select/* + use_hash (bsempms, bsdptms) */* From bsempms, bsdptms where bsempms. dpt_no = bsdptms. dpt_no;

25./* + driving_site (table )*/
Execute a query on a table in a different position than that selected by Oracle.
For example:
Select/* + driving_site (Dept) */* From bsempms, DEPT @ bsdptms where bsempms. dpt_no = Dept. dpt_no;

26./* + leading (table )*/
Use the specified table as the first table in the connection order.

27./* + cache (table )*/
When a full table scan is performed, the cache prompts that the retrieval block of the table can be placed in the nearest least recently used LRU list in the buffer cache.
For example:
Select/* + full (bsempms) cahe (bsempms) */emp_nam from bsempms;

28./* + nocache (table )*/
When a full table scan is performed, the cache prompts that the retrieval block of the table can be placed in the nearest least recently used LRU list in the buffer cache.
For example:
Select/* + full (bsempms) nocahe (bsempms) */emp_nam from bsempms;

29./* + append */
Insert directly to the end of the table to increase the speed.
Insert/* + append */into test1 select * From test4;

30./* + noappend */
By stopping parallel mode during the lifetime of the insert statement, you can start regular insert.
Insert/* + noappend */into test1 select * From test4;