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

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

尽信书不如无书

Oracle的优化器也不是万能的。

还是上次的SQL，开发说有时候执行时间超过3s。

我又查了查执行计划，发现有全表扫描和索引快速全扫描。这个是不符合预期的。

抽象问题如下:

create tableempas select * fromhr.employees;

create indexinx_hire_dateonemp(hire_date);

create indexinx_emp_idonemp(employee_id);

create indexinx_mgr_idonemp(manager_id);

查询主管号码为108的最近入职的3个雇员，并按照入职时间倒序排序,

SELECTt2.*

FROM (

SELECTrid

FROM (

SELECTemp.rowid ASrid,emp.employee_idASuser_id

FROMemp, (

SELECTemployee_id

FROMemp

WHEREmanager_id=108

)t

WHEREt.employee_id=emp.employee_id

ORDER BYhire_dateDESC

)

WHERE rownum <3

)t1,emp t2

WHEREt1.rid=t2.rowid

ORDER BYt2.hire_dateDESC;

全表扫描,哈希连接

使用Hint指定连接方式

SELECT /*+use_nl(t1,t2)*/t2.*

FROM (

SELECTrid

FROM (

SELECT /*+use_nl(t,emp)*/emp.rowid ASrid,emp.employee_idASuser_id

FROMemp, (

SELECTemployee_id

FROMemp

WHEREmanager_id=108

)t

WHEREt.employee_id=emp.employee_id

ORDER BYhire_dateDESC

)

WHERE rownum <3

)t1,emp t2

WHEREt1.rid=t2.rowid

ORDER BYt2.hire_dateDESC;

看来这个不是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;