ORACLE大表性能优化问题(求大神解答)

雨中的看客 2016-07-04 10:49:35

现有两张表
GOODS_PRICE(
GOODS_ID, PRICE, CODE,NAME ,RCODE （RCODE上建议了索引）
)
GOODS_NEW_PRICE(
GOODS_NEW_ID, PRICE, P_PRICE, LM_PRICE, LP_PRICE , CODE, NAME, RCODE, DEPT_CODE（RCODE, DEPT_CODE上建议了联合索引，该表是每个部门对应的价格表，如公司存在10个部门，GOODS_PRICE表有1千万条记录，那么该表存在的记录应该是1亿+）
)
GOODS_RATIO(
R_ID, P_RATIO, LM_RATIO, LP_RATIO, RCODE
)
业务数据上可能每天会对GOODS_NEW_PRICE表中的PRICE, P_PRICE, LM_PRICE, LP_PRICE 四个价格进行修改（记录在2亿左右）
每个月会新导入一份从第三方拿到的GOODS_PRICE表（记录3千万）
现在需要用GOODS_PRICE.PRICE与GOODS_RATIO表中的（P_RATIO, LM_RATIO, LP_RATIO）三个折扣比例字段进行计算，得出的结果与GOODS_NEW_PRICE表的（PRICE, P_PRICE, LM_PRICE, LP_PRICE）四个值进行比较。
如果GOODS_PRICE.PRICE与GOODS_NEW_PRICE.PRICE相等，不更新后面三个字段（P_PRICE, LM_PRICE, LP_PRICE）。
如果有变化且计算出来的P_PRICE小于GOODS_NEW_PRICE.P_PRICE则更新GOODS_NEW_PRICE.P_PRICE（LM_PRICE, LP_PRICE逻辑一样）。
关联关系为
GOODS_PRICE.CODE = GOODS_NEW_PRICE.CODE
GOODS_PRICE.NAME = GOODS_NEW_PRICE.NAME
GOODS_PRICE.RCODE = GOODS_NEW_PRICE.RCODE

请问各位大神，这个怎么样才能在一个小时左右完成呀。

...全文

970 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

嘚嗒搬运工 2016-07-05

打赏
举报

这么大的数据量，通过更新的形式一个小时搞不定的；可以试试停机备份，然后通过重建表的形式进行，半个小时内应该能搞定（之前有做过类似的，为了更新一个字段，六千多万的数据量）。

雨中的看客 2016-07-05

打赏
举报

引用 7 楼 wang123kui 的回复:

别误导别人，把过程改改吧，游标效率太低，换成集合操作吧，3000万的量说大不大说小不小，硬件还行的话，应该没问题的

公司没钱买服务器，也没钱请开发DBA，伤不起。现在还在想办法优化，我刚把存过改为了10000条左右提交一次，但是已经执行3小时了。后面我打算调存过时分机构来跑，多线程，不知道能不能快点。

ghx287524027 2016-07-04

打赏
举报

以下是几条优化的建议：
1.用TRUNCATE替代DELETE
    当删除表中的记录时，在通常情况下， 回滚段（rollback segments ） 用来存放可以被恢复的信息。 如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态（准确地说是恢复到执行删除命令之前的状况）
    而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。
（注： TRUNCATE只在删除全表适用，TRUNCATE是DDL不是DML）

2.用EXISTS替代IN
　在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS（或NOT EXISTS）通常将提高查询的效率。同理，NOT EXISTS和not in。

3.SELECT子句中避免使用 '*'
　当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用‘*’是一个方便的方法。不幸的是，这是一个非常低效的方法。实际上，ORACLE在解析的过程中， 会将‘*’依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

4.选择最有效率的表名顺序（只在基于规则的优化器中有效）
    ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表 driving table）将被最先处理。 在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时， 会运用排序及合并的方式连接它们。首先，扫描第一个表（FROM子句中最后的那个表）并对记录进行排序，然后扫描第二个表（FROM子句中最后第二个表），最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。
	
5.避免在索引列上使用计算
WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。

6.尽量多使用COMMIT
　只要有可能，在程序中尽量多使用COMMIT， 这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少：COMMIT所释放的资源：
　a.回滚段上用于恢复数据的信息。
  b.被程序语句获得的锁
　c.redo log buffer 中的空间
　d.ORACLE为管理上述3种资源中的内部花费
　（注意：在使用COMMIT时必须要注意到事务的完整性，现实中效率和事务完整性往往是鱼和熊掌不可得兼）

7.使用表的别名
    当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

8. 对于case when 可以考虑换成decode，应该也会快一点

雨中的看客 2016-07-04

打赏
举报

