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博客

优化Oracle with全表扫描的问题(二)

最新文章