Use hint to optimize Oracle execution plans

Background: A table is suddenly slow to query. cost100 + seconds or longer, seriously affecting production. Oracle getting started Tutorial: leadingvsorderedhint

Background: A table is suddenly slow to query. The cost is over 100 seconds, which seriously affects production. Oracle getting started Tutorial: leading vs ordered hint http ://

Background:

A table suddenly experienced slow queries, with a cost of more than 100 seconds, seriously affecting production.

Oracle getting started Tutorial: leading vs ordered hint

Oracle hint implements a column of leanding driver tables and hash multi-block reading.

Common hint statements for Oracle Optimization

Hint tuning prompt for Oracle

Original SQL:

Explain plan
Select * from (
Select ID, RET_NO retNo, FROM_SYS fromSy, TO_SYS toSys, COMMAND_CODE commandCode, COMMAND, STATUS,
EXT_CODE, ORIGN_CODE orignCode, error_message errorMessage, RE_F, RET_MSG retMsg
From interface_table where (command_code in ('aasss ')
And status in ('F', 'E') and (re_f = 'n') and FROM_SYS = 'my ')
Or (COMMAND_CODE in ('xxxx9', 'xxxx9') and FROM_SYS = 'text' and RE_F = 'n ')
) And MOD (id, 1) = 0 order by id) where rownum <= 100;

View the execution plan:

SELECT plan_table_output from table (DBMS_XPLAN.DISPLAY ('Plan _ table '));

Optimized SQL:

Explain plan
Select * from (
Select/* + index (INT_TABLE IX_INT_TABLE_2) */ID id, RET_NO retNo, FROM_SYS fromSy, TO_SYS toSys, COMMAND_CODE commandCode, COMMAND, STATUS,
EXT_CODE, ORIGN_CODE orignCode, error_message errorMessage, RE_F, RET_MSG retMsg
From interface_table where (command_code in ('aasss ')
And status in ('F', 'E') and (re_f = 'n') and FROM_SYS = 'my ')
Or (COMMAND_CODE in ('xxxx9', 'xxxx9') and FROM_SYS = 'text' and RE_F = 'n ')
) And MOD (id, 1) = 0 order by id) where rownum <= 100;

View the execution plan:

SELECT plan_table_output from table (DBMS_XPLAN.DISPLAY ('Plan _ table '));

Comparison:

View the execution plan. It turns out that full scan is used. When the data volume is large, it is very slow. After optimization, oracle takes priority over range scan. The hint index is the index of the unprocessed Id field, under normal circumstances, this data set is relatively small -------- so it can be optimized.

For specific analysis, we must look at the actual business data stored in the table and analyze the business relationship to find the minimum business set. The latter should understand the execution plan, according to rows, bytes, cost, time to find the optimal project. The analysis sequence cannot be reversed.

Q: Why does the oracle execution plan go through full scan after rownum is used?