Oracle一条SQL不同库执行顺序不同问题解决

最新推荐文章于 2024-04-02 14:56:03 发布

你好我是李白

最新推荐文章于 2024-04-02 14:56:03 发布

阅读量764

点赞数

分类专栏： Oracle 文章标签： SQL CBO optimizer_mode oracle CONCATENATION

本文链接：https://blog.csdn.net/jwocnimabi/article/details/101540187

版权

1.问题

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

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.通过外部表并使用CTAS加载大约38w数据

--外部表加载CSV文件
--由于dos以及编码格式，加载时出现了乱码问题，通过使用UE-文件-转换，转换为UTF-8以及Unix格式，成功加载

CREATE TABLE hr.fundinfo_tmp
(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)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY expdp
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ","
(PLANID,NAME,IDTYPE,IDNO,ACC,PORT,MONEY,AMUNT,STATE)
)
LOCATION('csv_data.csv')
)
reject limit unlimited;


create table hr.fundinfo as select hr.fundinfo_tmp;

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

--添加PLANID，IDNO，(PORT，ACC)，三个索引，port，acc为复合索引
CRAETE INDEX IDX_FUND_PID ON HR.FUNDINFO(PLANID);
CREATE INDEX IDX_FUND_ID ON HR.FUNDINFO(IDNO);
CREATE INDEX IDX_FUND_PC ON HR.FUNDINFO(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 fundinfo b where  a.idno=b.idno  and b.acc='299') 未纳税申购金额, 
       (select b.money from fundinfo b where a.idno=b.idno  and b.acc='211') 已纳税申购金额,0 
  from fundinfo a where  a.port='00000225' and a.acc in('211','299') 
 group by a.name, a.idtype, a.idno,a.port ;