優化Oracle with全表掃描的問題(二)

http://blog.itpub.net/29254281/viewspace-1242731/

盡信書不如無書
Oracle的優化器也不是萬能的。

還是上次的SQL，開發說有時候執行時間超過3s。
我又查了查執行計劃，發現有全表掃描和索引快速全掃描。這個是不符合預期的。

抽象問題如下:

create table emp as select * from hr.employees;
create index inx_hire_date on emp(hire_date);
create index inx_emp_id on emp(employee_id);
create index inx_mgr_id on emp(manager_id);

查詢主管號碼為108的最近入職的3個僱員，並按照入職時間倒序排序,

SELECT t2.*
FROM (
SELECT rid
FROM (
SELECT emp.rowid AS rid, emp.employee_id AS user_id
FROM emp, (
SELECT employee_id
FROM emp
WHERE manager_id = 108
) t
WHERE t.employee_id = emp.employee_id
ORDER BY hire_date DESC
)
WHERE rownum < 3
) t1, emp t2
WHERE t1.rid = t2.rowid
ORDER BY t2.hire_date DESC;

全表掃描,雜湊連線

使用Hint指定連線方式

SELECT /*+use_nl(t1,t2)*/ t2.*
FROM (
SELECT rid
FROM (
SELECT /*+use_nl(t,emp)*/ emp.rowid AS rid, emp.employee_id AS user_id
FROM emp, (
SELECT employee_id
FROM emp
WHERE manager_id = 108
) t
WHERE t.employee_id = emp.employee_id
ORDER BY hire_date DESC
)
WHERE rownum < 3
) t1, emp t2
WHERE t1.rid = t2.rowid
ORDER BY t2.hire_date DESC;

看來這個不是with的問題，而是優化器對於複雜的SQL不能正確的選擇路徑。

