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 41935 게시물 읽기
No. 41935
SQL 쿼리에서 인덱스 컬럼이 조건절에 없을때 인덱스 컬럼을 만들어서 검색속도 개선[DB 튜닝]
작성자
조장복(cjb1609)
작성일
2023-03-21 10:50
조회수
466

1. 인덱스 컬럼이 조건절에 없을때[DB 튜닝]

SELECT COUNT(Z.BBS_CD) AS count FROM



                                   (SELECT X.BBS_CD, Y.DOMAIN_CD, X.SEQ, X.TITLE,



                                          (SELECT CONTENTS



                                            FROM OP B



                                            WHERE B.SEQ = X.SEQ



                                           ) AS CONTENTS, X.SUMMARY, X.REG_DT



                                     FROM



                                         (SELECT BBS_CD, SEQ, TITLE, CONTENTS, SUMMARY,



REG_DT



                                           FROM OP



                                          WHERE 1=1



                                            AND TITLE LIKE '%' || '장애인' || '%'



                                          ) X, OP_M Y



                                     WHERE Y '%' ||X.BBS_CD|| '%'



                                    GROUP BY X.BBS_CD, Y.DOMAIN_CD, X.SEQ, X.TITLE,



X.SUMMARY, X.REG_DT, Y.USER_MENU_URL



                                    ) Z, OP_D W



                                 WHERE Z.DOMAIN_CD = W.DOMAIN_CD;

 

2. 조건절에 인덱스 컬럼을 만들어서 인덱스 Range Scan 을 통한 검색 속도 개선

SELECT COUNT(Z.BBS_CD) AS count FROM



                                   (SELECT



                                           X.BBS_CD, X.DOMAIN_CD, X.SEQ, X.TITLE,



                                          (SELECT CONTENTS



                                            FROM B



                                            WHERE B.SEQ = X.SEQ



                                              AND B.BBS_CD = X.BBS_CD



                                           ) AS CONTENTS, X.SUMMARY, X.REG_DT



                                     FROM           



                                         (SELECT  /*+ ORDERED  INDEX(X OP_B_PK) */



                                                  X.BBS_CD, Y.DOMAIN_CD, X.SEQ, X.TITLE,



X.CONTENTS, X.SUMMARY, X.REG_DT,



Y.USER_MENU_URL



                                          FROM (



                                                 (SELECT 



                                                  case WHEN substr(Y.USER_MENU_URL,instr(Y.USER_MENU_URL,'bbsCd='),6) = 'bbsCd=' THEN                                                            substr(Y.USER_MENU_URL,instr(Y.USER_MENU_URL,'bbsCd=')+6,4)



                                                       WHEN



substr(Y.USER_MENU_URL,instr(Y.USER_MENU_URL,'contentUid='),11) = 'contentUid=' THEN                                                            substr(Y.USER_MENU_URL,instr(Y.USER_MENU_URL,'contentUid=')+23,4)



                                                       WHEN substr(Y.USER_MENU_URL,instr(Y.USER_MENU_URL,'ebookfile/'),10) = 'ebookfile/' THEN                                                             substr(Y.USER_MENU_URL,instr(Y.USER_MENU_URL,'ebookfile/')+25,4)



                                                       ELSE NULL



                                                  end as BBS_CD, DOMAIN_CD,



USER_MENU_URL



                                                  FROM OP_M Y



                                                  WHERE Y.USER_MENU_URL LIKE



 '%' ||'bbsCd='|| '%'



                                                     OR Y.USER_MENU_URL LIKE



'%' ||'contentUid='|| '%'



                                                     OR Y.USER_MENU_URL LIKE



 '%' ||'ebookfile/'|| '%'



                                                  )                             



                                                  UNION



                                                 (SELECT



                                                  case WHEN



substr(Y.USER_MENU_URL,instr(Y.USER_MENU_URL,'q_ctgCd='),8) = 'q_ctgCd=' THEN                                                            substr(Y.USER_MENU_URL,instr(Y.USER_MENU_URL,'q_ctgCd=')+8,4)



                                                       ELSE NULL



                                                  end BBS_CD, DOMAIN_CD,



USER_MENU_URL



                                                  FROM OP_M Y



                                                  WHERE Y.USER_MENU_URL LIKE



 '%' ||'q_ctgCd='|| '%'



                                                  )



                                                 ) Y,  OP_B X



                                           WHERE 1=1



                                             AND X.TITLE LIKE '%' || '장애인' || '%'



                                             AND X.BBS_CD = Y.BBS_CD



                                          ) X



                                 GROUP BY X.BBS_CD, X.DOMAIN_CD, X.SEQ, X.TITLE,



X.SUMMARY, X.REG_DT, X.USER_MENU_URL



                                  ) Z, OP_D W



                                 WHERE Z.DOMAIN_CD = W.DOMAIN_CD;

 

[Top]
No.
제목
작성자
작성일
조회
41938대량 데이터 SQL 튜닝 및 병렬처리 활용 방법[DB 튜닝 및 Parallel 처리]
조장복
2023-03-21
431
41937병렬처리(Parallel Processing) 관련 사항[병렬 Parallel 처리]
조장복
2023-03-21
6095
41936Oracle 온라인 백업중 HA Failover가 실패하는 이유 및 조치 방법[DB 복구]
조장복
2023-03-21
347
41935SQL 쿼리에서 인덱스 컬럼이 조건절에 없을때 인덱스 컬럼을 만들어서 검색속도 개선[DB 튜닝]
조장복
2023-03-21
466
41934데이터 저장 [1]
costudy
2023-03-12
463
41933sqldeveloper 죽었니? 살았니? [1]
전상도
2023-02-28
584
41932row 복사하기 문의드립니다. [2]
최상민
2023-02-22
500
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.045초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다