대용량 데이터베이스 솔루션  (주)엔코아 정보 컨설팅 본 교육교재의 저작권은 (주)엔코아정보컨설팅에 있으며 법으로 보호됩니다. 무단 복사 또는 제본을 금지합니다.

목 차 수행속도 향상을 위한 필수항목 JOIN 수행속도 향상 원리 대용량 데이터베이스의 도전 JOIN과 LOOP QUERY 목 차 수행속도 향상을 위한 필수항목 대용량 데이터베이스의 도전 프로젝트 성공 3요소 병렬처리 멀티 쓰레드 서버 공유 메모리 관리 멀티블럭 처리 경합없는 조회 LOCKING MANAGEMENT 부분범위 처리와 전체범위 처리 부분범위 처리의 원리 및 응용 INDEX의 사용 규칙 INDEX 적용기준 INDEX MERGE 결합 INDEX INDEX 선정 기준 BITMAP INDEX JOIN 수행속도 향상 원리 JOIN과 LOOP QUERY NESTED LOOP JOIN SORT MERGE JOIN JOIN방법의 결정 STAR-JOIN & HASH-JOIN 순환관계 데이터 처리 Recursive SQL의 원리와 응용 CLUSTERING의 원리와 효과 CLUSTER 적용 기준 VIEW의 원리 다중처리의 원리 다중처리 활용 사례 DATA TYPE의 결정을 위한 고려사항 비용기준 최적화 (HINT) 실행계획 작성 및 분석 ACCEPT CARD에 따른 SQL의 변형 FETCH와 SELECT...INTO 의 차이 관계형 데이타베이스의 최적 사용을 위해서 ORACLE은 이러한 많은 물리적인 기능을 제공하고 있으며 이러한 기능을 바탕으로 논리적인 처리의 효율성을 향상시켜 주는 많은 처리 기법들을 보유하고 있읍니다. 세미나 진행중에 DEMO를 통해 보시게 되겠지만, 이러한 기법들은 H/W의 추가적인 투자없이 몇 십배의 처리효율을 낼 수 있도록 합니다. 효율적인 처리를 위해 가장 중요하고 기반이 되는 것이 데이타베이스 설계라 할 수 있읍니다만 , 너무 광범위한 내용이라서 시간관계상 이번 세미나에서는 언급하지 않겠음을 양지하시기 바랍니다. Array Processing 이란 처리단위를 여러 건으로 묶어 같이 처리하게 함으로써 시스템 오버헤드를 줄이게 하는 기법을 말하며, 부분범위처리란, 처리할 내용이 아무리 많더라도 일단 운반단위에 차게 되면 데이타를 Return 시키고, 사용자가 다음 Action을 취할 때까지 기다리므로 처리범위의 크기에 영향을 받지 않도록 하는 기법을 말합니다. 다양한 엑세스 경우를 항상 만족할 수 있도록 인덱스를 구성하는 것은 무엇보다 중요 하며, 처리할 범위가 넓어 인덱스 처리로는 부담이 되는 경우에는 클러스터링을 이용 하여 해결할 수가 있으며, 적절한 I / O의 분산을 실시해야 할 것 입니다. SQL은 3GL의 READ, WRITE가 아닙니다. 그 자체가 하나의 Application 이라 할 수 있으며 사용기법에 따라 엄청난 차이의 수행속도 및 생산성의 향상을 가져 올 수가 있게 됩니다. 오늘은 그 중 몇가지 대표적인 사례만 소개드리겠읍니다.

수행속도 향상을 위한 필수 항목 병렬 처리 멀티쓰레드, 멀티서버 메모리의 최적 활용 멀티블럭 처리 경합의 감소 LOCKING의 해소 저장 프로시져의 활용 시스템 진단 툴의 활용 부분범위 처리 인덱스의 활용 및 액세스 효율의 향상 JOIN의 최적화 클러스터링 뷰의 최적 활용 다중 처리(ARRAY PROCESSING) I / O 의 분산 및 SORT의 튜닝 적절한 데이터 타입의 사용 고성능 SQL의 활용 반 정규화(Denormalize) 관계형 데이타베이스의 최적 사용을 위해서 ORACLE은 이러한 많은 물리적인 기능을 제공하고 있으며 이러한 기능을 바탕으로 논리적인 처리의 효율성을 향상시켜 주는 많은 처리 기법들을 보유하고 있읍니다. 세미나 진행중에 DEMO를 통해 보시게 되겠지만, 이러한 기법들은 H/W의 추가적인 투자없이 몇 십배의 처리효율을 낼 수 있도록 합니다. 효율적인 처리를 위해 가장 중요하고 기반이 되는 것이 데이타베이스 설계라 할 수 있읍니다만 , 너무 광범위한 내용이라서 시간관계상 이번 세미나에서는 언급하지 않겠음을 양지하시기 바랍니다. Array Processing 이란 처리단위를 여러 건으로 묶어 같이 처리하게 함으로써 시스템 오버헤드를 줄이게 하는 기법을 말하며, 부분범위처리란, 처리할 내용이 아무리 많더라도 일단 운반단위에 차게 되면 데이타를 Return 시키고, 사용자가 다음 Action을 취할 때까지 기다리므로 처리범위의 크기에 영향을 받지 않도록 하는 기법을 말합니다. 다양한 엑세스 경우를 항상 만족할 수 있도록 인덱스를 구성하는 것은 무엇보다 중요 하며, 처리할 범위가 넓어 인덱스 처리로는 부담이 되는 경우에는 클러스터링을 이용 하여 해결할 수가 있으며, 적절한 I / O의 분산을 실시해야 할 것 입니다. SQL은 3GL의 READ, WRITE가 아닙니다. 그 자체가 하나의 Application 이라 할 수 있으며 사용기법에 따라 엄청난 차이의 수행속도 및 생산성의 향상을 가져 올 수가 있게 됩니다. 오늘은 그 중 몇가지 대표적인 사례만 소개드리겠읍니다.

대용량 데이터베이스의 도전 기술적 돌파구는 ? 대용량은 시대의 흐름 : 점점 더 커지고 있다... 대용량 데이터 처리는 본질적인 차이가 있다... 원하는 PERFORMANCE를 위해 Speedup : 보유 환경하에서 보다 빠른 처리가 요구됨 Scaleup : 추가적인 H/W 구입 Sizeup : 보다 대용량의 H/W가 필요 기술적 돌파구는 ? 1

대용량 데이터베이스 구축을 위해 ... 개발 시스템의 정확한 요구정의 요소기술의 파악 및 연구 핵심 고려요소에 대한 벤치마킹 유사 시스템의 참조 증명된 기술을 사용 적절한 환경구축 (H/W, S/W, NETWORK) 풍부한 경험을 가진 유능한 협력업체 활용 핵심 기술에 대한 컨설팅 단순하고 명료한 시스템 설계 개발자의 인식 전환 전문 기술 요원의 육성 3

프로젝트 성공 3 요소 관계형 데이터베이스에 맞는 단순 명료한 시스템 설계 관계형 데이터베이스에 맞는 단순 명료한 시스템 설계 시행착오를 줄이고 생산성을 향상시키며 핵심 문제를 해결할 수 있는 리더 기존의 개념을 버리고 새로운 개념으로 무장된 개발 요원 단순 명료한 설계 기술 리더 개발자 인식전환 프로젝트 4

단순 명료한 설계 ERD을 그릴 수 있는 사람은 많이 있지만 데이터를 모델링할 수 있는 사람은 많지 않다 !!! Entity5 Entity4 Entity6 Entity5 Entity4 Entity1 Entity1 Entity3 Entity3 Entity2 ERD을 그릴 수 있는 사람은 많이 있지만 데이터를 모델링할 수 있는 사람은 많지 않다 !!! 무엇을 어떻게 이용할 것인가? 정보의 단절을 어떻게 막을 것인가? 융통성과 통합성을 어떻게 유지할 것인가? 관계형 데이터베이스 특성을 어떻게 반영할 것인가? 단순 명료하면서도 원하는 수행속도를 보장받을 수 있는가?

요소기술 리더의 역할 모든 요소기술에 대한 자문, 시스템관리, 신기술 연구 전파 DBA DESIGNER PROGRAMMER 모든 요소기술에 대한 자문, 시스템관리, 신기술 연구 전파 요소기술에 기반을 둔 설계, 업무 지식, 개발기술 자문 개발 툴 사용 능력, SQL 구사 능력, 업무 지식 그러나 현실은 ... PROGRAMMER DESIGNER DBA 3GL 스타일의 툴 사용, 초보단계의 SQL 구사 , 피상적인 업무 지식 RDBMS 개념 미정립, 방법론 부재, 언어구사 능력 미흡, 업무개선 의지 부족 시스템 개발 경험 부족, 낮은 경력, SP 출신 위주, 시스템 자원을 관리.통제하는 정도의 역할

개발자의 인식전환 비절차형 처리 방식 (집합 개념이 필요) 잘못된 설계는 시스템에 엄청난 영향을 미친다. 절차형 처리 방식 비절차형 처리 방식 (집합 개념이 필요) 잘못된 설계는 시스템에 엄청난 영향을 미친다. 요구만 잘하면 좋은 처리과정 은 저절로 만들어 진다. DBMS가 좋은 역할을 하도록 전략적인 FACTOR를 부여해야... 데이터는 여러 건을 동시에 처리할 수 있다. 가능한 온라인 프로그램이 되도록 해야 한다. 1000만건 이하라면 결코 많은 데이터가 아니다. 같은 데이터를 동시에 사용하면 효율이 더 좋아진다. 우수한 전문가는 훨씬 밝은 미래가 보장된다. 절차형 처리 방식 설계는 대충해도 프로그램 에서 특별처리하면 된다. 모름지기 모든 처리과정은 내가 직접 만들어야 한다. 로직 구사를 잘 하는 사람이 우수한 사람이다. 데이터는 한건씩 처리할 수밖 에 없다. 데이터는 출력해서 보는 것이다. 100만건이면 많은 데이터다. 같은 데이터를 동시에 사용하 면 경합이 생긴다. 경력이 많아지면 관리자가 되야한다.

RDB, 왜 쉽고도 어려운가? 결과 SQL OPTIMIZER SQL해석 실행 계획 실행 작성 참조 참조 추출 ㄴㅍㄴㅇㄹㅇㄹㄴ 률ㄷㄱ34346 ㅓㅏㄴ아ㅓㅗㄴㅇ ㅓㅜㄴ야ㄷㅈㄷㅂ저 ㅊ리아ㅡㄹ ㅏㅡ치ㅏ ㅜ ㅓ투 93 ㅑㅇ너ㅓㅇㄹ너ㅐㅇ러ㅐㄿㄹㅇㄹ ㅓㅜㄴ ㅑ ㅑㅕㅜㅑ ㅜ랸웅ㄴ ㅑ어ㅐ우ㅐㅇ눈애ㅓ래ㅓㅐ앵래 8ㅈ9ㅗ9 ㅗㅑ야 ㅏㄴ어ㅐ B BB JHBJB M M J ㅐㅜ ㅜㄹ울 애ㅣㅓ애럴애ㅓㄹ애 ㅐ ㄹ앙ㄹㄹ이ㅏㅡㅈ냐ㅈㄷㅂ989ㅈ돌ㅍㄴㅇㄴ ㄴ어ㅐㅑㅓ내ㅑ ㅑㅇ너ㅓㅇㄹ너ㅐㅇ러ㅐㄿㄹㅇㄹ ㅓㅜㄴ ㅑㅕㅇ ㅑㅕㅜㅑ ㅜ랸웅ㄴ ㅑ어ㅐ우ㅐㅇ눈애ㅓ래ㅓㅐ앵래 8ㅈ9ㅗ9 ㅗ ㅑ야 ㅏㄴ어ㅐㅑ퍼ㅐㅜ ㅜㄹ울 ㄹ앙ㄹㄹ이ㅏㅡㅈ냐ㅈㄷㅂ 결과 select col1, col2*10, . . from account x, custommer y, transection z where x.acct = z.acct and y.cust = z.cust and jdate = ‘970609’; DATA Dictionary SQL해석 COL$ IND$ OBJ$ TAB$ VIEW$ 참조 실행 계획 작성 참조 실행 c u s t o m e r a n i account DATA 추출 사용자는 요구만 하고 OPTIMIZER가 실행계획 수립 수립된 실행계획에 따라 엄청난 수행속도 차이 발생 실행계획 제어가 어렵다. OPTIMIZER가 좋은 실행계획을 수립할 수 있도록 종합적이고 전략적인 FACTOR를 부여 비절차형으로 기술해야 함 집합적으로 접근해야 함 SQL이 어떤 역할을 담당하도록 구현할 것인가?

병렬처리(parallel operation) 병렬 조회 (Parallel Query Execution) 병렬 데이타 로딩 (Parallel Data Loading) 병렬 인덱스 생성 (Parallel Index Creation) 병렬 복구 (Parallel Recovery) 관계형 데이타베이스 기술은 다양한 시스템의 요구조건을 만족시키기 위하여 발전을 거듭해 왔읍니다만, 데이타베이스의 크기, 복잡성, 동시 사용자수 등이 일정 한계를 넘어서게 되면 문제가 발생하고 있읍니다. ORACLE 7.1에서는 이러한 문제를 해결하기 위해서 병렬처리, 데이타 이중화 및 시스템 관리부분의 개선을 이룩하였으며, 그 중 특히 병렬처리 기법은 SMP, 클러스터, MPP 등의 어떤 구조의 하드웨어에서도 실행이 가능하며 , 방대한 량의 데이타 처리를 프로세서의 수에 거의 비례하여 향상 시켜 이제는 다량 데이타 처리에 오히려 RDBMS가 더 유리해지게 되었읍니다. 병렬처리에는 다량 데이타의 스캔, SORT, 조인 등의 응답시간을 현저하게 줄여 주는 병렬조회와 일반 File의 데이타를 ORACLE로 LOAD할 때 복수개의 세션을 부여하여 신속하게 Loading 하는 병렬데이타 로딩, 대량 데이타의 인덱스 생성시간을 줄여 주는 병렬인덱스 생성, 시스템에 이상이 발생하였을 때 이를 신속히 복구시켜 주는 병렬복구 기능으로 크게 나눌 수가 있읍니다. 지금부터 병렬처리에 대해 좀 더 상세히 알아 보겠읍니다.

왜 병렬로 처리해야 하는가? 복잡한 질의어의 수행속도 개선 많은 부하의 업무도 무리없이 수행 배치 작업의 수행속도 개선 병렬처리 기술을 사용하지 않는 경우 단지 1 CPU 만 사용 병렬처리 기술을 사용하는 경우 모든 CPU를 사용 복잡한 질의어의 수행속도 개선 많은 부하의 업무도 무리없이 수행 배치 작업의 수행속도 개선 저렴한 가격으로 시스템 구성

실행구조 query coordination Select sum(revenue), store from line_items where profit (price,cost,units) > 0.2 group by store SQL Sort A ~ K Sort L ~ S Sort T ~ Z sort by store sum revenues Data query coordination dynamic data partitioning Scan scan line_items executing "profit" function in parallel

병렬처리 분할구조 정적 분할 동적 분할 Processors Processors CPU CPU Data Data 임의의 위치에 데이터 분산 저장 데이터 파일별 독립적인 관리 불가능 불규칙한 분포도를 가진 처리범위 작업시 상대적으로 유리 다량의 데이터 처리에 부담 정해진 CPU를 바꾸지 못함 끝난 CPU가 작업중인 CPU를 도와주지 못함 원하는 위치에 데이터를 저장 독립적인 INDEX 재생성 가능 데이터 파일별 독립적인 ONLINE, OFFLINE 전략적인 분할대책 수립이 필요 다량의 데이터 관리에 유리

병렬처리 사용방법 생성시 같이 정의 이미 작성된 오브젝트에 대한 병렬처리 해당 SQL만 병렬로 처리 : Hint 기능 사용 create table line_item (item_no number(7), item_name varchar(20), ... ) parallel 20; create index item_index on line_item (item) parallel 20; 이미 작성된 오브젝트에 대한 병렬처리 alter table line_item parallel 20; alter index item_index rebuild parallel 20; 응용프로그램의 수정없이 즉시 사용 해당 SQL만 병렬로 처리 : Hint 기능 사용 select /*+ parallel(line_item,20) */ item_name, ... from line_item, products .....

병렬처리 적용범위 TABLE ACCESS (FULL) MERGE JOIN NESTED LOOPS HASH JOIN SORT (GROUP BY) SORT (JOIN) SORT (ORDER BY) SORT (UNIQUE) AGGREGATE (GROUP BY) CREATE TABLE AS SELECT ... FROM ... UNION, UNION ALL ALTER INDEX .... REBUILD ...

