1 / 212

2004. 6. 신민수

ORACLE 강좌 TUNING & SQL 활용. 2004. 6. 신민수. 튜닝 & SQL 활용 1 튜닝 ? 부분범위처리 인덱스 조인. 들어가기 전에 …. 참고서적 : 대용량 데이타베이스 강좌 참고문서 : 엔코아 교육자료 , 기타 어디선가 받아놓았던 자료들 … ** SQL 문장 튜닝 공부하면서 정리해놓은 문서입니다 . 완성된것은 아니나 필요하신 분들에게 도움이 되었으면 합니다. 튜닝 ?.

sutton
Download Presentation

2004. 6. 신민수

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ORACLE 강좌 TUNING & SQL활용 2004. 6. 신민수

  2. 튜닝 & SQL활용 1 • 튜닝? • 부분범위처리 • 인덱스 • 조인

  3. 들어가기 전에… • 참고서적 : 대용량 데이타베이스 강좌 • 참고문서 : 엔코아 교육자료, 기타 어디선가 받아놓았던 자료들… • ** SQL문장 튜닝 공부하면서 정리해놓은 문서입니다. 완성된것은 아니나 필요하신 분들에게 도움이 되었으면 합니다.

  4. 튜닝? • 데이터량, WHERE절, 인덱스사용, 최적화 등을 통해 SQL문의 성능을 판단하는 작업이 SQL 튜닝이다. • 개발자는 자신의 시스템의 성능에 대한 책임을 다해야 한다. • 많은 사람들이 튜닝은 시스템의 성능이 떨어지고 사용자들이 불만을 하기 시작할 때 시작해도 무방한 것으로 생각한다. • 개발에 사용되는 SQL 문장을 Tuning한다. • 개발자는 Oracle을 사용할 경우라면, EXPLAIN PLAN 등의 유틸리티의 사용에 친숙해지고 그들의 프로그램에 광범위하게 적용할 수 있어야 한다. • Tuning은 결코 끝나지 않는 업무이다. • 시스템을 계속 운영하면서 성능을 지켜보아야 하고, 시스템의 사용자 요구사항을 분석하며, 여러 가지 도구를 이용하여 시스템을 지속적이고 효과적으로 Tuning해야 한다.

  5. 응답시간 문제 관리 • 대부분의 시스템에서 성능의 척도는 응답시간으로 관리 • 수용 가능한 응답시간의 정의 • 응답 시간의 고려는 On-Line 사용자로 한다 • 함수의 95% 정도는 2.5초 이하에 수행되어야 한다 • 전체 응답 시간이 10초를 초과해서는 안 된다 • 보고서 작업의 경우 중심 업무시간대에서 3분을 넘지 않도록 한다 • 이외에 GUI화면에서 한 field 에서 다른 field 로의 이전 시간 간격을 1초 미만으로 하는 제한을 둘 수 있다

  6. DB 환경 표준에 대한 설정 • 개발을 하기 전에 기본적인 환경에 대한 표준을 정할 필요가 있다. • 데이터베이스 SID :구축하고자 하는 업무와 의미가 맞는 데이터베이스의 이름. • 대규모 롤백 세그먼트:하나 이상의 대규모 롤백 세그먼트를 할당하는 것은 개발자들이 대규모 갱신 작업에서 이를 적절히 활용할 수 있도록 한다. • 테이블스페이스 구조:I/O 분산과 복구에 대한 유용성을 위해서라도 최소한 데이터와 인덱스는 다른 테이블스페이스에 나뉘어져야 한다. • 객체 명명 표준:테이블, 인덱스, 패키지, 트리거 등을 생성하기 전에 반드시 명명규칙을 정해야 한다. • 객체에 합당한 두 문자어나 줄임어의 사용을 충분히 하여 길고 번거로운 이름의 사용을 피하도록 한다. • 객체 구조:일관적인 객체구조의 정의는 성능향상의 관점이나 문제해결, 유지보수에도 이롭다.

  7. 일반적인 오류 • 충분히 숙고 되지 않은 SQL 문장을 이용한 프로그램에서 느린 응답시간을 보인다 • 적절하지 못한 인덱스의 사용 • 적절하지 않은 Optimizer의 사용

  8. 튜닝전 준비사항 • 데이터모델의 세부 다이아그램(ERD 등) • 테이블의 평균 로우 수, 한 로우의 평균크기 • 인덱스와 제약사항(Constraint)에 대한 모든 정보 • 모든 트리거와 그들의 기능 • 어느 프로그램이 어느 때에, 또 얼마나 많은 사용자에 의해 사용되는지? • 최대 집중시(Peak Time) 초당 수행되는 트랜잭션 크기 • 데이터정리 전략에 대한 문서(스토리지 등)

  9. 튜닝이 필요한 데이터모델 • 일대일 관계 • 일대일 관계는 불필요한 테이블 Join을 유발한다. • 많은 컬럼들을 포함하고 그 중 한 컬럼이 LONG유형일 경우 허용되기도 한다. • 온라인 트랜잭션 처리에 과도하게 큰 테이블이 존재하는 경우. • 다섯 개 이상의 테이블을 Join하는 SQL문이 다수 존재할 경우. • SUM을 포함하는 SQL문이 다수 존재할 경우. • 서브쿼리에 MAX나 MIN을 포함하는 SQL문이 다수 존재할 경우. • 모든 테이블이 단일컬럼 인덱스를 가질 경우. • Full Table Scan을 자주 유도하는 SQL문

  10. Autotrace & EXPLAIN PLAN ① SQLPLUS의 Autotrace 기능 활용 SET AUTOTRACE TRACEONLY EXP(SELECT시 PLAN만 보여줌) : SET AUTOTRACE ON ② PLAN_TABLE을 SQL문으로 조회하는 방법 플랜테이블 생성  인덱스 생성  explain plan set statement_id = 'a1' for  SQL> def id=a1  SQL> @plan

  11. 실행계획(Plan Table) CREATETABLE PLAN_TABLE ( STATEMENT_IDVARCHAR2(30), TIMESTAMP DATE, REMARKS VARCHAR2(80), OPERATION VARCHAR2(30), OPTIONS VARCHAR2(30), OBJECT_NODE VARCHAR2(128), OBJECT_OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(30), OBJECT_INSTANCE NUMBER, OBJECT_TYPE VARCHAR2(30), OPTIMIZER VARCHAR2(255), SEARCH_COLUMNS NUMBER, ID NUMBER, PARENT_ID NUMBER, POSITION NUMBER, COST NUMBER, CARDINALITY NUMBER, BYTES NUMBER, OTHER_TAG VARCHAR2(255), PARTITION_START VARCHAR2(255), PARTITION_STOP VARCHAR2(255), PARTITION_ID NUMBER, OTHER LONG, DISTRIBUTION VARCHAR2(30)) explain plan set statement_id = '1' into plan_table for Select * from wcm_content; SELECT lpad(operation, length(operation)+2*(level-1))|| decode(id,0,'Cost Estimate:'||decode(position,'0', 'N/A',position), null)||''||options|| decode(object_name,null,null,':')||rpad(object_owner, length(object_name)+1,',')||object_name|| decode(object_type,'UNIQUE',' (U) ', 'NIN_UNIQUE', '(NU)',null)||decode(object_instance,null,null,'('||object_instance||')') FROM PLAN_TABLE START with ID=0 AND STATEMENT_ID=&1 CONNECT BY PRIOR ID=PARENT_ID AND STATEMENT_ID=&1

  12. 부분범위 처리 부분범위처리란, 처리할 내용이 아무리 많더라도 일단 운반단위에 차게 되면 데이터를 Return 시키고, 사용자가 다음 Action을 취할 때까지 기다리므로 처리범위의 크기에 영향을 받지 않도록 하는 기법을 말한다. - 조건을 만족하는 전체 집합이 아닌 일부부만 액세스 - 데이타량이 많아도 퍼포먼스에 지장이 없고, 오히려 향상 - 인덱스나 클러스터를 적절히 활용한 소트의 대체(Order by를 안하도록 한다.)  원하는 정보의 빠른 조회 혹은 응답속도의 향상 SELECT ENAME, SAL FROM EMP WHERE ENAME > ' ‘ -- 실행 계획이 index를 탄다. 자동으로 ename으로 소트 되어져서 나온다. -- 전체 범위의 악당인 order by를 피할 수 있다. SELECT ENAME, SAL FROM EMP ORDER BY ENAME -- 전체 범위 처리. -- 전체를 읽고 난 다음 ename로 정렬한다.

  13. 부분범위 처리 기타 • 그 외.. • - 테이블을 액세스하지 않고 인덱스만 사용하도록 유도 • EXISTS의 활용(in보다 효율적, EXISTS는 옵티마이저가 부분범위처리한다) • Order by생략(불필요한 정렬작업 생략) • Min/Max • - ROWNUM의 활용 -> stop key를 이용 • Query를 이원화 하여 일부분씩 스캔 하도록 유도(소트의 범위를 줄임) • 등의 활용예를 통해 부분범위 처리를 확인해 볼 수 있다. • 옵티마이저 힌트는 절대 적인 것은 아니다. 힌트를 줘 놓고 힌트를 쓸수 있는 요소가 없으면 힌트가 사용 되지 않는다. 때문에 멍텅구리 조건을 주는 경우가 있다.

  14. 부분범위처리 전체범위처리 2 차 가 공 1 차 스 캔 2 차 가 공 1 차 스 캔 운반단위 운반단위 조건을 만족하는 Row 수가 Array Size 에 도달되면 멈춤(stop key) Full Range Scan 후 가공하여 Array Size 만큼 추출 부분범위 처리의 개념

  15. 부분범위 처리 (SORT를 대신하는 인덱스) SELECT * FROM PRODUCT WHERE YMD = '951023' AND ITEM LIKE 'AB%'ORDER BY YMD, ITEM SELECT * FROM PRODUCT WHERE YMD = '951023' AND ITEM LIKE 'AB%'; 부 분 스 캔 SORT 1 일 분 량 전 체 스 캔 운반단위 운반단위 . . . . . . . . INDEX(YMD) TABLE INDEX (YMD+ITEM) TABLE 싱글인덱스 결합인덱스

  16. 부분범위 처리 (SORT 대체) SQL> SELECT --+ INDEX_DESC(A orddate) ORDDATE, CUSTNO FROM ORDER1T A WHERE ORDDATE between '940101' and '941130' SQL> SELECT ORDDATE, CUSTNO FROM ORDER1T WHERE ORDDATE between '940101' and '941130' ORDER BY ORDDATE DESC 0.01 sec 5.2 sec 21200 SORT ORDER BY 21200 TABLE ACCESS BY ROWID ORDER1T 21201 INDEX RANGE SCAN ORD_ORDDATE 20 INDEX RANGE SCAN DESCENDING ORDDATE SQL> SELECT --+ INDEX_DESC(A orddate) ORDDATE, CUSTNO FROM ORDER1T A WHERE ORDDEPT LIKE '7%' AND ORDDATE <= '991231' SQL> SELECT ORDDATE, CUSTNO FROM ORDER1T WHERE ORDDEPT LIKE '7%' ORDER BY ORDDATE DESC 12.5 sec 0.02 sec 42000 SORT ORDER BY 42000 TABLE ACCESS BY ROWID ORDER1T 42001 INDEX RANGE SCAN ORD_ORDDEPT 20 INDEX RANGE SCAN DESCENDING ORDDATE Index를 타게 하기 위해 멍텅구리조건 : ORDDATE <= ‘991231’

  17. SELECTDEPT, SUM(QTY).FROM PRODUCT WHERE DEPT LIKE '12%' GROUP BY DEPT; SELECT DEPT, SUM(QTY)FROM PRODUCT WHERE DEPT LIKE '12%' GROUP BY DEPT; GROU P B Y GROU P B Y 운반단위 운반단위 INDEX (DEPT) TABLE INDEX (DEPT+QTY) 부분범위 처리 (INDEX만 처리) 싱글인덱스 결합인덱스

  18. SELECT/*+ INDEX_DESC( A INDEX1) */ SEQ + 1 FROM PRODUCT A WHERE DEPT = '12300' AND ROWNUM = 1; SELECT MAX(SEQ) + 1FROM PRODUCT WHERE DEPT = '12300'; S O R T 운반단위 MAX(SEQ)+1 INDEX (DEPT) TABLE 부분범위 처리 (Max 처리) 싱글인덱스 결합인덱스 운반단위 SEQ + 1 INDEX 1 (DEPT+SEQ)

  19. 부분범위 처리 (Max 처리 실습) SELECT /*+ index_desc(emptest HIREDATE_IDX) */ hiredate FROM EMPTEST WHERE hiredate < '99991231' AND ROWNUM = 1; SQL> SELECT MAX(hiredate) FROM EMPTEST Index를 타게 하기 위해 멍텅구리조건 : ord_date < '99991231' SELECT /*+ INDEX_DESC( A PK_EMPNO3) */ empno + 1 FROM EMPTEST2 A WHERE DEPTNO = 10 AND ROWNUM = 1; SELECT MAX(empno) + 1 FROM EMPTEST2 WHERE DEPTNO = 10; Index : DEPT Index : DEPTNO +empno Max를 사용시 fullscan을 인덱스를 통해 부분범위처리로 처리(소팅을 하지 않음) 테이블스캔 안하고 인덱스만 스캔할수 있다면??

  20. 부분범위 처리 SQL 예제 SQL> SELECT MAX(ORDDATE) FROM ORDER1T WHERE ORDDEPT = '430' AND STATUS = '30' SQL> SELECT --+ INDEX_DESC(A dept_date) ORDDATE FROM ORDER1T A WHERE ORDDEPT = '430' AND STATUS='30' AND ROWNUM = 1 MAX 처리 2.53 sec 0.01 sec 1 SORT AGGREGATE 15230 INDEX RANGE SCAN DEPT_DATE_ST 1 COUNT STOPKEY 1 TABLE ACCESS BY ROWID ORDER1T 2 INDEX RANGE SCAN DESCENDING DEPT_DATE SQL> SELECT TYPE, COUNT(*) FROM ORDER2T WHERE ITEM LIKE 'HJ%' GROUP BY TYPE SQL> SELECT STATUS, COUNT(*) FROM ORDER2T WHERE ITEM LIKE 'HJ%' GROUP BY STATUS INDEX 만 처리 10.3 sec 2.5 sec 20 SORT GROUP BY 36631INDEX RANGE SCAN ITEM_STATUS 20 SORT GROUP BY 36630 TABLE ACCESS BY ROWID ORDER2T 36631 INDEX RANGE SCAN ITEM_STATUS

  21. 부분범위 처리 (EXISTS) SELECT 1 INTO :CNT FROM DUAL WHERE EXISTS (SELECT 'X' FROM ITEM_TAB WHERE DEPT = '101' AND SEQ > 100 ) . . . . . . . IF CNT > 0 . . . . . . . SELECT COUNT(*) INTO :CNT FROM ITEM_TAB WHERE DEPT = '101' AND SEQ > 100 . . . . . . . IF CNT > 0 . . . . . . . . . . 운반 단위 운반 단위 O COUNT ..... ..... X TABLE TABLE INDEX (DEPT) INDEX (DEPT)

  22. SELECT COUNT(*) INTO :CNT FROM ITEM_TAB WHERE DEPT = '101' AND SEQ > 100 . . . . . . . . IF CNT > 0 . . . . . . . . 운반 단위 COUNT ..... ..... TABLE INDEX (DEPT) 부분범위 처리 (ROWNUM) SELECT 1 INTO :CNT FROM ITEM_TAB WHERE DEPT = '101' AND SEQ > 100 AND ROWNUM = 1 . . . . . . . . IF CNT > 0 . . . . . . . . 운반 단위 O X TABLE INDEX (DEPT)

  23. 부분범위 처리 사용원칙 ?

  24. 부분범위 처리 사용원칙

  25. 인덱스 • 인덱스는 "값 + rowid" 로 구성되어 있다. • 동일한 값을 같은 경우 rowid순으로 소트 되어 있다. • 때문에 같은 블럭에서 테이블 액세스가 이루어 진다.

  26. 부장 000062BE.0001.0001 인덱스의 구조 SELECT empno, ename, job FROM emp WHERE job between '부장' and '이사' EMPNO ENAME JOB INDEX-KEY ROWID 7654 강감찬 부장 과장 0000A95B.0002.0001 7900 류관순 과장 과장 0000A95B.0005.0001 과장 0000E62E.0009.0001 7689 황진이 과장 과장 0000E9BE.0002.0001 7934 변강쇠 부장 7499 이순신 차장 부장 000062BE.0003.0001 7369 안중근 이사 부장 000093A6.0005.0001 7844 조자룡 차장 부장 000093B2.000B.0001 7839 장보고 과장 이사 000069C5.0001.0001 7531 신윤복 차장 SORT된 결과 차장 0000E9BE.0002.0001 7432 김유신 부장 차장 0000E9BE.0005.0001 7856 홍길동 과장 차장 0000E9BE.000B.0001 7827 김두환 부장 TABLE (EMP) INDEX (JOB)

  27. 인덱스가 없는 테이블 액세스

  28. 인덱스를 이용한 테이블 액세스

  29. 인덱스 컬럼 • 검색조건에 빈번히 이용되는 컬럼 혹은 컬럼의 집합(SQL문의 WHERE절) • ORDER BY, GROUP BY, UNION, DISTINCT(TEMP TABLESPACE) 등 정렬이 필요한 구문에 빈번히 이용되는 컬럼 혹은 컬럼의 집합 • 집계함수의 이용에 사용되는 컬럼 혹은 컬럼의 집합 • 수정이 잦은 컬럼은 인덱스 지정 억제(오히려 부하를 줄수 있다) • 외부키(Foreign Key)는 클러스터인덱스 지정

  30. 잠깐! 클러스터 테이블?? • 클러스터는 하나 이상의 테이블이 물리적으로 같이 저장되어 있는 그룹을 말한다. 관련된 행들이 같이 저장되어 있다면 디스크 액세스는 시간을 개선시킨다. • 클러스터 안에 있는 관련된 테이블의 컬럼을 cluster key 라고 말한다.

  31. 인덱스 적용 대상 • 중,대규모 테이블 • 대상 데이터의 10% 이내의 데이터 요구인 경우(분포도) • 스캔의 범위를 줄이고자 할때 • 정렬을 필요로 하는 경우 • 테이블에 접근하지 않고 인덱스만으로 데이터 요구를 해결하고자 하는 경우

  32. 인덱스 적용시 고려사항 • 인덱스를 위한 추가적인 기억공간을 필요로 함(index tablespace) • 인덱스와 테이블 I/O수의 합계가 전체 테이블을 스캔하는 I/O수보다 적은 경우에만 성능향상에 기여한다고 볼 수 있음(손익분기점: 10~15%) • 여러 컬럼을 결합하여 인덱스를 구성하는 경우 컬럼의 순서 중요 • 입력, 삭제 혹은 인덱스 컬럼의 값을 수정하는 경우 인덱스 데이터도 변경되어야 하므로 그에 따른 오버헤드도 고려할 것 • 테이블당 인덱스의 수는 4개 정도가 적당 • 무분별한 인덱스는 등록,수정, 삭제작업시 부하. • 새로 추가된 인덱스는 기존 액세스 경로에 영향을 미칠 수 있음.(rank 등)

  33. 인덱스 대상테이블 선정 • 테이블의 크기가 5~6 블럭 이상 • 무작위접근(Random Access)이 빈번한 경우 • 특정범위/순서의 데이터조회가 필요한 경우 • Join의 연결고리가 되는 컬럼 • 참조 무결성을 지정한 경우 연결고리 되는 컬럼 • Nested Join이 일어나는 테이블(?) • 단순 보관용 이거나 전체조회용일 경우에는 인덱스를 생성하지 않는다. • 여러 개의 인덱스가 있는 경우 동시에 많은 양의 입력/수정/삭제가 이루어지면 부하가 발생하므로 인덱스의 수나 생성시점 또한 중요하다.

  34. 분포도와 손익분기점 • 인덱스를 생성하고자 하는 컬럼의 분포도는 10~15%를 넘지 않아야 한다. • 분포도 = 1/컬럼값의 종류 * 100 • = 데이터별 평균 로우 수/테이블의 총 로우 수 *100 • 인덱스의 선택성(Selectivity) • 테이블의 전체 레코드중에서 구분값(Distinct Value)이 차지하는 비율. • 선택성 = (구분값의 갯수 / 전체 ROW 갯수) * 100 • 선택성이 높을 수록 인덱스의 분포도는 양호 •  Unique Index의 선택성은 1.00 • 예) 테이블의 레코드 수:1000, 구분값:950 => 선택성 :950/1000 (0.95)

  35. 인덱스의 활용(적용기준) • 6 블럭 이상의 테이블에 적용 (6블럭이하는 연결고리만) • 컬럼의 분포도가10 ~ 15 % 이내인경우 적용 • 분포도가 범위이내더라도 절대량이 많은 경우에는 단일 테이블 클러스터링을 검토할 것 • 분포도가 범위 이상이더라도 부분범위처리를 목적으로 하는 경우에는 적용 • 인덱스만을 사용하여 요구를 해결하고자 하는 경우는 분포도가 나쁘더라도 적용할 수 있음(손익분기점) 1 컬럼값의 종류 컬럼값의 평균 로우수 테이블의 총 로우수 분포도 = * 100 = * 100 손익분기점이란 ?

  36. o 2 차 가 공 2 차 가 공 x x o o x 운반 단위 운반 단위 o x o ..... ..... o • 10,000 row read를 • 위해 1,000 block • access • 높은 Hit Ratio • 1,000 row read를 • 위해 1,000 block • access • 낮은 Hit Ratio o x INDEX (FLD) TAB TAB 손익분기점 손익 분기점 (10 ~15%) • 고려사항 • Scan 범위 • Hit Ratio • Clustering Factor • 가정 • 전체 10,000 row • 1 block당 평균 10 row • 전체 1,000 block Index Range Scan Full Table Scan

  37. 인덱스의 효율적 이용 • 일반적으로 가장 좋은 하나의 인덱스만 사용하는 것이 여러 개의 인덱스를 사용하는 것보다 유리하다.  인덱스 머지(Index Merge)의 회피 • 인덱스 테이블과 데이터 테이블은 각기 다른 디스크에 배치 • 인덱스 테이블에 대한 적절한 기억공간 할당 • 테이블 접근없이 인덱스만으로 데이터 요구 해결 • 인덱스 생성 및 삭제 시점의 효율적 운용 검토 • 데이터 로드나 일시에 많은 데이터를 입력할 경우 인덱스에 대한 부하를 줄이기 위해 인덱스를 삭제, 입력 및 로드가 끝난 이후 재생성 • 인덱스의 명명은 생성목적과 의미를 내포

  38. 데이터 검색 방식 • FULL- TABLE SCAN데이터 BLOCK만을 읽는다.다중 BLOCK I/O를 한다(DB_FILE_MULTIBLOCK_READ_COUNT)PARALLEL QUERY OPTION 기능을 사용할 수 있다.SELECT 되는 ROW의 수가 많은 경우에 유리하다.(책을 예로 생각하라) • INDEX SCANINDEX BLOCK 과 데이터 BLOCK 을 읽는다.다중 BLOCK I/O 를 할 수 없다.SELECT 되는 ROW 의 수가 적은 경우에 유리하다.(10~15% 이내) • FAST-FULL INDEXED SCANSELECT 에 사용된 모든 컬럼이 INDEX 내에 포함이 되는 경우즉 INDEX 에만 접근하면 윈하는 데이터가 전부 있는 경우에 사용한다.FULL-TABLE SCAN 을 해야 하는 경우에 FULL-TABLE SCAN 에 대한 대체 방법.FULL-TABLE SCAN 보다 가볍다.FULL-TABLE SCAN 의 장점인 다중 BLOCK I/O 와 PARALLEL QUERY 를 사용한다.

  39. 옵티마이저의 실행계획 작성 우선 순위(Rule Based ) 1 ROWID에 의한 1 로우 액세스 2 클러스터 조인에 의한 1 로우 액세스 3 Unique Key 또는 Primary Key를 사용하는 Hash Cluster Key에 의한 1 로우 액세스 4 Unique Key 또는 Primary Key에 의한 1 로우 액세스 5 클러스터 조인 6 해시 클러스터 키 7 인덱스 클러스터 키 8 복합 키 9 단일 컬럼 인덱스 10 인덱스 컬럼에서의 바운드 범위 조회 11 인덱스 컬럼에서의 언바운드 범위 조회 12 소트 병합 조인 13 인덱스 컬럼의 최대 또는 최소값 14 인덱스 컬럼에서의 ORDER BY 사용 15 테이블의 전체 검색

  40. INDEX 를 사용하지 않는 경우 • 좌변을 가공하지 마라.(인덱스컬럼의 연산, 결합 등의 변형) •  SAL * 12 = 2000  SAL = 2000/12로 • NULL은 가급적 사용하지 마라.(NULL 비교) • 부정형 비교( <>, NOT ) • 함수를 사용하는 경우(SUBSTR, NVL 등) • LIKE문에서 '%ABC%','%ABC'는 사용못함. 'AB%'가능. • WHERE절에 인덱스가 2개 있다면 인덱스의 우선순위에 의해 LIKE보다는 =조건이 작동한다.(취사선택)

  41. INDEX 를 사용하지 않는 경우 INDEX COLUMN의 변형 SELECT * FROM DEPT WHERE SUBSTR(DNAME,1,2) = 'AC' New Feature Function Based Index 사용시는 예외 NOT Operator SELECT * FROM EMP WHERE JOB <> 'SALES' NULL, NOT NULL SELECT * FROM EMP WHERE ENAME IS NOT NULL Optimizer 의 취사선택 SELECT * FROM EMP WHERE JOB LIKE 'AB%' AND EMPNO = '7890'

  42. INDEX COLUMN의 변형(external) SELECT * FROM DEPT WHERE SUBSTR(DNAME,1,2) = 'AC' SELECT * FROM DEPT WHERE DNAME LIKE 'AC%' 테이블의 모든 내용을 읽어서 서브 스트링을 모두 돌린 다음 같은것 찾는다. 만약 index잡혀 있다면 index에서 ABC로 시작 하는 것 찾아서 그것만 테이블 액세스 한다. SELECT * FROM EMPTEST WHERE SAL * 12 = 12000000 SELECT * FROM EMPTEST WHERE SAL = 12000000 / 12 한번만 연산후 그 값인것 찾는다. sal * 12를 모두 다한후 비교 한다. SELECT * FROM EMPTEST WHERE TO_CHAR(HIREDATE,'YYYYMMDD') = '19811203' SELECT * FROM EMPTEST2 WHERE HIREDATE = '19811203' 1차 인덱스 컬럼은 Char타입으로 해야한다.

  43. INDEX COLUMN의 변형(external) SELECT * FROM EMP WHERE NVL(COMM,0) < 100 NULL 공포?  가능한 테이블 설계시에 예방.(뒤장에서) SELECT * FROM EMP WHERE NVL(JOB,'') = 'CLERK' SELECT * FROM EMP WHERE JOB LIKE 'CLERK%' SELECT * FROM EMP WHERE DEPTNO = 30 AND JOB = 'MANAGER' SELECT * FROM EMP WHERE DEPTNO || JOB = '30MANAGER'

  44. 의도적인 SUPPRESSING(억제) SELECT * FROM EMP WHERE JOB = 'MANAGER' SELECT * FROM EMP WHERE RTRIM(JOB) = 'MANAGER' • 인덱스는 분포도 10~15% 이하의 경우 효율적. • 분포도가 90%이면 엄청속도가 느려진다. 따라서 • 옵티마이저가 인덱스를 타지 않도록 의도적으로 • SUPPRESSING한다.(사람이 해주는거다 ㅡ,.ㅡ;) SELECT * FROM EMP WHERE JOB || '' = 'MANAGER' SELECT * FROM EMP WHERE EMPNO + 0 = 8978 --NUMBER형의 경우 SELECT * FROM EMP WHERE EMPNO = 8978 의도적인 Suppressing은 타입에 관계없이 RTRIM()을 사용하자 !!!

  45. 의도적인 SUPPRESSING(억제) CUST LIKE ‘AA%’ 패턴과 CUST =‘AA’ 의 차이점?? 200 sec 0.2 sec SELECT * FROM AAA WHERE CUST LIKE 'DN%' AND RTRIM(STATUS) LIKE '9%' --일부러 한쪽의 인덱스는 안쓰게 만들었다. SELECT * FROM AAA WHERE CUST LIKE 'DN%' AND STATUS LIKE '9%' 값이 틀려 지면 더 적은 ROWID가 있을 수 있으므로 인덱스 머지 방식은 절대로 사용할 수 없다. 둘중 하나의 인덱스를 선정 합니다. 랭킹을 비교... 둘다 LIKE이므로 만들어진 시점을 비교하여 최근에 만들어진 인덱스를 사용합니다. 전부다 비기면 나중에 쓰여진 인덱스를 사용. STATUS인덱스를 사용 했다면 엄청느려짐. ‘8%‘, ‘7%‘………. STATUS STATUS컬럼은 9인경우를 제외하면 좁은 분포도를 가진다. 인덱스를 사용하게 되면 9 값을 처리하게 될때 손익분기점을 상회하므로 인덱스를 사용하지 않게 해야 한다.  fullscan이 오히려 효율 '9%'

  46. INDEX COLUMN의 변형 (internal) 예제 테이블 CREATE TABLE SAMPLET ( CHR CHAR(10), NUM NUMBER (12,3), VAR VARCHAR2(20), DAT DATE) SELECT * FROM SAMPLET WHERECHA = 10 SELECT * FROM SAMPLET WHERETO_NUMBER(CHA)= 10 SELECT * FROM SAMPLET WHERENUM LIKE '9410%' SELECT * FROM SAMPLET WHEREDAT = '01-JAN-94' SELECT * FROM SAMPLET WHERETO_CHAR(NUM)LIKE '9410%' SELECT * FROM SAMPLET WHERE DAT= TO_DATE('01-JAN-94')

  47. INDEX COLUMN의 변형 (internal) SELECT * FROM SAMPLET WHERE DAT = '01-JAN-94' SELECT * FROM SAMPLET WHEREVAR = 10 SELECT * FROM SAMPLET WHERE TO_NUMBER(VAR) = 10 SELECT * FROM SAMPLET WHERE DAT = TO_DATE('01-JAN-94') SELECT * FROM SAMPLET WHERE DAT = CHA SELECT * FROM SAMPLET WHERE NUM = CHA SELECT * FROM SAMPLET WHERE NUM = TO_NUMBER(CHAR) SELECT * FROM SAMPLET WHERE DAT = TO_DATE(CHA)

  48. INDEX COLUMN의 변형 (internal) 예제 TABLE ACCESS FULL CHULGOT SQL> SELECT SUM(UNCOST) FROM CHULGOT WHERE STATUS = 90 1 row, 28.5 sec SQL> SELECT SUM(UNCOST) FROM CHULGOT WHERE STATUS = '90' SORT AGGREGATE TABLE ACCESS BY ROWID CHULGOT INDEX RANGE SCAN CH_STATUS 1 row, 0.15 sec SQL> SELECT CHULNO, CUSTNO, UNCOST FROM CHULGOT WHERE CFMDEPT LIKE '71%' TABLE ACCESS FULL CHULGOT rows, sec NUMBER type NESTED LOOPS TABLE ACCESS FULL ORDER1T TABLE ACCESS BY ROWID CHULGOT INDEX RANGE SCAN CH_CFMDEPT SQL> SELECT ORDNO, CHULNO, STATUS FROM ORDER1T X, CHULGOT Y WHERE X.CUSTNO = Y.CUSTNO AND X.ORDDEPT = Y.CFMDEPT AND y.CHULDATE LIKE '9711%' rows, 71 sec

  49. INDEX COLUMN의 변형 (internal) 실습예제 • 서로 다른 Data Type의 비교시의 Internal Conversion (옵티마이저의 인공지능은 사람을 따라올수 없다!!) SELECT * FROM EMPTEST WHERE empno = 7654 SELECT * FROM EMPTEST WHERE empno = '7654' Empno(PK) 는 현재 char형. char형을 number형으로 비교함. FULL SCAN  TO_CHAR(7654)로 변환한다. SELECT * FROM EMP WHERE hiredate = '1980-12-17' SELECT * FROM EMPTEST WHERE hiredate = '1980-12-17' date형 FULL SCAN  옵티마이저가 TO_DATE('1980-12-17')로 변환한다. SELECT * FROM EMPTEST WHERE hiredate = TO_DATE( '1980-12-17') -- 우변이 가공되므로 인덱스 사용에 문제 없음.

  50. NOT Operator SELECT * FROM EMP a WHERE a.empno > 0 AND NOT EXISTS ( SELECT * FROM EMP b WHERE b.empno = a.empno AND b.JOB = 'SALESMAN') SELECT * FROM EMP WHERE JOB <> 'SALESMAN' SELECT * FROM EMP WHERE empno > 0 MINUS SELECT * FROM EMP b WHERE b.JOB = 'SALESMAN' -- <> 형은 인덱스가 안타므로 부정을 긍정으로 만들어서 사용한다.

More Related