create or replace view plan_result_view_bak as
select A."AREA_NAME",A."CAPTION",A."LOAD",A."CASE_ID",A."PHYUNIT_ID",A."DATA_TIME",A."MW",A.mw as GT1,
B.mw as GT2,
C.mw as ST1,
D.mw as D25,
E.mw as D26,
F.mw as D27,
O.mw as D28,
G.mw as G05,
H.mw as G06,
I.mw as G07,
J.mw as G08,
K.mw as T22,
L.mw as T23,
M.mw as G03,
N.mw as G04,
'' as Importation,
'' as group1,
'' as Importation1,
'' as group2,
'' as Importation2,
'' as group3,
'' as Importation3
from
(select * from plan_result_view t where t.caption='GT1')A,
(select data_time,mw from plan_result_view_phy t where t.caption='GT2')B,
(select data_time,mw from plan_result_view_phy t where t.caption='ST1')C,
(select data_time,mw from plan_result_view_phy t where t.caption='D25')D,
(select data_time,mw from plan_result_view_phy t where t.caption='D26')E,
(select data_time,mw from plan_result_view_phy t where t.caption='D27')F,
(select data_time,mw from plan_result_view_phy t where t.caption='D28')O,
(select data_time,mw from plan_result_view_phy t where t.caption='G05')G,
(select data_time,mw from plan_result_view_phy t where t.caption='G06')H,
(select data_time,mw from plan_result_view_phy t where t.caption='G07')I,
(select data_time,mw from plan_result_view_phy t where t.caption='G08')J,
(select data_time,mw from plan_result_view_phy t where t.caption='T22')K,
(select data_time,mw from plan_result_view_phy t where t.caption='T23')L,
(select data_time,mw from plan_result_view_phy t where t.caption='G03')M,
(select data_time,mw from plan_result_view_phy t where t.caption='G04')N
where A.data_time=B.data_time
and A.data_time=C.data_time
and A.data_time=D.data_time
and A.data_time=E.data_time
and A.data_time=F.data_time
and A.data_time=G.data_time
and A.data_time=H.data_time
and A.data_time=I.data_time
and A.data_time=J.data_time
and A.data_time=K.data_time
and A.data_time=L.data_time
and A.data_time=M.data_time
and A.data_time=N.data_time
and A.data_time=O.data_time
order by A.data_time
这条SQL语句查询速度是
SELECT STATEMENT, GOAL = ALL_ROWS             Cost=36000792477     Cardinality=16520828626489     Bytes=9.10297657319544E15     Time=432009510     CPU cost=1.27784592177941E17


select A."AREA_NAME",A."CAPTION",A."LOAD",A."CASE_ID",A."PHYUNIT_ID",A."DATA_TIME",A."MW",A.mw as GT1,
(select mw from plan_result_view_phy t where t.caption='GT2' and   A.case_id=t.CASE_ID and A.data_time=t.data_time) as GT2,
(select mw from plan_result_view_phy t where t.caption='ST1' and   A.case_id=t.CASE_ID and A.data_time=t.data_time) as ST1,
(select mw from plan_result_view_phy t where t.caption='D25' and   A.case_id=t.CASE_ID and A.data_time=t.data_time) as D25,
(select mw from plan_result_view_phy t where t.caption='D26' and   A.case_id=t.CASE_ID and A.data_time=t.data_time) as D26,
(select mw from plan_result_view_phy t where t.caption='D27' and   A.case_id=t.CASE_ID and A.data_time=t.data_time) as D27,
(select mw from plan_result_view_phy t where t.caption='D28' and   A.case_id=t.CASE_ID and A.data_time=t.data_time) as D28,
(select mw from plan_result_view_phy t where t.caption='G05' and   A.case_id=t.CASE_ID and A.data_time=t.data_time) as G05,
(select mw from plan_result_view_phy t where t.caption='G06' and   A.case_id=t.CASE_ID and A.data_time=t.data_time) as G06,
(select mw from plan_result_view_phy t where t.caption='G07' and   A.case_id=t.CASE_ID and A.data_time=t.data_time) as G07,
(select mw from plan_result_view_phy t where t.caption='G08' and   A.case_id=t.CASE_ID and A.data_time=t.data_time) as G08,
(select mw from plan_result_view_phy t where t.caption='T22' and   A.case_id=t.CASE_ID and A.data_time=t.data_time) as T22,
(select mw from plan_result_view_phy t where t.caption='T23' and   A.case_id=t.CASE_ID and A.data_time=t.data_time) as T23,
(select mw from plan_result_view_phy t where t.caption='G03' and   A.case_id=t.CASE_ID and A.data_time=t.data_time) as G03,
(select mw from plan_result_view_phy t where t.caption='G04' and   A.case_id=t.CASE_ID and A.data_time=t.data_time) as G04,
'' as Importation,
'' as group1,
'' as Importation1,
'' as group2,
'' as Importation2,
'' as group3,
'' as Importation3
from (select * from plan_result_view t where t.caption='GT1')A
order by A.data_time
调整后的语句
SELECT STATEMENT, GOAL = ALL_ROWS             Cost=3367     Cardinality=3317     Bytes=295213     Time=41     CPU cost=532112313


原来SQL语句调优是这么关键- -