병렬데이타 로딩 (Parallel Data Loading) SQL*Loader를 여러개의 세션에서 동시 수행 동일한 테이블에 동시에 Fast Bulk Load CPU 갯수에 따라 선형적인 수행성능 증가 1백만건 sqlload parallel=true sqlload parallel=true 1백만건 다량의 일반 파일을 ORACLE 테이블로 Load 하기 위해서 병렬 데이타 로딩 기능을 사용하여 수백, 수천만건의 데이타를 빠른 시간에 Load 할 수가 있읍니다. Load 유틸리티인 SQL*Loader를 그림에서와 같이 'sqlload parallel=true' 로 지정하면 여러 개의 세션에서 동시에 하나의 테이블로 Load를 할 수 있으므로 CPU 갯수에 따라 거의 비례적으로 수행속도를 증가시킬 수가 있읍니다. sqlload parallel=true 1백만건

병렬인덱스 생성 (Parallel Index Creation) CREATE INDEX 명령어 수행시 create index idx_1 on line_item(ltem_no) parallel 10; 여러개의 CPU에서 동시에 수행 인덱스 생성에 필요한 Table Scan, Sort 기능을 병렬로 수행 초기 DB 데이타 적재(LOAD) 시 인덱스 재구성 시 alter index idx_1 rebuild parallel 10; BITMAP INDEX 생성 뒤에 말씀을 드리게 되겠지만 인덱스는 RDB에서 아주 중요한 역활을 하게 되며, 이 인덱스는 인덱스 컬럼과 ROWID 순으로 SORT되어 생성되 므로 대용량의 테이블의 인덱스 생성은 많은 시간을 필요로 하게 됩니다. 생성 방법은 CREATE INDEX 명령어 수행시 Parallel 20개로만 주면 여러 개의 CPU에서 동시에 작업이 수행되어 인덱스 생성에 필요한 TABLE SCAN, SORT를 Parallel 로 처리합니다. 이 처리방식은 초기에 데이타베이스로 데이타를 생성시킬 때 많이 사용되며 일반적으로 인덱스는 필요에 따라 자주 인덱스를 재구성하는 경우가 많은 데 이런 경우 병렬처리로 인덱스를 생성하게 되면 쉽게 이를 극복할 수가 있을 것 입니다.

Client/Server Security Data Integrity Client Request DBMS Server DBMS Call Data Implicit Auditing Computed Values Operations Cascading Operations Integrity Referential Client Security Profiles Access Control Entity Complex Request DBMS Server 누가 어떤 부분을 처리하도록 할 것인가 ? 호출회수를 어떻게 줄일 것인가 ? 세션을 계속 유지할 것인가 ? 5

내부 구조 Server Listener Dispatcher Dispatcher Database Buffer PMON LCKn RECO SMON User Listener User User Dispatcher Dispatcher Request Queue Response Queue Database Buffer Shared SQL Pool Server Multi-Threaded Server Redo Log Buffer Dedicated Server Dedicated Server ARCH ORACLE의 내부구조를 통해 좀 더 상세히 말씀 드리겠읍니다. 먼저, 우측에 있는 SYSTEM GLOBAL AREA 와 각종 BACK GROUND PROCESSOR들을 간략하게 설명하드리면, 우측 중간에 있는 BOX는 메모리내에 있는 SGA, 즉 시스템 공유영역입니다. 여기에는 데이타를 서로 공유하기 위한 Database Buffer가 있고, SQL을 공유하기 위한 Shared SQL Pool이 있읍니다. 그 밑의 녹색부분의 Redo log buffer는 변경된 데이타를 잠시 보관했다가 LGWR에 의해 하면하단의 붉은 색 파일로 표시된 Redo Log 파일에 Write 하게 됩니다. Redo log file은 디스크 크레쉬를 대비해 여러개의 Group으로 지정할 수 있으며, 이 또한 대용량 시스템에서 안정성을 보장하기 위해 아주 중요한 기능이 될 것입니다. 가득 찬 Log file은 ARCH 프로세서에 의해 디스크나 TAPE로 COPY 됩니다. Database Buffer 에 있던 내용은 필요에 따라 DBWR에 의해 Database File로 Write 되며, 우측 상단에 있는 PMON 과 SMON은 USER 프로세서나 ORACLE 시스템을 Monitoring 하고 있다가 이상이 발생되면 즉시 이를 복구 시켜 주는 역할을 합니다. 이제 좌측에 있는 멀티 쓰레디드 서버를 말씀드리겠읍니다. 이 내용을 보다 명확하게 설명하기 위해서 보다 상세하게 표현된 다음 화면으로 넘어 가겠 읍니다. DBWR CKPT LGWR Dedicated Server A B C A B C A B C User User User Group Database Files Redo Log File

? ? ? 병렬 SERVER 1 + 1 + 1 = 3 가용성 성능향상 비용절감 어떻게 PING을 감소시킬 것인가 $$$$$ 100 Users 100 MIPS $$ 가용성 성능향상 비용절감 ? 1 + 1 + 1 = 3 어떻게 PING을 감소시킬 것인가 단위 SERVER당 세션 수는 과연 감소하는가 ? ? 12

공유 메모리 캐쉬 SGA SQL1 SQL1 SQL2 DATA DATA Shared SQL Pool Database Buffer Cache R2 Txn2 Col Addr 500 SQL1 의 SQL2 의 Context Context Area Area R1 Header Block B1 Header Block B2 CLERK 550 DATA DATA 또한, ORACLE은 데이타를 서로 공유하기 위해 커다란 데이타 공유영역, 즉 Database Buffer Cache 를 가지고 있읍니다. 이것은 마치 저수지나 댐이 가뭄과 홍수에 대비하기 위해 항상 일정량의 수위를 유지시키는 것과 같이 보다 많은 데이타의 공유를 위해 가능한 많은 량의 데이타를 메모리에 상주시키면서 새로운 작업을 위한 Free Space를 적절히 유지하기 위해 LRU 알고리즘에 의해 오래된 데이타부터 필요에 따라 수시로 교체해 신선한 물이 저수지에 있도록 하는 것입니다. User가 어떤 데이타를 요구한 데이타가 메모리에 있다면 바로 메모리에서 제공하게 되면 없을 때만 디스크를 엑세스 하게 되는 것입니다. 가끔 이런 질문을 많이 들어 보게 됩니다. "우리가 구축하고자 하는 시스템은 동일한 파일을 수 많은 User들이 동시에 엑세스해야 하는데 디스크 Bottleneck이 우려되므로 어떻게 하면 좋겠느냐" 는 식의 걱정을 많이 합니다. 그러나, 이런 걱정은 전혀 하실 필요가 없읍니다. 데이타를 메모리내에서 공유하기 때문에 어차피 읽어야 할 데이타라면 오히려 빈번할 수록 더욱 유리해지기 때문입니다. Database Buffer Cache에는 User 데이타 뿐만 아니라 파싱을 위한 Data Dictionary 정보를 저장하는 캐쉬 등을 별도로 가지고 있어 보다 많은 부분 에 메모리 엑세스를 가능하게하여 수행속도를 개선시킵니다. SQL1 SQL1 SQL2

멀티블럭처리 SGA Server LGWR DBWR User Data Files Log Files Shared Pool Database Buffer Cache Redo Log Buffer Shared SQL Area Server LGWR DBWR 오라클은 I/O의 효율을 위해서 모든 처리를 멀티블럭, 즉 여러개의 블럭을 모아 두었다가 동시에 처리하여 Peak Time 시의 Bottleneck을 피할 수 있읍 니다. Datafile에 있는 데이타블럭이 공유 메모리내로 올라오거나 DBWR를 통해 다시 쓰여질 때 뿐만 아니라 Log Buffer에 있는 수정된 내용들이 LGWR 를 통해 쓰여 질 때나 처리한 데이타를 USER에게 제공할 때도 항상 멀티블럭 으로 처리됩니다. 또 하나 중요한 것은 이러한 매커니즘을 가능하게 하기 위해 Deffed Write, 즉 지연기록을 하게 됩니다. 이 개념은 User가 어떤 데이타를 수정하여 Commit을 시켰다고 하더라도 그 즉시 수정된 내용이 디스크에 쓰여지지 않고 나중에 필요에 따라 여러개의 블럭을 동시에 Write 하지만 사용자에게는 언제나 Commit 된 데이타를 보게 하는 개념입니다. 그러므로 Peak Time이라 하더라도 디스크 Bottleneck이 걸리지 않기 때문에 대용량 시스템에서는 아주 중요한 아키텍쳐라고 할 수 있읍니다. Data Files User Log Files

경합없는 조회 SGA DBWR LGWR A B C Server 2 Server 1 User 2 User 1 Shared Pool Database Buffer Cache Redo Log Buffer R2 Txn2 Col Addr 500 Shared SQL Area R1 Header Block 500 550 B1 Header Block B2 CLERK 550 DBWR LGWR Server 2 Server 1 User 2 User 1 A B Block B1 B2 R1 EMP Table R2 RB01 SQL> SELECT sal From emp WHERE job='CLERK' C SQL> UPDATE emp SET sal=sal*1.1 WHERE job='CLERK'

행단위 LOCKING (Row-Level Locking) Page-Level Lock user 1 user 2 user 3 user 4 UPDATE UPDATE UPDATE Waiting BLOCK ORACLE은 ROW-LEVEL LOCKING, 즉 행단위로 LOCK을 관리하는 것을 말합니다. 기존의 Page Level Locking 이나 일부 RDBMS에서 가지 고 있는 Block-level Locking 과는 큰 차이가 있으며, 특히 많은 USER가 사용하는 대용량 시스템에서는 그 차이가 아주 심하게 나타나게 됩니다. 그림을 통해 비교해 보겠읍니다. USER1 이 어떤 ROW를 Update 했읍니다. Row-level Locking 에서는 다른 사람이 동일한 ROW를 Update 하고 있지 않는 한 Lock Waiting 이 되지 않으므로 USER2가 Update 를 수행 했읍니다. 그러나 Page-level Locking을 하는 경우에는 어느 한 USER 만 Update를 하고 있어도 해당 Page는 모두 Lock이 걸리게 되므로 Waiting을 하게 될 것입니다. ORACLE은 3번 과 4번 USER 처럼 동일한 ROW를 Update 하고자하면 데이타의 일관성 유지를 위해 Lock을 발생시키게 됩니다. 그러면, 앞서 말씀드린 경합없는 조회와 Row level Locking 에 대한 DEMO 를 간단하게 보여 드리겠읍니다. Waiting UPDATE Waiting Waiting BLOCK PAGE

. . for update 경합의 해소 .... col1 col2 Lock 10 AA 20 AB 실패 20 AB 20 CM select col1, col2 from AT where col1 = '20' and rownum = 1 for update; Lock 처리 Commit col1 col2 10 AA 20 AB 실패 select col1, col2 from AT where col1 = '20' and rownum = 1 for update; 재시도 처리 Commit 20 AB select col1 from AT where col1 = '10' and rownum = 1 for update; 실패 재시도 실패 재시도 처리 Commit 20 CM . 20 SK 21 BA . AT table select col1, col2 from AT where col1 = '20' and rownum = 1 for update; .... 실패 실패 재시도 재시도 처리 Commit

. . . . for update 경합의 해소 . col1 col2 10 AA 20 AB 20 AB 20 CM 20 SK select squ.nextval into :A from dual; select rnum, col1, col2 from (select rownum as rnum,col1,col2 from AT where col1 = '20') where rnum = :A and rownum = 1; CREATE SEQUENCE squ start with 1 increment by 1 maxvalue 10 cycle cache 9 col1 col2 select squ.nextval into :A from dual; select rnum, col1, col2 from (select rownum as rnum,col1,col2 from AT where col1 = '20') where rnum = :A and rownum = 1; 10 AA 20 AB 20 AB select squ.nextval into :A from dual; select rnum, col1, col2 from (select rownum as rnum,col1,col2 from AT where col1 = '20') where rnum = :A and rownum = 1; 20 CM . 20 SK 20 BA . . . AT table select squ.nextval into :A from dual; select rnum, col1, col2 from (select rownum as rnum,col1,col2 from AT where col1 = '20') where rnum = :A and rownum = 1; .

부분범위처리 (Partial Range Scan)

부분범위처리 (속도향상 원리) YES NO 수원갑니까? NO YES 인천갑니까? 인천 수원

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

부분범위처리 (Partial Range Scan) 조건을 만족하는 전체집합이 아닌 일부분만 ACCESS DATA양이 많아도 PERFORMANCE에 지장이 없고, 오히려 향상 INDEX나 CLUSTER를 적절히 활용한 SORT의 대체 MAX 처리 TABLE은 ACCESS하지않고 INDEX만 사용하도록 유도 EXISTS의 활용 ROWNUM의 활용 QUERY를 이원화 하여 일부분씩 SCAN하도록 유도 Stored Function을 이용

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

부분범위처리 SQL 예제 (SORT 대체) 0.01 sec 5.2 sec 12.5 sec 0.02 sec 21200 SORT ORDER BY 21200 TABLE ACCESS BY ROWID ORDER1T 21201 INDEX RANGE SCAN ORD_ORDDATE SQL> SELECT ORDDATE, CUSTNO FROM ORDER1T WHERE ORDDATE between '940101' and '941130' ORDER BY ORDDATE DESC 5.2 sec 20 INDEX RANGE SCAN DESCENDING ORDDATE SQL> SELECT --+ INDEX_DESC(A orddate) ORDDATE, CUSTNO FROM ORDER1T A WHERE ORDDATE between '940101' and '941130' 0.01 sec 42000 SORT ORDER BY 42000 TABLE ACCESS BY ROWID ORDER1T 42001 INDEX RANGE SCAN ORD_ORDDEPT SQL> SELECT ORDDATE, CUSTNO FROM ORDER1T WHERE ORDDEPT LIKE '7%' ORDER BY ORDDATE DESC 12.5 sec 20 INDEX RANGE SCAN DESCENDING ORDDATE SQL> SELECT --+ INDEX_DESC(A orddate) ORDDATE, CUSTNO FROM ORDER1T A WHERE ORDDEPT LIKE '7%' AND ORDDATE <= '991231' 0.02 sec sjskjskjs

부분범위처리 (Index만 처리) SELECT DEPT, SUM(QTY) FROM PRODUCT WHERE DEPT LIKE '12%' GROUP BY DEPT; 운반단위 INDEX (DEPT) TABLE GROU P B Y SELECT DEPT, SUM(QTY). FROM PRODUCT WHERE DEPT LIKE '12%' GROUP BY DEPT; 운반단위 INDEX (DEPT+QTY) GROU P B Y

부분범위처리 (MAX 처리) SELECT /*+ INDEX_DESC( A INDEX1) */ SELECT NVL(MAX(SEQ), 0) + 1 FROM PRODUCT WHERE DEPT = '12300'; 운반단위 INDEX (DEPT) TABLE S O R T MAX(SEQ)+1 SELECT /*+ INDEX_DESC( A INDEX1) */ SEQ + 1 FROM PRODUCT A WHERE DEPT = '12300' AND ROWNUM = 1; 운반단위 INDEX 1 (DEPT+SEQ) SEQ + 1

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

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

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

1:M JOIN의 부분범위 유도 전체범위 부분범위 SUB_QUERY 의 수행결과를 MAIN_QUERY 에서 사용할 수 없음 SELECT x.CUST_NO, x.ADDR, x.NAME, ............. FROM CUST x, REQT y WHERE x.CUST_NO = y.CUST_NO AND x.CUST_STAT in ('A', 'C', 'F') AND y.UN_PAY > 0 GROUP BY x.CUST_NO HAVING SUM(y.UN_PAY) between :VAL1 and :VAL2 전체범위 SELECT x.CUST_NO, x.ADDR, x.NAME, ............. FROM CUST x WHERE CUST_STAT in ('A', 'C', 'F') AND EXISTS ( SELECT 'X' FROM REQT y WHERE y.CUST_NO = x.CUST_NO AND UN_PAY > 0 GROUP BY x.CUST_NO HAVING SUM(y.UN_PAY) between :VAL1 and :VAL2 ) SUB_QUERY 의 수행결과를 MAIN_QUERY 에서 사용할 수 없음 부분범위

1:M JOIN의 부분범위 유도 전체범위 부분범위 Create or replace Function unpay_sum SELECT x.CUST_NO, x.ADDR, x.NAME, ............. FROM CUST x, REQT y WHERE x.CUST_NO = y.CUST_NO AND x.CUST_STAT in ('A', 'C', 'F') AND y.UN_PAY > 0 GROUP BY x.CUST_NO HAVING SUM(y.UN_PAY) between :VAL1 and :VAL2 ; 전체범위 부분범위 Create or replace Function unpay_sum (v_custno in varchar2) return number is sum_unpay number ; begin .............. select sum(un_pay) into sum_unpay from reqt where cust_no = v_custno and un_pay > 0 ; ................ return sum_unpay ; end unpay_sum ; Select cust_no, addr, un_pay, ........... from ( select cust_no, addr, unpay_sum(cust_no) as un_pay, .... from cust where cust_stat in ('A', 'C', 'F') ) where un_pay between :VAL1 and :VAL2

