新接手了一个数据库环境，有用户抱怨速度慢，经过简单的检查，找到了一个问题SQL语句。
这里继续寻找导致问题的真正原因。
SQL优化引出的问题（一）：http://yangtingkun.itpub.net/post/468/458343
上面一篇文章找到的问题，并通过使用HINT解决了问题，但是并没有找到问题的真正原因，而有时候了解问题的原因比解决问题更加重要。
首先要说明的是，表的建立很不规范，表上居然没有建立主外键的关系，只是主键列上建立了唯一索引。
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS
2 WHERE TABLE_NAME IN ('INF_PRODUCT', 'INF_DRUG', 'INF_PRODUCT_PROPERTY')
3 AND CONSTRAINT_TYPE IN ('P', 'R');
no rows selected
SQL> SELECT DISTINCT CONSTRAINT_TYPE FROM USER_CONSTRAINTS
2 WHERE TABLE_NAME IN ('INF_PRODUCT', 'INF_DRUG', 'INF_PRODUCT_PROPERTY');
C
-
C
索引的建立也有问题：
SQL> SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
2 FROM USER_IND_COLUMNS
3 WHERE TABLE_NAME IN ('INF_PRODUCT', 'INF_DRUG', 'INF_PRODUCT_PROPERTY');
TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
-------------------- ------------------------- --------------- ---------------
INF_DRUG INF_DRUG_HAOCAI_FLAG HAOCAI_FLAG 1
INF_DRUG INF_DRUG_NAME DRUG_NAME 1
INF_DRUG INDEX_DRUG_ID DRUG_ID 1
INF_DRUG INF_DRUG_DRUG_CODE DRUG_CODE 1
INF_PRODUCT INF_PRODUCT_ENABLE_FLAG ENABLE_FLAG 1
INF_PRODUCT INF_PRODUCT_ID PRODUCT_ID 1
INF_PRODUCT INF_DRUG_ID DRUG_ID 1
INF_PRODUCT INF_FACTORY_ID FACTORY_ID 1
INF_PRODUCT_PROPERTY INF_PRODUCT_PROD_ID PRODUCT_ID 1
INF_PRODUCT_PROPERTY INF_PRODUCT_PLAT_ID PLAT_ID 1
INF_PRODUCT_PROPERTY INF_PROPERTY_ID PROPERTY_ID 1
INF_PRODUCT_PROPERTY INF_PRODUCT_PLAT PLAT_ID 1
INF_PRODUCT_PROPERTY INF_PRODUCT_PLAT PRODUCT_ID 2
13 rows selected.
一些BTREE索引建立在选择度极差的列上：
SQL> SELECT INDEX_NAME, INDEX_TYPE, DISTINCT_KEYS FROM USER_INDEXES
2 WHERE INDEX_NAME IN ('INF_DRUG_HAOCAI_FLAG', 'INF_PRODUCT_ENABLE_FLAG');
INDEX_NAME INDEX_TYPE DISTINCT_KEYS
------------------------- --------------------------- -------------
INF_DRUG_HAOCAI_FLAG NORMAL 3
INF_PRODUCT_ENABLE_FLAG NORMAL 2
应该说，这些设计上的问题从一定程度上导致了Oracle无法产生优秀的执行计划。但是这些并不是问题的真正原因。即使没有主外键关系，即使建立了不合适的索引，Oracle通过分析统计信息也应该将不合理的执行计划排除掉。
即使Oracle没有选择最优的计划，也不应该选择效率如此之差的计划，显然Oracle这里存在问题，或者是Oracle对统计信息的判断有误，或者统计信息的收集存在问题。
再次检查执行计划：
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ FIRST_ROWS */*
3 FROM
4 (
5 SELECT ROWNUM ROW_NUM, A.*
6 FROM
7 (
8 SELECT A.PRODUCT_ID, C.DRUG_NAME, C.MODE_NAME, A.MIDDLE_PACK_RATE
9 FROM INF_PRODUCT A, INF_PRODUCT_PROPERTY B, INF_DRUG C
10 WHERE B.PLAT_ID=59
11 AND A.ENABLE_FLAG='1'
12 AND A.PRODUCT_ID = B.PRODUCT_ID
13 AND A.DRUG_ID = C.DRUG_ID
14 AND (INSTR(UPPER(C.DRUG_NAME), '阿') <> 0
15 OR INSTR(UPPER(C.ENGLISH_NAME), '阿') <> 0
16 OR INSTR(UPPER(C.WUBI_CODE), '阿') <> 0
17 OR INSTR(UPPER(C.PINYIN_CODE), '阿') <> 0
18 OR INSTR(UPPER(A.PRODUCT_NAME), '阿') <> 0
19 OR INSTR(UPPER(A.PINYIN_CODE), '阿') <> 0
20 OR INSTR(UPPER(A.WUBI_CODE), '阿') <> 0)
21 ) A
22 WHERE ROWNUM <= 40
23 )
24 WHERE ROW_NUM >= 31
25 ;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 820377798
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 4602 | 113 (1)| 00:00:02 |
|* 1 | VIEW | | 39 | 4602 | 113 (1)| 00:00:02 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS | | 39 | 3627 | 113 (1)| 00:00:02 |
| 4 | NESTED LOOPS | | 32 | 2688 | 81 (2)| 00:00:02 |
| 5 | TABLE ACCESS FULL| INF_DRUG | 58535 | 3544K| 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL| INF_PRODUCT | 1 | 22 | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | INF_PRODUCT_PLAT | 1 | 9 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_NUM">=31)
2 - filter(ROWNUM<=40)
6 - filter("A"."ENABLE_FLAG"='1' AND "A"."DRUG_ID"="C"."DRUG_ID" AND
(INSTR(UPPER("C"."DRUG_NAME"),'阿')<>0 OR INSTR(UPPER("C"."ENGLISH_NAME"),'阿')<>0
OR INSTR(UPPER("C"."WUBI_CODE"),'阿')<>0 OR INSTR(UPPER("C"."PINYIN_CODE"),'阿')<>0
OR INSTR(UPPER("A"."PRODUCT_NAME"),'阿')<>0 OR
INSTR(UPPER("A"."PINYIN_CODE"),'阿')<>0 OR INSTR(UPPER("A"."WUBI_CODE"),'阿')<>0))
7 - access("B"."PLAT_ID"=59 AND "A"."PRODUCT_ID"="B"."PRODUCT_ID")
26 rows selected.
从第6步看，Oracle认为INF_PRODUCT全表扫描的代价仅为1，从而导致了问题的产生。
但是观察统计信息，没有发现异常的统计信息：
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS FROM USER_TABLES
2 WHERE TABLE_NAME IN ('INF_PRODUCT', 'INF_PRODUCT_PROPERTY', 'INF_DRUG');
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
INF_DRUG 58535 691
INF_PRODUCT 61344 502
INF_PRODUCT_PROPERTY 495212 7090
看来只有怀疑Oracle的CBO了。
为了更好的进行测试，将产品环境的3张表导出到本地测试环境，分别导入10.2.0.3和10.2.0.1两个环境中。而出现问题的数据库版本为10203。
先看10203中的表现：
SQL> CONN YANGTK/YANGTK@YTK
已连接。
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ FIRST_ROWS */*
3 FROM
4 (
5 SELECT ROWNUM ROW_NUM, A.*
6 FROM
7 (
8 SELECT A.PRODUCT_ID, C.DRUG_NAME, C.MODE_NAME, A.MIDDLE_PACK_RATE
9 FROM INF_DRUG C, INF_PRODUCT A, INF_PRODUCT_PROPERTY B
10 WHERE B.PLAT_ID=59
11 AND A.ENABLE_FLAG='1'
12 AND A.PRODUCT_ID = B.PRODUCT_ID
13 AND A.DRUG_ID = C.DRUG_ID
14 AND (INSTR(UPPER(C.DRUG_NAME), '阿') <> 0
15 OR INSTR(UPPER(C.ENGLISH_NAME), '阿') <> 0
16 OR INSTR(UPPER(C.WUBI_CODE), '阿') <> 0
17 OR INSTR(UPPER(C.PINYIN_CODE), '阿') <> 0
18 OR INSTR(UPPER(A.PRODUCT_NAME), '阿') <> 0
19 OR INSTR(UPPER(A.PINYIN_CODE), '阿') <> 0
20 OR INSTR(UPPER(A.WUBI_CODE), '阿') <> 0)
21 ) A
22 WHERE ROWNUM <= 40
23 )
24 WHERE ROW_NUM >= 31
25 ;
已解释。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 820377798
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 4602 | 62 (2)| 00:00:01 |
|* 1 | VIEW | | 39 | 4602 | 62 (2)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS | | 39 | 3627 | 62 (2)| 00:00:01 |
| 4 | NESTED LOOPS | | 32 | 2688 | 30 (4)| 00:00:01 |
| 5 | TABLE ACCESS FULL| INF_DRUG | 58535 | 3544K| 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL| INF_PRODUCT | 1 | 22 | 0 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | INF_PRODUCT_PLAT | 1 | 9 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_NUM">=31)
2 - filter(ROWNUM<=40)
6 - filter("A"."ENABLE_FLAG"='1' AND "A"."DRUG_ID"="C"."DRUG_ID" AND
(INSTR(UPPER("C"."DRUG_NAME"),'阿')<>0 OR INSTR(UPPER("C"."ENGLISH_NAME"),'阿')<>0
OR INSTR(UPPER("C"."WUBI_CODE"),'阿')<>0 OR INSTR(UPPER("C"."PINYIN_CODE"),'阿')<>0
OR INSTR(UPPER("A"."PRODUCT_NAME"),'阿')<>0 OR
INSTR(UPPER("A"."PINYIN_CODE"),'阿')<>0 OR INSTR(UPPER("A"."WUBI_CODE"),'阿')<>0))
7 - access("B"."PLAT_ID"=59 AND "A"."PRODUCT_ID"="B"."PRODUCT_ID")
已选择26行。
问题重现了，执行计划中的第6步COST出奇的小，导致了Oracle选择了全表扫描作为执行计划，而这里如果认为的改为INDEX，则代价为：
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ FIRST_ROWS */*
3 FROM
4 (
5 SELECT ROWNUM ROW_NUM, A.*
6 FROM
7 (
8 SELECT /*+ ORD_ORDERED NO_EXPAND USE_NL(A C) USE_NL(B) FULL(C) INDEX(A INF_DRUG_ID) */
9 A.PRODUCT_ID, C.DRUG_NAME, C.MODE_NAME, A.MIDDLE_PACK_RATE
10 FROM INF_DRUG C, INF_PRODUCT A, INF_PRODUCT_PROPERTY B
11 WHERE B.PLAT_ID=59
12 AND A.ENABLE_FLAG='1'
13 AND A.PRODUCT_ID = B.PRODUCT_ID
14 AND A.DRUG_ID = C.DRUG_ID
15 AND (INSTR(UPPER(C.DRUG_NAME), '阿') <> 0
16 OR INSTR(UPPER(C.ENGLISH_NAME), '阿') <> 0
17 OR INSTR(UPPER(C.WUBI_CODE), '阿') <> 0
18 OR INSTR(UPPER(C.PINYIN_CODE), '阿') <> 0
19 OR INSTR(UPPER(A.PRODUCT_NAME), '阿') <> 0
20 OR INSTR(UPPER(A.PINYIN_CODE), '阿') <> 0
21 OR INSTR(UPPER(A.WUBI_CODE), '阿') <> 0)
22 ) A
23 WHERE ROWNUM <= 40
24 )
25 WHERE ROW_NUM >= 31
26 ;
已解释。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4033682336
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 4720 | 249 (0)| 00:00:03 |
|* 1 | VIEW | | 40 | 4720 | 249 (0)| 00:00:03 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS | | 40 | 3720 | 249 (0)| 00:00:03 |
| 4 | NESTED LOOPS | | 32 | 2688 | 217 (0)| 00:00:03 |
| 5 | TABLE ACCESS FULL | INF_DRUG | 58535 | 3544K| 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| INF_PRODUCT | 1 | 22 | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | INF_DRUG_ID | 2 | | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | INF_PRODUCT_PLAT | 1 | 9 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_NUM">=31)
2 - filter(ROWNUM<=40)
6 - filter("A"."ENABLE_FLAG"='1' AND (INSTR(UPPER("C"."DRUG_NAME"),'阿')<>0 OR
INSTR(UPPER("C"."ENGLISH_NAME"),'阿')<>0 OR INSTR(UPPER("C"."WUBI_CODE"),'阿')<>0 OR
INSTR(UPPER("C"."PINYIN_CODE"),'阿')<>0 OR INSTR(UPPER("A"."PRODUCT_NAME"),'阿')<>0 OR
INSTR(UPPER("A"."PINYIN_CODE"),'阿')<>0 OR INSTR(UPPER("A"."WUBI_CODE"),'阿')<>0))
7 - access("A"."DRUG_ID"="C"."DRUG_ID")
8 - access("B"."PLAT_ID"=59 AND "A"."PRODUCT_ID"="B"."PRODUCT_ID")
已选择27行。
可以看到，使用索引来进行连接的代价是3，从而导致第一层NESTED LOOP的代价为217，远远超过了刚才全表扫描的代价。
现在看看10.2.0.1中的情况：
SQL> CONN YANGTK/YANGTK@YTK102
已连接。
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ FIRST_ROWS */*
3 FROM
4 (
5 SELECT ROWNUM ROW_NUM, A.*
6 FROM
7 (
8 SELECT A.PRODUCT_ID, C.DRUG_NAME, C.MODE_NAME, A.MIDDLE_PACK_RATE
9 FROM INF_DRUG C, INF_PRODUCT A, INF_PRODUCT_PROPERTY B
10 WHERE B.PLAT_ID=59
11 AND A.ENABLE_FLAG='1'
12 AND A.PRODUCT_ID = B.PRODUCT_ID
13 AND A.DRUG_ID = C.DRUG_ID
14 AND (INSTR(UPPER(C.DRUG_NAME), '阿') <> 0
15 OR INSTR(UPPER(C.ENGLISH_NAME), '阿') <> 0
16 OR INSTR(UPPER(C.WUBI_CODE), '阿') <> 0
17 OR INSTR(UPPER(C.PINYIN_CODE), '阿') <> 0
18 OR INSTR(UPPER(A.PRODUCT_NAME), '阿') <> 0
19 OR INSTR(UPPER(A.PINYIN_CODE), '阿') <> 0
20 OR INSTR(UPPER(A.WUBI_CODE), '阿') <> 0)
21 ) A
22 WHERE ROWNUM <= 40
23 )
24 WHERE ROW_NUM >= 31
25 ;
已解释。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2045796448
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 4720 | 139 (0)| 00:00:02 |
|* 1 | VIEW | | 40 | 4720 | 139 (0)| 00:00:02 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS | | 40 | 3720 | 139 (0)| 00:00:02 |
| 4 | NESTED LOOPS | | 32 | 2688 | 107 (0)| 00:00:02 |
| 5 | TABLE ACCESS BY INDEX ROWID| INF_PRODUCT | 52835 | 1135K| 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | INF_PRODUCT_ENABLE_FLAG | | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| INF_DRUG | 1 | 62 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | INDEX_DRUG_ID | 1 | | 0 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | INF_PRODUCT_PLAT | 1 | 9 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_NUM">=31)
2 - filter(ROWNUM<=40)
6 - access("A"."ENABLE_FLAG"='1')
7 - filter(INSTR(UPPER("C"."DRUG_NAME"),'阿')<>0 OR INSTR(UPPER("C"."ENGLISH_NAME"),'阿')<>0 OR
INSTR(UPPER("C"."WUBI_CODE"),'阿')<>0 OR INSTR(UPPER("C"."PINYIN_CODE"),'阿')<>0 OR
INSTR(UPPER("A"."PRODUCT_NAME"),'阿')<>0 OR INSTR(UPPER("A"."PINYIN_CODE"),'阿')<>0 OR
INSTR(UPPER("A"."WUBI_CODE"),'阿')<>0)
8 - access("A"."DRUG_ID"="C"."DRUG_ID")
9 - access("B"."PLAT_ID"=59 AND "A"."PRODUCT_ID"="B"."PRODUCT_ID")
已选择29行。
在10.2.0.1中，Oracle采用了不同的执行计划，且不论这个执行计划是否是最优的，最起码它避免了NESTED LOOP中被驱动表的全表扫描。
仅从这一点看，这个执行计划就满足要求了。那么通过HINT使得10201中出现刚刚10203中的问题执行计划，并检查这个时候INF_PRODUCT的全表扫描的代价：
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ FIRST_ROWS */*
3 FROM
4 (
5 SELECT ROWNUM ROW_NUM, A.*
6 FROM
7 (
8 SELECT /*+ ORD_ORDERED NO_EXPAND USE_NL(A C) USE_NL(B) FULL(C) FULL(A) */
9 A.PRODUCT_ID, C.DRUG_NAME, C.MODE_NAME, A.MIDDLE_PACK_RATE
10 FROM INF_DRUG C, INF_PRODUCT A, INF_PRODUCT_PROPERTY B
11 WHERE B.PLAT_ID=59
12 AND A.ENABLE_FLAG='1'
13 AND A.PRODUCT_ID = B.PRODUCT_ID
14 AND A.DRUG_ID = C.DRUG_ID
15 AND (INSTR(UPPER(C.DRUG_NAME), '阿') <> 0
16 OR INSTR(UPPER(C.ENGLISH_NAME), '阿') <> 0
17 OR INSTR(UPPER(C.WUBI_CODE), '阿') <> 0
18 OR INSTR(UPPER(C.PINYIN_CODE), '阿') <> 0
19 OR INSTR(UPPER(A.PRODUCT_NAME), '阿') <> 0
20 OR INSTR(UPPER(A.PINYIN_CODE), '阿') <> 0
21 OR INSTR(UPPER(A.WUBI_CODE), '阿') <> 0)
22 ) A
23 WHERE ROWNUM <= 40
24 )
25 WHERE ROW_NUM >= 31
26 ;
已解释。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 820377798
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 4720 | 13658 (8)| 00:02:44 |
|* 1 | VIEW | | 40 | 4720 | 13658 (8)| 00:02:44 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS | | 40 | 3720 | 13658 (8)| 00:02:44 |
| 4 | NESTED LOOPS | | 32 | 2688 | 13626 (8)| 00:02:44 |
| 5 | TABLE ACCESS FULL| INF_DRUG | 58535 | 3544K| 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL| INF_PRODUCT | 1 | 22 | 118 (7)| 00:00:02 |
|* 7 | INDEX RANGE SCAN | INF_PRODUCT_PLAT | 1 | 9 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_NUM">=31)
2 - filter(ROWNUM<=40)
6 - filter("A"."ENABLE_FLAG"='1' AND "A"."DRUG_ID"="C"."DRUG_ID" AND
(INSTR(UPPER("C"."DRUG_NAME"),'阿')<>0 OR INSTR(UPPER("C"."ENGLISH_NAME"),'阿')<>0
OR INSTR(UPPER("C"."WUBI_CODE"),'阿')<>0 OR INSTR(UPPER("C"."PINYIN_CODE"),'阿')<>0
OR INSTR(UPPER("A"."PRODUCT_NAME"),'阿')<>0 OR
INSTR(UPPER("A"."PINYIN_CODE"),'阿')<>0 OR INSTR(UPPER("A"."WUBI_CODE"),'阿')<>0))
7 - access("B"."PLAT_ID"=59 AND "A"."PRODUCT_ID"="B"."PRODUCT_ID")
已选择26行。
SQL> EXPLAIN PLAN FOR SELECT * FROM INF_PRODUCT;
已解释。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3581118231
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61344 | 6769K| 120 (7)| 00:00:02 |
| 1 | TABLE ACCESS FULL| INF_PRODUCT | 61344 | 6769K| 120 (7)| 00:00:02 |
---------------------------------------------------------------------------------
已选择8行。
这个执行计划中的代价才是一个合理的值，对比10201和10203两种不同的情况，基本上可以确定10203中CBO存在bug，导致计算NESTED LOOP中的全表扫描代价过低。
根据上面的问题描述，到metalink中搜索了一下，结果发现Doc ID: Note:4878299.8描述了这个问题，这个patch影响的版本为10104和10203，Oracle在10.2.0.4和11.1.0.6中解决了这个bug。
Oracle给出的临时解决方法正是使用INDEX的HINT。