oracle索引失效，仅查询条件不一样

SQL> explain plan for
2

2 select
3 nvl(sum(t1.bal),0)
4 from t_master t1
5 left join view_v2_card_status t2 on t1.card_no = t2.card_no
6 where t1.acc_status != 9
7 and t2.mer_id = '007001000020001'
8 and t2.card_ind = '121'
9 and t2.face_value = 0;
10

SQL> explain plan for
2

2 select
3 nvl(sum(t1.bal),0)
4 from t_master t1
5 left join view_v2_card_status t2 on t1.card_no = t2.card_no
6 where t1.acc_status != 9
7 and t2.mer_id = '007001000020001'
8 and t2.card_ind = '121'
9 and t2.face_value = 0;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 1797025674

| Id | Operation | Name | Rows | Bytes | Co

| 0 | SELECT STATEMENT | | 1 | 33 | 34
| 1 | SORT AGGREGATE | | 1 | 33 |
| 2 | NESTED LOOPS | | 304 | 10032 | 34
| 3 | VIEW | VIEW_V2_CARD_STATUS | 380 | 4180 | 34
| 4 | UNION-ALL | | | |
| 5 | PARTITION RANGE ALL | | 1 | 58 | 11
|* 6 | TABLE ACCESS FULL | T_CARD_STATUS | 1 | 58 | 11
| 7 | PARTITION RANGE ALL | | 1 | 58 | 11
|* 8 | TABLE ACCESS FULL | T_CARD_STATUS | 1 | 58 | 11
| 9 | PARTITION RANGE ALL | | 378 | 16254 | 11
|* 10 | TABLE ACCESS FULL | T_CARD_STATUS | 378 | 16254 | 11
|* 11 | TABLE ACCESS BY INDEX ROWID| T_MASTER | 1 | 22 |

|* 12 | INDEX UNIQUE SCAN | SYS_C0011973 | 1 | |

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation id):

6 - filter("T"."CARD_TYPE"=4 AND "T"."CARD_IND"=121 AND "T"."FACE_VALUE"=0 AN
"T"."OPEN_MER_ID" IS NULL THEN "T"."MER_ID" ELSE "T"."OPEN_MER_ID"
8 - filter("T"."CARD_TYPE"=9 AND "T"."CARD_IND"=121 AND "T"."FACE_VALUE"=0 AN
"T"."OPEN_MER_ID" IS NULL THEN "T"."MER_ID" ELSE "T"."OPEN_MER_ID"
10 - filter("T"."OPEN_MER_ID"='007001000020001' AND "T"."CARD_IND"=121 AND "T"
"T"."FACE_VALUE"=0 AND "T"."CARD_TYPE"<>9 AND "T"."CARD_TYPE"<>4)
11 - filter("T1"."ACC_STATUS"<>9)
12 - access("T1"."CARD_NO"="T2"."CARD_NO")

31 rows selected

SQL> explain plan for
2

2 select
3 nvl(sum(t1.bal),0)
4 from t_master t1
5 left join view_v2_card_status t2 on t1.card_no = t2.card_no
6 where t1.acc_status != 9
7 and t2.mer_id = '007001000020001'
8 and t2.card_ind = '121'
9 and t2.face_value = 1.00;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 2604679300

| Id | Operation | Name | Rows | B

| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | NESTED LOOPS | | 7 |
| 3 | VIEW | VIEW_V2_CARD_STATUS | 9 |
| 4 | UNION-ALL | | |
|* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_CARD_STATUS | 1 |
|* 6 | INDEX RANGE SCAN | SYS_FACEVALUE | 20193 |
|* 7 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_CARD_STATUS | 1 |
|* 8 | INDEX RANGE SCAN | SYS_FACEVALUE | 20193 |
|* 9 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_CARD_STATUS | 7 |
|* 10 | INDEX RANGE SCAN | SYS_FACEVALUE | 20193 |
|* 11 | TABLE ACCESS BY INDEX ROWID | T_MASTER | 1 |

|* 12 | INDEX UNIQUE SCAN | SYS_C0011973 | 1 |

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation id):

5 - filter("T"."CARD_TYPE"=4 AND "T"."CARD_IND"=121 AND CASE WHEN "T"."OPEN_
ELSE "T"."OPEN_MER_ID" END ='007001000020001')
6 - access("T"."FACE_VALUE"=1.00)
7 - filter("T"."CARD_TYPE"=9 AND "T"."CARD_IND"=121 AND CASE WHEN "T"."OPEN_
ELSE "T"."OPEN_MER_ID" END ='007001000020001')
8 - access("T"."FACE_VALUE"=1.00)
9 - filter("T"."OPEN_MER_ID"='007001000020001' AND "T"."CARD_IND"=121 AND "T"
"T"."CARD_TYPE"<>9 AND "T"."CARD_TYPE"<>4)
10 - access("T"."FACE_VALUE"=1.00)
11 - filter("T1"."ACC_STATUS"<>9)
12 - access("T1"."CARD_NO"="T2"."CARD_NO")

34 rows selected

http://www.2cto.com/database/201412/365562.html