oracle一条sql运行时间很长,运行很快的两个单独的SQL在一起运行慢了20多倍

Oracle10.2.0.4 ,  自动分析统计 。

/* Formatted on 2009/11/04 10:42 (Formatter Plus v4.8.8) */

SELECT   LEVEL, b.ID, NULL parent_id, b.name_en, b.name_zh_tw, b.name_zh_cn,

b.seq, b.TYPE, b.description,

DECODE (b.action,

NULL, '',

'selectFunction(''' || b.ID || ''',''' || b.action || ''')'

) action,

DECODE (b.TYPE,

'A', 'menu.gif',

'M', 'folders.gif',

'F', 'drafts.gif',

'R', 'helper.gif',

'W', 'working.gif',

'folder.gif'

) image

FROM pf_access_log al, pf_access_node b

WHERE al.active_flag = '1'

AND b.active_flag = '1'

AND al.access_node_id = b.ID

AND al.cur_id = 'A97229D00AA120B2003FDB294D09A060'

AND b.ID IN

(

SELECT     a.ID

FROM (SELECT DISTINCT ID, app_id, parent_id, seq, name_en,

name_zh_tw, name_zh_cn, TYPE,

description, action

FROM (SELECT     a.*

FROM pf_access_node a

WHERE a.active_flag = '1'

START WITH (   EXISTS (

SELECT *

FROM pf_role_user b,

pf_role c,

pf_role_access d

WHERE c.active_flag =

'1'

AND b.role_id =

c.ID

AND c.ID =

d.role_id

AND b.user_id =

'A97229D00AA120B2003FDB294D09A060'

AND d.access_node_id =

a.ID)

OR EXISTS (

SELECT *

FROM pf_agent

WHERE active_flag =

'1'

AND agent_type =

'FUNC'

AND agent_id =

'9EEF6CDA0A86825101AFF012F9B905C1'

AND ref_id =

a.ID

AND NVL

(start_date,

SYSDATE

) <=

SYSDATE

AND NVL

(end_date,

SYSDATE

) >=

SYSDATE)

)

CONNECT BY PRIOR a.ID = a.parent_id

UNION

SELECT     a.*

FROM pf_access_node a

WHERE a.active_flag = '1'

START WITH (   EXISTS (

SELECT *

FROM pf_role_user b,

pf_role c,

pf_role_access d

WHERE c.active_flag =

'1'

AND b.role_id =

c.ID

AND c.ID =

d.role_id

AND b.user_id =

'A97229D00AA120B2003FDB294D09A060'

AND d.access_node_id =

a.ID)

OR EXISTS (

SELECT *

FROM pf_agent

WHERE active_flag =

'1'

AND agent_type =

'FUNC'

AND agent_id =

'9EEF6CDA0A86825101AFF012F9B905C1'

AND ref_id =

a.ID

AND NVL

(start_date,

SYSDATE

) <=

SYSDATE

AND NVL

(end_date,

SYSDATE

) >=

SYSDATE)

)

CONNECT BY PRIOR a.parent_id = a.ID)

ORDER BY parent_id, seq) a

WHERE 1 = 1

START WITH a.parent_id IS NULL

CONNECT BY PRIOR a.ID = a.parent_id

)

ORDER BY al.access_times   DESC

整个SQL运行需要 56秒 ， 中间那一段需要 1~2 秒 。最大的table 也就1万笔记录 。其他都是1000多笔记录的小表 。

这里我们暂且不管执行计划 。

执行中间那一段SQL语句，得出ID 为100个记录 。 将100个记录拿出来当作常量来执行，整个SQL运行需要2秒 。

SELECT   LEVEL, b.ID, NULL parent_id, b.name_en, b.name_zh_tw,

b.name_zh_cn, b.seq, b.TYPE, b.description,

DECODE (b.action,

NULL, '',

'selectFunction('''

|| b.ID

|| ''','''

|| b.action

|| ''')'

) action,

DECODE (b.TYPE,

'A', 'menu.gif',

'M', 'folders.gif',

'F', 'drafts.gif',

'R', 'helper.gif',

'W', 'working.gif',

'folder.gif'

) image

FROM pf_access_log a, pf_access_node b

WHERE a.active_flag = '1'

AND b.active_flag = '1'

AND a.access_node_id = b.ID

AND a.cur_id = 'A97229D00AA120B2003FDB294D09A060'

AND b.ID IN

(

'1B9C09500AA1213001D36DFEA6344E78',

'1B9DA9130AA1213001D36DFE69D9CB9B',

'6FD84BC10A868294006A3960CF1EF8F7',

'29D693D30AA120D601295FE866598112',

'29D7F3410AA120D601295FE8FEEB8A8A',

'6881642B0A86829400A94237E92B114C',

'6882A5E20A86829400A94237F1AE58D6',

'7D3FCA7D0AA1226F013C550F5230A625',

'6C07DE110A86829400C45EE2F8DCF1C1',

'7D84C6890AA1226F006A63D3188AEA08',

'7D7F64D50AA1226F006A63D3A4C28B4A',

'7D85603D0AA1226F006A63D37F9082E1',

'7D8603C50AA1226F006A63D3EAB9D172',

'97C087040A86308F002069B196C2786C',

'087530D00A86309E003C084AC5C55059',

'221B09DF0A8630A0003F8CD11060590C',

'9071BCCB0A9D8C7601F7309A80858BDB',

'97C125E40A86308F002069B130B5AFA6',

'B00A584E0A863099007DA670627C2E5E',

'B00AE8D60A863099007DA6706D133011',

'B3C3F77B0AA120B2009D92159244984C',

'0CD6787E0A8682940061A2EC98DB7AA2',

'B3C685880AA120B2009D921547EE4C25',

'B3C77FF60AA120B2009D9215DDAC2974',

'B3C926910AA120B2009D9215E5CFF7DE',

'B3CA58E70AA120B2009D921573C49010',

'B3CB88CC0AA120B2009D92157CA5E399',

'B3CD42630AA120B2009D9215461FE014',

'DF19B9C40AA121300094CB8BD1D167EA',

'E45537880AA1226F01BF9B456D87EFDA',

'E85929120AA121300179779569BEF303',

'E9A0E3100AA12130003AFB99A0BDB053',

'B3CD8E7F0AA120B2009D9215A72FD925',

'55B78E0E0AA1213001264EAB86952C5C',

'567E05D60AA12130019A0203F4CBC5A2',

'B7D01CF10AA1226F0087D894DFE1FEEB',

'B850B9B80AA1226F008207B231AC619B',

'B85301A90AA1226F008207B2F7BF3C60',

'B520D2210A0D085601EB5666475C4547',

'B5222C810A0D085601EB566651325E46',

'E4D249CC0AA1226F01E323825A3BF1E7',

'E4D344B00AA1226F01E32382A82BBC62',

'E4D539010AA1226F01E32382621C3A83',

'E4D667C80AA1226F01E32382FCFCF801',

'E4D6B5BD0AA1226F01E323826714663F',

'E4D6F0470AA1226F01E32382856C73C9',

'E4D7403F0AA1226F01E32382CB2F1667',

'E4D7A2580AA1226F01E32382CB3D4909',

'E4D81EFF0AA1226F01E32382B194967E',

'E4D8C42E0AA1226F01E32382863F915C',

'E4D912510AA1226F01E32382F809F5A4',

'F8BEF5020AA1226F00CC74390CD20748',

'BD2D0AC40AA1230000968B350145DA62',

'6B1DB2180AA120C000FA00941C5230C0',

'AB29C1880AA12300007B7A29B98C2F9B',

'AB2D50A20AA12300007B7A29703D1827',

'B20257900AA1226F00859D9872DF9BDC',

'BC2AA31C0A8682940097B815CBDAB94B',

'BD2F883A0AA1230000968B358E2D6EBE',

'140661CB0AA1226F00B27310A76C13BA',

'231422DE0AA1226F007696F0674DC45F',

'9D9F1CCE0AA1226F00853D85A98590D7',

'9DA06EE20AA1226F00853D856A60694D',

'AB305BD50AA12300007B7A29B0FFE0A6',

'AB30E0580AA12300007B7A29380855B8',

'AB31670D0AA12300007B7A291755DB73',

'B6B9629B0AA1226F0087D894DD0193F6',

'BD30BBB90AA1230000968B35C62DCFAF',

'BD30FDC40AA1230000968B35239B8F48',

'BD3132030AA1230000968B3587299488',

'BD31765F0AA1230000968B352BB9B66E',

'C5E1E9AC0A0D08560135324998F023E4',

'C5E251EC0A0D085601353249E4E3458B',

'E0C08A880A0D0858012D64E9FF504D70',

'E4DE14100AA1226F01E3238216F09C3F',

'E4DE65EC0AA1226F01E323826F94379E',

'CD30DEB10AA1226F00C722436198AD7E',

'C0468B0E0A0D085601353249942E457F',

'C04917380A0D08560135324913CAB513',

'CD368F7A0AA1226F00C72243DB150968',

'CD36E7D80AA1226F00C72243D129A2C6',

'CD31F4280AA1226F00C72243011AFC91',

'549753DA0AA120D601DB484D2679547F',

'8CF341120AA1226F01A734FF92C33BA7',

'8CF415310AA1226F01A734FFEE7E35E7',

'8CF492B20AA1226F01A734FF78DF5200',

'8CF4EB830AA1226F01A734FFD4E17111',

'8CF56A4C0AA1226F01A734FF651DA554',

'8CF6193F0AA1226F01A734FF700E6F90',

'8CF66B590AA1226F01A734FF2E6B5006',

'8CF6ED8D0AA1226F01A734FF5E6AF5C9',

'8CF77CC20AA1226F01A734FF16008DD5',

'8CF829440AA1226F01A734FF03C3C815',

'8CF88E3A0AA1226F01A734FF8CFE70BA',

'E4DB1D970AA1226F01E32382C317666E',

'E4DB77D00AA1226F01E32382E6906B6E',

'F1271AB70A9D8C7601DEADF08AE34924',

'F128403C0A9D8C7601DEADF0688D1B9E',

'FEF86DC10A86309E003335AE13E0C1B9',

'FEF8B76C0A86309E003335AE00D478A1'

)

ORDER BY a.access_times DESC