1.问题

创建了表fundinfo，数据量大概为38w左右，使用同一个SQL语句在测试库与生产产生了不同的执行计划，并且在生产添加部分索引之后，反而SQL会非常慢，删除(port,acc)复合索引，速度明显加快。

数据库很空闲，IO状况良好。

能帮忙分析一下这个SQL执行顺序以及原因，谢谢~

2.表结构

CREATE TABLE fundinfo

(PLANID VARCHAR2(40),

 NAME VARCHAR2(40),

IDTYPE VARCHAR2(20),

 IDNO VARCHAR2(40),

 ACC VARCHAR2(10),

 PORT  VARCHAR2(20),

 MONEY NUMBER(21,11),

 AMUNT NUMBER(21,11),

 STATE VARCHAR2(30)

);

3.通过导入csv文件，加载38w数据

4.表添加索引，收集统计信息

添加PLANID，IDNO，(PORT，ACC)，三个索引，port，acc为复合索引。

execute dbms_stats.gather_table_stats(ownname => 'xx',tabname => 'FUNDINFO' ,estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true);

5.查询SQL

select a.name 个人姓名,

       a.idtype 证件类型,

       a.idno 证件编号,a.port 组合,'3001' 账户编号,'30000004001' 投资组合编号,

       round(sum(a.money),2)  申购金额,'' 申购比例,

       (select b.money  from njsys.fundinfo b where  a.idno=b.idno  and b.port='00000225' and b.acc='299') 未纳税申购金额, 

       (select b.money from njsys.fundinfo b where a.idno=b.idno  and b.port='00000225' and b.acc='211') 已纳税申购金额,0 

  from njsys.fundinfo a where  a.port='00000225' and a.acc in('211','299') 

 group by a.name, a.idtype, a.idno,a.port ;

6.测试库执行计划，测试库只需要大概一两分钟即可完成查询

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1273383357

-----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               | 46636 |  2322K|       |  1962   (1)| 00:00:24 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| FUNDINFO      |     1 |    37 |       |     5   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | FUNDINFO_IDNO |    13 |       |       |     3   (0)| 00:00:01 |

|*  3 |  TABLE ACCESS BY INDEX ROWID| FUNDINFO      |     1 |    37 |       |     5   (0)| 00:00:01 |

|*  4 |   INDEX RANGE SCAN          | FUNDINFO_IDNO |    13 |       |       |     3   (0)| 00:00:01 |

|   5 |  HASH GROUP BY              |               | 46636 |  2322K|  3120K|  1962   (1)| 00:00:24 |

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|*  6 |   TABLE ACCESS FULL         | FUNDINFO      | 46636 |  2322K|       |  1369   (1)| 00:00:17 |

-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("B"."ACC"='299' AND "B"."PORT"='00000225')

   2 - access("B"."IDNO"=:B1)

   3 - filter("B"."ACC"='211' AND "B"."PORT"='00000225')

   4 - access("B"."IDNO"=:B1)

   6 - filter("A"."PORT"='00000225' AND ("A"."ACC"='211' OR "A"."ACC"='299'))

7.生产库执行计划，生产库需要十几二十分钟才能完成

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3246304852

-----------------------------------------------------

| Id  | Operation                     | Name        |

-----------------------------------------------------

|   0 | SELECT STATEMENT              |             |

|*  1 |  TABLE ACCESS BY INDEX ROWID  | FUNDINFO    |

|*  2 |   INDEX RANGE SCAN            | FUNDINFO_PC |

|*  3 |  TABLE ACCESS BY INDEX ROWID  | FUNDINFO    |

|*  4 |   INDEX RANGE SCAN            | FUNDINFO_PC |

|   5 |  SORT GROUP BY                |             |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|   6 |   CONCATENATION               |             |

|   7 |    TABLE ACCESS BY INDEX ROWID| FUNDINFO    |

|*  8 |     INDEX RANGE SCAN          | FUNDINFO_PC |

|   9 |    TABLE ACCESS BY INDEX ROWID| FUNDINFO    |

|* 10 |     INDEX RANGE SCAN          | FUNDINFO_PC |

-----------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("B"."IDNO"=:B1)

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

   2 - access("B"."PORT"='00000225' AND "B"."ACC"='299')

   3 - filter("B"."IDNO"=:B1)

   4 - access("B"."PORT"='00000225' AND "B"."ACC"='211')

   8 - access("A"."PORT"='00000225' AND "A"."ACC"='299')

  10 - access("A"."PORT"='00000225' AND "A"."ACC"='211')

Note

-----

   - rule based optimizer used (consider using cbo)