求SQL查询大数据优化

Question

两个表的数据分别为6千万，字段都是100多个，我想从中提取些有用的字段组成新的表，我写的语句如下：CREATE TABLE TG_CDR09_FIX_FL_ERR_SJZ
AS 
SELECT A.CFEE + A.LFEE + A.OTHERFEE RATE_1,
       B.STD_CHARGE,
       A.DISCOUNT_CFEE + A.DISCOUNT_LFEE + A.DISCOUNT_OTHERFEE RATE_2,
       B.CHARGE1,B.CHARGE2,
       B.CHARGE1+B.CHARGE2  old_CHARGE,
       A.FREE_CODE,A.RESERVER3,
       SUBSTR(A.FREE_CODE,
              INSTR(A.FREE_CODE, '|', 1, 6) + 1,
              INSTR(A.FREE_CODE, '|', 1, 7) - INSTR(A.FREE_CODE, '|', 1, 6) - 1) DISCNT_CODE,
       A.IMSI_NUMBER,A.OTHER_PARTY,A.CALL_DURATION,
       B.RATE_PLAN_ID,B.PRODUCT_OBJ_ID,
       A.CHANNEL_NO,A.START_DATE,A.USER_ID,
       B.SERV_ID,
       A.CFEE,A.LFEE,A.OTHERFEE,A.DISCOUNT_CFEE,A.DISCOUNT_LFEE,A.DISCOUNT_OTHERFEE,A.SEQ_NO
  FROM 
       F_BILLING_SJZ.UR_FIX_LOCAL_T@TO_COM_ACT1_PARAM B,
       TG_CDR09_FIX_FL_SJZ                            A
 WHERE A.SEQ_NO = B.TICKET_ID
   AND A.RESERVER3 = B.EVENT_TYPE
   AND (A.CFEE + A.LFEE + A.OTHERFEE <> B.STD_CHARGE AND
       A.DISCOUNT_CFEE + A.DISCOUNT_LFEE + A.DISCOUNT_OTHERFEE = B.CHARGE1+B.CHARGE2 OR
       (A.CFEE + A.LFEE + A.OTHERFEE = B.CHARGE1+B.CHARGE2 AND
       A.DISCOUNT_CFEE + A.DISCOUNT_LFEE + A.DISCOUNT_OTHERFEE <>
       B.CHARGE1) OR (A.CFEE + A.LFEE + A.OTHERFEE <> B.CHARGE1+B.CHARGE2 AND
       A.DISCOUNT_CFEE + A.DISCOUNT_LFEE +
       A.DISCOUNT_OTHERFEE <> B.CHARGE1+B.CHARGE2))
 ORDER BY B.RATE_PLAN_ID, A.USER_ID, DISCNT_CODE, A.RESERVER3;小弟不才，求人指导。

Answer 1

这样DBA会骂我的，占空间太大了，这还只是一个地市的，不包括其它地市的。

Answer 2

SELECT STATEMENT, GOAL = ALL_ROWS 3236709 1257705 757138410
 SORT ORDER BY 3236709 1257705 757138410
  HASH JOIN 3076767 1257705 757138410
   REMOTE UR_FIX_LOCAL_T 492926 60262318 6508330344
   TABLE ACCESS FULL UCR_HSD TG_CDR09_FIX_FL_SJZ 874872 57105469 28210101686

Answer 3

 AND (A.CFEE + A.LFEE + A.OTHERFEE <> B.STD_CHARGE AND
       A.DISCOUNT_CFEE + A.DISCOUNT_LFEE + A.DISCOUNT_OTHERFEE = B.CHARGE1+B.CHARGE2 OR
       (A.CFEE + A.LFEE + A.OTHERFEE = B.CHARGE1+B.CHARGE2 AND
       A.DISCOUNT_CFEE + A.DISCOUNT_LFEE + A.DISCOUNT_OTHERFEE <>
       B.CHARGE1) OR (A.CFEE + A.LFEE + A.OTHERFEE <> B.CHARGE1+B.CHARGE2 AND
       A.DISCOUNT_CFEE + A.DISCOUNT_LFEE +
       A.DISCOUNT_OTHERFEE <> B.CHARGE1+B.CHARGE2))
 ORDER BY B.RATE_PLAN_ID, A.USER_ID, DISCNT_CODE, A.RESERVER3;都建上索引

Answer 4

order by 是不是 多余的。

Answer 5

第一 把order by去掉；
第二，如果查询结果数据量较小的话，注意建立合适的索引；如果数据量大的话，那么不走索引了，直接用并行和use_hash提示会快一点。

Answer 6

dawugui的建议很好。
to LZ：你可以建两张临时表，每次用完自动释放空间，也就不会占很大空间了。

Answer 7

