database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41946 게시물 읽기
No. 41946
ORACLE DB SQL 쿼리 튜닝-6번째[DB 튜닝]
작성자
조장복(cjb1609)
작성일
2023-03-21 11:02
조회수
3,165

ORACLE DB SQL 쿼리 튜닝을 통한 처리속도 저하 요인을 파악하고 개선한 사항 참조하세요[DB 튜닝]


3. SQL 쿼리

3.1 튜닝전 일부 쿼리
SELECT A.HG_CD, HG_NM, GWANHAL_JIYOOK_CD, GWANHAL_SIDO_CD,
        HIGH_JONGRYU_GB, GUKRIP_GB, JUSO_CD_ID, ZIP_CODE, JUSO1,
       JUSO2, TEL_NO, FAX_NO, HOMEPAGE_JUSO, UPDATE_DT,
       UPDATE_MAN, HG_JONGRYU_GB
FROM JGDBMGR.JGAC08TT A , (select hg_cd, jg_year from JGDBMGR.jgcg01tt where jg_year = '2009' and use_yn = 'Y' group by hg_cd, jg_year ) b
WHERE  HG_JONGRYU_GB = '04'
   AND HG_NM LIKE '%원주%'
   and a.hg_cd = b.hg_cd
   and a.bungyo_hg_cd is null
ORDER BY  HG_NM

 


3.2 튜닝후 일부 쿼리
SELECT A.HG_CD, A.HG_NM, A.GWANHAL_JIYOOK_CD,
          A.GWANHAL_SIDO_CD, A.HIGH_JONGRYU_GB, A.GUKRIP_GB,
          A.JUSO_CD_ID, A.ZIP_CODE, A.JUSO1, A.JUSO2,
          A.TEL_NO, A.FAX_NO, A.HOMEPAGE_JUSO, A.UPDATE_DT, 
          A.UPDATE_MAN, A.HG_JONGRYU_GB
FROM  JGDBMGR.JGAC08TT A
WHERE A.HG_JONGRYU_GB = '04'
   AND A.HG_NM LIKE '%원주%'
   AND A.bungyo_hg_cd is null
   AND EXISTS (SELECT /*+ INDEX(B JGCG01TT_PK) */ 1
                    FROM   JGDBMGR.jgcg01tt B
                    WHERE  B.jg_year = '2009'
                        AND  A.hg_cd = B.hg_cd
                        AND  B.use_yn = 'Y')
ORDER BY  A.HG_NM

 

 

4. 실행 PLAN

4.1 튜닝전
Rows     Execution Plan
------  --------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      5   SORT (GROUP BY)
     25    HASH JOIN
      5     TABLE ACCESS   MODE: ANALYZED (FULL) OF 'JGAC08TT' (TABLE)
50829     TABLE ACCESS   MODE: ANALYZED (FULL) OF 'JGCG01TT' (TABLE)

 

4.2 튜닝 후
Rows     Execution Plan
------  --------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      5   SORT (ORDER BY)
      5    NESTED LOOPS (SEMI)
      5     TABLE ACCESS   MODE: ANALYZED (FULL) OF 'JGAC08TT' (TABLE)
      5     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID)  
                OF 'JGCG01TT' (TABLE)
      5      INDEX   MODE: ANALYZED (RANGE SCAN) OF 'JGCG01TT_PK'
                 (INDEX (UNIQUE))

 

[Top]
No.
제목
작성자
작성일
조회
41961NLS_DATE_FORMAT변경관련 질문입니다.
우태열
2023-05-13
4784
41948ORACLE DB SQL 쿼리 튜닝-8번째[DB 튜닝]
조장복
2023-03-21
5775
41947ORACLE DB SQL 쿼리 튜닝-7번째[DB 튜닝]
조장복
2023-03-21
5059
41946ORACLE DB SQL 쿼리 튜닝-6번째[DB 튜닝]
조장복
2023-03-21
3165
41945ORACLE DB SQL 쿼리 튜닝-5번째[DB 튜닝]
조장복
2023-03-21
3052
41944ORACLE DB SQL 쿼리 튜닝-4번째[DB 튜닝]
조장복
2023-03-21
1042
41943ORACLE DB SQL 쿼리 튜닝-3번째[DB 튜닝]
조장복
2023-03-21
399
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.046초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다