不要乱加hint

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