CREATE OR REPLACE PROCEDURE P_MODIFY_PARTS_NEWPRICE(userId IN NUMBER, fileName IN VARCHAR2) Authid Current_User IS v_potherRatio NUMBER(12,2); -- 分公司其他品牌厂方指导价比例 v_lpotherRatio NUMBER(12,2); -- 分公司其他品牌本地厂方指导价比例 v_lotherRatio NUMBER(12,2); -- 分公司其他品牌本地正厂指导价比例 v_lmpOtherRatio NUMBER(12,2); -- 分公司其他品牌本地市场通用指导价比例 v_rownum NUMBER(12); -- 记录数 v_rcnt NUMBER(12); -- 原表记录数 v_analysCnt NUMBER(12); -- 导入表执行次数 v_OtherBrand VARCHAR2(10); -- 其他品牌 v_sql VARCHAR2(3000); -- 执行的SQL CURSOR organIds IS SELECT * FROM T_PTY_INSURER I WHERE I.STATUS = '1' AND I.IS_CLAIM_ORGAN = 'Y'; C_ROW organIds%rowtype; BEGIN -- 删除原表中重复记录 EXECUTE IMMEDIATE 'DELETE FROM T_GOODS_PRICE WHERE (CODE, RCODE, NAME) IN (SELECT CODE, RCODE, NAME FROM T_GOODS_PRICE GROUP BY CODE, RCODE, NAME HAVING COUNT(1) > 1) AND GOODS_ID NOT IN (SELECT MAX(GOODS_ID) FROM T_GOODS_PRICE GROUP BY CODE, RCODE, NAME HAVING COUNT(1) > 1)'; COMMIT; -- 读取执行次数 v_analysCnt := 0; -- 创建临时表 execute immediate 'SELECT analys_count FROM t_clm_uploadzip WHERE file_name = :fileName' INTO v_analysCnt USING fileName; v_rcnt := 0; -- 读取原表记录 execute immediate 'SELECT count(1) as CNT FROM T_GOODS_newprice' INTO v_rcnt; -- 如果该导入是第一次执行，并且生产表有数据，那么需要备份 IF v_analysCnt = 0 AND v_rcnt > 0 THEN -- 清空原备份记录 execute immediate 'TRUNCATE TABLE T_GOODS_newprice_backup'; -- 备份原表记录 execute immediate 'INSERT INTO T_GOODS_newprice_backup SELECT * FROM T_GOODS_newprice'; dbms_output.put_line('INSERT INTO T_GOODS_newprice_backup SELECT * FROM T_GOODS_newprice'); COMMIT; END IF; -- 删除引用外键 v_rownum := 0; execute immediate 'select count(1) AS CNT from user_constraints t where t.table_name = :tableName and t.constraint_type = :fkType and t.constraint_name = :fkName' INTO v_rownum USING 'T_GOODS_NEWPRICE_APPROVAL','R','FK_CLM_NEWPRICE_PARTSPRICE_ID'; IF v_rownum = 1 THEN execute immediate 'ALTER TABLE T_GOODS_NEWPRICE_APPROVAL DROP CONSTRAINT FK_CLM_NEWPRICE_PARTSPRICE_ID'; END IF ; -- 删除原来的主键 v_rownum := 0; execute immediate 'select count(1) AS CNT from user_constraints t where t.table_name = :tableName and t.constraint_type = :pkType' INTO v_rownum USING 'T_GOODS_NEWPRICE','P'; IF v_rownum = 1 THEN execute immediate 'ALTER TABLE T_GOODS_NEWPRICE DROP CONSTRAINT PK_T_GOODS_NEWPRICE_ID'; END IF ; -- 清空现有业务表 execute immediate 'TRUNCATE TABLE T_GOODS_NEWPRICE'; v_rownum := 0; -- 创建临时表 execute immediate 'select count(1) AS CNT from user_tables where TABLE_NAME = :tableName' INTO v_rownum USING 'T_GOODS_NEWPRICE_TEMP'; if v_rownum = 1 then execute immediate 'drop table T_GOODS_newprice_temp'; end if; COMMIT; v_sql := 'CREATE GLOBAL TEMPORARY TABLE T_GOODS_newprice_temp AS SELECT ORGAN_ID,P_PRICE,LP_PRICE,L_PRICE,LMP_PRICE, REMARK,RCODE,CODE,NAME,LIMIT_NUM FROM T_GOODS_NEWPRICE WHERE 1 = 2'; execute immediate v_sql; v_OtherBrand := 'Other'; v_rownum := 0; FOR C_ROW IN organIds LOOP BEGIN execute immediate 'SELECT r.P_PRICE,r.LP_PRICE,r.L_PRICE,r.LMP_PRICE FROM T_BRAND R WHERE R.ORGAN_ID = :organId AND r.brand_code = :brand' INTO v_potherRatio,v_lpotherRatio,v_lotherRatio,v_lmpOtherRatio USING C_ROW.ORGAN_ID,v_OtherBrand; execute immediate 'INSERT INTO T_GOODS_newprice_temp (ORGAN_ID,P_PRICE,LP_PRICE,L_PRICE,LMP_PRICE,REMARK,RCODE,CODE,NAME,LIMIT_NUM) SELECT PI.ORGAN_ID, PI.PRICE * (CASE WHEN vr.P_PRICE IS NULL THEN :potherRatio ELSE vr.P_PRICE END) P_PRICE, PI.PRICE * (CASE WHEN vr.LP_PRICE IS NULL THEN :lpotherRatio ELSE vr.LP_PRICE END) LP_PRICE, PI.PRICE * (CASE WHEN vr.L_PRICE IS NULL THEN :lotherRatio ELSE vr.L_PRICE END) L_PRICE, PI.PRICE * (CASE WHEN vr.LMP_PRICE IS NULL THEN :lmpOtherRatio ELSE vr.LMP_PRICE END) LMP_PRICE, PI.REMARK, PI.RCODE, PI.CODE, PI.NAME, PI.LIMIT_NUM FROM (SELECT P.GOODS_ID, :organId ORGAN_ID, P.PRICE, P.RCODE, P.CODE, P.NAME, P.REMARK, P.LJSL AS LIMIT_NUM FROM T_GOODS_PRICE P ) PI LEFT JOIN (SELECT DISTINCT R.ORGAN_ID, R.P_PRICE, R.LP_PRICE, R.L_PRICE, R.LMP_PRICE, V.RCODE FROM T_BRAND R, T_GOODS V WHERE R.BRAND_CODE = V.BRAND AND R.BRAND_CODE != :brand AND R.STATUS = ''1'') VR ON PI.RCODE = VR.RCODE AND PI.ORGAN_ID = VR.ORGAN_ID' USING v_potherRatio,v_lpotherRatio,v_lotherRatio,v_lmpOtherRatio,C_ROW.ORGAN_ID,v_OtherBrand; execute immediate 'SELECT COUNT(1) AS cnt FROM T_GOODS_newprice_temp' INTO v_rownum; dbms_output.put_line(v_rownum || ' ' || C_ROW.ORGAN_ID); -- 将记录插入中间表 -- 如果没有改变PRICE，价格不更新，如果有更新，那么以价格较低的为准 v_sql := 'INSERT INTO T_GOODS_NEWPRICE SELECT NP.PARTS_PRICE_ID, POLD.ORGAN_ID, POLD.P_PRICE, (CASE WHEN POLD.P_PRICE = NP.P_PRICE THEN np.LP_PRICE ELSE (CASE WHEN POLD.LP_PRICE < np.LP_PRICE THEN pold.LP_PRICE ELSE np.LP_PRICE END) END), (CASE WHEN POLD.P_PRICE = NP.P_PRICE THEN np.L_PRICE ELSE (CASE WHEN POLD.L_PRICE < np.L_PRICE THEN pold.L_PRICE ELSE np.L_PRICE END) END), (CASE WHEN POLD.P_PRICE = NP.P_PRICE THEN np.LMP_PRICE ELSE (CASE WHEN POLD.LMP_PRICE < np.LMP_PRICE THEN pold.LMP_PRICE ELSE np.LMP_PRICE END) END), SYSDATE, :cUserId, SYSDATE, :uUserId, POLD.REMARK, POLD.RCODE, POLD.CODE, POLD.NAME, 1,1,NP.LIMIT_NUM FROM T_GOODS_newprice_temp POLD,T_GOODS_NEWPRICE_BACKUP NP WHERE POLD.ORGAN_ID = NP.ORGAN_ID AND POLD.RCODE = NP.RCODE AND POLD.CODE = NP.CODE AND POLD.NAME = NP.NAME'; dbms_output.put_line(v_sql); execute immediate v_sql USING userId,userId; -- 插入TEMP表中有但是原表没有的记录 v_sql := 'INSERT INTO T_GOODS_newprice (parts_price_id, organ_id,P_PRICE,LP_PRICE,L_PRICE, LMP_PRICE,create_date,create_user,update_date,update_user,remark, RCODE,CODE,NAME,is_jy,status,limit_num) SELECT S_CLM_PARTS_NEWRPICE_PRICE_ID.NEXTVAL,A.ORGAN_ID,A.P_PRICE,A.LP_PRICE, A.L_PRICE,A.LMP_PRICE,SYSDATE,:cUserId,SYSDATE,:uUserId,A.REMARK,A.RCODE, A.CODE,A.NAME,1,1,A.LIMIT_NUM FROM (SELECT TP.ORGAN_ID,TP.P_PRICE, TP.LP_PRICE,L_PRICE,LMP_PRICE, TP.REMARK,TP.RCODE,TP.CODE,TP.NAME,TP.LIMIT_NUM FROM T_GOODS_NEWPRICE_TEMP TP WHERE NOT EXISTS (SELECT 1 FROM T_GOODS_NEWPRICE_BACKUP NP WHERE TP.ORGAN_ID = NP.ORGAN_ID AND TP.RCODE = NP.RCODE AND TP.NAME = NP.NAME AND TP.CODE = NP.CODE) AND TP.ORGAN_ID = :organId) A'; execute immediate v_sql USING userId,userId,C_ROW.ORGAN_ID; -- 插入原表中有但是新表中没有的记录 v_sql := 'INSERT INTO T_GOODS_newprice (parts_price_id, organ_id,P_PRICE,LP_PRICE,L_PRICE, LMP_PRICE,create_date,create_user,update_date,update_user,remark, RCODE,CODE,NAME,is_jy,status,limit_num) SELECT TP.PARTS_PRICE_ID, TP.ORGAN_ID,TP.P_PRICE, TP.LP_PRICE,L_PRICE,LMP_PRICE,SYSDATE, :cUserId,SYSDATE,:uUserId,TP.REMARK,TP.RCODE,TP.CODE,TP.NAME,TP.IS_JY,TP.STATUS,TP.LIMIT_NUM FROM T_GOODS_NEWPRICE_BACKUP TP WHERE NOT EXISTS (SELECT 1 FROM T_GOODS_NEWPRICE_TEMP NP WHERE TP.ORGAN_ID = NP.ORGAN_ID AND TP.RCODE = NP.RCODE AND TP.NAME = NP.NAME AND TP.CODE = NP.CODE) AND TP.ORGAN_ID = :organId'; execute immediate v_sql USING userId,userId,C_ROW.ORGAN_ID; COMMIT; END; END LOOP; -- 增加主键 execute immediate 'ALTER TABLE T_GOODS_NEWPRICE ADD CONSTRAINT PK_T_GOODS_NEWPRICE_ID PRIMARY KEY (PARTS_PRICE_ID)'; -- 增加外键 execute immediate 'alter table T_GOODS_NEWPRICE_APPROVAL add constraint FK_CLM_NEWPRICE_PARTSPRICE_ID foreign key (PARTS_PRICE_ID) references T_GOODS_NEWPRICE (PARTS_PRICE_ID)'; -- 删除临时表 execute immediate 'drop TABLE T_GOODS_newprice_temp'; END; /

