不要乱加hint

来源：互联网发布：c 和java的三大框架编辑：程序博客网时间：2024/05/16 08:07

SQL> SELECT   id FROM (SELECT /*+ INDEX(si SIE_INVOICES_N3) */ si.id FROM binbin1 si,
2    binbin2 bs where si.id = bs.id and si.type ='AR_INVOICE' AND
3     si.import_failed_reason is null AND (si.imp_into_mis_flag is NULL OR si.imp_into_mis_flag = 'N') AND (si.type != 'AP_INVOICE'
4     OR si.batch_number IS NOT NULL) AND bs.current_status = '300_HAS_APPROVED') WHERE rownum < 6 ;

no rows selected

Elapsed: 00:00:01.76

Execution Plan

----------------------------------------------------------Plan hash value: 1985019629-----------------------------------------------------------------------------------------------------| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT               |                    |     5 |   235 |   276K  (1)| 00:55:23 ||*  1 |  COUNT STOPKEY                 |                    |       |       |            |          ||*  2 |   TABLE ACCESS BY INDEX ROWID  | SIE_INVOICES       |     1 |    24 |     3   (0)| 00:00:01 ||   3 |    NESTED LOOPS                |                    |     5 |   235 |   276K  (1)| 00:55:23 ||   4 |     TABLE ACCESS BY INDEX ROWID| WFR_BILL_STATUS    | 99261 |  2229K|  8743   (1)| 00:01:45 ||*  5 |      INDEX RANGE SCAN          | WFR_BILL_STATUS_N4 | 99261 |       |   679   (1)| 00:00:09 ||*  6 |     INDEX RANGE SCAN           | SIE_INVOICES_N3    |     1 |       |     2   (0)| 00:00:01 |-----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter(ROWNUM<6)   2 - filter("SI"."type"='AR_INVOICE' AND              ("SI"."type"<>'AP_INVOICE' OR "SI"."BATCH_NUMBER" IS NOT NULL) AND              ("SI"."IMP_INTO_MIS_FLAG"='N' OR "SI"."IMP_INTO_MIS_FLAG" IS NULL) AND              "SI"."IMPORT_FAILED_REASON" IS NULL)   5 - access("BS"."CURRENT_STATUS"='300_HAS_APPROVED')   6 - access("SI"."id"="BS"."id")Statistics----------------------------------------------------------          1  recursive calls          0  db block gets     234682  consistent gets          0  physical reads          0  redo size        330  bytes sent via SQL*Net to client        481  bytes received via SQL*Net from client          1  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          0  rows processed

从awr里抓出一个sql，发现有hint，去掉hint后飞速。
SQL>
SQL>   SELECT   id FROM (SELECT si.id FROM binbin1 si,
2    binbin2 bs where si.id = bs.id and si.type ='AR_INVOICE' AND
3     si.import_failed_reason is null AND (si.imp_into_mis_flag is NULL OR si.imp_into_mis_flag = 'N') AND (si.type != 'GL_VOUCHER'
4     OR si.batch_number IS NOT NULL) AND bs.current_status = '300_HAS_APPROVED') WHERE rownum < 6 ;

no rows selected

Elapsed: 00:00:00.06

Execution Plan----------------------------------------------------------Plan hash value: 144640525-----------------------------------------------------------------------------------------------------| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT               |                    |     5 |   235 |    23   (0)| 00:00:01 ||*  1 |  COUNT STOPKEY                 |                    |       |       |            |          ||*  2 |   TABLE ACCESS BY INDEX ROWID  | WFR_BILL_STATUS    |     1 |    23 |     3   (0)| 00:00:01 ||   3 |    NESTED LOOPS                |                    |     5 |   235 |    23   (0)| 00:00:01 ||*  4 |     TABLE ACCESS BY INDEX ROWID| SIE_INVOICES       |    26 |   624 |     5   (0)| 00:00:01 ||*  5 |      INDEX RANGE SCAN          | SIE_INVOICES_N8    |   107 |       |     3   (0)| 00:00:01 ||*  6 |     INDEX RANGE SCAN           | WFR_BILL_STATUS_N2 |     1 |       |     2   (0)| 00:00:01 |-----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter(ROWNUM<6)   2 - filter("BS"."CURRENT_STATUS"='300_HAS_APPROVED')   4 - filter(("SI"."IMP_INTO_MIS_FLAG"='N' OR "SI"."IMP_INTO_MIS_FLAG" IS NULL) AND              ("SI"."type"<>'GL_VOUCHER' OR "SI"."BATCH_NUMBER" IS NOT NULL) AND              "SI"."IMPORT_FAILED_REASON" IS NULL)   5 - access("SI"."type"='AR_INVOICE')   6 - access("SI"."id"="BS"."id")Statistics----------------------------------------------------------          1  recursive calls          0  db block gets         33  consistent gets          8  physical reads          0  redo size        330  bytes sent via SQL*Net to client        481  bytes received via SQL*Net from client          1  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          0  rows processed