Optimize Oracle's execution plan with hint

Background:

A table suddenly appears query very slow situation, cost 100+ seconds or more, seriously affect production.

Original sql:

Explain plan for SELECT * FROM (select ID id,ret_no retno, From_sys fromsy, To_sys Tosys, Command_code Commandcode, Comman D, STATUS, Ext_code, Orign_code origncode,error_message errormessage, Re_f, ret_msg retmsg from Interface_table where (co Mmand_code in (' Aasss ')  and status in (' F ', ' E ') and (Re_f = ' N ') and From_sys = ' MEE ') or (Command_code in (' XXXX ', ' XXXX9 ') ) and From_sys = ' EXT ' and re_f = ' N ')) and MOD (ID, 1) = 0  order by ID) where rownum <=  ;

To view its execution plan:

SELECT Plan_ Table_output from TABLE (Dbms_xplan. DISPLAY (' plan_table ')); 

Plan Hash value:1871549687 -------------------------------------------------------------------------------- --------------------| id  | operation                      | name               | rows  | Bytes | Cost (%CPU) | time     |--------------------------------------------------------------------------------- -------------------|   0 | SELECT statement              |                     |     |   382k|   637   (1) | 00:00:08 | | *  1 |  COUNT stopkey                 |                    |        |       |             |          | |    2 |   view                         |                     |   |   386k|    637   (1) | 00:00:08 | | *  3 |    TABLE ACCESS by INDEX rowid| interface_table    |   355 | 55735 |   637   (1) | 00:00:08 | | *  4 |     INDEX full scan           | Pk_interface_table |  1439 |       |   280   (2) | 00:00:04 |----------------------------------------------------------------------------------------------------

Optimized sql:

Explain plan for SELECT * FROM (select/*+ Index (int_table ix_int_table_2) */ID id,ret_no Retno, From_sys fromsy, To_sys t Osys, Command_code Commandcode, COMMAND, STATUS, Ext_code, Orign_code origncode,error_message errormessage, RE_F, RET_ MSG retmsg from Interface_table where ((The Command_code in (' Aasss ')  and status in (' F ', ' E ') and (Re_f = ' N ') and From_sys = ' MEE ') or (Command_code in (' XXXX ', ' XXXX9 ') and From_sys = ' EXT ' and re_f = ' N ') "and MOD (ID, 1) = 0 ORDER by ID) where RO Wnum <=  ;

To view its execution plan:

SELECT plan_table_output from table (Dbms_xplan. DISPLAY (' plan_table '));

Plan Hash value:3625182869 -------------------------------------------------------------------------------- ------------------------| id  | operation                        | name                 | rows  | Bytes | Cost (%CPU) | time     |--------------------------------------------------------------------------------- -----------------------|   0 | SELECT statement                |                        |    |   382k| 19105   (1) | 00:03:50 | | *  1 |  COUNT STOPKEY            &Nbsp;     |                       |       |        |            |           | |    2 |   view                           |                       |   356 |   1376k| 19105   (1) | 00:03:50 | | *  3 |    SORT ORDER by stopkey        |                       |    356 | 55892 | 19105 &nbsp (1) | 00:03:50 | |    4 |     concatenation                |                       |       |        |            |           | | *  5 |      TABLE ACCESS by INDEX rowid| interface_table      |    69 | 10833 |  9552   (1) | 00:01:55 | | *  6 |       INDEX RANGE scan           | ix_interface_table_2 | 77145 |       |    99   (0) | 00:00:02 | | *  7 |      TABLE ACCESS by INDEX rowid| Interface_table      |   287 | 45059 |  9552   (1) | 00:01:55 | | *  8 |       INDEX RANGE scan           | ix_interface_table_2 | 77145 |       |    99   (0) | 00:00:02 |------------------------------------------------------------------------------------------------------ --

Compare:

Viewing the execution plan, the original is to use full scan-very slow when the volume of data is large; The index of the optimized Oracle priority range Scan,hint is indexed for the Unhandled identity field, which normally is relatively small--------so it can be optimized.

Specific analysis, we must look at the actual table stored business data, analysis of its business relationship to find the smallest business set, the latter to understand the implementation plan, according to rows, bytes, cost, time to find the best project. This analysis sequence cannot be inverted.

Question: Why does the Oracle execution plan go full scan after using rownum?

Go: How to read the execution plan: http://jadethao.iteye.com/blog/1613943