由于没有收集直方图而走全表

SELECT T.INSTOREITEMID, II.TEXTATTR12, T.binbin2ID FROM binbin1 T, binbin2 II WHERE II.TEXTATTR11 = '0' AND T.binbin2ID=II.binbin2ID ;
no rows selected

Elapsed: 00:04:14.28

Execution Plan
----------------------------------------------------------
Plan hash value: 655340539

-------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                           |  2501K|    62M|       |   254K  (2)| 00:50:56 |
|*  1 |  HASH JOIN              |                           |  2501K|    62M|    59M|   254K  (2)| 00:50:56 |
|*  2 |   TABLE ACCESS FULL     | binbin2               |  2501K|    31M|       | 63841   (4)| 00:12:47 |
|   3 |   VIEW                  | index$_join$_001          |  4902K|    60M|       |   181K  (2)| 00:36:21 |
|*  4 |    HASH JOIN            |                           |       |       |       |            |          |
|*  5 |     INDEX FAST FULL SCAN| RELATIONSHIP_161_FK       |  4902K|    60M|       | 24574   (2)| 00:04:55 |
|   6 |     INDEX FAST FULL SCAN| PK_binbin1 |  4902K|    60M|       |   110K  (1)| 00:22:12 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."binbin2ID"="II"."binbin2ID")
   2 - filter("II"."TEXTATTR11"='0')
   4 - access(ROWID=ROWID)
   5 - filter("T"."binbin2ID" IS NOT NULL)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     401976  consistent gets
     274992  physical reads
        320  redo size
        469  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

binbin2.TEXTATTR11建了索引，但是没有走索引，需要04:14跑完。强制走索引之后，28秒跑完。

SQL>       SELECT  /*+index(II IND_TEXTATTR11) */ T.INSTOREITEMID, II.TEXTATTR12, T.binbin2ID FROM binbin1 T, binbin2 II WHERE II.TEXTATTR11 = '0' AND T.binbin2ID=II.binbin2ID ;

no rows selected

Elapsed: 00:00:28.65

Execution Plan
----------------------------------------------------------
Plan hash value: 2932971856

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                           |  2501K|    62M|       |   340K  (2)| 01:08:07 |
|*  1 |  HASH JOIN                   |                           |  2501K|    62M|    59M|   340K  (2)| 01:08:07 |
|   2 |   TABLE ACCESS BY INDEX ROWID| binbin2               |  2501K|    31M|       |   149K  (1)| 00:29:58 |
|*  3 |    INDEX RANGE SCAN          | IND_TEXTATTR11            |  2501K|       |       |  5022   (2)| 00:01:01 |
|   4 |   VIEW                       | index$_join$_001          |  4902K|    60M|       |   181K  (2)| 00:36:21 |
|*  5 |    HASH JOIN                 |                           |       |       |       |            |          |
|*  6 |     INDEX FAST FULL SCAN     | RELATIONSHIP_161_FK       |  4902K|    60M|       | 24574   (2)| 00:04:55 |
|   7 |     INDEX FAST FULL SCAN     | PK_binbin1 |  4902K|    60M|       |   110K  (1)| 00:22:12 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."binbin2ID"="II"."binbin2ID")
   3 - access("II"."TEXTATTR11"='0')
   5 - access(ROWID=ROWID)
   6 - filter("T"."binbin2ID" IS NOT NULL)

Statistics
----------------------------------------------------------
         84  recursive calls
          0  db block gets
     113241  consistent gets
         79  physical reads
          0  redo size
        469  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

经过分析是由于binbin2.TEXTATTR11直方图没有收集，应对binbin2.TEXTATTR11单独收集直方图。