데이터베이스 질문과 답변
쿼리를 나름 짜 보았는데
더 좋은 방법이 잘 떠오르질 않네요
혹시 더 좋은 접근 방법이 있을까요?
SELECT
dd.rnum
,dd.DEPTH0
,(select eee.menu_nm from tn_menu eee where eee.site_no=2 and eee.menu_no = dd.DEPTH1) as DEPTH1
,(select eee.menu_nm from tn_menu eee where eee.site_no=2 and eee.menu_no = dd.DEPTH2) as DEPTH2
,(select eee.menu_nm from tn_menu eee where eee.site_no=2 and eee.menu_no = dd.DEPTH3) as DEPTH3
,(select eee.menu_nm from tn_menu eee where eee.site_no=2 and eee.menu_no = dd.DEPTH4) as DEPTH4
,dd.menu_ty_code
,dd.menu_charger_nm
,dd.menu_charger_orgnzt_nm
,dd.menu_charger_telno
FROM
(
SELECT
cc.rnum
,cc.site_nm
,cc.site_no
,cc.menu_no
,cc.menu_nm
,cc.upper_menu_no
,cc.upper_menu_nm
,cc.menu_ty_code
,cc.menu_charger_nm
,cc.menu_charger_orgnzt_nm
,cc.menu_charger_telno
,cc.menu_lvl
,cc.DEPTH0
,case when cc.DEPTH1!=0 then cc.DEPTH1 when cc.menu_lvl = 4 then (select ddd.upper_menu_no from tn_menu ddd where ddd.site_no=2 and ddd.menu_no = cc.DEPTH2 and rownum =1) else 0 end AS DEPTH1
,case when cc.DEPTH2!=0 then cc.DEPTH2 else 0 end AS DEPTH2
,case when cc.DEPTH3!=0 then cc.DEPTH3 else 0 end AS DEPTH3
,case when cc.DEPTH4!=0 then cc.DEPTH4 else 0 end AS DEPTH4
FROM
(
SELECT
bb.rnum
,bb.site_nm
,bb.site_no
,bb.menu_no
,bb.menu_nm
,bb.upper_menu_no
,bb.upper_menu_nm
,bb.menu_ty_code
,bb.menu_charger_nm
,bb.menu_charger_orgnzt_nm
,bb.menu_charger_telno
,bb.menu_lvl
,bb.DEPTH0
,case when bb.DEPTH1!=0 then bb.DEPTH1 when bb.menu_lvl = 3 then (select ccc.upper_menu_no from tn_menu ccc where ccc.site_no=2 and ccc.menu_no = bb.DEPTH2 and rownum =1) else 0 end AS DEPTH1
,case when bb.DEPTH2!=0 then bb.DEPTH2 when bb.menu_lvl = 4 then (select ccc.upper_menu_no from tn_menu ccc where ccc.site_no=2 and ccc.menu_no = bb.DEPTH3 and rownum =1) else 0 end AS DEPTH2
,case when bb.DEPTH3!=0 then bb.DEPTH3 else 0 end AS DEPTH3
,case when bb.DEPTH4!=0 then bb.DEPTH4 else 0 end AS DEPTH4
FROM
( SELECT
rownum rnum,
(select b.site_nm from tn_site b where a.site_no = b.site_no) site_nm,
a.* ,
(LEVEL-1) AS menu_lvl,
(select f.menu_nm from tn_menu f where f.menu_lvl = 0 and f.site_no=2) AS DEPTH0 ,
case when LEVEL-1 = 1 then a.menu_no when LEVEL-1 = 2 then a.upper_menu_no else 0 end AS DEPTH1,
case when LEVEL-1 = 2 then a.menu_no when LEVEL-1 = 3 then a.upper_menu_no else 0 end AS DEPTH2,
case when LEVEL-1 = 3 then a.menu_no when LEVEL-1 = 4 then a.upper_menu_no else 0 end AS DEPTH3,
case when LEVEL-1 = 4 then a.menu_no else 0 end AS DEPTH4
FROM
(SELECT menu.site_no ,
menu.menu_no ,
menu.upper_menu_no ,
(SELECT menu_nm
FROM tn_menu c
WHERE menu.site_no = c.site_no
AND menu.upper_menu_no = c.menu_no
) AS upper_menu_nm ,
menu.uri_wrd ,
menu.menu_nm ,
menu.menu_ty_code ,
menu.post_ty_code ,
menu.sub_main_at ,
menu.link_url ,
menu.link_trgt ,
menu.board_no ,
st.board_ty ,
menu.program_no ,
menu.menu_ordr ,
menu.meta_tag ,
menu.gnb_at ,
menu.lnb_at ,
menu.dspy_at ,
menu.menu_charger_nm ,
menu.menu_charger_orgnzt_nm ,
menu.menu_charger_telno ,
menu.menu_charger_updt_dttm
FROM tn_pblcate_menu menu,
tn_board_st st
WHERE menu.site_no=2
AND menu.usg_at = 'Y'
AND menu.board_no = st.board_no(+)
) a START
WITH menu_no = 1 CONNECT BY PRIOR menu_no = upper_menu_no
ORDER SIBLINGS BY menu_ordr
) bb
) cc
) dd
;
1. 어느 단계에서 느린것인지?
- 단계별로 수행해서 확인해 보세요.
- a 단계인지? bb 단계인지? cc? dd?
- 스칼라서브쿼리 때문에 느린건 아닌지?
2. 쓸모 없이 조회하고 버려지는 항목이 많네요?
- 최종 단계에 필요하지 않은 항목을 서브쿼리에서 조회하고 있네요?
3. Case 문이 상당히 복잡하고, 무의미하게 사용되었네요.
- 원하시는 결과물(depth0 ~ depth4)에 대한 설명을 해주세요.
SELECT ROWNUM rnum
, (SELECT b.site_nm FROM tn_site b WHERE b.site_no = a.site_no) site_nm
, REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(menu_nm, ','), '[^,]+', 1, 1) depth0
, REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(menu_nm, ','), '[^,]+', 1, 2) depth1
, REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(menu_nm, ','), '[^,]+', 1, 3) depth2
, REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(menu_nm, ','), '[^,]+', 1, 4) depth3
, REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(menu_nm, ','), '[^,]+', 1, 5) depth4
, menu_ty_code
, menu_charger_nm
, menu_charger_orgnzt_nm
, menu_charger_telno
FROM (SELECT site_no
, menu_no
, upper_menu_no
, menu_nm
, menu_ordr
, menu_ty_code
, menu_charger_nm
, menu_charger_orgnzt_nm
, menu_charger_telno
FROM tn_pblcate_menu menu
WHERE site_no = 2
AND usg_at = 'Y'
) a
START WITH menu_no = 1
CONNECT BY PRIOR menu_no = upper_menu_no
ORDER SIBLINGS BY menu_ordr
;
감사합니다!
제가 너무 쓸모 없는 단계에서 스칼라 서브쿼리 호출을 많이 했네요 진짜 ㅠㅠ
regexp_substr은 모르는 범위였는데 배워갑니다!
감사합니다!
) 버튼을 클릭하여 작성 하시면 됩니다.