QUERY 이원화를 이용한 부분범위 처리 연간 개인별 급여 현황 부서코드 기준일 96 / 06 / 10 11% 부서코드 성 명 사 번 직 책 본 봉 수 당 입사일 1110 홍길동 12345 부장 42,000,000 5,000,000 82/10/10 1110 박문수 13674 과장 34,000,000 2,400,000 85/01/12 1110 김태수 14351 대리 27,000,000 91/08/15 1110 홍민철 14879 사원 24,000,000 94/09/20 1110 이수형 15278 사원 22,000,000 95/01/01 1120 김명훈 13565 부장 43,100,000 5,000,000 81/03/15 1120 정창수 18126 과장 35,240,000 2,000,000 84/10/20 SELECT 부서코드, 사번, min(직책), sum(본봉), sum(수당) ... FROM 급여테이블 x, 사원테이블 y WHERE x.사번 = y.사번 AND x.부서코드 LIKE ‘11%’ AND x.급여일 between ‘970101’ and ‘971231’ GROUP BY 부서코드, x.사번 SELECT 부서코드 into :DEPTNO FROM 부서테이블 WHERE 부서코드 like ‘11%’ ; 여기서 :DEPTNO는 배열변수로 지정된 호스트 변수이며 부서코드는 인덱스를 가짐 SELECT :DEPTNO, 사번, min(직책), sum(본봉), sum(수당) ... FROM 급여테이블 x, 사원테이블 y WHERE x.사번 = y.사번 AND x.부서코드 = :DEPTNO AND x.급여일 between ‘970101’ and ‘971231’ GROUP BY x.사번

INDEX의 구조 INDEX (JOB) TABLE (EMP) SELECT empno, ename, job FROM emp WHERE job between '부장' and '이사' INDEX-KEY ROWID EMPNO ENAME JOB 과장 0000A95B.0002.0001 7654 강감찬 부장 과장 0000A95B.0005.0001 7900 류관순 과장 과장 0000E62E.0009.0001 7689 황진이 과장 과장 0000E9BE.0002.0001 7934 변강쇠 부장 부장 000062BE.0001.0001 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 김두환 부장 INDEX (JOB) TABLE (EMP)

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

INDEX COLUMN의 변형(external) SELECT * FROM EMP WHERE SUBSTR(ENAME,1,3) = 'ABC' SELECT * FROM EMP WHERE ENAME LIKE 'ABC%' SELECT * FROM EMP WHERE SAL * 12 = 12000000 SELECT * FROM EMP WHERE SAL = 12000000 / 12 SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYMMDD') = '940101' SELECT * FROM EMP WHERE HIREDATE = TO_DATE('940101','YYMMDD') SELECT * FROM EMP WHERE NVL(COMM,0) < 100 ?

INDEX COLUMN의 변형(external) SELECT * FROM EMP WHERE EMPNO BETWEEN 100 AND 200 AND NVL(JOB,'X') = 'CLERK' SELECT * FROM EMP WHERE EMPNO BETWEEN 100 AND 200 AND JOB = 'CLERK' SELECT * FROM EMP WHERE DEPTNO || JOB = '10SALESMAN' SELECT * FROM EMP WHERE DEPTNO = '10' AND JOB = 'SALSMAN' 의도적인 SUPPRESSING SELECT * FROM EMP WHERE JOB = 'MANAGER' SELECT * FROM EMP WHERE RTRIM(JOB) = 'MANAGER' SELECT * FROM EMP WHERE EMPNO = 8978 SELECT * FROM EMP WHERE RTRIM(EMPNO) = 8978

SUPPRESSING 예제 4 rows, 0.51 sec 4 rows, 0.03 sec rows, sec rows, sec SQL> SELECT CUSTNO, CHULDATE FROM CHULGOT WHERE CUSTNO = 'DN02' AND STATUS = '90' TABLE ACCESS BY ROWID CHULGOT AND-EQUAL INDEX RANGE SCAN CH_STATUS INDEX RANGE SCAN CH_CUSTNO 4 rows, 0.51 sec SQL> SELECT CUSTNO, CHULDATE FROM CHULGOT WHERE CUSTNO = 'DN02' AND RTRIM(STATUS) = '90' TABLE ACCESS BY ROWID CHULGOT INDEX RANGE SCAN CH_CUSTNO 4 rows, 0.03 sec SQL> SELECT CUSTNO, CHULDATE FROM CHULGOT WHERE CUSTNO LIKE 'DN%' AND STATUS LIKE '9%' rows, sec sjskjskjs SQL> SELECT CUSTNO, CHULDATE FROM CHULGOT WHERE CUSTNO LIKE 'DN%' AND RTRIM(STATUS) LIKE '9%' rows, sec

SUPPRESSING 예제 rows, sec rows, sec rows, sec rows, sec SQL> SELECT X.CUSTNO, CHULDATE, CUSTNAME FROM MECHUL1T X, MECHUL2T Y WHERE X.SALENO = Y.SALENO AND X.SALEDEPT = '710' AND Y.SALEDATE LIKE '9411%' rows, sec SQL> SELECT X.CUSTNO, CHULDATE, CUSTNAME FROM MECHUL1T X, MECHUL2T Y WHERE X.SALENO = Y.SALENO AND RTRIM(X.SALEDEPT) = '710' AND Y.SALEDATE LIKE '9411%' rows, sec SQL> SELECT X.ORDNO, ORDDATE, ITEM FROM ORDER1T X, ORDER2T Y WHERE X.ORDNO = Y.ORDNO AND X.ORDDATE LIKE '9411%' AND Y.ORDDEPT = '710' ORDER BY ORDDATE rows, sec sjskjskjs SQL> SELECT X.ORDNO, ORDDATE, ITEM FROM ORDER1T X, ORDER2T Y WHERE RTRIM(X.ORDNO) = Y.ORDNO AND X.ORDDATE LIKE '9411%' AND Y.ORDDEPT = '710' rows, sec

INDEX COLUMN의 변형(internal) SELECT * FROM SAMPLET WHERE CHA = 10 CREATE TABLE SAMPLET ( CHA CHAR(10), NUM NUMBER (12,3), VAR VARCHAR2(20), DAT DATE) SELECT * FROM SAMPLET WHERE TO_NUMBER(CHA) = 10 SELECT * FROM SAMPLET WHERE NUM LIKE '9410%' SELECT * FROM SAMPLET WHERE DAT = '01-JAN-94' SELECT * FROM SAMPLET WHERE TO_CHAR(NUM) LIKE '9410%' SELECT * FROM SAMPLET WHERE DAT = TO_DATE('01-JAN-94')

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

INDEX COLUMN의 변형 (internal) 예제 SQL> SELECT SUM(UNCOST) FROM CHULGOT WHERE STATUS = 90 TABLE ACCESS FULL CHULGOT 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 sjskjskjs 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

NOT Operator ‘천씨’ 성을 갖는 사람도 극소 이며 영업사원도 극소이므로 ENAME_IDX와 JOB_IDX 모두 SELECT 'Not found !' INTO :COL1 FROM EMP WHERE EMPNO <> '1234' SELECT ’Not found' INTO :COL1 FROM DUAL WHERE NOT EXISTS ( SELECT '' FROM EMP WHERE EMPNO = '1234') SELECT * FROM EMP WHERE ENAME LIKE ’천%' AND JOB <> 'SALES' SELECT * FROM EMP a WHERE a.ENAME LIKE ’천%' AND NOT EXISTS ( SELECT '' FROM EMP b WHERE a.Empno = b.Empno AND b.JOB = 'SALES') ‘천씨’ 성을 갖는 사람도 극소 이며 영업사원도 극소이므로 ENAME_IDX와 JOB_IDX 모두 효율이 좋다. SELECT * FROM EMP WHERE ENAME LIKE ’천%' MINUS FROM EMP b WHERE b.JOB = 'SALES'

NULL, NOT NULL SELECT * FROM EMP WHERE ENAME IS NOT NULL SELECT * WHERE COMM IS NOT NULL SELECT * FROM EMP WHERE COMM > 0 SELECT * FROM EMP WHERE COMM IS NULL

¥ ¥ ¥ ¥ ¥ ? ? ? ? ? _ _ = = + + NULL 값의 적용 기준 10 10 혼돈 NULL 이란 ? 모르는 값도 1,A등의 값과 같이 하나의 값이다. ¥ ¥ ¥ _ = ¥ ¥ + 10 _ ? ? ? = ? ? + 10 어떤 값보다 크지도 않고 작지도 않다. 그러므로 어떤 값과 비교될 수 없다. 즉, NULL과의 연산결과는 NULL이 된다. DATA 소실우려 NULL 공포증 확실한 DEFAULT 처리 확정된 것 없다 아직 미확정 혼돈 TABLE PROGRAM 적 용 기 준 미확정 값을 표현하고자 할 때 특정 값이 지나치게 많고 나머지 값만 주로 인덱스로 액세스하고자 할 때 (예; 완료,미결) 결합인덱스의 구성컬럼이 된다면 NOT NULL로 입력조건값으로 자주 사용되면 NOT NULL로

SELECT /*+ INDEX(EMP JOB_IDX) */ * OPTIMIZER의 취사 선택 EMPNO Index만 사용 SELECT * FROM EMP WHERE ENAME LIKE 'AB%' AND EMPNO = '7890' RANKING 의 차이 ENAME or JOB index중 하나만 사용 , 혹은 Full scan SELECT * FROM EMP WHERE ENAME LIKE 'AB%' AND JOB LIKE 'SA%' INDEX Merge 회피 FULL Table Scan SELECT * FROM EMP WHERE EMPNO > '10' Low COST 의 선택 JOB Index만 사용 SELECT /*+ INDEX(EMP JOB_IDX) */ * FROM EMP WHERE ENAME LIKE 'AB%' AND JOB LIKE 'SA%' HINT 에 의한 선택

COST_based vs RULE_based SELECT * FROM EMP WHERE JOB = 'SALESMAN' AND EMPNO = '7890' INDEX merge (and_equel) Rule based SELECT * FROM EMP WHERE ENAME LIKE 'AB%' AND EMPNO = '7890' 항상 EMPNO Index만 사용 SELECT * FROM EMP WHERE ENAME BETWEEN '1101' AND '1210' AND JOB LIKE 'SA%' 항상 나중에 생성된 Index만 사용 SELECT * FROM EMP WHERE JOB = 'SALESMAN' AND EMPNO = '7890' INDEX merge (and_equel), 특정 iNDEX Cost based SELECT * FROM EMP WHERE ENAME LIKE 'AB%' AND EMPNO = '7890' 분포도에 따라 ENAME index 도 사용 SELECT * FROM EMP WHERE ENAME LIKE 'AB%' AND JOB LIKE 'SA%' 분포도에 따라 index 사용, 혹은 full scan

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

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

INDEX Merge SELECT COL1, COL2, ...... FROM TAB1 WHERE COL1 = 'ABC' AND COL2 = '123' ; COL1 rowid COL2 rowid ABC 10 ABC 15 ABC 26 ABC 28 ABC 32 ABC 67 BCA 12 BCA 27 BCA 48 BCA 85 CBA 35 CBA 48 CBA 64 CBA 99 . . . . . . . 123 7 123 9 123 32 123 35 123 46 123 52 123 56 123 65 123 67 123 68 123 75 123 99 321 48 321 85 . . . . . . 32 ABC 123 . . . . . 67 ABC 123 . . . . . INDEX (COL1) INDEX (COL2) TABLE (TAB1)

INDEX MERGE (결합 인덱스와 비교) INDEX MERGE 결합 인덱스 ABC 55 10 ABC 67 15 CBA 85 35 CBA 123 46 CBA 214 64 . . . . . . . . . COL1 COL2 rowid INDEX (COL1 + COL2) 결합 인덱스 INDEX (COL1) COL1 rowid COL2 rowid . . . . . . 100 29 105 10 123 7 123 9 123 32 123 35 123 46 123 52 123 56 123 65 123 67 123 68 123 75 . . . . . . ABC 10 ABC 15 ABC 26 ABC 28 ABC 32 ABC 67 BCA 12 BCA 27 BCA 56 BCA 85 CBA 35 CBA 46 CBA 64 CBA 99 . . . . . . . INDEX (COL2)

INDEX MERGE (불리한 경우) 처리량이 오히려 증가 처리량이 훨씬 감소 SELECT COL1, COL2, ...... FROM TAB1 WHERE A = 'ABC' AND B LIKE '12%' ; INDEX 를 MERGE하여 A와 B의 조건을 모두 만족하는 ROW만 선택 TABLE ACCESS BY ROWID 운반 단위 INDEX(A) INDEX(B) TABLE 처리량이 오히려 증가 A INDEX만 사용하여 TABLE ACCESS BY ROWID 운반 단위 TABLE ACCESS 후 B 조건을 CHECK X O INDEX(A) TABLE 처리량이 훨씬 감소 Index Merge는 테이블 액세스를 하기 전에 먼저 인덱스간에 Merge를 통해 테이블 액세스 양을 줄여준다. 그러나 Merge 양이 늘어나 테이블을 랜덤으로 액세스하는 일량보다 크게 된다면 오히려 불리하게 된다. 즉, Merge 되는 index의 분포도 편차가 크고 Merge되는 비율이 높을수록 (Merge되는 Rows의 수가 많다면) Index merge는 불리하게 된다.

INDEX MERGE (유리한 경우) MERGE 되는 INDEX들의 분포도 차이가 적고 SELECT COL1, COL2, ...... FROM TAB1 WHERE A = 'ABC' AND B = '123' ; MERGE 되는 INDEX들의 분포도 차이가 적고 MERGE 된 ROW의 수가 아주 적은 경우 INDEX MERGE 운반 단위 INDEX(A) INDEX(B) TABLE 유 리 운반 단위 X O INDEX(A) TABLE SINGLE INDEX 불 리 Merge되는 Index들의 분포도 차이가 적고 Merge된 Row의 수가 아주 적은 경우에는 Index Merge가 유리하게 된다.

INDEX MERGE (유리한 경우) MERGE 되는 비율이 높고 부분범위 처리를 하는 경우 INDEX MERGE 유 리 SELECT 'X' FROM DUAL WHERE EXISTS ( SELECT 'X' FROM TAB1 WHERE A = 'ABC' AND B = '123' ) ; MERGE 되는 비율이 높고 부분범위 처리를 하는 경우 운반 단위 INDEX(A) INDEX(B) TABLE INDEX MERGE 유 리 운반 단위 X O INDEX(A) TABLE SINGLE INDEX 불 리

INDEX MERGE 예제 1 rows, 0.02 sec 1 rows, 0.02 sec 4 rows, 0.74 sec SQL> SELECT CUSTNO, CHULDATE FROM CHULGOT WHERE CUSTNO = 'DN02' AND CHULDATE = '941003' TABLE ACCESS BY ROWID CHULGOT AND-EQUAL INDEX RANGE SCAN CH_CUSTNO INDEX RANGE SCAN CH_CHULDATE 1 rows, 0.02 sec SQL> SELECT CUSTNO, CHULDATE FROM CHULGOT WHERE CUSTNO = 'DN02' AND CHULDATE LIKE '9410%' TABLE ACCESS BY ROWID CHULGOT INDEX RANGE SCAN CH_CUSTNO 1 rows, 0.02 sec SQL> SELECT --+ AND_EQUAL(A ch_custno ch_status) CUSTNO, CHULDATE FROM CHULGOT A WHERE CUSTNO = 'DN02' AND STATUS = '90' TABLE ACCESS BY ROWID CHULGOT AND-EQUAL INDEX RANGE SCAN CH_STATUS INDEX RANGE SCAN CH_CUSTNO 4 rows, 0.74 sec sjskjskjs SQL> SELECT --+ INDEX(A ch_custno) CUSTNO, CHULDATE FROM CHULGOT A WHERE CUSTNO = 'DN02' AND STATUS = '90' TABLE ACCESS BY ROWID CHULGOT INDEX RANGE SCAN CH_CUSTNO 4 rows, 0.02 sec