將原來的SQL修改如下,一致性讀降為1000左右。
WITH t1
AS (SELECT to_userid
FROM friend_list f
WHERE f.userid = 411602438),
t2
AS (SELECT 'fc' AS t, rid, operTime
FROM ( SELECT /*+use_nl(t1,mc)*/
mc.ROWID rid, mc.operTime
FROM music_cover mc, t1
WHERE mc.userid = t1.to_userid
AND mc.opus_stat > 0
AND operTime IS NOT NULL
AND SYNC_FLAG = 1
ORDER BY mc.operTime DESC)
WHERE ROWNUM < 50
UNION ALL
SELECT 'yc', rid, operTime
FROM ( SELECT /*+use_nl(t1,mo)*/
mo.ROWID rid, mo.operTime
FROM music_original mo, t1
WHERE mo.userid = t1.to_userid
AND mo.opus_stat > 0
AND operTime IS NOT NULL
AND SYNC_FLAG = 1
ORDER BY mo.operTime DESC)
WHERE ROWNUM < 50
UNION ALL
SELECT 'sp', rid, operTime
FROM ( SELECT /*+use_nl(t1,mv)*/
mv.ROWID rid, mv.operTime
FROM music_video mv, t1
WHERE mv.userid = t1.to_userid
AND mv.opus_stat > 0
AND operTime IS NOT NULL
AND SYNC_FLAG = 1
ORDER BY mv.operTime DESC)
WHERE ROWNUM < 50
UNION ALL
SELECT 'bz', rid, operTime
FROM ( SELECT /*+use_nl(t1,ma)*/
ma.ROWID rid, ma.operTime
FROM music_accompany ma, t1
WHERE ma.userid = t1.to_userid
AND ma.opus_stat > 0
AND operTime IS NOT NULL
AND SYNC_FLAG = 1
ORDER BY ma.operTime DESC)
WHERE ROWNUM < 50
UNION ALL
SELECT 'rz', rid, operTime
FROM ( SELECT /*+use_nl(t1,bl)*/
bl.ROWID rid, bl.operTime
FROM blog_list bl, t1
WHERE bl.userid = t1.to_userid
AND bl.opus_stat > 0
AND operTime IS NOT NULL
ORDER BY bl.operTime DESC)
WHERE ROWNUM < 50
UNION ALL
SELECT 'xc', rid, operTime
FROM ( SELECT /*+use_nl(t1,pl)*/
pl.ROWID rid, pl.operTime
FROM photo_list pl, t1
WHERE pl.userid = t1.to_userid
AND pl.opus_stat > 0
AND operTime IS NOT NULL
ORDER BY pl.operTime DESC)
WHERE ROWNUM < 50),
t3
AS (SELECT *
FROM (SELECT TT.*, ROWNUM RN
FROM ( SELECT *
FROM t2
ORDER BY operTime DESC) TT
WHERE ROWNUM < 50)
WHERE RN >= 0),
t4
AS (SELECT /*+use_nl(t3,mc,ma,mo,mv,bl,pl)*/
t3.t opusType,
DECODE (t3.t,
'fc', 2,
'yc', 2,
'sp', 2,
'bz', 2,
'xc', 4,
'rz', 5)
type_code,
mc.userid
|| mo.userid
|| mv.userid
|| ma.userid
|| bl.userid
|| pl.userid
userId,
mc.file_url
|| mo.file_url
|| mv.file_url
|| ma.file_url
|| bl.file_url
|| pl.file_url
fileUrl,
mc.opus_Name
|| mo.opus_Name
|| mv.opus_name
|| ma.opus_name
|| bl.opus_name
|| pl.opus_name
opusName,
mc.opus_id
|| mo.opus_id
|| mv.opus_id
|| ma.opus_id
|| bl.opus_id
|| pl.opus_id
opusId,
TO_DATE (
TO_CHAR (mc.operTime, 'yyyy-mm-dd HH24:mi:ss')
|| TO_CHAR (mo.operTime, 'yyyy-mm-dd HH24:mi:ss')
|| TO_CHAR (mv.operTime, 'yyyy-mm-dd HH24:mi:ss')
|| TO_CHAR (ma.operTime, 'yyyy-mm-dd HH24:mi:ss')
|| TO_CHAR (bl.operTime, 'yyyy-mm-dd HH24:mi:ss')
|| TO_CHAR (pl.operTime, 'yyyy-mm-dd HH24:mi:ss'),
'yyyy-mm-dd HH24:mi:ss')
operTime,
mv.opus_desc
|| mo.opus_desc
|| mc.opus_desc
|| ma.opus_desc
|| bl.opus_desc
|| pl.opus_desc
opusDesc,
mv.album_id
|| mo.album_id
|| mc.album_id
|| ma.album_id
|| bl.album_id
|| pl.album_id
albumId,
mv.visit_num
|| mo.visit_num
|| mc.visit_num
|| ma.visit_num
|| bl.visit_num
|| pl.visit_num
visitNum
FROM t3
LEFT JOIN music_cover mc ON (t3.rid = mc.ROWID)
LEFT JOIN music_accompany ma ON (t3.rid = ma.ROWID)
LEFT JOIN music_original mo ON (t3.rid = mo.ROWID)
LEFT JOIN music_video mv ON (t3.rid = mv.ROWID)
LEFT JOIN blog_list bl ON (t3.rid = bl.ROWID)
LEFT JOIN photo_list pl ON (t3.rid = pl.ROWID))
SELECT /*+ ordered use_nl(t4,base) */
base.nickname,
base.showing,
DECODE (t4.type_code,
2, (SELECT al.album_name
FROM music_album al
WHERE al.album_id = t4.albumId),
4, (SELECT al.album_name
FROM photo_album al
WHERE al.album_id = t4.albumId),
5, (SELECT al.album_name
FROM blog_album al
WHERE al.album_id = t4.albumId))
albumName,
(SELECT COUNT (*)
FROM user_comment com
WHERE com.typeid = t4.type_code
AND t4.opusId = com.to_id
AND status >= 0)
commentTotal,
t4.*
FROM t4, mvbox_user.user_baseinfo base
WHERE base.userid = t4.userId
ORDER BY t4.operTime DESC;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1253090/，如需轉載，請註明出處，否則將追究法律責任。

優化Oracle with全表掃描的問題(二)

相關文章