INSERT INTO T_ANALYSIS (FID,USERID,FCHANNELID,ORDINCOME) SELECT SEQ_ANALYSIS.Nextval,USERID,
FCHANNELID,
ORDINCOME FROM (SELECT C.USERID, C.CID, NULL LOGDATE, TO_NUMBER(A.FCUSTERID) FCUSTERID, TO_NUMBER(A.FCUSTERID) CUSTERNAME, A.FCHANNELID, A.FCHANNELNAME, NVL(SUM(ROUND(A.ORDERCOUNT * B.FSMSDB)), 0) ORDERCOUNT, NVL(SUM(ROUND(A.ORDPEOPLE * B.FSMSUSER)), 0) ORDPEOPLE, NVL(SUM(ROUND(A.ORDINCOME * B.FSMSDB)), 0) ORDINCOME FROM (SELECT TO_CHAR(A.LOGDATE, 'YYYY-MM-DD') LOGDATE, TO_NUMBER(A.FCUSTERID) FCUSTERID, TO_NUMBER(A.FCUSTERID) CUSTERNAME, A.FCHANNELID, A.FCHANNELNAME, NVL(SUM(A.ORDERCOUNT), 0) ORDERCOUNT, NVL(SUM(A.ORDPEOPLE), 0) ORDPEOPLE, NVL(SUM(A.ORDINCOME), 0) ORDINCOME FROM (SELECT T.LOGDATE, T.FPROVINCE, T.FCUSTERID, T.CUSTERNAME, T.FCHANNELID, T.FCHANNELNAME, T.ORDERCOUNT, T.ORDPEOPLE, T.ORDINCOME, T.INCOMEFLAG FROM T_DAYLOG_SMSINCOME_FRONT T WHERE T.LOGDATE >= '26-5月 -08' AND T.LOGDATE < '30-6月 -08' AND T.PARTID >= '0526' AND T.PARTID < '0630' AND T.HSFLAG = 1 AND T.FCHANNELID NOT LIKE '106230309281%' AND T.FCHANNELID NOT LIKE '106208899281%' AND T.FCHANNELID NOT LIKE '106660609881%' AND T.FCHANNELID NOT LIKE '106230309281%' AND T.FCHANNELID NOT LIKE '106208899281%' AND T.FCHANNELID NOT LIKE '106660609881%') A WHERE 1 = 1 AND A.INCOMEFLAG = 1 GROUP BY TO_CHAR(A.LOGDATE, 'YYYY-MM-DD'), TO_NUMBER(A.FCUSTERID), A.FCHANNELID, A.FCHANNELNAME, A.FPROVINCE ORDER BY 1) A INNER JOIN (SELECT USERID, CID FROM T_MPNEW_USER WHERE USERID < 1000) C ON A.FCUSTERID = C.CID INNER JOIN (SELECT T.FID, T.FTIME, T.FSMSDB, T.FSMSUSER FROM EMBED_COOPERATION_LIST T WHERE T.FTIME >= '2008-05-26' AND T.FTIME < '2008-06-30') B ON (A.LOGDATE = B.FTIME AND B.FID = TO_CHAR(C.USERID)) WHERE 1 = 1 GROUP BY NULL, TO_NUMBER(A.FCUSTERID), A.FCHANNELID, A.FCHANNELNAME, C.CID, C.USERID ORDER BY 1) A
这个不一定!如果一个子查询中的结果集是已序的,那么很有可能会提高整个SQL的执行效率! 例如: select t1.id,t2.a,t2.b,t1.c from t1,(select id,a,b from t order by id) t2 where t1.id=t2.id 实践中体会到的,id是索引
数据库是要先读一遍数据然后在写一遍数据,只读的时候8秒钟,再插入,我想就不只8秒了,我认为写磁盘要比读磁盘还要慢;
还有,如果插入数据,那么数据库还要建立索引,如果索引列很多将会更慢!你可以拿出来你的目标表结构看看!
create table T_ANALYSIS
( FID NUMBER(10) primary key ,
USERID NUMBER(5),
CID NUMBER(5),
LOGDATE DATE,
FCUSTERID NUMBER(10),
CUSTERNAME VARCHAR2(50),
FCHANNELID VARCHAR2(20),
FCHANNELNAME VARCHAR2(50),
ORDERCOUNT NUMBER(10),
ORDPEOPLE NUMBER(10),
ORDINCOME NUMBER(10)
)
事务越短越好
(FID,USERID,FCHANNELID,ORDINCOME)
SELECT SEQ_ANALYSIS.Nextval,USERID,
FCHANNELID,
ORDINCOME
FROM (SELECT C.USERID,
C.CID,
NULL LOGDATE,
TO_NUMBER(A.FCUSTERID) FCUSTERID,
TO_NUMBER(A.FCUSTERID) CUSTERNAME,
A.FCHANNELID,
A.FCHANNELNAME,
NVL(SUM(ROUND(A.ORDERCOUNT * B.FSMSDB)), 0) ORDERCOUNT,
NVL(SUM(ROUND(A.ORDPEOPLE * B.FSMSUSER)), 0) ORDPEOPLE,
NVL(SUM(ROUND(A.ORDINCOME * B.FSMSDB)), 0) ORDINCOME
FROM (SELECT TO_CHAR(A.LOGDATE, 'YYYY-MM-DD') LOGDATE,
TO_NUMBER(A.FCUSTERID) FCUSTERID,
TO_NUMBER(A.FCUSTERID) CUSTERNAME,
A.FCHANNELID,
A.FCHANNELNAME,
NVL(SUM(A.ORDERCOUNT), 0) ORDERCOUNT,
NVL(SUM(A.ORDPEOPLE), 0) ORDPEOPLE,
NVL(SUM(A.ORDINCOME), 0) ORDINCOME
FROM (SELECT T.LOGDATE,
T.FPROVINCE,
T.FCUSTERID,
T.CUSTERNAME,
T.FCHANNELID,
T.FCHANNELNAME,
T.ORDERCOUNT,
T.ORDPEOPLE,
T.ORDINCOME,
T.INCOMEFLAG
FROM T_DAYLOG_SMSINCOME_FRONT T
WHERE T.LOGDATE >= '26-5月 -08'
AND T.LOGDATE < '30-6月 -08'
AND T.PARTID >= '0526'
AND T.PARTID < '0630'
AND T.HSFLAG = 1
AND T.FCHANNELID NOT LIKE '106230309281%'
AND T.FCHANNELID NOT LIKE '106208899281%'
AND T.FCHANNELID NOT LIKE '106660609881%'
AND T.FCHANNELID NOT LIKE '106230309281%'
AND T.FCHANNELID NOT LIKE '106208899281%'
AND T.FCHANNELID NOT LIKE '106660609881%') A
WHERE 1 = 1
AND A.INCOMEFLAG = 1
GROUP BY TO_CHAR(A.LOGDATE, 'YYYY-MM-DD'),
TO_NUMBER(A.FCUSTERID),
A.FCHANNELID,
A.FCHANNELNAME,
A.FPROVINCE
ORDER BY 1) A
INNER JOIN (SELECT USERID, CID
FROM T_MPNEW_USER
WHERE USERID < 1000) C ON A.FCUSTERID = C.CID
INNER JOIN (SELECT T.FID, T.FTIME, T.FSMSDB, T.FSMSUSER
FROM EMBED_COOPERATION_LIST T
WHERE T.FTIME >= '2008-05-26'
AND T.FTIME < '2008-06-30') B ON (A.LOGDATE =
B.FTIME AND
B.FID =
TO_CHAR(C.USERID))
WHERE 1 = 1
GROUP BY NULL,
TO_NUMBER(A.FCUSTERID),
A.FCHANNELID,
A.FCHANNELNAME,
C.CID,
C.USERID
ORDER BY 1) A
在T_ANALYSIS 中我建了一个主见FID,在userid上建了一个 索引 ,结果还是和原来一样
--Order by 语句影响执行效率
一律把order by 去掉
估计可以节省一半的时间
例如:
select t1.id,t2.a,t2.b,t1.c
from t1,(select id,a,b from t order by id) t2
where t1.id=t2.id
实践中体会到的,id是索引