INDEX MERGE 와 결합 INDEX 비교 예제 SQL> SELECT --+ AND_EQUAL(A ch_chuldate ch_status) CUSTNO, CHULDATE FROM CHULGOT A WHERE CHULDATE = ‘941003’ AND STATUS = ’90’ TABLE ACCESS BY ROWID CHULGOT AND-EQUAL INDEX RANGE SCAN CH_STATUS INDEX RANGE SCAN CH_CHULDATE 13 rows, 0.21sec TABLE ACCESS BY ROWID CHULGOT INDEX RANGE SCAN CH_STATUS SQL> SELECT CUSTNO, CHULDATE FROM CHULGOT WHERE CHULDATE LIKE ‘9501%’ AND STATUS = ’90’ 0 rows, 6.05sec SQL> CREATE INDEX CH_DEPT_STATUS ON CHULGOT (CHULDATE,STATUS) SQL> SELECT CUSTNO, CHULDATE FROM CHULGOT WHERE CHULDATE LIKE ‘9801%’ AND STATUS = ’40’ rows, sec SQL> SELECT CUSTNO, CHULDATE FROM CHULGOT A WHERE CHULDATE = ‘980120’ AND STATUS = ’90’ 13 rows, 위의 조건은 0.01초 정도 소요되나 아래 조건은 5초 정도 소요됨…..

EQUAL이 결합인덱스에 미치는 영향 SELECT * FROM TAB1 WHERE COL1 = 'A' AND COL2 = '112' COL1 COL2 ROWID INDEX A 115 23 A 116 29 A 117 25 A 118 26 A 119 30 A 120 19 A 121 32 B 110 41 B 111 45 A 110 10 A 111 11 A 112 5 A 113 18 A 114 54 COL2 COL1 ROWID INDEX 110 A 10 110 B 41 110 C 57 110 D 81 111 A 11 111 B 39 111 C 76 111 D 98 112 A 5 112 B 73 112 C 89 112 D 77 113 A 18 113 B 65

EQUAL이 결합인덱스에 미치는 영향 INDEX INDEX SELECT * FROM TAB1 WHERE COL1 = ‘A’ AND COL2 between ‘111’ and ‘113’ COL1 COL2 ROWID INDEX A 110 10 A 111 11 A 112 5 A 115 23 A 113 18 A 114 22 A 116 29 A 117 25 A 118 26 A 119 30 A 120 19 A 121 32 B 110 41 B 111 45 COL2 COL1 ROWID INDEX 110 A 10 110 B 41 111 A 11 111 B 65 111 C 96 111 D 78 112 A 5 112 B 73 112 C 79 112 D 97 113 A 18 113 B 45 113 C 67 114 A 22

IN을 이용한 ACCESS 효율향상 INDEX1 INDEX1 SELECT * FROM TAB1 WHERE COL1 = ‘A’ AND COL2 between ‘111’ and ‘112’ TABLE ACCESS BY ROWID TAB1 INDEX RANGE SCAN INDEX1 INDEX1 111 A 11 111 B 65 111 C 96 111 D 5 112 A 73 112 B 18 112 C 45 114 A 22 110 A 10 110 B 41 COL2 COL1 ROWID CONCATENATION TABLE ACCESS BY ROWID TAB1 INDEX RANGE SCAN INDEX1 INDEX RANGE SCAN INDEX1 COL2 COL1 ROWID INDEX1 110 A 10 110 B 41 111 A 11 111 B 65 111 C 96 111 D 5 112 A 73 112 B 79 112 C 18 114 A 22 SELECT * FROM TAB1 WHERE COL1 = ‘A’ AND COL2 in ( ‘111’, ‘112’)

추가된 인덱스컬럼의 역할 Select * from tab1 where A = '2' and C = 51 1 20 10 A B Rowid INDEX (A+B) 1 20 10 2 30 11 2 40 47 2 50 54 2 60 58 2 70 61 3 40 21 TAB1 A B C Rowid 1 20 30 10 2 30 40 11 2 40 45 47 2 50 46 54 2 60 49 58 2 70 51 61 INDEX (A+B+C) TAB1 3 40 41 21

INDEX SKIP SCAN Cardinality가 낮은 선행 컬럼 + 높은 후행칼럼으로 구성된 인덱스일수록 큰 효과 두 번째 컬럼의 값만 쿼리의 조건으로 공급한다. 내부적으로 선행 컬럼 값의 Distinct Value 만큼의 Logical Sub-Index로 나눠서 SCAN한다. Analyze 되어있는 오브젝트에 적용된다. Logical Sub-Index L 0010100001 L 0010100002 L 0100100001 L 0100100002 L 0110100001 L …… S 0010100001 S 0010100003 S 0010100004 S …… Index Scan 예제) TABLE: BALANCE (계좌잔고) ACCNT_TYPE VARCHAR2(2) 계좌종류 ( L, S ) ACCNT_NO VARCHAR2(10) 계좌번호 AMT NUMBER 현재잔고 …… INDEX: BL_IDX2 (ACCNT_TYPE, ACCNT_NO) L S 0010100001 0010100002 0100100001 0100100002 0110100001 0110100002 0110100003 0110100004 …… 0010200001 0010200002 0020200001 0020200002 0030200001 0030200002 0030200003 0030200004 …… sjskjskjs SQL> SELECT ACCNT_TYPE, ID FROM BALANCE WHERE ACCNT_NO = ‘0010200002’ 320,000 rows 0.01 sec SELECT STATEMENT GOAL: CHOOSE INDEX GOAL: ANALYZED (SKIP SCAN) OF ‘BL_IDX2' (NON-UNIQUE)

결합인덱스 컬럼순서결정 항상 사용하는가 ? 항상 '='로 사용되는가 ? 분포도가 좋은 컬럼 우선 SORT 순서는 ? 어떤 컬럼을 추가 ? (후보선수)

인덱스 선정 절차 해당 테이블의 액세스 유형 조사 대상 컬럼의 선정 및 분포도 분석 반복 수행되는 액세스경로(Critical Access Path)의 해결 클러스터링 검토 인덱스 컬럼의 조합 및 순서의 결정 시험생성 및 테스트 수정이 필요한 Application 조사 및 수정 일괄 적용

액세스 유형의 조사 (설계단계) 반복 수행되는 액세스 형태를 찾는다. 분포도가 아주 양호한 컬럼을 찾아 액세스 유형을 찾는다. 액세스 유형의 조사 (설계단계) 반복 수행되는 액세스 형태를 찾는다. 분포도가 아주 양호한 컬럼을 찾아 액세스 유형을 찾는다. 자주 넓은 범위의 조건이 부여되는 경우를 찾는다. 자주 조건절에 사용되는 컬럼들의 액세스 유형을 찾는다. 자주 결합되어 사용되는 경우를 찾는다. SORT 의 유형을 조사한다. 일련번호를 부여하는 경우를 찾는다. 통계자료 추출을 위한 액세스 유형을 조사한다.

튜닝시의 인덱스 선정 (예) 현 인 덱 스 주 요 컬 럼 분 포 도 ACCESS PATH 설 의 계 사 변 항 경 TABLE_NAME SALET 매출정보　관리 테이블 TOTAL ROWS 101,500 PCTFREE/PCTUSED 10 / 60 주 요 컬 럼 분 포 도 ACCESS PATH 회수 범위 인덱스 (1) (2) (3) (4) (5) (6) 의 사 항 설 계 변 경 (1) (2) (3) (4) (5) (6) 컬럼명 종류 평균 최대 최소 특 기 사 항 1 SALEDATE, SALEDEPT 2 SALEDATE like , SALEDEPT 3 CUSTNO, SALEDATE between 4 SALEDATE like, STATUS(60), CUSTNO like 5 STATUS in, AGENTNO 6 SALENO, ITEM 7 ITEM, SALEDATE like, SALEDEPT like 8 SALEDATE like,STATUS,group by CUSTNO 9 SALEDEPT, SALEDATE like, SALETYPE, order by SALEDATE, SALETYPE 10 SALEDATE like, (CUSTNO,STATUS or CUSTNO,SALETYPE) 11 SALETYPE 9000 4 650 5 35000 3 56000 3 1 1 2500 2 9000 4 FULL SALENO SALEDATE STATUS SALEDEPT CUSTNO ITEM SALEDEPT SALEDATE SALETYPE saleno 9000 11 100 1 saledate 1000 100 150 20 월평균(2500건) saledept 20 5000 9000 1200 status 25 4000 56000 80 60,90:30000건 이상,나머지:평균300 custno 3200 32 12500 10 DN01:12500, 나머지:500건 이하 item 1200 85 800 5 . STATUS가 60, 90인 경우는 인덱스를 사용하지 않게 할것 . 2,9번 액세스는 2개월이내로 처리범위를 제한할 것 . 3,10번 액세스는 CUSTNO=DN01 인 경우 SALEDATE 범위를 1개월로 제한할 것 2 3,2 4 1 2,3 ITEM SALEDATE CUSTNO STATUS SALENO (CLUSTER) SALEDATE 현 인 덱 스

INDEX의 활용 (선정기준) 분포도가 좋은 컬럼은 단독적으로 생성하여 활용도 향상 자주 조합되어 사용되는 경우는 결합인덱스 생성 각종 액세스 경우의 수를 만족할 수 있도록 인덱스간의 역할 분담 가능한 수정이 빈번하지 않는 컬럼 기본키 및 외부키 (조인의 연결고리가 되는 컬럼) 결합 인덱스의 컬럼 순서 선정에 주의 반복수행(loop 내) 되는 조건은 가장 빠른 수행속도를 내게 할 것 실제 조사된 액세스 종류를 토대로 선정 및 검증

INDEX의 활용 (고려사항) 새로 추가된 인덱스는 기존 액세스 경로에 영향을 미칠 수 있음 지나치게 많은 인덱스는 오버헤드를 발생 넓은 범위를 인덱스로 처리시 많은 오버헤드 발생 옵티마이져를 위한 통계데이타를 주기적으로 갱신 인덱스의 개수는 테이블의 사용형태에 따라 적절히 생성 분포도가 양호한 컬럼도 처리범위에 따라 분포도가 나빠질 수 있음 인덱스 사용원칙을 준수해야 인덱스가 사용되어짐 조인(join)시에 인덱스가 사용여부에 주의

추가된 INDEX 가 미치는 영향 ABD C AB C SELECT * FROM TAB1 WHERE A = '10' AND B = '941005' AND C = '123' ABD C INDEX1 INDEX2 D column 추가 ABD LIKE '10'||'941005'||'%' C = '123' INDEX2 만 사용 AB C INDEX1 INDEX2 INDEX1 만 사용 AB = '10'||'941005' C = '123'

추가된 INDEX가 미치는 영향 (예제) 1 rows, 0.01sec 1 rows, 37.7sec 1 rows, CHULITEM table Primary Key : CHULNO + ORDNO + ITEM SQL> SELECT CHULNO, ORDNO, ITEM, CHULQTY FROM CHULITEM WHERE CHULNO = '2565' AND ORDNO = '8584' AND LOT = 'P0009' 1 rows, 0.01sec TABLE ACCESS BY ROWID CHULITEM INDEX RANGE SCAN PK_CHULITEM SQL> SELECT CHULNO, ORDNO, ITEM, CHULQTY FROM CHULITEM WHERE CHULNO = '2565' AND ORDNO = '8584' AND LOT = 'P0009' 1 rows, 37.7sec SQL> CREATE INDEX CI_LOT ON CHULITEM (LOT) TABLE ACCESS BY ROWID CHULITEM INDEX RANGE SCAN CI_LOT sjskjskjs SQL> SELECT CHULNO, ORDNO, ITEM, CHULQTY FROM CHULITEM WHERE CHULNO = '2565' AND ORDNO = '8584' AND LOT = 'P0009' 1 rows, 0.01 sec SQL> CREATE INDEX CI_LOT_ITEM ON CHUITEM (LOT,ITEM) TABLE ACCESS BY ROWID CHULITEM INDEX RANGE SCAN PK_CHULITEM

분포도 차이가 심한 경우의 INDEX COL1 분포도 TABLE1 6% 7% 4% 5% C D B E A (78%) CREATE INDEX index_name ON table_name (COL1); TABLE1 (10000 row) KEY COL1 . . . . COL1 = 'A' 를 그대로 COL1 KEY . . . . A B ROWID TABLE1 (10000 row) INDEX1 COL1 = 'A' 를 COL1 NULL로 COL1 KEY . . . . Null B ROWID C TABLE1 (10000 row) INDEX1 (2200 row) COL1 분포도 6% 7% 4% 5% C D B E A (78%)

INDEX 재생성 현존하는 INDEX를 읽어 재생성 DDL Lock을 발생시키지 않음 병렬처리 가능 ALTER INDEX index_name REBUILD UNRECOVERABLE PARALLEL 10 ; Re-balanced Index Unbalanced Index 현존하는 INDEX를 읽어 재생성 DDL Lock을 발생시키지 않음 병렬처리 가능 Direct database reads and writes 멀티 블럭을 비동기적으로 I/O REDO LOG 에 쓰지 않도록 할 수 있음

비트맵 인덱스(Bitmap Index) CREATE BITMAP INDEX prod_color ON prod(color); <Green, 0100000000100100000> <Red, 0001010011000001001> <Yellow, 0010001000001000010> < Blue, 1000100100010010100> 새로운 형태의 인덱스 분포도가 낮은 컬럼에 유효 전통적인 b*_Tree 인덱스의 단점 해소(or, not, null,..) I/O의 획기적인 감소 블럭레벨 Locking 데이터웨어하우스에 적당 Bitmap Indexes An attractive alternative to normal b-tree indexes in situations where Tables are very large (millions of rows) Tables have low-cardinality index key values (ex. sex, age, marital status, zipcode) Queries have bitmap indexes on all columns that commonly appear in WHERE clauses Queries have individual WHERE clause conditions that are satisfied by 1000's of rows Implementation For each distinct column (key) value, create a bitmap representing that key's presence/absence in each row Results in a set of <key, bitmap> pairs Oracle7 provides a fast method to convert 1's into ROWIDs for high-performance row retrieval Store the pairs in a b-tree ordered by key to quickly locate bitmaps for equivalence and range comparisons Accelerated Operations Queries: row identification, aggregates, nested loop joins UPDATEs/DELETEs: row identification Summarization (CREATE TABLE AS SELECT)

비트맵 인덱스(Bitmap Index) SELECT count(*) FROM parts WHERE table 001 GREEN MED 98.1 002 RED MED 1241 003 RED SMALL 100.1 004 BLUE LARGE 54.9 005 RED MED 124.1 006 GREEN SMALL 60.1 ... .... ..... ... partno color size weight SELECT count(*) FROM parts WHERE size = 'MED' AND color = 'RED' 1 1 Index on COLOR color = 'BLUE' 0 0 0 1 0 0 1 0 1 0 1 0 0 0 1 0 color = 'RED' 0 1 1 0 1 0 0 1 0 0 0 0 1 0 0 1 color = 'GREEN' 1 0 0 0 0 1 0 0 0 1 0 1 0 1 0 0 size = 'SMALL' 0 0 1 0 0 1 0 1 0 1 0 0 0 1 0 1 size = 'MED' 1 1 0 0 1 0 1 0 0 0 0 1 0 1 0 0 size = 'LARGE' 0 0 0 1 0 0 0 0 1 0 1 0 1 0 1 0 Index on SIZE

비트맵 조인인덱스(Bitmap Join Index) SELECT SUM(F.amount) FROM Fact F, Dem D WHERE F.fk_id = D.pk_id AND D.gubun = ‘COLD' ; CREATE BITMAP INDEX bji_Fact_color ON Fact (D.gubun) FROM Fact F, Dem D WHERE F.fk_id = D.pk_id ; Dem 테이블의 gubun 컬럼의 값 Fact Table Dem Table Fact Index 새로운 형태의 인덱스 다중 테이블의 조인 결과를 기준 테이블의 로우를 기준으로 연결 테이블의 컬럼을 비트맵으로 구성. 비트맵 인덱스와 동일한 구조 인덱스 만으로 엑세스 가능. 데이터웨어하우스에 적당

비트맵 조인인덱스(BitmapJoinIndex) SELECT STATEMENT SORT (AGGREGATE) TABLE ACCESS (BY INDEX ROWID) OF FACT BITMAP CONVERSION (TO ROWIDS) BITMAP INDEX (SINGLE VALUE) OF BJI_FACT_COLOR 1 FACT Join table 001 GREEN 98.1 COLD 002 RED 1241 HOT 003 RED 100.1 HOT 004 BLUE 54.9 COLD 005 RED 124.1 HOT 006 GREEN 60.1 COLD ... .... ..... ... partno color weight gubun SELECT SUM(F.amount) FROM Fact F, Dem D WHERE F.fk_id = D.pk_id AND D.gubun = ‘COLD' ; Index on BJI_FACT_COLOR gubun = ‘COLD' 0 0 0 1 0 0 1 0 1 0 1 0 0 0 1 0 gubun = 'HOT' 0 1 1 0 1 0 0 1 0 0 0 0 1 0 0 1 gubun = ‘ETC' 1 0 0 0 0 1 0 0 0 1 0 1 0 1 0 0

