SELECT *
FROM (SELECT CASE
WHEN V2.FIELD1 > 0 THEN
'B'
ELSE
'R'
END VFIELD1,
CASE
WHEN V2.FIELD2 > 0 THEN
'B'
ELSE
'R'
END VFIELD2,
V2.FIELD3 || '''' || V2.FIELD4 ||
V2.FIELD5 AS CTMODEL,
(SELECT rtrim(LIST(DISTINCT FIELD6 || '/'), '/')
FROM TABLE2
WHERE ID = V2.ID) FIELD6LIST,
(SELECT rtrim(LIST(DISTINCT FIELD7 || '/'), '/')
FROM TABLE3
WHERE ID = V2.ID) FIELD7LIST,
(SELECT T4.STATE
FROM TABLE4 T4
WHERE T4.CNO = V2.CINNO) INSTATE,
(SELECT T4.STATE
FROM TABLE4 T4
WHERE T4.CNO = V2.COUTNO) OUTSTATE,
(SELECT CASE
WHEN COUNT(*) > 0 THEN
'Y'
ELSE
'N'
END
FROM TABLE5 T5
LEFT JOIN TABLE6 PARTITION(PARTCUR) T6
ON T5.MID = T6.MID
LEFT JOIN TABLE7 T7
ON T7.JCODE = T6.JCODE
WHERE T5.ID = V2.ID
AND T6.JCODE = 'C1'
and rownum = 1) ISMID,
(SELECT CASE
WHEN COUNT(*) > 0 THEN
'Y'
ELSE
'N'
END
FROM TABLE5 T5
LEFT JOIN TABLE6 PARTITION(PARTCUR) T6
ON T5.MID = T6.MID
LEFT JOIN TABLE7 T7
ON T7.JCODE = T6.JCODE
WHERE T5.ID = V2.ID
AND T6.JCODE = 'C2'
AND ROWNUM = 1) ISTOP,
(SELECT CASE
WHEN COUNT(V1.VID) > 0 THEN
'Y'
ELSE
'N'
END
FROM VIEW1 V1
WHERE V1.ID = V2.ID
AND V1.TYPE = 'T') ISPREE
FROM VIEW2 V2) TBVIEW2大概有100多万条记录,VIEW1大概30W条记录,其他表不多,在不带任何where条件的情况下,如何最大地提升此查询的速度?请高手指点分不多了,下次有分单独开贴给,谢谢了!
FROM (SELECT CASE
WHEN V2.FIELD1 > 0 THEN
'B'
ELSE
'R'
END VFIELD1,
CASE
WHEN V2.FIELD2 > 0 THEN
'B'
ELSE
'R'
END VFIELD2,
V2.FIELD3 || '''' || V2.FIELD4 ||
V2.FIELD5 AS CTMODEL,
(SELECT rtrim(LIST(DISTINCT FIELD6 || '/'), '/')
FROM TABLE2
WHERE ID = V2.ID) FIELD6LIST,
(SELECT rtrim(LIST(DISTINCT FIELD7 || '/'), '/')
FROM TABLE3
WHERE ID = V2.ID) FIELD7LIST,
(SELECT T4.STATE
FROM TABLE4 T4
WHERE T4.CNO = V2.CINNO) INSTATE,
(SELECT T4.STATE
FROM TABLE4 T4
WHERE T4.CNO = V2.COUTNO) OUTSTATE,
(SELECT CASE
WHEN COUNT(*) > 0 THEN
'Y'
ELSE
'N'
END
FROM TABLE5 T5
LEFT JOIN TABLE6 PARTITION(PARTCUR) T6
ON T5.MID = T6.MID
LEFT JOIN TABLE7 T7
ON T7.JCODE = T6.JCODE
WHERE T5.ID = V2.ID
AND T6.JCODE = 'C1'
and rownum = 1) ISMID,
(SELECT CASE
WHEN COUNT(*) > 0 THEN
'Y'
ELSE
'N'
END
FROM TABLE5 T5
LEFT JOIN TABLE6 PARTITION(PARTCUR) T6
ON T5.MID = T6.MID
LEFT JOIN TABLE7 T7
ON T7.JCODE = T6.JCODE
WHERE T5.ID = V2.ID
AND T6.JCODE = 'C2'
AND ROWNUM = 1) ISTOP,
(SELECT CASE
WHEN COUNT(V1.VID) > 0 THEN
'Y'
ELSE
'N'
END
FROM VIEW1 V1
WHERE V1.ID = V2.ID
AND V1.TYPE = 'T') ISPREE
FROM VIEW2 V2) TBVIEW2大概有100多万条记录,VIEW1大概30W条记录,其他表不多,在不带任何where条件的情况下,如何最大地提升此查询的速度?请高手指点分不多了,下次有分单独开贴给,谢谢了!
1、将view1 和 view2 的数据放入中间临时表,然后建立相关索引进行关联查询。
2、增加/* parallel(table_name,8) */ 这样的多线程操作提示
3、直接修改session目前的session多线程操作,然后再查询。
alter session enable parallel query;
FROM TABLE2
WHERE ID = V2.ID) FIELD6LIST,
(SELECT rtrim(LIST(DISTINCT FIELD7 || '/'), '/')
FROM TABLE3
WHERE ID = V2.ID) FIELD7LIST,
(SELECT T4.STATE
FROM TABLE4 T4
WHERE T4.CNO = V2.CINNO) INSTATE,
(SELECT T4.STATE
FROM TABLE4 T4
WHERE T4.CNO = V2.COUTNO) OUTSTATE,
(SELECT CASE
WHEN COUNT(*) > 0 THEN
'Y'
ELSE
'N'
END
FROM TABLE5 T5
LEFT JOIN TABLE6 PARTITION(PARTCUR) T6
ON T5.MID = T6.MID
LEFT JOIN TABLE7 T7
ON T7.JCODE = T6.JCODE
WHERE T5.ID = V2.ID
AND T6.JCODE = 'C1'
and rownum = 1) ISMID,
(SELECT CASE
WHEN COUNT(*) > 0 THEN
'Y'
ELSE
'N'
END
FROM TABLE5 T5
LEFT JOIN TABLE6 PARTITION(PARTCUR) T6
ON T5.MID = T6.MID
LEFT JOIN TABLE7 T7
ON T7.JCODE = T6.JCODE
WHERE T5.ID = V2.ID
AND T6.JCODE = 'C2'
AND ROWNUM = 1) ISTOP,
(SELECT CASE
WHEN COUNT(V1.VID) > 0 THEN
'Y'
ELSE
'N'
END
FROM VIEW1 V1
WHERE V1.ID = V2.ID
AND V1.TYPE = 'T') ISPREE
(1)将view1 和view2建成物化视图,增量刷新
(2)建立索引,通过查询执行计划,确定所建立的索引能用上。提示:在sql中尽量少用函数,函数在执行计划中无法体现。