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 41947 게시물 읽기
No. 41947
ORACLE DB SQL 쿼리 튜닝-7번째[DB 튜닝]
작성자
조장복(cjb1609)
작성일
2023-03-21 11:04
조회수
5,121

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 SUBSTR(JUSO_CD_ID, 0, 4) = SUBSTR( '1168000000' , 0, 4)
   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 SUBSTR(A.JUSO_CD_ID, 0, 4) = SUBSTR( '1168000000' , 0, 4)
   AND A.bungyo_hg_cd is null
   AND EXISTS (SELECT 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
     21   SORT (GROUP BY)
   105    TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID)
                OF 'JGCG01TT' (TABLE)
   127     NESTED LOOPS
     21      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'JGAC08TT' (TABLE)
   105      INDEX   MODE: ANALYZED (RANGE SCAN) OF 'JGCG01TT_PK'
                 (INDEX (UNIQUE))

 

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

 

[Top]
No.
제목
작성자
작성일
조회
41962GLOBAL TEMPORARY TABLE COMMIT 옵션 변경가능 한지요 ? [1]
이규동
2023-06-15
2738
41961NLS_DATE_FORMAT변경관련 질문입니다.
우태열
2023-05-13
4868
41948ORACLE DB SQL 쿼리 튜닝-8번째[DB 튜닝]
조장복
2023-03-21
5872
41947ORACLE DB SQL 쿼리 튜닝-7번째[DB 튜닝]
조장복
2023-03-21
5121
41946ORACLE DB SQL 쿼리 튜닝-6번째[DB 튜닝]
조장복
2023-03-21
3231
41945ORACLE DB SQL 쿼리 튜닝-5번째[DB 튜닝]
조장복
2023-03-21
3120
41944ORACLE DB SQL 쿼리 튜닝-4번째[DB 튜닝]
조장복
2023-03-21
1109
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.019초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다