Function Based Index INDEX (Birth_mm ) TABLE (EMP) CREATE INDEX Birth_mm ON Emp(substr(birth,1,2)); SELECT empno, ename, job FROM emp WHERE substr (birth,1,2) = ’07’ INDEX-KEY ROWID EMPNO ENAME Birth 01 0000A95B.0002.0001 7654 강감찬 0730 01 0000A95B.0005.0001 7900 류관순 0621 02 0000E62E.0009.0001 7689 황진이 0524 03 0000E9BE.0002.0001 7934 변강쇠 0711 07 000062BE.0001.0001 7499 이순신 0813 07 000062BE.0003.0001 7369 안중근 0723 07 000093A6.0005.0001 7844 조자룡 0911 12 000093B2.000B.0001 7839 장보고 0201 12 000069C5.0001.0001 7531 신윤복 0420 SORT된 결과 INDEX (Birth_mm ) TABLE (EMP)

Function Based Index Index Column의 변형에 유연하게 사용할 수 있는 인덱스 - Cost-Based optimization 에서만 사용 가능 - Index 생성 후 통계정보 생성 필수 Parameter Requirement - QUERY_REWRITE_INTEGRITY = TRUSTED - QUERY_REWRITE_ENABLED = TRUE - COMPATIBLE = 8.1.0.0.0 이상 User Privilege - QUERY REWRITE - GLOBAL QUERY REWRITE MS SQL Server는 계산된 컬럼을 테이블에 생성 가능하며 이 컬럼에 인덱스 생성을 지원한다. Bitmap Indexes An attractive alternative to normal b-tree indexes in situations where Tables are very large (millions of rows) Tables have low-cardinality index key values (ex. sex, age, marital status, zipcode) Queries have bitmap indexes on all columns that commonly appear in WHERE clauses Queries have individual WHERE clause conditions that are satisfied by 1000's of rows Implementation For each distinct column (key) value, create a bitmap representing that key's presence/absence in each row Results in a set of <key, bitmap> pairs Oracle7 provides a fast method to convert 1's into ROWIDs for high-performance row retrieval Store the pairs in a b-tree ordered by key to quickly locate bitmaps for equivalence and range comparisons Accelerated Operations Queries: row identification, aggregates, nested loop joins UPDATEs/DELETEs: row identification Summarization (CREATE TABLE AS SELECT)

조인(Join) 수행속도 결정 요소 (Driving) TABLE1 TABLE2 TABLE3 (10000 row) (1000 row) (2 row) . . . 1 A 2 C 3 D 4 K 5 M 6 F 7 E 8 M . . . . A 가 P 나 C 라 H 사 E 마 라 10 마 20 최소 10,000회 이상 ACCESS TABLE3 TABLE2 TABLE1 (10000 row) (2 row) 라 10 마 20 (1000 row) A 가 P 나 C 라 S 마 . . . E 마 1 A 2 C 3 D 4 K 5 M 6 F 7 E 8 M . . . . 최대 6회 이하 ACCESS

조인(Join) 수행속도 결정 요소 (Join 순서) TABLE1 TABLE2 TABLE3 (10000 row) . . . 1 A 2 C 3 D 4 K 5 M 6 F 7 E 8 M . . . . C 10 E 20 (2 row) A 가 P 나 C 라 H 사 E 마 (1000 row) TABLE1 TABLE3 TABLE2 (1000 row) (10000 row) (2 row) . . . 1 A 2 C 3 D 4 K 5 M 6 F 7 E 8 M . . . . C 10 E 20 A 가 P 나 C 라 S 마 E 마 TABLE1과 두번째 TABLE2(TABLE3)를 연결하는 일량은 성공한 결과에 관계없이 동일 그러나 연결에 성공한 양은 다음 연결할 일량에 영향을 미침

조인(Join) 수행속도 결정 요소 (Index 영향) TAB1 TAB2 RANGE SCAN INDEX SCAN INDEX 있음 TAB1 . . . . . . . . . A . . . . . . . . . D . . . . . . . . . B . . . . . . . . . . . . . . . . . . . K FLD . . . KEY1 TAB2 KEY2 FLD . . . K . . . . . . . A . . . . . . . . . . . . . . . D . . . . . . . Z . . . . . . . INDEX 있음 INDEX 있음 TAB1 FLD . . . KEY1 . . . . . . . . . A . . . . . . . . . D . . . . . . . . . B . . . . . . . . . . . . . . . . . . . K INDEX 없음 TAB2 KEY2 FLD . . . K . . . . . . . A . . . . . . . . . . . . . . . D . . . . . . . Z . . . . . . . 한쪽에 INDEX TAB1 TAB2 RANGE SCAN FULL SCAN OPTIMIZER는 무조건 의 방법으로 처리 양쪽 모두 INDEX가 없으면 SORT MERGE 방법으로 처리 의 경우는 [ FULL SCAN]이 TAB1의 대상 ROW 마다 한번씩 실행하며

실행계획에 따른 ACCESS양 . . ACCESS양이 많음 ACCESS량이 적음 o o x x TAB1 TAB2 SELECT a.FLD1, ..., b.FLD1,... FROM TAB2 b, TAB1 a WHERE a.KEY1 = b.KEY2 AND b.FLD2 like 'A%' AND a.FLD1 = '10' ACCESS양이 많음 운반 단위 INDEX (FLD1) TAB1 TAB2 (KEY2) FLD1='10' KEY2 = KEY1 o x 5000 row . 100 row 50 row FLD2 like ‘A%’ ACCESS량이 적음 운반 단위 INDEX (FLD2) (KEY1) KEY1 = KEY2 FLD2 like 'A%' 100 row . 70 row 50 row x TAB1 TAB2 o FLD1 =‘10’ 14

처리범위에 따른 ACCESS량 500 row 10 row . . o x x TAB1 TAB2 TAB1 TAB2 SELECT a.FLD1, ..., b.FLD1,... FROM TAB2 b TAB1 a WHERE a.KEY1 = b.KEY2 AND b.FLD2 = 'ABC' AND a.FLD1 = '10' TUNING 방법 ANALYZE table HINT (/*+ ORDERED */) SUPRESSING ( a.FLD1||'' = '10') 연결고리 이상 없음 RULE-BASE OPTIMIZER 운반 단위 FLD1='10' KEY2 = KEY1 FLD2 = ABC' 를Check o x 500 row . 50 row 3 row INDEX (FLD1) TAB1 TAB2 (10,000 row) (1,000 row) (KEY2) COST-BASE OPTIMIZER, TUNING 운반 단위 FLD1='10' 을 Check KEY1 = KEY2 FLD2 = 'ABC' 10 row . 5 row 3 row x TAB1 TAB2 (10,000 row) (1,000 row) INDEX (FLD2) (KEY1)

JOIN의 순서에 따른 속도차이 (예제) 1 rows, 0.15 sec 1 rows, 0.04Sec SQL> SELECT CHULNO, CHULDATE, CUSTNAME FROM CUSTOMER Y, CHULGOT X WHERE X.CUSTNO = Y.CUSTNO AND X.CHULDATE = '941003' AND Y.NATION = 'KOR' NESTED LOOPS TABLE ACCESS BY ROWID CUSTOMER INDEX RANGE SCAN CU_NATION TABLE ACCESS BY ROWID CHULGOT AND-EQUAL INDEX RANGE SCAN CH_CUSTNO INDEX RANGE SCAN CH_CHULDATE 1 rows, 0.15 sec SQL> SELECT CHULNO, CHULDATE, CUSTNAME FROM CHULGOT X, CUSTOMER Y WHERE X.CUSTNO = Y.CUSTNO AND X.CHULDATE = '941003' AND Y.NATION = 'KOR' NESTED LOOPS TABLE ACCESS BY ROWID CHULGOT INDEX RANGE SCAN CH_CHULDATE TABLE ACCESS BY ROWID CUSTOMER INDEX UNIQUE SCAN PK_CUSTNO 1 rows, 0.04Sec sjskjskjs 실행계획을 위한 Optimizer Hint를 ORDERED로 지정하였다고 가정한 경우

JOIN시 INDEX의 영향 (예제) 1 rows, 0.15 sec 1 rows, 0.04 sec rows, sec SQL> SELECT CUSTNO, CHULDATE, CUSTNAME FROM CUSTOMER Y, CHULGOT X WHERE X.CUSTNO = Y.CUSTNO AND X.CHULDATE = '941130' AND Y.NATION = 'KOR' NESTED LOOPS TABLE ACCESS BY ROWID CUSTOMER INDEX RANGE SCAN CU_NATION TABLE ACCESS BY ROWID CHULGOT AND-EQUAL INDEX RANGE SCAN CH_CUSTNO INDEX RANGE SCAN CH_CHULDATE 1 rows, 0.15 sec SQL> SELECT CUSTNO, CHULDATE, CUSTNAME FROM CUSTOMER Y, CHULGOT X WHERE RTRIM(X.CUSTNO) = Y.CUSTNO AND X.CHULDATE = '941130' AND Y.NATION = 'KOR' NESTED LOOPS TABLE ACCESS BY ROWID CHULGOT INDEX RANGE SCAN CH_CHULDATE TABLE ACCESS BY ROWID CUSTOMER INDEX UNIQUE SCAN PK_CUSTNO 1 rows, 0.04 sec SQL> SELECT CUSTNO, CHULDATE, CUSTNAME FROM CUSTOMER Y, CHULGOT X WHERE RTRIM(X.CUSTNO) = RTRIM(Y.CUSTNO) AND X.CHULDATE = '941130' AND Y.NATION = 'KOR' rows, sec sjskjskjs

액세스경로의 결정 CASE ACCESS PATH A2 B2 TAB1 A2 = '10' B1 B2 TAB1 A1 A2 A2 A2 B2 B2 #* #* TAB1 A2 = '10' TAB2 B1 = A1 and B2 like 'B%' TAB3 C1 = B2 TAB2 B2 like 'B%' TAB1 A1 = B1 and A2 = '10' TAB3 Full table scan TAB2 B2 = C1 and B2 like 'B%' . . . . . . . . . . . . . . . . . . . . . . . . . #* C1 A1 B1 & B2 TAB3 C1 C2 #* SELECT A1, A2, . . , B1, B2, . . , C1, C2, . . . FROM TAB1 x, TAB2 y, TAB3 z WHERE x.A1 = y.B1 and z.C1 = y.B2 and x.A2 = '10' and y.B2 like 'B%' 상수값을 받을 수 있어야 액세스 자격이 획득됨 어떤 액세스 경로가 가장 유리한가? 인덱스 구조가 어떻게 되어 있는가? 15

JOIN 과 LOOP-QUERY (전체범위처리) . . 2 차 가 공 2 차 가 공 SQL 운반 단위 운반 단위 SQL . . . SQL . SQL SQL TAB1 TAB2 TAB1 TAB2

JOIN 과 LOOP-QUERY(TAB1 에 의한 전체범위) 2 차 가 공 2 차 가 공 SQL 운반 단위 SQL 운반 단위 . . SQL . TAB1 TAB2 TAB1 TAB2 ALL_ROW 처리 시 유리 FIRST_ROW 처리 시 유리

JOIN 과 LOOP-QUERY (TAB1 에 의한 Group by) SQL 운반 단위 운반 단위 SQL . . . . . . SQL Sort Group by Sort Group by TAB1 TAB2 TAB1 TAB2 항상 불리 항상 유리

Nested Loop JOIN SELECT a.FLD1, ..., b.FLD1,... FROM TAB1 a, TAB2 b WHERE a.KEY1 = b.KEY2 AND a.FLD1 = 'AB' AND b.FLD2 = '10' TABLE ACCESS BY ROWID TABLE ACCESS BY ROWID KEY2= KEY1 FLD2 ='10' check FLD1='AB' o o o 순차적 (부분범위처리 가능) 종속적 (먼저 처리되는 테이블의 처리범위에 따라 처리량 결정) 랜덤(Random) 액세스 위주 연결고리 상태에 따라 영향이 큼 주로 좁은 범위 처리에 유리 운반 단위 x INDEX (FLD1) TAB1 TAB2 INDEX (KEY2)

Sort Merge JOIN 동시적 (무조건 전체범위처리) 독립적 (자기의 처리범위만 으로 처리량 결정) SELECT /*+ use_merge(a b) */ a.FLD1, ..., b.FLD2,... FROM TAB1 a, TAB2 b WHERE a.KEY1 = b.KEY2 AND a.FLD1 = 'AB' AND b.FLD2 = '10' TABLE ACCESS BY ROWID TABLE ACCESS BY ROWID FLD1='AB' FLD2='10' a.KEY1= b.KEY2 를 조건으로 Merge S O R T S O R T 동시적 (무조건 전체범위처리) 독립적 (자기의 처리범위만 으로 처리량 결정) 스캔(Scan) 액세스 위주 연결고리 상태에 영향이 없음 주로 넓은 범위 처리에 유리 . . . . . . 운반단위 INDEX (FLD1) TAB1 TAB2 INDEX (FLD2)

Nested Loop JOIN이 유리한 경우 TABLE ACCESS BY ROWID TABLE ACCESS BY ROWID SELECT a.FLD1, ..., b.FLD1,... FROM TAB1 a, TAB2 b WHERE a.KEY1 = b.KEY2 AND a.FLD1 = 'AB' KEY2= KEY1 FLD1='AB' x b.FLD2 = '10' 이 없어도 일의 량에 별로 영향을 주지 않음 운반 단위 b.FLD2 = '10' 인 Check 기능만 없어짐 INDEX (FLD1) TAB1 TAB2 INDEX (KEY2) Nested Loop JOIN

Sort Merge JOIN SELECT /*+ USE_MERGE(a b) */ a.FLD1, ..., b.FLD2,... FROM TAB1 a, TAB2 b WHERE a.KEY1 = b.KEY2 AND a.FLD1 = 'AB' INDEX (FLD1) TAB1 TAB2 FLD1='AB' TABLE ACCESS BY ROWID 운반단위 . S O R T FULL TABLE SCAN a.KEY1= b.KEY2 를 조건으로 Merge b.FLD2 = '10' 이 없으면 일의 량이 크게 증가

Nested_loop, Sort_merge 예제 SQL> SELECT X.CUSTNO,CHULDATE, CUSTNAME FROM CHULGOT X, CUSTOMER Y WHERE X.CUSTNO = Y.CUSTNO AND X.CHULDATE = '941003' NESTED LOOPS TABLE ACCESS BY ROWID CHULGOT INDEX RANGE SCAN CH_CHULDATE TABLE ACCESS BY ROWID CUSTOMER INDEX UNIQUE SCAN PK_CUSTNO 55 rows, 0.02 sec ALL_ROWS FIRST_ROWS SORT GROUP BY NESTED LOOPS TABLE ACCESS BY ROWID CHULGOT INDEX RANGE SCAN CH_CHULDATE TABLE ACCESS BY ROWID CUSTOMER INDEX UNIQUE SCAN PK_CUSTNO SQL> SELECT NATION, SUM(CHULTIME) FROM CHULGOT X, CUSTOMER Y WHERE X.CUSTNO = Y.CUSTNO AND X.CHULDATE = '941003' GROUP BY NATION ALL_ROWS FIRST_ROWS 26 rows, 0.04 sec sjskjskjs

Nested_loop, Sort_merge 예제 MERGE JOIN SORT JOIN TABLE ACCESS FULL CHULGOT TABLE ACCESS FULL CUSTOMER SQL> SELECT X.CUSTNO,CHULDATE, CUSTNAME FROM CHULGOT X, CUSTOMER Y WHERE X.CUSTNO = Y.CUSTNO 5.44 sec NESTED LOOPS TABLE ACCESS BY ROWID CUSTOMER INDEX UNIQUE SCAN PK_CUSTNO 0.02 sec ALL_ROWS FIRST_ROWS SORT GROUP BY MERGE JOIN SORT JOIN TABLE ACCESS FULL CHULGOT TABLE ACCESS FULL CUSTOMER SQL> SELECT NATION, SUM(CHULTIME) FROM CHULGOT X, CUSTOMER Y WHERE X.CUSTNO = Y.CUSTNO GROUP BY NATION SORT_MERGE 8.33 sec SQL> SELECT --+ RULE NATION, SUM(CHULTIME) NESTED LOOPS TABLE ACCESS FULL CUSTOMER TABLE ACCESS BY ROWID CHULGOT INDEX RANGE SCAN CH_CUSTNO NESTED_LOOP 17.5 sec sjskjskjs

Many to One JOIN(전체범위) Nested Loop JOIN SELECT a.FLD, SUM(b.VAL) FROM TAB1 a, TAB2 b WHERE a.KEY1 = b.KEY2 AND a.FLD1 = 'AB' GROUP BY a.FLD TABLE ACCESS BY ROWID TABLE ACCESS BY ROWID KEY2= KEY1 FLD1='AB' G R O U P B Y ..... ..... TAB1 #* DEPT * FLD1 * FLD TAB2 #* CDATE * VAL ..... 운반 단위 ..... ..... ..... INDEX (FLD1) TAB1 TAB2 INDEX (KEY2) Nested Loop JOIN