--1、把WHERE中有关计算的字段全做成视图(B表中用到的字段同样做成另外一个视图)
create view V_TG_CDR09_FIX_FL_SJZ
select A.CFEE + A.LFEE + A.OTHERFEE as FeeWhere1,
       A.DISCOUNT_CFEE + A.DISCOUNT_LFEE + A.DISCOUNT_OTHERFEE as FeeWhere2,
       A.CFEE + A.LFEE + A.OTHERFEE RATE_1 as FeeSum1,
       A.DISCOUNT_CFEE + A.DISCOUNT_LFEE + A.DISCOUNT_OTHERFEE RATE_2 as FeeSum2,
       SUBSTR(A.FREE_CODE,
              INSTR(A.FREE_CODE, '|', 1, 6) + 1,
              INSTR(A.FREE_CODE, '|', 1, 7) - INSTR(A.FREE_CODE, '|', 1, 6) - 1) DISCNT_CODE,
       A.FREE_CODE,A.RESERVER3,
       A.IMSI_NUMBER,A.OTHER_PARTY,A.CALL_DURATION,
       A.CHANNEL_NO,A.START_DATE,A.USER_ID,
       A.CFEE,A.LFEE,A.OTHERFEE,A.DISCOUNT_CFEE,A.DISCOUNT_LFEE,A.DISCOUNT_OTHERFEE,A.SEQ_NO
from TG_CDR09_FIX_FL_SJZ A
--2、你的语句将可以改为
SELECT A.FeeSum1,
       B.STD_CHARGE,
       A.FeeSum2,
       B.CHARGE1,
       B.CHARGE2,
       B.CHARGE1 + B.CHARGE2 old_CHARGE,
       A.FREE_CODE,
       A.RESERVER3,
       A.DISCNT_CODE,
       A.IMSI_NUMBER,
       A.OTHER_PARTY,
       A.CALL_DURATION,
       B.RATE_PLAN_ID,
       B.PRODUCT_OBJ_ID,
       A.CHANNEL_NO,
       A.START_DATE,
       A.USER_ID,
       B.SERV_ID,
       A.CFEE,
       A.LFEE,
       A.OTHERFEE,
       A.DISCOUNT_CFEE,
       A.DISCOUNT_LFEE,
       A.DISCOUNT_OTHERFEE,
       A.SEQ_NO
  FROM F_BILLING_SJZ.UR_FIX_LOCAL_T@TO_COM_ACT1_PARAM B,
       V_TG_CDR09_FIX_FL_SJZ                            A
 WHERE A.SEQ_NO = B.TICKET_ID
   AND A.RESERVER3 = B.EVENT_TYPE
   AND (FeeWhere1 <> B.STD_CHARGE AND FeeWhere2 = B.CHARGE1 + B.CHARGE2 OR
       (FeeWhere1 = B.CHARGE1 + B.CHARGE2 AND FeeWhere1 <> B.CHARGE1) OR
       (FeeWhere1 <> B.CHARGE1 + B.CHARGE2 AND
       FeeWhere2 <> B.CHARGE1 + B.CHARGE2))
 ORDER BY B.RATE_PLAN_ID, A.USER_ID, DISCNT_CODE, A.RESERVER3;
--3、注 适当添加索引到order by 的字段上

Answer 8

一个省10多个地市，这还只是一种业务，建临时表不太好方便吧！目前可以接受不加order by.

Answer 9

F_BILLING_SJZ.UR_FIX_LOCAL_T@TO_COM_ACT1_PARAM B,
       TG_CDR09_FIX_FL_SJZ                            A你这个是本地表和远程表直接关联，性能很差的，尽量把小的那个表用到部分在远处/本地（看大表位置）复制一份，再关联性能可能就好了

Answer 10

还是建议建立物化视图试一下，至于更新可以用JOB设定在半夜无人时刷新。
把一些分析计算结果统计在视图中后，查询时的结果应该还是可以接受的。

Answer 11

order by非常费时间..数据量这么大

Answer 12

CREATE TABLE TG_CDR09_FIX_FL_ERR_SJZ？为什么要这样搞呢，你需要建立一个表，可以不查找任何数据，然后把这些数据导过来呀。

Answer 13

1.建表为什么要ORDER BY?直接去掉；
2.确保两表为HASH关联；
3.建议这一部分在创建完成的表中再作一次过滤处理：
AND (A.CFEE + A.LFEE + A.OTHERFEE <> B.STD_CHARGE AND
       A.DISCOUNT_CFEE + A.DISCOUNT_LFEE + A.DISCOUNT_OTHERFEE = B.CHARGE1+B.CHARGE2 OR
       (A.CFEE + A.LFEE + A.OTHERFEE = B.CHARGE1+B.CHARGE2 AND
       A.DISCOUNT_CFEE + A.DISCOUNT_LFEE + A.DISCOUNT_OTHERFEE <>
       B.CHARGE1) OR (A.CFEE + A.LFEE + A.OTHERFEE <> B.CHARGE1+B.CHARGE2 AND
       A.DISCOUNT_CFEE + A.DISCOUNT_LFEE +
       A.DISCOUNT_OTHERFEE <> B.CHARGE1+B.CHARGE2))