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