Many to One JOIN(전체범위) Sort Merge JOIN SELECT /*+ USE_MERGE(a b) */ a.FLD, SUM(b.VAL) FROM TAB1 a, TAB2 b WHERE a.KEY1 = b.KEY2 AND a.FLD1 = 'AB' GROUP BY a.FLD TABLE ACCESS BY ROWID a.KEY1= b.KEY2 를 조건으로 Merge FULL TABLE SCAN FLD1='AB' S O R T S O R T TAB2 #* DEPT #* CDATE * VAL . GROUP BY TAB1 #* DEPT * FLD1 * FLD 운반단위 INDEX (FLD1) TAB1 TAB2 Sort Merge JOIN

Many to One JOIN(부분범위) Nested Loop JOIN SELECT a.FLD, b.CDATE FROM TAB1 a, TAB2 b WHERE a.KEY1 = b.KEY2 AND a.FLD1 = 'AB' TABLE ACCESS BY ROWID TABLE ACCESS BY ROWID KEY2= KEY1 FLD1='AB' ..... ..... TAB2 #* DEPT #* CDATE * VAL 운반 단위 TAB1 #* DEPT * FLD1 * FLD INDEX (FLD1) TAB1 TAB2 INDEX (KEY2) Nested Loop JOIN

JOIN 방법의 결정 Nested Loop JOIN Sort Merge JOIN 부 Driving 분 table 범 위 결정 가 능 Driving 조건 First_rows 부 분 범 위 처 리 좁 다 넓 다 Nested Loop JOIN Driving table 결정 Check 조건 넓 다 좁 다 Driving 과 Check 조건 교환 불가능 받은 경우와 받지 않은 경우를 비교 All_rows 가 능 불가능 유 리 불 리 Sort Merge JOIN

X X STAR JOIN . 참조 Tables JOIN 실적 Table Part# Desc Wt LJ3 Printer 47 D1200 Modem 5 M17 Monitor 55 DID District Manager Region 1 Atlanta J Smith SouthEast 2 Dallas R Lablanc South 3 Chicago L Monroe Central Color 103 Red 401 White 911 Blue 참조 Tables JOIN 실적 Table Order# Part# Color Cust# DID ... 1243 LJ3 401 1001 47 1245 M17 911 1006 5 900 D1200 103 876 55 6451 M14 441 14 . X . X 여러 개의 작은 테이블을 Cartesian product로 조인 그 결과와 대용량의 테이블 조인 랜덤 액세스를 현저하게 감소 /*+ star */ 힌트 사용 인덱스 구조에 따라 주의 가능한 인라인 뷰 및 ordered, use_nl 힌트로 제어하는 방법을 사용할 것

Hash Join ③ ④ ⑥ ⑨ ② ⑦ ⑤ ① ⑧ ⑩ ⑪ UGA SELECT /*+ use_hash(a b) full(a) full(b) */ A.FLD1, …, B.COL1…. FROM TAB_S A, TAB_B B WHERE A.KEY1 = B.KEY2 AND A.FLD1 = ‘AB’ AND B.FLD2 = ’10’ Hash Function 1 저장할Partition 결정 Function 2 Hash value 생성 ④ ③ ⑩ ⑥ ⑨ ⑤ C21 C31 C41 C12 C22 C32 C33 ⑦ P3 C31 P2 C21 P1 C11 C41 P4 파티션 수 결정 ② UGA Build Input 결정 ① Hash area TAB_S Bitmap vector C11 Hash Table ⑧ 조건을 만족 하지 않는 경우 ⑪ 운반단위 TAB_B

Semi Join Semi Join 제한사항 Subquery에는 하나의 Table이 존재 Filter를 Join으로 SQL> SELECT * FROM dept a WHERE exists (SELECT deptno FROM emp b WHERE a.deptno =b.deptno and sal >= 3000) SQL> SELECT * FROM dept a WHERE exists (SELECT /*+ hash_sj(a,b) */ deptno FROM emp b WHERE a.deptno = b.deptno and sal >= 3000 ) FILTER TABLE ACCESS (FULL) OF DEPT TABLE ACCESS (BY INDEX ROWID) OF EMP INDEX (RANGE SCAN) OF EMP_IDX01 HASH JOIN SEMI TABLE ACCESS FULL DEPT TABLE ACCESS FULL EMP Semi Join 제한사항 Subquery에는 하나의 Table이 존재 Subquery안의 Subquery에는 사용 못함 Subquery에서의 Main Table과의 연결은 Equal만 가능 Subquery안에 GROUP BY, CONNECT BY, ROWNUM을 사용하지 못함

순환관계의 처리(1:M) C O D E 대 비 법 순 환 전 개 법 A B C D E F H J G TAB1 TAB2 TAB3 1000 1100 1200 1110 1120 1210 1211 1212 1111 ID . . . . 1000 . . . 1100 . . . 1110 . . . 1111 . . . 1120 . . . 1200 . . . 1210 . . . 1211 . . . 1212 . . . 사용하기가 쉽고 간편하며 수행속도에 상대적으로 유리함 구조 변경에 매우 취약함(식별자 변경) 구조 변경시 과거 데이터의 수정이 필요함 C O D E 대 비 법 ID . . . . P_ID A . . . B . . . A C . . . A D . . . B E . . . B G . . . D F . . . C H . . . F J . . . F 순 환 전 개 법 A B C D E F H J G 구조(순환관계)가 변경 되도 식별자는 변하지 않아 과거 데이터 변경 불필요 사용이 약간 불편하며 숙달된 기술력이 필요 약간의 기술력 필요(잘못 사용시 수행속도 저하)

User Stored FUNCTION으로 일부 해결 가능 순환관계의 처리(1:M) [ 순환관계로의 통합이 필요한 이유 ] TAB4 TAB1 TAB2 TAB3 TAB5 SELECT x.COL, . . ., y.COL, . . ., z.COL. . . FROM TAB1 x, TAB2 y, TAB3 z, TAB4 w WHERE (w.TYPE = ‘1’ AND w.KEY = x.KEY or w.TYPE = ‘2’ AND w.KEY = y.KEY or w.TYPE = ‘3’ AND w.KEY = z.KEY ) AND w.conditions . . . . . . . . INDEX 형태에 따라 3배의 일량이 증가됨 User Stored FUNCTION으로 일부 해결 가능 SELECT x.COL, . . ., y.COL, . . ., FROM TAB5 x, TAB4 w WHERE w.KEY = x.KEY AND w.conditions . . . . . . . . 일량이 증가하지 않음

순환관계의 처리(M:M) A B C D E F H J G P K TAB1 BOM BOM TAB1 ID . . . . . . . . . . TAB1 ID P_ID 소요량 . . . . . A 1 . . . . B A 3 . . . . P 1 . . . . B P 2 . . . . K P 5 . . . . D B 3 . . . . E B 2 . . . . G D 3 . . . . H D 2 . . . . C A 2 . . . . F C 5 . . . . H F 2 . . . . . . . . . . . . . . . . . BOM

순환관계 처리 SQL (JOIN과 비교) 순전개시는 P_ID 인덱스 필요 역전개시는 ID 인덱스 필요 실 행 SELECT LEVEL , COL1, COL2,...... PSEUDO 컬럼 SELECT COL1, COL2,...... JOIN 테이블 FROM TAB1 ( 동일테이블 조인 간주 ) FROM TAB1 x, TAB2 y WHERE conditions . . . . . . AND check_conditions . . . . . . CHECK 조건 조건 JOIN CONNECT BY PRIOR ID = P_ID and conditions . . . WHERE x. KEY = y.KEY 선처리테이블 ALIAS 선처리 테이블 조건 START WITH conditions AND driving_table_conditions SELECT LPAD(‘ ‘, 2*LEVEL)||COL1. . . . FROM BOM WHERE 소요량 > 2 CONNECT BY PRIOR ID = START WITH = ‘A’ P_ID 순전개시는 P_ID 인덱스 필요 역전개시는 ID 인덱스 필요 ID P_ID 소요량 . . . . . A 1 . . . . B A 3 . . . . P 1 . . . . B P 2 . . . . D B 3 . . . . C A 2 . . . . E B 2 . . . . G D 3 . . . . H D 2 . . . . . . . . . . . . . . . . . 실 행 효율적인 인덱스 사용을 위해 NULL 보다 SPACE 지정 이 유리

순환관계 처리 SQL (형태별) SELECT LPAD(‘ ‘, 2*LEVEL)||COL1. . . . FROM BOM 소 그 룹 별 순 환 관 계 전 체 집 합 순 환 관 계 1000 1100 1200 1110 1120 1210 1211 1212 1111 T_ID = 1 T_ID = 2 T_ID = 3 A E F H J G 1 B 2 D K C P O SELECT LPAD(‘ ‘, 2*LEVEL)||COL1. . . . FROM BOM CONNECT BY PRIOR ID = P_ID AND T_ID = 1 START WITH P_ID = ‘A’ SELECT LPAD(‘ ‘, 2*LEVEL)||COL1. . . . FROM BOM CONNECT BY PRIOR ID = P_ID START WITH P_ID = ‘A’ CONNECT BY PRIOR ID1 = P_ID1 AND PRIOR ID2 = P_ID2 START WITH P_ID1 = ‘A’ AND P_ID2 = ‘10’ PK가 한컬럼 두컬럼

순환관계 처리 SQL (조건별) SELECT LPAD(‘ ‘, 2*LEVEL)||COL1. . . . 예 하 그 룹 미 전 개 특 정 경 우 만 미 추 출 A C F H J B D E G A C F H J B D E G SELECT LPAD(‘ ‘, 2*LEVEL)||COL1. . . . FROM BOM CONNECT BY PRIOR ID = P_ID AND ID <> ‘F’ START WITH P_ID = ‘A’ SELECT LPAD(‘ ‘, 2*LEVEL)||COL1. . . . FROM BOM WHERE ID <> ‘F’ CONNECT BY PRIOR ID = P_ID START WITH P_ID = ‘A’

X X O O 순환관계 처리 SQL (제한사항) JOIN 사용 제한 (버전에 따라) SUBQUERY 사용 제한(버전에 따라) SELECT LPAD(‘ ‘, 2*LEVEL)||COL1. . . . FROM BOM CONNECT BY PRIOR ID = P_ID AND ID <> ‘F’ START WITH P_ID IN ( SELECT PK FROM ITEM WHERE TYPE = ‘1’ ) ; X SELECT LPAD(‘ ‘, 2*LEVEL)||x.COL1, y.COL2 . . FROM BOM x, ITEM y WHERE x.FK = y.PK CONNECT BY PRIOR ID = P_ID AND ID <> ‘F’ START WITH P_ID = ‘A’ ; X SELECT LPAD(‘ ‘, 2*LEVEL)||x.COL1, y.COL2 . . FROM ( SELECT LEVEL lev, FK, COL1. . . . FROM BOM CONNECT BY PRIOR ID = P_ID AND ID <> ‘F’ START WITH P_ID = ‘A’ ) x, ITEM y WHERE x.FK = y.PK ; O DECLARE CURSOR C1 IS SELECT PK FROM ITEM WHERE TYPE = ‘1’ ; BEGIN FOR C1_REC IN C1 LOOP SELECT LPAD(‘ ‘, 2*LEVEL)||COL1. . . . FROM BOM CONNECT BY PRIOR ID = P_ID AND ID <> ‘F’ START WITH P_ID = :PK ; . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . O

