Microsoft PowerPoint - Oracle Data Access Pattern.ppt

Similar documents
Microsoft PowerPoint - Oracle Data Join Method.pptx [읽기 전용]

Jerry Held


다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

SQL Tuning Business Development DB

歯sql_tuning2

Microsoft Word - SQL튜닝_실습교재_.doc

결과보고서

WINDOW FUNCTION 의이해와활용방법 엑셈컨설팅본부 / DB 컨설팅팀정동기 개요 Window Function 이란행과행간의관계를쉽게정의할수있도록만든함수이다. 윈도우함수를활용하면복잡한 SQL 들을하나의 SQL 문장으로변경할수있으며반복적으로 ACCESS 하는비효율역

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

SQL Server 에서 SQL 튜닝시알아야할힌트와사용 방법 엑셈컨설팅본부 /DB 컨설팅팀박성호 Optimizer 가 SQL 을해석할때항상최적의실행계획을생성하지는못한다. 복잡한 SQL 일수록최적의실행계획을생성하기위해고려해야할대상 (Table, Index 가많은경우 )

객관식 1. 아래의쿼리를만족하는결과를가장잘설명한것은? SELECT A.* FROM HR.EMPLOYEES A, HR.EMPLOYEES B WHERE 1=1 AND A.MANAGER_ID = B.EMPLOYEE_ID AND B.SALARY >= ANY A.SALARY;

untitled

ePapyrus PDF Document

SQL 실행계획을 이용한 패턴튜닝_ _최종.ppt [호환 모드]