ghx287524027 2016-07-04

打赏
举报

贴一下你写的语句

雨中的看客 2016-07-04

打赏
举报

引用 1 楼 js14982 的回复:

允许的话，开并行看看执行计划，看看语句有没优化的可能

这个是现在在用的生产表

js14982 2016-07-04

打赏
举报

允许的话，开并行看看执行计划，看看语句有没优化的可能

桃花岛黄岛主 2016-07-04

打赏
举报

别误导别人，把过程改改吧，游标效率太低，换成集合操作吧，3000万的量说大不大说小不小，硬件还行的话，应该没问题的

雨中的看客 2016-07-04

打赏
举报

引用 5 楼 ghx287524027 的回复:

以下是几条优化的建议：
1.用TRUNCATE替代DELETE
    当删除表中的记录时，在通常情况下， 回滚段（rollback segments ） 用来存放可以被恢复的信息。 如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态（准确地说是恢复到执行删除命令之前的状况）
    而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。
（注： TRUNCATE只在删除全表适用，TRUNCATE是DDL不是DML）

2.用EXISTS替代IN
　在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS（或NOT EXISTS）通常将提高查询的效率。同理，NOT EXISTS和not in。

3.SELECT子句中避免使用 '*'
　当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用‘*’是一个方便的方法。不幸的是，这是一个非常低效的方法。实际上，ORACLE在解析的过程中， 会将‘*’依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

4.选择最有效率的表名顺序（只在基于规则的优化器中有效）
    ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表 driving table）将被最先处理。 在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时， 会运用排序及合并的方式连接它们。首先，扫描第一个表（FROM子句中最后的那个表）并对记录进行排序，然后扫描第二个表（FROM子句中最后第二个表），最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。
	
5.避免在索引列上使用计算
WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。

6.尽量多使用COMMIT
　只要有可能，在程序中尽量多使用COMMIT， 这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少：COMMIT所释放的资源：
　a.回滚段上用于恢复数据的信息。
  b.被程序语句获得的锁
　c.redo log buffer 中的空间
　d.ORACLE为管理上述3种资源中的内部花费
　（注意：在使用COMMIT时必须要注意到事务的完整性，现实中效率和事务完整性往往是鱼和熊掌不可得兼）

7.使用表的别名
    当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

8. 对于case when 可以考虑换成decode，应该也会快一点

非常感谢，我试试