X O 순환관계 처리 SQL (제한사항) A B C D E F H J G ID 소요량 총소요량 A 1 1 B 3 3 D 2 6 SELECT LPAD(‘ ‘, 2*(LEVEL-1)||ID .. . FROM BOM CONNECT BY PRIOR ID = P_ID START WITH ID = ‘A’ ; ID P_ID 소요량 . . . . . A 1 . . . . B A 3 . . . . D B 2 . . . . E B 1 . . . . G D 5 . . . . C A 2 . . . . F C 4 . . . . H F 2 . . . . J F 1 . . . . A B C D E F H J G 1 3 2 5 4 DECLARE TYPE QtyTAbTyp is TABLE of bom.qty%TYPE index by binary_integer; soyo QtyTabTyp; i binary_integer := 0; tot number := 1; BEGIN FOR n IN (select id, level lev, qty from bom connect by prior id = pid start with pid = 0 ) LOOP soyo(n.lev) := n.qty; FOR i IN 1..n.lev LOOP tot := tot * soyo(i); END LOOP; . . . . . . . . . . tot := 1; END LOOP; END; ID 1 2 3 4 총소요량 A 1 1 B 1 3 3 D 1 3 2 6 G 1 3 2 5 30 E 1 3 1 3 C 1 2 2 F 1 2 4 8 H 1 2 4 2 16 J 1 2 4 1 8 O

CLUSTERING FACTOR TABLE 6 row 검색을 위해 2 block access 3 row 검색을 위해 101 940101 . . . 101 940305 . . . 101 941003 . . . 102 940306 . . . 101 940228 . . . 220 940301 . . . 220 940205 . . . 220 940721 . . . 220 940128 . . . 102 940914 . . . 102 940805 . . . 102 940621 . . . 101 940406 . . . 101 940115 . . . 123 940107 . . . 123 940312 . . . 123 941003 . . . 123 940228 . . . 301 941012 . . . 301 940308 . . . 301 940815 . . . 220 941003 . . . 102 940216 . . . 212 940105 . . . 212 940503 . . . 212 940821 . . . 123 940406 . . . 123 940528 . . . TABLE 6 row 검색을 위해 2 block access 101 . . 102 . . . . . . . 940101 . . 940105 . . 940107 . . . . . . . . . . 940301 . . 941003 . . 3 row 검색을 위해 3 block access

CLUSTERING FACTOR의 증가 CLUSTERING TABLE CLUSTER CLUSTER INDEX 101 940101 . . 101 940305 . . 101 941003 . . 101 940406 . . 101 940228 . . 123 940228 . . 123 940406 . . 123 940528 . . 212 940105 . . 212 940503 . . CLUSTER INDEX 101 . . 102 . . . . . . . 123 . . 212 . . 220 . . 301 . . 6 row 검색을 위해 2 block access 101 940115 . . 102 940914 . . 102 940805 . . 102 940621 . . 102 940216 . . 212 940821 . . 220 940301 . . 220 940205 . . 220 940721 . . 220 940128 . . 102 940306 . . 123 940107 . . 123 940312 . . 123 941003 . . 220 941003 . . 301 941012 . . 301 940308 . . 301 940815 . . 301 941005 . .

CLUSTERING FACTOR의 증가 SORTING TABLE TABLE INDEX 6 row 검색을 위해 101 . . 102 . . . . . . . 101 940101 . . 101 940305 . . 101 941003 . . 101 940406 . . 101 940228 . . 123 940228 . . 123 940406 . . 123 940528 . . 212 940105 . . 212 940503 . . 6 row 검색을 위해 2 block access 101 940115 . . 102 940914 . . 102 940805 . . 102 940621 . . 102 940216 . . 212 940821 . . 220 940301 . . 220 940205 . . 220 940721 . . 220 940128 . . 102 940306 . . 123 940107 . . 123 940312 . . 123 941003 . . 220 941003 . . 301 941012 . . 301 940308 . . 301 940815 . . 301 941005 . .

CLUSTER의 활용 Cluster INDEX TABLE SCAN RANDOM ACCESS를 줄이자 !!! Column Header_id 111 18 112 55 . . . . . 123 99 . . . . . SCAN RANDOM ACCESS를 줄이자 !!! BLOCK CLUSTER 18

CLUSTER의 활용 (multi-table cluster) FILE SYSTEM ORACLE DATABASE RECORD KEY COL1 COL2 C(1) C(2) . . . . C(n) 저장 TABLE1 TABLE2 KEY COL1 COL2 type C . . . . T C1 C2 Cn Clustering

CLUSTER의 활용 지정된 컬럼값의 순서대로 로우를 저장시키는 방법 하나 혹은 그 이상의 테이블을 같은 클러스터내 저장 가능 엑세스기법이 아니라 엑세스 효율향상을 위한 물리적 저장기법 검색의 효율을 높여주나 입력, 수정, 삭제시는 부하 증가 분포도가 넓을 수록 오히려 유리 (인덱스의 단점을 해결 5~7배) 분포도가 넓은 테이블의 클러스터링은 오히려 저장공간 절약

CLUSTER의 활용 (선정기준) 6 블록 이상의 테이블 다량의 범위를 자주 액세스해야 하는 경우 인덱스를 사용한 처리가 부담이 되는 넓은 분포도 여러 개의 테이블이 빈번한 조인을 일으킬 때 반복 컬럼이 정규화 작업에 의해 어쩔 수 없이 분할된 경우 UNION, DISTINCT, ORDER BY, GROUP BY 가 빈번한 컬럼이면 고려해 볼 것 수정이 자주 발생하지 않는 컬럼 처리범위가 넓어 문제가 발생되는 경우는 단일 테이블 클러스터링 조인이 많아 문제가 발생되는 경우는 다중 테이블 클러스터링

CLUSTER의 활용 (고려사항) 데이터 처리(입력,수정,삭제)에 오버헤드 발생 주의 인덱스로도 충분한 범위는 클러스터링 효과가 없음 클러스터 키는 수정이 빈번하지 않을 것 각종 액세스형태에 대해 인덱스와 적절한 역할 분담 클러스터링은 기존의 인덱스의 수를 감소시킴(인덱스 재구성) 클러스터 SIZE parameter 가 중요 클러스터 키별 로우 수의 편차가 심하지 않을 것 클러스터에 데이터 입력시 로우가 적은 테이블부터 실시할 것 클러스터링된 테이블 조인시 로우 수의 역순으로 FROM 절에 기술할 것 클러스터 키를 첫번째로 하는 인덱스는 생성하지 말 것

INDEX 와 CLUSTER 비교 INDEX 사용 CLUSTER 사용 해당 INDEX만큼 TABLE ACCESS 반복 Rowid Columns INDEX TABLE 1 AB 123 . . . . . . . . . . . 4 CA 354 . . 12 BS 123 . . 3 BB 217 . . 10 BD 123 . . 9 CS 5 . . . . . . . . . . . 99 DD 123 . . Index Rowid column 999 . . . 111 3 . . . . 123 1 123 10 123 12 . . . . . 123 99 1 AB . . . . . 12 BS . . . . . 10 BD . . . . . . . . . . . . . . 99 DD . . . . . 123 10 한번 CLUSTER HEADER를 찾아 SCAN 111 1 . . . . 999 . . CLUSTER INDEX Cluster Cluster Key Header 3 BB . . . . . . . . . . . . . . 10 Cluster Header . . . . . . . . . TABLE Rowid Columns

CLUSTERING 예제 SQL> ANALYZE TABLE MECHUL2T COMPUTE STATISTICS; SQL> SELECT AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME = 'MECHUL2T'; SQL> SELECT AVG(COUNT(*)) FROM MECHUL2T GROUP BY SALEDATE; SQL> CREATE CLUSTER MECHUL2# (SALEDATE CHAR(6) ) PCTFREE 10 PCTUSED 60 SIZE 3800; SQL> CREATE INDEX MECHUL2#X ON CLUSTER MECHUL2#; CLUSTER_SIZE = (AVG_ROW_LEN * AVG(COUNT(*)) ) * 1.1 여기서 - AVG_ROW_LEN = ROW의 평균길이, - AVG(COUNT(*)) = Cluster Key 당 평균 row) SQL>RENAME MECHUL2T TO MECHULCT SQL> CREATE TABLE MECHUL2T ( SALENO VARCHAR2(6) NOT NULL, . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ) CLUSTER MECHUL2# (SALEDATE) sjskjskjs SQL> INSERT INTO MECHUL2T SELECT * FROM MECHULCT; SQL> DROP TABLE MECHULCT; SQL> CREATE INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . .

INDEX vs CLUSTER 속도 비교 (예제) SQL> SELECT --+ RULE SUM(SALEQTY) FROM MECHUL2T WHERE SALEDATE LIKE '9410%' SORT AGGREGATE TABLE ACCESS CLUSTER MECHUL2T INDEX RANGE SCAN MECHUL2#X 4091 rows, 0.18 sec SORT AGGREGATE TABLE ACCESS FULL MECHULCT SQL> SELECT SUM(SALEQTY) FROM MECHULCT WHERE SALEDATE LIKE '9410%' 4091 rows, 3.08 sec sjskjskjs SQL> SELECT --+ INDEX(A mc_saledate) SUM(SALEQTY) FROM MECHULCT A WHERE SALEDATE LIKE '9410%' SORT AGGREGATE TABLE ACCESS BY ROWID MECHULCT INDEX RANGE SCAN MC_SALEDATE 4091 rows, 1.5 sec

CLUSTER 사용을 위한 조치 (예제) 1.68 sec 0.07 sec 0.02 sec NESTED LOOPS TABLE ACCESS BY ROWID MECHUL1T INDEX RANGE SCAN M1_SALEDEPT TABLE ACCESS CLUSTER MECHUL2T INDEX RANGE SCAN MECHUL2#X SQL> SELECT CUSTNO, CHULNO, SALEDATE FROM MECHUL1T X, MECHUL2T Y WHERE X.SALENO = Y.SALENO AND X.SALEDEPT = '710' AND Y.SALEDATE LIKE '9410%' 1.68 sec SQL> SELECT CUSTNO, CHULNO, SALEDATE FROM MECHUL1T X, MECHUL2T Y WHERE X.SALENO = Y.SALENO AND X.SALEDEPT = '710' AND Y.SALEDATE LIKE '9410%' NESTED LOOPS TABLE ACCESS BY ROWID MECHUL1T INDEX RANGE SCAN M1_SALEDEPT TABLE ACCESS BY ROWID MECHUL2T INDEX RANGE SCAN PK_MECHUL2T 0.07 sec SQL> SELECT CUSTNO, CHULNO, SALEDATE FROM MECHUL1T X, MECHUL2T Y WHERE X.SALENO = Y.SALENO AND X.SALEDEPT + 0 = '710' AND Y.SALEDATE LIKE '9410%' NESTED LOOPS TABLE ACCESS CLUSTER MECHUL2T INDEX RANGE SCAN MECHUL2#X TABLE ACCESS BY ROWID MECHUL1T INDEX UNIQUE SCAN PK_SALENO 0.02 sec sjskjskjs

VIEW 의 구조 DATA DICTIONARY PARSING CREATE VIEW EMP_VIEW (E_NO,E_NAME) AS SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO = '10' PARSING 1. Shared SQL Memory 검색 2. SQL 보관 3. SQL 해석, 분석 4. Execution Plan 작성 5. Shared SQL Memory 보관 6. Binding 7. Execution 8. Release SELECT * FROM EMP_VIEW WHERE ENAME LIKE 'S%' DATA DICTIONARY OBJ$ OBJ# OWNER # NAME TYPE . . . . . . . 123 12 EMP 2 . . . . . . 152 12 EMP_VIEW 5 . . . . . . 123 1 EMPNO NUMBER . . . . . 123 2 ENAME CHAR . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 1 E_NO NUMBER . . . . . 152 2 E_NAME CHAR . . . . . COL$ OBJ# COL# NAME TYPE . . . . . . VIEW$ OBJ# TEXT 152 SELECT EMPNO,ENAME FROM EMP WHERE DEPTNO = '10' IND$ CLU$ . . . . . . . SELECT EMPNO,ENAME FROM EMP WHERE DEPTNO = '10' AND ENAME LIKE 'S%'

VIEW 의 활용 CREATE VIEW VIEW2 (부서,년월,합계) AS SELECT 부서, 년월,SUM(수량) FROM TAB1 x, VIEW2 y WHERE x.부서 = y.부서 AND y.년월 = '9610' ORDER BY 지역 CREATE VIEW VIEW2 (부서,년월,합계) AS SELECT 부서, 년월,SUM(수량) FROM TAB2 GROUP BY 부서,년월 VIEW 의 활용 10 9610 01 20 10 9610 02 30 10 . . . . . . . . . 10 9612 31 . . . 20 9610 01 20 20 9610 02 60 20 . . . . . . . . . 20 9612 31 50 30 9610 01 20 30 9610 02 30 30 . . . . . . . . . 30 9612 31 50 . . . . . . . . . . . TAB2 SELECT 부서명, SUM(수량) FROM TAB1 x, TAB2 y WHERE x.부서 = y.부서 AND y.년월 = '9610' GROUP BY 부서명 ORDER BY 지역 ... 부서 년월 일 수량 부서 부서명 지역 10 경리과 서울 20 자금과 서울 30 총무과 부산 TAB1 10 9610 01 20 10 9610 02 30 10 . . . . . . . . . 10 9612 31 . . . 20 9610 01 20 20 9610 02 60 20 . . . . . . . . . 20 9612 31 50 30 9610 01 20 30 9610 02 30 30 . . . . . . . . . 30 9612 31 50 . . . . . . . . . . . TAB2 부서 년월 일 수량 G R O U P B Y 부서 부서명 지역 TAB1 10 20 30 . . 년월 = ‘9610’ 10 경리과 서울 20 자금과 서울 30 총무과 부산

다중처리 (Array Processing) X O X 한 명 이동 수백 번 운행 자유롭게 이동 수십 명 이동 수십 번 운행 넓은 길 필요 수백 명 이동 한 번 운행 레일이 반드시 필요 20

다중처리 (Array Processing) 한번 DBMS 호출에 여러 건의 DATA를 처리하는 방법 DBMS 호출 (CALL)은 시스템 OVERHEAD의 주범 ARRAY PROCESSING은 시스템 OVERHEAD를 감소 TOOL, UTILITY에 따라 차이 SQL*FORMS, SQL*PLUS등은 TOOL에서 제공 PRO*SQL, SQL*LOADER등은 APPLICATION에서 지정 ARRAY 단위는 사용자가 지정 가능, 지나치면 OVERHEAD 발생 APPLICATION 작성방법은 3GL과 많은 차이 개발자들의 인식전환이 필요

다중처리 (Array Processing) 1회 스캔(100) 100row 가공처리 100row 1회 처리(100) 2회스캔(100) 2회 처리(100) 3회스캔(100) 3회 처리(100) n회스캔(100) n회 처리(100) 작 업 BUFFER (100 ROW) 100row 100row 한번 FETCH 시 여러 건을 동시에 액세스 액세스와 동시에 데이터의 가공처리 액세스 되면서 INSERT, UPDATE 구별 여러 건을 동시에 INSERT 혹은 UPDTAE 21

Online 다중처리 TABLE BUFFER TERMINAL 1회 스캔(10) EXECUTE_QUERY 2회 스캔(10) NEXT SCROLL 3회 스캔(10) NEXT SCROLL TABLE BUFFER TERMINAL

다중처리 (Non Array Processing) exec sql begin declare section; varchar v_ jongcode[8]; int v_cprice; varchar v_dsum_rowid[20]; exec sql end declare section; EXEC SQL DECLARE c1 CURSOR FOR SELECT a.jongcode, a.cprice, b.rowid FROM dprice_c a, dsum_s b WHERE a.jongcode like '45%' AND a.jongcode = b.jongcode(+) AND a.cdate = b.cdate(+); EXEC SQL OPEN c1; for (;;) { EXEC SQL FETCH c1 INTO :v_jongcode, :v_cprice, :v_dsum_rowid; if (sqlca.sqlcode == 1403) break; EXEC SQL UPDATE DSUM_S SET csum = csum + :v_cprice WHERE ROWID = :v_dsum_rowid; if (sqlca.sqlcode == 1403) EXEC SQL INSERT INTO DSUM_S values ( :v_jongcode, '19940829', :v_cprice); EXEC SQL COMMIT WORK; } EXEC SQL CLOSE c1; EXEC SQL COMMIT WORK RELEASE;

다중처리 (Batch) S x O R T OUTER JOIN FETCH 가공처리 ¢ BUFFER로 ARRAY FETCH TABLE1 과 TABLE2를 OUTER JOIN (ROWID로 SORT하여 추출) ¢ x TABLE2 DATA3 100 DATA4 200 ……….. …. DATAn 900 FLD2 NUM2 TABLE1 DATA1 100 DATA2 200 DATA3 300 DATA4 70 DATAn 600 FLD1 NUM1 BUFFER 가공 처리 . . . . . . . . . TABLE2. ROWID NUM 1 100 200 . . . NULL . . . . . . . . ROWID3 ROWID4 ROWIDn 300 70 2 S O R T . . . . . ROWID로 ARRAY PROCESSING UPDATE ARRAY INSERT

다중처리 (Array Processing) #define ARRAY_SIZE 100 EXEC SQL BEGIN DECLARE SECTION; int v_num1[ARRAY_SIZE]; VARCHAR v_fld1[ARRAY_SIZE][10]; VARCHAR v_rowid[ARRAY_SIZE][20]; short i_rowid[ARRAY_SIZE]; int loop; EXEC SQL END DECLARE SECTION; * EXEC SQL DECLARE C1 CURSOR FOR SELECT a.num1, a.fld1, b.rowid FROM TABLE1 a, TABLE2 b WHERE a.fld1 = b.fld2(+) ORDER BY B.ROWID ;

다중처리 (Array Processing) EXEC SQL OPEN c1; indt = 1; while (indt) { EXEC SQL FETCH c1 INTO :v_num1,:v_fld1,:v_rowid:i_rowid; if (sqlca.sqlcode == 1403) indt = 0; loop = sqlca.sqlerrd[2] - num_ret; num_ret = sqlca.sqlerrd[2]; /* Reset the number. */ /* rowid is null at the fist row fetched */ if ( i_rowid[0] == -1 ) { process_up_ins(0); /* INSERT ONLY */ } else { /* rowid is null at the last row fetched */ if ( i_rowid[loop - 1] == -1 ) { process_up_ins(1); /* UPDATE AND INSERT BOTH */ process_up_ins(2); /* UPDATE ONLY */ } EXEC SQL COMMIT WORK; EXEC SQL CLOSE c1; EXEC SQL COMMIT WORK RELEASE;

다중처리 (Array Processing) process_up_ins( proc_flag ) int proc_flag; { if (proc_flag == 2) { EXEC SQL FOR :loop UPDATE TABLE2 SET num2 = num2 + :v_num1 WHERE ROWID = :v_rowid; } else if (proc_flag == 1) { EXEC SQL FOR :loop INSERT INTO TABLE2 SELECT :v_fld1, :v_num1 FROM dual WHERE :v_rowid IS NULL; WHERE :v_rowid IS NOT NULL AND ROWID = :v_rowid; } else { VALUES ( :v_fld1, :v_num1 ); }

다중처리 (Array Processing)-StoredProcedure CREATE OR REPLACE PROCEDURE ArrFetch AS V_ARRAY_SIZE CONSTANT INTEGER := 5; V_EMPID DBMS_SQL.NUMBER_TABLE; V_ENAME DBMS_SQL.VARCHAR2_TABLE; V_Cursor1 INTEGER; V_RTN INTEGER; V_FETCH_CNT INTEGER; V_SelectStmt VARCHAR2(200); BEGIN V_Cursor1 := DBMS_SQL.OPEN_CURSOR; V_SelectStmt := 'SELECT EMPNO, ENAME FROM EMP'; DBMS_SQL.PARSE(V_Cursor1, V_SelectStmt, DBMS_SQL.V7); DBMS_SQL.DEFINE_ARRAY(V_Cursor1, 1, V_EMPID, V_ARRAY_SIZE, 1); DBMS_SQL.DEFINE_ARRAY(V_Cursor1, 2, V_ENAME, V_ARRAY_SIZE, 1); V_RTN := DBMS_SQL.EXECUTE(V_Cursor1); LOOP V_FETCH_CNT := DBMS_SQL.FETCH_ROWS(V_Cursor1); DBMS_SQL.COLUMN_VALUE(V_Cursor1,1,V_EMPID); DBMS_SQL.COLUMN_VALUE(V_Cursor1,2,V_ENAME); IF V_FETCH_CNT = 0 OR V_FETCH_CNT < V_ARRAY_SIZE THEN EXIT; END IF; END LOOP; DBMS_SQL.CLOSE_CURSOR(V_Cursor1); END ArrFetch;

내부커서의 보관 SQL AREA 의 튜닝 SQL 커서의 튜닝 (HOLD_CURSOR) EXEC ORACLE OPTION (RELEASE_CURSOR=YES); EXEC SQL DECLARE c1 FOR SELECT ...............; EXEC SQL OPEN c1; for(; ;) { EXEC SQL FETCH c1 INTO :col1, :col2; if (sqlca.sqlcode == 1403) break; EXEC ORACLE OPTION (RELEASE_CURSOR=NO); EXEC ORACLE OPTION (HOLD_CURSOR=YES); EXEC SQL UPDATE TABLE1 SET ..........; ............... EXEC SQL INSERT INTO TABLE2 ..........; } EXEC SQL CLOSE c1; 내부커서의 보관

SQL AREA 의 튜닝 공유할 수 없는 SQL Dynamic SQL의 사용 Binding이 먼저 일어나는 경우 SELECT * FROM EMP; SELECT * FROM EMP; SELECT * FROM EMP WHERE DEPTNO = :V_DEPTNO; SELECT * FROM EMP WHERE DEPTNO = :D_NO; SELECT * FROM EMP; SELECT * FROM Emp; SELECT * FROM EMP WHERE DEPTNO = '10'; SELECT * FROM EMP WHERE DEPTNO = '20'; SELECT * FROM EMP; SELECT * FROM SCOTT.EMP; Dynamic SQL의 사용 Binding이 먼저 일어나는 경우

분리 뷰(Partition Views) 최적화 CREATE VIEW Q1 AS select * from JAN_ORDERS union all select * from FEB_ORDERS select * from MAR_ORDERS SELECT sum(revenue) FROM Q1 WHERE ORDER_DATE between '28-JAN-95' AND '23-FEB-95' JAN_ ORDERS table : Index Scan FEB_ ORDERS table : Full table Scan MAR_ORDERS table : Index 에 의해 바로 미처리를 결정 JAN_ORDERS: Index Scan FEB_ORDERS: Full Table Scan MAR_ORDERS: Index Scan for partition elimination

CHAR 타입 특 성 적 용 데이터가 고정길이인 경우 고정길이 문자 데이터(Alphanumeric) 최대 길이 : 255 (default = 1) 다른 타입에 비해 제한이 적다 일부 입력시 BLANK 가 자동으로 채워짐 전혀 값을 주지 않으면 NULL 상태로 입력 지정된 길이보다 길면 입력시 에러가 발생 데이터가 고정길이인 경우 컬럼 길이가 짧고 거의 모든 데이타의 길이 가 일정량을 넘는 경우 고려 가변길이로 지정할 경우 많은 체인(chain) 발생이 우려되는 경우 곧 이어 데이터가 입력되는 경우 테이블 생성 SQL에 DEFAULT constraint 를 ' '(blank) 로 지정 길이의 편차가 심한 경우에 사용하면 불필요한 저장공간의 낭비, 수행속도가 저하

VARCHAR2 특 성 적 용 가변길이 문자 데이터(Alphanumeric) 데이터가 가변 길이인 경우 최대 길이 : 4000 (반드시 길이 지정) 8.0 부터 가능 다른 타입에 비해 제한이 적다 일부만 입력시 뒷부분은 NULL 입력한 값의 뒷부분에 있는 BLANK도 같이 입력 전혀 값을 주지 않으면 NULL 상태 입력 지정된 길이보다 길면 입력시 에러 발생 데이터가 가변 길이인 경우 컬럼 길이의 편차가 심한 경우 NULL 로 입력되는 경우가 많은 경우 적절한 PCTFREE 를 부여하지 않으면 체인발생 가능성이 높다 테이블 생성 SQL에 DEFAULT constraint 를 ' '(space) 로 지정하여 원하는 만큼의 길이를 확보하여 체인을 감소 가능한 CHAR 타입보다 VARCHAR2 사용

문자타입의 비교 법칙 양쪽 모두 CHAR 어느 한쪽이 VARCHAR2 상수값과 비교 길이가 서로 다르면 짧은 쪽에 SPACE를 추가하여 길이를 같게 한 후 비교 서로 다른 문자가 나올 때까지 비교 달라진 첫번째 문자의 값에 따라 크기 결정 BLANK의 수만 다르다면 서로 같은 값 양쪽 모두 CHAR 서로 다른 문자가 나올 때까지 비교 길이가 다르면 짧은 것이 끝날 때까지만 비교 후 길이가 긴 것이 크다고 판단 길이가 같고 다른 것이 없다면 같다고 판단 VARCHAR2는 NOT NULL까지가 길이 어느 한쪽이 VARCHAR2 상수값과 비교 상수쪽을 변수타입과 동일하게 바꾸어 비교 변수쪽이 CHAR 이면 의 경우가 적용 변수쪽이 VARCHAR2면 의 경우로 적용

문자타입의 비교 법칙 'ABC' COL2 : VARCHAR2(7) A B C COL1 : CHAR(7) COL1 COL2 blank COL1 COL2 'ABC' 서로 비교를 해야 하는 경우(JOIN) 같은 타입으로 통일할 것 가능한 VARCHAR2로 통일

NUMBER 적 용 특 성 NUMBER : 길이를 제한하지 않을 정수입력 지정된 소수점이하의 값은 반올림되어 저장 되므로 감안하여 소수점 자리수 결정 문자값과 비교되면 상대타입을 숫자타입으로 바꾸어 비교하므로 인덱스를 생성할 컬럼은 가능한 문자타입으로 할 것 NUMBER 로 지정된 컬럼을 LIKE 'char%' 로 비교하면 인덱스를 사용하지 않음 NUMBER(p,s) 로 지정시 p는 s의 자리수를 포함한 길이므로 감안하여 P의 자리수를 결정 NUMBER 타입은 항상 가변 길이 이므로 충분하게 지정할 것 음수, ZERO, 양수 저장 범위 : 1.0 x 10 ~ 9.9...9 x 10 전체 자리수는 38자리를 넘을 수 없다 소수점은 -84 ~ 127 소수점이 지정되지 않았을 때 소수점이 입력되거나, 지정된 소수점자리 이상 입력되면 반올림되어 저장 지정한 정수 자리수 이상 입력시 에러 -130 125

DATE 특 성 적 용 일자와 시간을 저장 일자나 시간의 연산이 빈번한 경우 사용 포함정보 : 세기, 년도, 월, 일, 시간, 분, 초 NLS_DATE_FORMAT을 이용하여 국가별 특수성을 해결 특별히 시간을 지정하지 않으면 00:00:00 특별히 일자를 지정하지 않았다면 현재 월의 1일로 지정됨 SYSDATE는 현재일과 시간을 제공 일자나 시간의 연산이 빈번한 경우 사용 시간이 정상적으로 입력된 경우 일자를 '='로 비교할 수 없음 LIKE, SUBSTR 등 STRING 비교 불가능 자주 조건절에 사용되거나 EUC에 적용시 문자타입을 적용하는 것을 고려해 볼 것 주로 DATE 연산이나 기록(logging), TIMESTAMP로 사용되는 컬럼은 사용 INDEX를 적용할 컬럼은 문자타입으로 JOIN 되는 상대 컬럼과는 같은 타입으로

TIMESTAMP 타입 특 성 적 용 SQL :1999 표준 지원 (9i 부터 추가됨) 밀리초를 통해 초단위 이하의 트랜잭션을 관리 가능 LIKE, SUBSTR 등 STRING 비교 불가능 전세계적 데이터 처리시 지역 시간대별 처리 가능 주로 DATE 연산이나 기록(logging), TIMESTAMP로 사용되는 컬럼을 사용 사용자 세션에 맞게 시간차 자동 계산 SQL :1999 표준 지원 (9i 부터 추가됨) DATE 데이터 형의 확장 포함정보 : 세기, 년도, 월, 일, 시간, 분, 초, 밀리초 WITH TIME ZONE 을 추가하여 지역별 시간차를 관리. WITH LOCAL TIME ZONE을 추가하여 세션의 지역 시간대에 맞추어 값을 변환 특별히 밀리초를 지정하지 않으면 000000 밀리초 포멧 : ‘FF’

INTERVAL 타입 특 성 적 용 SQL :1999 표준 지원 (9i 부터 추가됨) DATE 데이터 형의 확장 시간차에 대한 기록으로 관리 가능 기준시간대와의 시간차로 관리 가능 처리에 대한 시간차 지정 가능 예) 생산 또는 유통기한을 시간차로 지정하여 자동 계산되게 관리 가능하다. DATE DataType과의 직접 연산 가능 SQL :1999 표준 지원 (9i 부터 추가됨) DATE 데이터 형의 확장 두 Datetime 값의 차이를 저장하기 위해 사용 포함정보 : 년, 월, 일, 시간, 분, 초 두 가지 종류의 정밀도를 제공. INTERVAL YEAR TO MONTH 시간 간격을 연 및 월을 이용하여 저장 INTERVAL DAY TO SECOND 시간 간격을 일, 시간, 분, 초를 이용 저장

데이터 길이의 지정 VARCHAR2 는 가능한 충분하게 최대치를 부여 CHAR는 가능한 최소의 길이를 지정 한글과 영숫자가 입력되는 컬럼은 충분한 길이를 부여 특히 IBM에서는 SO,SI를 감안할 것 NUMBER 타입의 길이를 제한하지 않을 때는 NUMBER로만 지정하고 가능한 충분하게 지정 NUMBER 타입의 소수점은 그 이하에서 반올림되어 저장되므로 감안해서 지정 NUMBER 타입은 소수점을 지정하지 않으면 소수점 이하에서 반올림되어 정수로 저장되므로 소수점 저장을 원하면 반드시 소수점 지정 NUMBER 타입의 길이는 소수점을 포함한 길이므로 필요한 정수부분을 확인

비용 기준 최적화 (HINT) /*+ */ SYNTAX hint text EXAMPLE SELECT DELETE SELECT UPDATE hint text /*+ */ EXAMPLE SELECT name, height, weight FROM patients WHERE gender = 'M' ; /*+ INDEX(PATIENTS GENDER_INDEX) 남자환자가 거의 없으므로 GENDER_INDEX를 사용함 */

비용 기준 최적화 (HINT) 기 능 설 명 RULE ↔ CHOOSE, RULE BASED OPETIMIZER를 사용 기 능 설 명 RULE ↔ CHOOSE, RULE BASED OPETIMIZER를 사용 FIRST_ROWS 첫째 레코드의 추출시간을 최소화할 목적으로 최적화 (Response Time Goal) ALL_ROWS 모든 레코드를 모두 처리하는 시간을 최소화할 목적으로 최적화 (Throughput) FULL 지정된 테이블에 대한 전체 스캔 ROWID 지정된 테이블에 대한 ROWID에 의한 테이블 스캔 CLUSTER 지정된 테이블에 대한 클러스터 스캔 HASH 지정된 테이블에 대한 해쉬 스캔 INDEX_ASC 내림차순 (순차적)으로 인덱스를 스캔 INDEX_DESC 오름차순 (역순)으로 인덱스를 스캔 INDEX_FFS 주어진 인덱스를 FAST FULL SCAN AND_EQUALS 여러 개의 인덱스를 머지하여 사용 (2~5개) ORDERED FROM절에 기술된 순서대로 테이블을 조인 USE_NL 먼저 특정 테이블의 행을 억세스하여 그 값에 해당하는 다른 테이블의 행을 찾는 작업을 해당범위까지 실행하는 조인 USE_MERGE 먼저 각각의 테이블의 처리범위를 스캔하여 정열한 후 서로 머지하면서 조인 USE_HASH 주어진 테이블에 대하여 HASH JOIN하도록 함 CACHE ↔ NOCACHE, Full table scan시 block을 LRU의 MRU 위치에 둔다

버전별 HINT 비교 H I N T 7.x 8.0 8i 9i Optimization Approaches and Goals ALL_ROWS FIRST_ROWS(n) CHOOSE RULE Join Orders ORDERED STAR Parallel Execution PARALLEL NOPARALLEL PQ_DISTRIBUTE PARALLEL_INDEX NOPARALLEL_INDEX O O O O

버전별 HINT 비교 H I N T 7.x 8.0 8i 9i Access Methods O O O O FULL ROWID CLUSTER HASH INDEX INDEX_ASC INDEX_COMBINE INDEX_JOIN INDEX_DESC INDEX_FFS NO_INDEX AND_EQUAL O O O O

버전별 HINT 비교 H I N T 7.x 8.0 8i 9i Query Transformations O O O O USE_CONCAT NO_EXPAND REWRITE NOREWRITE MERGE NO_MERGE STAR_TRANSFORMATION FACT NO_FACT O O O O

버전별 HINT 비교 H I N T 7.x 8.0 8i 9i Join Operations USE_NL USE_MERGE USE_HASH DRIVING_SITE LEADING MERGE_AJ MERGE_SJ HASH_AJ HASH_SJ NL_AJ NL_SJ O O O O

버전별 HINT 비교 H I N T 7.x 8.0 8i 9i Additional Hints APPEND NOAPPEND CACHE NOCACHE UNNEST NO_UNNEST PUSH_(JOIN_)PRED NO_PUSH_(JOIN_)PRED PUSH_SUBQ ORDERED_PREDICATES CURSOR_SHARING_EXACT O O O O

EXPLAIN PLAN create unique index plan_index on plan_table (statement_id, id) index plan_table oracle7/rdbms/admin/utlxplan.sql explain plan set statement_id = 'a1' for select col3, sum(col4) from tab1 where a.col1 in ('10', '50') group by col3 수동 sql> set autot trace exp sql> select col3, sum(col4) from tab1 where a.col1 in ('10', '50') group by col3 자동 Insert 실행계획

수동 EXPLAIN PLAN 조회 plan_table plan.sql run SQL> def id=a1 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_owner) + 1,',')||object_name|| decode (object_type,'UNIQUE',' (U) ','NON-UNIQUE', '(NU)',null) || decode(object_instance,null,null,'('||object_instance||')') FROM PLAN_TABLE START with ID = 0 and STATEMENT_ID = '&&id' CONNECT by prior ID = PARENT_ID and STATEMENT_ID = '&&id' plan_table plan.sql SQL> def id=a1 SQL> @plan run ID OPERATION OPTIONS OBJECT_NAME 0 SELECT STATEMENT 1 CONCATENATION 2 TABLE ACCESS BY ROWID :SCOTT. TAB1(1) 3 INDEX RANGE SCAN :SCOTT.TAB1_IDX (NU) 4 TABLE ACCESS BY ROWID :SCOTT.TAB1(1) 5 INDEX RANGE SCAN :SCOTT.TAB1_IDX (NU) 실행계획

SQL 실행계획 1 2 6 3 4 5 SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND not exists (SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal) 1 2 6 FILTER NESTED LOOPS TABLE ACCESS (FULL) salgrade 3 4 (FULL) emp (BY ROWID) dept 5 INDEX (UNIQUE SCAN) pk_dept ID OPERATION OPTIONS OBJECT_NAME 1 FILTER 2 NESTED LOOPS 3 TABLE ACCESS FULL EMP 4 TABLE ACCESS BY ROWID DEPT 5 INDEX UNIQUE SCAN PK_DEPT 6 TABLE ACCESS FULL SALGRADE 22

Accept Card에 따른 SQL의 변형 Application (입력조회) Accept Card의 경우의 수 출고일자 : 고객번호 : 출고번호 출고시간 도착일시 출고일시 차량종류 운송비 출고부서 Field명 : chuldate : custno 경우 (출고일자) (고객번호) 1 941130 null 2 null BC40 3 null null 4 9411% BC40 1) Select * from CHULGOT WHERE CHULDATE = '941130' ; 2) Select * from CHULGOT where CUSTNO = 'BC40' ; 3) Select * from CHULGOT ; 4) Select * from CHULGOT where CHULDATE like '9411%' and CUSTNO = 'BC40' ; Dynamic SQL이용 PARSING 전 SQL상태 경우 SELECT * FROM CHULGOT WHERE CHULDATE LIKE :chuldate||'%' AND CUSTNO LIKE :custno||'%'; Like문 이용 PARSING 전 SQL상태