@OneToOne(cascade = = "addr_id") private Addr addr; public Emp(String ename, Addr addr) { this.ename = ename; this.a

MS-SQL SERVER 대비 기능

단답형 (26 회기출문제 ) 1. 아래와같은테이블이있을때아래의 SQL 결과에대해서 Oracle, SQL Server 순서로적으시오 TAB1 COL1 CHAR(10) COL2 CHAR(10) INSERT INTO TAB1 VALUES ('1',''); INSERT INT

13주-14주proc.PDF

I T C o t e n s P r o v i d e r h t t p : / / w w w. h a n b i t b o o k. c o. k r

ORACLE-SQL

0. Intro ORACLE 社 Oracle, My SQL, Exadata IBM 社 DB2, Informix SAP 社 ASE, IQ, ASA Microsoft 社 SQL Server Teradata 社 Teradata 공통점은? Altibase 社 Altibase

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

DBMS & SQL Server Installation Database Laboratory

RDB개요.ppt

PowerPoint 프레젠테이션

Bind Peeking 한계에따른 Adaptive Cursor Sharing 등장 엑셈컨설팅본부 /DB 컨설팅팀김철환 Bind Peeking 의한계 SQL 이최초실행되면 3 단계의과정을거치게되는데 Parsing 단계를거쳐 Execute 하고 Fetch 의과정을통해데이터

Microsoft PowerPoint - 10Àå.ppt

객관식 1번풀이사전지식 * 비교연산자 ANY ( 서브쿼리 ) - 서브쿼리의결과에존재하는어느하나의값이라도만족하는조건을의미. 비교연산자로 " > " 를사용했다면메인쿼리는서브쿼리의값들중어떤값이라도만족하면되므로서브쿼리의결과의최소값보다큰모든건이조건을만족 - SOME 과동일 1번

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

Result Cache 동작원리및활용방안 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 ORACLE DBMS 를사용하는시스템에서 QUERY 성능은무엇보다중요한요소중하나이며그 성능과직접적인관련이있는것이 I/O 이다. 많은건수를 ACCESS 해야만원하는결과값을얻을수있는 QUER

슬라이드 1

예제소스는 에서다운로드하여사용하거나툴바의 [ 새쿼리 ]( 에아래의소스를입력한다. 입력후에는앞으로실습을위해서저장해둔다. -- 실습에필요한 Madang DB 와 COMPANY DB 를모두생성한다. -- 데이터베이스생성 US

InsertColumnNonNullableError(#colName) 에해당하는메시지출력 존재하지않는컬럼에값을삽입하려고할경우, InsertColumnExistenceError(#colName) 에해당하는메시지출력 실행결과가 primary key 제약에위배된다면, Ins

제목 레이아웃

´ÙÁß Row °á°ú¸¦ ´ÜÀÏÇàÀ¸·Î Äĸ¶·Î ºÐ¸®ÇØ Ãâ·ÂÇÏ´Â ¹æ¹ý

USER GUIDE

NLJ BATCH 과부분범위처리 엑셈컨설팅본부 / DB 컨설팅팀오수영 개요 오라클은새로운버전이출시될때마다한층업그레이드된기능들이추가된다. 이기능들은사용자에게편리함을제공함은물론이고, 기존의기능들이성능적으로업그레이드되어보다강력해지기도한다. 그러나때로는새롭게추가된기능으로인해,

Microsoft PowerPoint - 1_3_DBA_SQL_tuning

Microsoft Word - [Unioneinc] 특정컬럼의 통계정보 갱신_ _ldh.doc

5장 SQL 언어 Part II

FlashBackt.ppt

Microsoft PowerPoint - 27.pptx

강의 개요

문서 템플릿

목차 BUG 문법에맞지않는질의문수행시, 에러메시지에질의문의일부만보여주는문제를수정합니다... 3 BUG ROUND, TRUNC 함수에서 DATE 포맷 IW 를추가지원합니다... 5 BUG ROLLUP/CUBE 절을포함하는질의는 SUBQUE

PowerPoint Presentation

PostgreSQL 2 Uniersity of California at Berkeley ( ) 에서개발된관계형데이터베이스서버인 Ingres 가시초 ( 후에 Computer Associates 에인수됨 ) 1

歯PLSQL10.PDF

PowerPoint Presentation

PowerPoint 프레젠테이션

CD-RW_Advanced.PDF

10.ppt

3 S Q L A n t i p a t t e r n s Trees/intro/parent.sql CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, parent_id BIGINT UNSIGNED, comment TEXT

TECHNICAL WHITE PAPER Tibero Optimizer SQL Execution Plan October 2012

Oracle Apps Day_SEM

Oracle Database 10g: Self-Managing Database DB TSC

목차 BUG DEQUEUE 의 WAIT TIME 이 1 초미만인경우, 설정한시간만큼대기하지않는문제가있습니다... 3 BUG [qp-select-pvo] group by 표현식에있는컬럼을참조하는집합연산이존재하지않으면결괏값오류가발생할수있습니다... 4

Slide 1

PowerPoint Presentation

Tablespace On-Offline 테이블스페이스 온라인/오프라인

step 1-1

Index

1 01 [ ] [ ] plus 002

슬라이드 제목 없음

DB Tuning Oracle SQL Tuning실무사례(9)

초보자를 위한 분산 캐시 활용 전략

Spring Boot/JDBC JdbcTemplate/CRUD 예제

º¸µµ¿Â

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

ÀüÀÚÇö¹Ì°æ-Áß±Þ

Slide 1

ALTIBASE HDB Patch Notes

TITLE

How To Write Efficient SQL Queries with Tips N Tricks

ORACLE 꼭 이 책의 내용들을 다 외울 정도로 공부하여 스스로의 지식으로 만들기 바랍 니다. 마치 작은 씨앗에서 큰 나무가 자라나는 것처럼 이 책 한 권은 작지만 이 책을 다 읽은 후에는 큰 나무가 되기를 바랍니다. 이 책은 SQL 책입니다. 그러나 SQL을 잘 작

MySQL-.. 1

<C1A4BAB8C3B3B8AE5FBBEABEF7B1E2BBE75FC7CAB1E25F E687770>

°¡°Ç6¿ù³»ÁöÃÖÁ¾

Microsoft Word - 기술노트[19회] Flashback.doc

목차 BUG offline replicator 에서유효하지않은로그를읽을경우비정상종료할수있다... 3 BUG 각 partition 이서로다른 tablespace 를가지고, column type 이 CLOB 이며, 해당 table 을 truncate

SQL Tuning Business Development DB SQL - -SQL -SQL

빅데이터분산컴퓨팅-5-수정

Intra_DW_Ch4.PDF

ALTIBASE HDB Patch Notes

Microsoft PowerPoint - Tech-iSeminar_Logminer.ppt

비트와바이트 비트와바이트 비트 (Bit) : 2진수값하나 (0 또는 1) 를저장할수있는최소메모리공간 1비트 2비트 3비트... n비트 2^1 = 2개 2^2 = 4개 2^3 = 8개... 2^n 개 1 바이트는 8 비트 2 2

Oracle 10g SQL , PL/SQL 튜닝

< C4BFB8AEBEEEC6D0BDBA20BBE7B7CAC1FD5FB0C7C3E02E687770>


CRM Fair 2004

62

Microsoft Word - PLSQL.doc

MySQL-Ch10

Observational Determinism for Concurrent Program Security

Tina Admin

SQL


4 CD Construct Special Model VI 2 nd Order Model VI 2 Note: Hands-on 1, 2 RC 1 RLC mass-spring-damper 2 2 ζ ω n (rad/sec) 2 ( ζ < 1), 1 (ζ = 1), ( ) 1

SQL PLAN MANAGEMENT 활용 엑셈컨설팅본부 /DB 컨설팅팀장정민 개요 오라클은비롯한많은관계형 DBMS 에서는사용자의 SQL 질의를효율적으로처리하기위해옵티마이저를사용하고있다. 옵티마이저는유저가수행하는 SQL 을받아실행계획을생성하고, 실제 SQL 은이실행계획을

Transcription:

Special Key Note Oracle Data Access Pattern ( 주 ) 오픈메이드컨설팅 오동규수석컨설턴트 1

What is Data Access Pattern? > 데이터를 I/O 하는방식 Index Scan Full Table Scan Rowid 2

Why is The Pattern Important? >SQL 의성능을좌지우지함. >SQL 성능이슈의 1/3 이 Data Access Pattern 이잘못되어발생 > 모든 DBMS 의 Data Access Pattern이유사함. Index Scan Full Table Scan Rowid 3 가지용도를알아야한다. 3

How do You learn Data Access Pattern? Only Book? Probably Not! Then How? 4

Oracle Data Access Pattern- 목차 Here it is EveryThing About Oracle Data Access Pattern Rowid Index Unique Scan Index Range Scan Index Inlist Iterator Index Skip Scan Index Full Scan Index Fast Full Scan Bit Map Index Combination Full Table Scan Index 사용 5

Oracle Data Access Pattern- 상세목차 Rowid Index Unique Scan Index Range Scan Index Range Scan Descending Index Range Scan (min/max) Index Inlist Iterator Index Inlist Iterator Descending Index Skip Scan Index Skip Scan Descending Index Full Scan Index Full Scan Descending Index Full Scan (min/max) Index Fast Full Scan BIT MAP OR BIT MAP AND BIT MAP MINUS BIT MAP MERGE Full Table Scan Index Fast Full Scan 은예외적으로 Full Table Scan 처럼 Multi I/O 를사용함 Bit Map Index Combination 의 4 가지 Pattern 6

Rowid > 발생조건 > 특징 > 적용 Rowid가조건으로공급된경우 인덱스를사용하여 Table 을 access 한경우 Rowid를이용해서해당테이블의특정 의특정 Row를찾아간다. 가장빠른 Access 방식이다. max /min 일자를찾아서그일자에해당하는값을 select 할때 주로 self join 시사용 > Hint /*+ rowid( 테이블명또는테이블별칭 ) */ 7

Rowid- 예제 ( 인라인뷰사용 ) 요구사항 : 부서별로가장최근에입사한사원정보를한명씩출력하시오. select b.empno, b.ename, b.deptno, b.job, b.hiredate from (select substr(max(to_char(hiredate,'yyyymmdd') ROWID), 9) as rid group by deptno) a, emp b where b.rowid = a.rid --------------------------------------------------------------------------------------------------------- Id Operation Name Starts A-Rows A-Time Buffers Reads Used-Mem --------------------------------------------------------------------------------------------------------- 1 NESTED LOOPS 1 3 00:00:00.01 10 2 2 VIEW 1 3 00:00:00.01 7 2 3 HASH GROUP BY 1 3 00:00:00.01 7 2 578K (0) 4 TABLE ACCESS FULL EMP 1 14 00:00:00.01 7 2 5 TABLE ACCESS BY USER ROWID EMP 3 3 00:00:00.01 3 0 --------------------------------------------------------------------------------------------------------- 8

Rowid- 예제 ( 서브쿼리사용 ) 요구사항 : 부서별로가장최근에입사한사원정보를한명씩출력하시오. select b.empno, b.ename, b.deptno, b.job, b.hiredate b where b.rowid in ( select substr(max(to_char(hiredate,'yyyymmdd') ROWID), 9) group by deptno) ------------------------------------------------------------------------------------------------------------- Id Operation Name Starts A-Rows A-Time Buffers Reads Used-Mem ------------------------------------------------------------------------------------------------------------- 1 NESTED LOOPS 1 3 00:00:00.01 10 6 2 VIEW VW_NSO_1 1 3 00:00:00.01 7 6 3 HASH UNIQUE 1 3 00:00:00.01 7 6 597K (0) 4 HASH GROUP BY 1 3 00:00:00.01 7 6 592K (0) 5 TABLE ACCESS FULL EMP 1 14 00:00:00.01 7 6 6 TABLE ACCESS BY USER ROWID EMP 3 3 00:00:00.01 3 0 ------------------------------------------------------------------------------------------------------------- 9

Index Unique Scan > 발생조건 > 특징 > 적용 Unique 인덱스및 PK 인덱스의모든컬럼에대하여 Where 절에 = 조건으로상수및변수가들어올때발생됨. 해당조건을만족하는하나의 row 만 Scan 한다. 빠른성능 OLTP 시스템 건단위로처리해야하는경우 > Hint /*+ index( 테이블명또는테이블별칭인덱스명 ) */ select /*+ index(emp pk_emp) */ empno, ename where empno = 7782 ; 10

Index Unique Scan- 예제 select empno, ename where empno = 7782 ; EMPNO 인덱스 인덱스 EMP 테이블 테이블 7782 CLARK MANAGER. 7782 ------------------------------------------------------------------------------------------------ Id Operation Name Starts E-Rows A-Rows A-Time Buffers ------------------------------------------------------------------------------------------------ 1 TABLE ACCESS BY INDEX ROWID EMP 1 1 1 00:00:00.01 2 * 2 INDEX UNIQUE SCAN PK_EMP 1 1 1 00:00:00.01 1 ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("empno"=7782) 11

Index Range Scan > 발생조건 Non-Unique Index 를 Access 하는경우 Unique Index를구성하고있는컬럼중일부컬럼에만값이공급된경우 Unique Index에 Range 조건 (like, between, >, <, >=, <=) 으로값이공급되는경우 > 특징 해당조건을만족하는범위 + 아닌값하나 (1PlusScan) 를읽게된다. Range 조건이들어온경우 Index구성순서상이후에있는컬럼에공급된조건들은작업범위를줄이는데작용하지못한다. 예외상황 :9i 이후부터 index skip scan 수행속도가 Range 에의해서좌우된다. > 적용 10만건이하의건수를 access 할때 10만건이상이라도부분범위처리가가능할때 10만건이상이라도인덱스만 scan 하고 table access 가없을때 주로 OLTP > Hint /*+ index( 테이블명또는테이블별칭인덱스명 ) */ /*+ index_desc( 테이블명또는테이블별칭인덱스명 ) */ 12

Index Range Scan- 예제 EMPNO 인덱스 인덱스 EMP 테이블 테이블 7839 KING NULL. select empno, ename, mgr where empno BETWEEN 7782 AND 7839; 7782 CLARK 7839. 7782 7788 7839 7844 7788 SCOTT 7566. 1 Plus Scan ------------------------------------------------------------------------------------------------ Id Operation Name Starts E-Rows A-Rows A-Time Buffers ------------------------------------------------------------------------------------------------ 1 TABLE ACCESS BY INDEX ROWID EMP 1 3 3 00:00:00.01 5 * 2 INDEX RANGE SCAN PK_EMP 1 3 3 00:00:00.01 2 ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("empno">=7782 AND "EMPNO"<=7839) 13

Index Range Scan Descending - 예제 select empno, ename, mgr where empno BETWEEN 7782 AND 7839 EMPNO 인덱스 7698 인덱스 EMP 테이블 테이블 7839 KING NULL. 7782 CLARK 7839. order by empno desc 7782 7788 7788 SCOTT 7566. 7839 ---------------------------------------------------------------------------------------------------------- Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads ---------------------------------------------------------------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID EMP 1 3 3 00:00:00.01 5 3 * 2 INDEX RANGE SCAN DESCENDING PK_EMP 1 3 3 00:00:00.01 2 1 ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("empno"<=7839 AND "EMPNO">=7782) 14

Index Range Scan (min/max)- 예제 EMPNO 인덱스 인덱스 select max(empno) where empno between 7782 and 7839 7782 7788 7839 ---------------------------------------------------------------------------------------------------------- Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads ---------------------------------------------------------------------------------------------------------- 1 SORT AGGREGATE 1 1 1 00:00:00.01 1 1 2 FIRST ROW 1 2 1 00:00:00.01 1 1 * 3 INDEX RANGE SCAN (MIN/MAX) PK_EMP 1 2 1 00:00:00.01 1 1 ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("empno">7839) 15

Index Inlist Iterator > Iteration > 발생조건 > 특징 > 적용 > Hint 의미 : 같은일을반복함 OR 조건이나 IN 조건에상수 ( 변수 ) 가들어오면발생함 Concatenation 과비슷하나 Union 으로풀리지않고반복수행된다. 값이상수나변수로공급될때만발생한다. 수행속도가반복횟수에의해서좌우된다. Iteration 에의해 Index range scan 및 index unique scan 이발생됨 Concatenation 과동일하나 Inlist Iterator 가유리함. Index Range Scan 이더유리한경우가있으므로무조건적용하지말것. OLTP 에적용하고대용량배치 SQL 에서는피한다. /*+ NUM_INDEX_KEYS( 테이블명인덱스명컬럼개수 ) */ Oracle 10.2.0.2 부터사용가능 인덱스 Layout: EMP_JOB_MGR_SAL_IDX (JOB_ID, MANAGER_ID, SALARY) SELECT /*+ NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 2) */ a.* FROM hr.employees a WHERE job_id = :v_job AND manager_id IN (:v_manager1, :v_manager2) 인덱스의 2 번째컬럼까지 Inlist Iterator 로 scan 하라는의미 16

Index Inlist Iterator- 예제 EMPNO 인덱스 인덱스 EMP 테이블 테이블 7839 KING NULL. select empno, ename, mgr where empno in (7782, 7839); 혹은 1 select empno, ename, mgr 2 where (empno = 7782 or empno = 7839); 7782 7839 7782 CLARK 7839. ------------------------------------------------------------------------------------------------- Id Operation Name Starts E-Rows A-Rows A-Time Buffers ------------------------------------------------------------------------------------------------- 1 INLIST ITERATOR 1 2 00:00:00.01 4 2 TABLE ACCESS BY INDEX ROWID EMP 2 2 2 00:00:00.01 4 * 3 INDEX UNIQUE SCAN PK_EMP 2 2 2 00:00:00.01 2 ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access(("empno"=7499 OR "EMPNO"=7839)) 17

Index Inlist Iterator Descending- 예제 EMPNO 인덱스 인덱스 EMP 테이블 테이블 7839 KING NULL. select empno, ename, mgr where empno in (7839, 7782) order by empno desc; 2 혹은 select empno, ename, mgr 1 where (empno = 7839 or empno = 7782) order by empno desc; 7782 7839 7782 CLARK 7839. ----------------------------------------------------------------------------------------------------------- Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads ----------------------------------------------------------------------------------------------------------- 1 INLIST ITERATOR 1 2 00:00:00.01 5 2 2 TABLE ACCESS BY INDEX ROWID EMP 2 2 2 00:00:00.01 5 2 * 3 INDEX RANGE SCAN DESCENDING PK_EMP 2 2 2 00:00:00.01 3 1 ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access(("empno"=7499 OR "EMPNO"=7839)) 18

Index Skip Scan > 발생조건 > 특징 > 적용 > Hint 결합인덱스시처음이나중간의조건이빠졌을경우 9i 이전까지는결합인덱스에서첫번째컬럼이사용되지않으면인덱스의두번째컬럼부터는인덱스스캔이불가능하였다. 9i 부터 Index Skip Scan 으로빠진조건을제외한나머지조건을 scan 하는것이 가능해짐. 기본적으로 index range scan 과특징이유사함 인덱스가 col1 + col2 + col3 로되어있을때 col1 컬럼조건이 where 조건에서빠진경우에 col2 + col3 인덱스를만든것과비슷한효과를 낼수있음. 위와비슷하게 col2 컬럼조건이 where 조건에서빠진경우에도 col1 + col3 인덱스를만든것과비슷한효과를낼수있음. 주로좁은범위를 scan 할때 Skip 되는컬럼의값의종류가많지않을때 /*+ index_ss( 테이블명또는테이블별칭인덱스명 ) */ 19

Index Skip Scan - 예제 Index Skip Scan 인덱스 Lay out : mgr + job + deptno select /*+ index_ss(emp IX_EMP_N1) */ empno, ename, mgr where job = 'CLERK' and deptno between 10 and 20 --------------------------------------------------------------------------------------------------- Id Operation Name Starts E-Rows A-Rows A-Time Buffers --------------------------------------------------------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID EMP 1 1 4 00:00:00.01 6 * 2 INDEX SKIP SCAN IX_EMP_N1 1 4 4 00:00:00.01 2 --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("job"='clerk' AND "DEPTNO">=10 AND "DEPTNO"<=30) filter(("job"='clerk' AND "DEPTNO">=10 AND "DEPTNO"<=30)) mgr + job + deptno 결합 7698 7782 7788 7839 7902 CLERK CLERK CLERK MANAGER 결합인덱스 30 10 NULL 20 CLERK 20 Filter Skip CLERK 30 7907 1 Plus Scan select empno, ename, mgr where mgr in (7902, 7698, 7839, 7566, 7782, 7788) and job = 'CLERK' and deptno between 10 and 30 Index skip scan 의효과는왼쪽처럼 mgr 의모든조건을추가한것과같음. 20

Index Skip Scan Descending- 예제 인덱스 Lay out : mgr + job + deptno mgr + job + deptno 결합 결합인덱스 select /*+ index_ss_desc(emp IX_EMP_N1) */ empno, ename, mgr where job = 'CLERK' and deptno between 10 and 30 order by mgr desc, job desc, deptno desc 7698 7782 7788 7839 7902 7907 CLERK 30 CLERK 10 CLERK NULL MANAGER 20 CLERK 20 CLERK 30 1 Plus Scan Filter Skip ------------------------------------------------------------------------------------------------------------ Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads ------------------------------------------------------------------------------------------------------------ 1 TABLE ACCESS BY INDEX ROWID EMP 1 1 4 00:00:00.01 5 3 * 2 INDEX SKIP SCAN DESCENDING IX_EMP_N1 1 4 4 00:00:00.01 2 1 ------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("job"='clerk' AND "DEPTNO">=10 AND "DEPTNO"<=30) filter(("job"='clerk' AND "DEPTNO">=10 AND "DEPTNO"<=30)) 21

Index Full Scan > 특징 > 적용 해당인덱스의모든 을한번에한 씩순차적으로읽어내려간다.(Single I/O) 데이터가가적고전체건에대하여인덱스컬럼으로 sort 해야하는경우 min/max 를구할경우 Scan 해야할데이터가가많은경우는부분범위처리가가능하고인덱스컬럼으로 sort 를해야하는경우만적용할것. > Hint /*+ index( 테이블명또는테이블별칭인덱스명 ) */ 직접적으로 full scan 을유도하는힌트는없음. 22

Index Full Scan Scan - 예제 select empno order by empno 1 EMPNO 인덱스 2 인덱스 3 ------------------------------------------------------------------------------------ Id Operation Name Starts E-Rows A-Rows A-Time Buffers ------------------------------------------------------------------------------------ 1 INDEX FULL SCAN PK_EMP 1 1 14 00:00:00.01 2 ------------------------------------------------------------------------------------ 23

Index Full Scan Scan Descending - 예제 인덱스를거꾸로 Scan 함 select empno order by empno desc 3 EMPNO 인덱스 2 인덱스 1 ----------------------------------------------------------------------------------------------- Id Operation Name Starts E-Rows A-Rows A-Time Buffers ----------------------------------------------------------------------------------------------- 1 INDEX FULL SCAN DESCENDING PK_EMP 1 14 14 00:00:00.01 2 ----------------------------------------------------------------------------------------------- 24

Index Full Scan (min/max)- 예제 select max(empno) EMPNO 인덱스 인덱스 -------------------------------------------------------------------------------------------------------- Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads -------------------------------------------------------------------------------------------------------- 1 SORT AGGREGATE 1 1 1 00:00:00.01 1 1 2 INDEX FULL SCAN (MIN/MAX) PK_EMP 1 14 1 00:00:00.01 1 1 -------------------------------------------------------------------------------------------------------- 아래의 SQL 을수행한효과와같음 select /*+ index_desc(emp pk_emp) */ empno WHERE empno > 0 and rownum = 1 25 2 7934 1

Index Fast Full Scan > 발생조건 > 특징 > 적용 > Hint Where 절이나 Select 절에사용된컬럼이모두하나의인덱스에구성된컬럼인경우 결합 Index 의경우최소한한 Column 이 NOT Null 로지정되어있거나 where 절에명시적으로 not null 조건을부여해야한다. 한번에 DB_FILE_MULTIBLOCK_READ_COUNT 에서정한크기씩끝까지읽어내려가며결과값의 Sort 가보장되지않는다.(Multi I/O) Full Table Scan 보다읽어야할 의수가적어항상유리하다. 넓은범위검색 주로배치쿼리나 OLAP 에서사용됨 /*+ index_ffs( 테이블명또는테이블별칭인덱스명 ) */ 26

Index Fast Full Scan Scan - 예제 select /*+ index_ffs(emp pk_emp) */ empno 1 EMPNO 인덱스 인덱스 2 Multi- I/O 3 Multi- I/O -------------------------------------------------------------------------------------------------- Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads -------------------------------------------------------------------------------------------------- 1 INDEX FAST FULL SCAN PK_EMP 1 1 14 00:00:00.01 4 2 -------------------------------------------------------------------------------------------------- 27

Bit Map Index Combination > 발생조건 > 특징 > 적용 > Hint Bit Map 인덱스가한테이블에 2 개이상일때 where 조건에두인덱스에해당하는컬럼의조건을 where 절에서모두사용할때발생함. 9.2 미만버젼에서는 Index Merge Plan 이발생함. 반드시 Bit Map 인덱스가아니라도 Index Combination 이발생할수있음. 이경우에는 Bit Map Conversion 이추가로발생함. 첫번째인덱스의와두번째인덱스의를이용하여두집합간에 AND, OR, MINUS, MERGE 연산을하여데이터를엑세스한다. 주로 DW 나대용량배치인경우적용 OLTP 인경우는두개의인덱스조건이모두똑똑한경우적용가능. 하지만하나의조건만으로 filter 되어나오는결과건수와두개의조건으로 filter 되는나오는결과건수가비슷하다면비효율이발생함으로무조건적용해서는 안됨. /*+ INDEX_COMBINE( 테이블명또는테이블별칭인덱스 1, 인덱스 2) */ 28

Bit Map Index Combination - 예제 인덱스 LayOut : IX_EMP_N2 : mgr IX_EMP_N3 : deptno select /*+ index_combine(emp IX_EMP_N2 IX_EMP_IDX3) */ empno, ename where mgr = 7839 or deptno = 10 Bit Map Or ----------------------------------------------------------------------------------------------------------------- Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads ----------------------------------------------------------------------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID EMP 1 2 5 00:00:00.01 4 2 2 BITMAP CONVERSION TO ROWIDS 1 5 00:00:00.01 2 1 3 BITMAP OR 1 1 00:00:00.01 2 1 4 BITMAP CONVERSION FROM ROWIDS 1 1 00:00:00.01 1 0 * 5 INDEX RANGE SCAN IX_EMP_N3 1 3 00:00:00.01 1 0 6 BITMAP CONVERSION FROM ROWIDS 1 1 00:00:00.01 1 1 * 7 INDEX RANGE SCAN IX_EMP_N2 1 3 00:00:00.01 1 1 ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("deptno"=10) 7 - access("mgr"=7839) 29

Bit Map Index Combination - 예제 Bit Map And 인덱스 LayOut : IX_EMP_N2 : mgr IX_EMP_N3 : deptno select /*+ index_combine(emp IX_EMP_N2 IX_EMP_IDX3) */ empno, ename where mgr = 7839 and deptno = 10 ----------------------------------------------------------------------------------------------------------------- Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads ----------------------------------------------------------------------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID EMP 1 1 1 00:00:00.01 3 2 2 BITMAP CONVERSION TO ROWIDS 1 1 00:00:00.01 2 1 3 BITMAP AND 1 1 00:00:00.01 2 1 4 BITMAP CONVERSION FROM ROWIDS 1 1 00:00:00.01 1 0 * 5 INDEX RANGE SCAN IX_EMP_N3 1 3 00:00:00.01 1 0 6 BITMAP CONVERSION FROM ROWIDS 1 1 00:00:00.01 1 1 * 7 INDEX RANGE SCAN IX_EMP_N2 1 3 00:00:00.01 1 1 ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("deptno"=10) 7 - access("mgr"=7839) 30

Bit Map Index Combination - 예제 인덱스 drop 후 bit map index 생성 create bitmap index IX_EMP_N2 on emp (mgr); create bitmap index IX_EMP_N3 on emp (deptno); select /*+ index_combine(emp IX_EMP_N2 IX_EMP_IDX3) */ empno, mgr, deptno where mgr > 7600 and deptno > 20 ------------------------------------------------------------------------------------------------------ Id Operation Name Starts A-Rows A-Time Buffers Used-Mem ------------------------------------------------------------------------------------------------------ 1 TABLE ACCESS BY INDEX ROWID EMP 1 6 00:00:00.01 4 2 BITMAP CONVERSION TO ROWIDS 1 6 00:00:00.01 2 3 BITMAP AND 1 1 00:00:00.01 2 4 BITMAP MERGE 1 1 00:00:00.01 1 1024 (0) * 5 BITMAP INDEX RANGE SCAN IX_EMP_N3 1 1 00:00:00.01 1 6 BITMAP MERGE 1 1 00:00:00.01 1 2048 (0) * 7 BITMAP INDEX RANGE SCAN IX_EMP_N2 1 5 00:00:00.01 1 ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("deptno">20) filter("deptno">20) 7 - access("mgr">7600) filter("mgr">7600) Bit Map Merge 31

Bit Map Index Combination - 예제 인덱스 drop 후 bit map index 생성 create bitmap index IX_EMP_N2 on emp (mgr); create bitmap index IX_EMP_N3 on emp (deptno); select /*+ index_combine(emp IX_EMP_N2 IX_EMP_IDX3) */ empno, mgr, deptno where NOT( mgr = 7698 ) and deptno = 20 Bit Map Minus ----------------------------------------------------------------------------------------------------- Id Operation Name Starts A-Rows A-Time Buffers Reads ----------------------------------------------------------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID EMP 1 5 00:00:00.05 7 1 2 BITMAP CONVERSION TO ROWIDS 1 5 00:00:00.05 4 1 3 BITMAP MINUS 1 1 00:00:00.05 4 1 4 BITMAP MINUS 1 1 00:00:00.05 3 1 * 5 BITMAP INDEX SINGLE VALUE IX_EMP_N3 1 1 00:00:00.05 2 1 * 6 BITMAP INDEX SINGLE VALUE IX_EMP_N2 1 1 00:00:00.01 1 0 * 7 BITMAP INDEX SINGLE VALUE IX_EMP_N2 1 1 00:00:00.01 1 0 ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("deptno"=20) 6 - access("mgr"=7698) 7 - access("mgr" IS NULL) 32

Full Table Scan > 발생조건 > 특징 > 적용 > Hint 아무런조건없이 Table 을읽게한경우 인덱스가걸려있지않은컬럼에대해서조건을주고 Table 을읽게한경우 인덱스가걸려있는컬럼에조건을부여했을지라도 Optimizer 가 Full Table Scan 이유리하다고판단한경우 테이블의첫 Row 가들어있는 부터 HWM(High Water Mark) 까지읽는다 DB_FILE_MULTIBLOCK_READ_COUNT 가 16 이면한번 i/o 할때 16 block 씩 scan 한다. (Multi I/O) 넓은범위검색 주로배치쿼리나 OLAP 에서사용됨 /*+ full( 테이블명또는테이블별칭인덱스명 ) */ 33

Full Table Scan- 예제 select /*+ full(emp) */ empno, ename 1 EMP 테이블 테이블 2 Multi- I/O 3 Multi- I/O ------------------------------------------------------------------------------------ Id Operation Name Starts E-Rows A-Rows A-Time Buffers ------------------------------------------------------------------------------------ 1 TABLE ACCESS FULL EMP 1 1 14 00:00:00.01 8 ------------------------------------------------------------------------------------ 34

Oracle Data Access Pattern- 정리 Rowid Index Unique Scan Index Range Scan Index Inlist Iterator Index Skip Scan Index Full Scan Index Fast Full Scan Bit Map Index Combination Full Table Scan Index 사용 35

Special Key Note Oracle Data Access Pattern 감사합니다. 36