Accept Card에 따른 SQL의 변형 Dynamic SQL 실행절차 Like문의 실행절차 Dynamic SQL의 특징 입력 Binding SQL Parsing Execute Like문의 실행절차 입력 SQL Parsing Binding Execute LIKE 문의 특징 입력사항에 따라 최적화처리 변동 Tool에 따라 처리방법이 상이 . SQL*Forms : Execute_Query, Enter_Query . SQL*Plus : '&'또는 '&&' . Precompiler : Dynamic SQL(4 Method) Tool에서 제공되지 않을 경우 구현이 복잡 Dynamic SQL의 특징 입력된 값이 Binding되기전에 처리경로를 확정 처리경로로 확정된 필드가 입력되지 않았을 때, Performance저하 간단 명료하게 작성 가능 처리경로가 되는 필드는 필수입력필드로 설정필요

착각 Fetch 와 Select ... into 와의 차이점 Fetch Select ... into N 건 처리시 사용한다 EXEC SQL SELECT col1, col2 INTO :var1, :var2 FROM tab1; Cusor 선언 Parsing Cursor 닫음 DECLARE cursor_name Cursor FOR SELECT col1, col2 OPEN cursor_name; FETCH cursor_name INTO :var1, :var2; CLOSE cursor_name; N 번 1 번 착각 한건 찾아올때 쓴다 N 건 처리시 사용한다 N회 Array fetch 한번 Array fetch

용도 주의점 Fetch 와 Select ... into 와의 차이점 Fetch Select ... into MAX를 모를때 MAX가 지나치게 넓어서 한번 FETCH로는 무리한 경우 (100 * N) * ? MAX를 알 때 사용 (100 * n) * 1 용도 Loop Query SQL 내에 cursor문을 다시 declare 해서는 절대 안됨 Loop 내에 반복되는 n건 처리 Declare SQL에서 Join 하여 해결 주의점