Transcript 1차세미나
RDB Seminar (1) – Index, Join 2008.1.9 By bleujin INDEX RDB 아키텍쳐의 구조 인덱스의 원리 조인의 원리 Add 1. 실행계획 보기 Add 2. 비슷하지만 다른 쿼리 2 /57 RDB Architecture Architecture 이해 Instance Memory User Process Server Process Database Database File 3 /57 RDB Architecture DB에 대한 오해 파일 IO에 비해서 DB가 느리다? 쿼리 결과를 Cache 하면 빨라진다? 조인하면 느려진다? Emp 테이블 empno(4), ename(10), sal(4), managerid(4), comm(4) 데이터 건수가 많아지면 느려진다? Dept 테이블 deptno(4), dname(20), loc(10) Query : select * from emp, dept where emp.deptno = dept.deptno 조인 : 100,000 * (4 + 10 + 4 + 4 + 4) + 1000 * (4 + 20 + 10) byte ???? 역정규화 : 100,000 * (4 + 10 + 4 + 4 + 4 + 20 ) byte 4 /57 RDB Architecture Metaphor 5 /57 RDB Architecture Oracle DB Architecture 6 /57 Index Index 의 원리 Index 단점 1. 모든 인덱스는 DML을 느리게 만든다 2. 모든 인덱스는 공간과 그밖의 데이타 베이스의 자원을 소모한다. 3. 인덱스는 시간이 지날수록 애플리케이션의 성능을 악화시킨다. 4 .인덱스의 효율을 복구하려면 인덱스를 재구성하는 작업이 필요하며, 이 작업은 그 자체로 성능과 데이타의 이용성에 좋지 않는 영향을 주는 부담스러운 작업이다. 5. 옵티마이저가 쿼리를 해결하는데 인덱스가 도움이 되지 않는다고 판단하면 (즉 아무런 이유없 이 자원과 공간을 소모하고, 성능을 떨어뜨리는 경우) 인덱스는 이용되지 않는다. Index 단점 옵티마이저는 인덱스가 충분히 선택할만 하다고 판단될때 그리고 인덱스 자체만으로 쿼리에 대답할수 있는 경우 인덱스를 이용하여 좀더 적은 I/O로 검색 결과를 내보낼수 있다. 7 /57 Index Index 원리 Page 37 Akhtar Ganio ... Page 41 Akhtar 4:706:01 Barr 4:705:03 Con 4:704:01 Funk 4:706:02 Funk 4:704:02 Akhtar ... Martin Page 12 - Root Page 51 Ganio 4:709:01 Hall 4:709:04 Jones 4:709:02 Jones 4:708:03 Jones 4:707:03 Non-Leaf Level Page 28 Martin Smith ... Page 61 Martin 4:708:01 Matey 4:706:04 Ota 4:707:02 Phua 4:708:02 Rudd 4:705:01 Non clustered Index Page 71 Leaf Level Smith 4:706:03 (Key Value) Smith 4:708:04 Smith 4:707:01 White 4:704:03 White 4:705:02 Heap Page 704 01 ... Conn 02 ... Funk 03 ... White ... ... ... ... ... ... File ID #4 Page 705 01 ... Rudd 02 ... White 03 ... Barr ... ... ... ... ... ... Page 706 01 ... Akhtar 02 ... Funk 03 ... Smith 04 ... Matey ... ... ... Page 707 01 ... Smith 02 ... Ota 03 ... Jones ... ... ... ... ... ... 8 /57 Page 808 01 ... Martin 02 ... Phua 03 ... Jones 04 ... Smith ... ... ... Page 709 01 ... Ganio 02 ... Jones 03 ... Hall ... ... ... ... ... ... Index Index 의 원리 Index를 사용하는 경우와 아닌 경우 SARG Non-SARG WHERE name=‘Smith’ WHERE salary=commission WHERE salary < 3000 WHERE Salary!= 3000 WHERE price = 100/12 WHERE price*12=100 WHERE au_lname like ‘S%’ WHERE substring(au_lname,1,1)=‘S’ WHERE price between 9 and 20 WHERE price < 2 and price > 4 WHERE au_lname like ‘Sm%’ WHERE startDay || startTime = ‘20020101123412’ Index Column PK, Where 조건에 자주 사용되며 Selectivity가 낮을때 Group by, Order by 에 자주 사용되는 컬럼, 조인이나 FK 9 /57 Index Searching B Tree i <- 1 while i <= n[x] and k > keyi[x] do i <- i + 1 Data page if i <= n[x] and k = keyi[x] then return (x, i) 123456789011, 21, bleujin, seoul, [email protected] if leaf[x] 123456789012, then return NIL 22, Admin, busan, none 123456789013, 19, novision, seoul, [email protected] else Disk-Read(ci[x]) 123456789014, 17, sehan, seoul, [email protected] return B-Tree-Search(c i[x], k) 10 /57 Index Insert B Tree i <- n[x] if leaf[x] then while i >= 1 and k < keyi[x] do keyi+1[x] <- keyi[x] i <- i - 1 keyi+1[x] <- k n[x] <- n[x] + 1 Disk-Write(x) else while i >= and k < keyi[x] do i <- i - 1 i <- i + 1 Disk-Read(ci[x]) if n[ci[x]] = 2t - 1 then B-Tree-Split-Child(x, i, ci[x]) if k > keyi[x] then i <- i + 1 B-Tree-Insert-Nonfull(ci[x], k) 11 /57 Index Update / Delete B Tree(Oracle) 컬럼A 컬럼B 컬럼C 컬럼D 컬럼E ROWID A1 B3 C2 D2 E3 AAAA B2 C3 D1 E1 BBBB D3 E2 DDDD D3 E4 CCCC A1 B2 A2 B1 ColA_idx C1 ColBD_idx ColE_idx 컬럼A ROWID 컬럼B 컬럼D ROWID 컬럼E ROWID A1 AAAA B1 D3 CCCC E1 BBBB A1 DDDD B2 C3 BBBB E2 DDDD A2 CCCC B2 DDDD E3 AAAA AAAA E4 CCCC B3 Non Unique Index C2 Composite Unique Index 12 /57 Unique Index(Not Null) Index Update / Delete B Tree(In Other DBMS) 컬럼A 컬럼B 컬럼C 컬럼D 컬럼E ROWID A1 B3 C2 D2 E3 AAAA null B2 C3 D1 E1 BBBB A1 B2 null D3 E2 DDDD A2 B1 C1 D3 E4 CCCC ColA_idx ColBD_idx ColE_idx 컬럼A ROWID 컬럼B 컬럼D ROWID 컬럼E ROWID null BBBB B1 D3 CCCC E1 BBBB A1 AAAA B2 DDDD E2 DDDD A1 DDDD B2 C3 BBBB E3 AAAA A2 CCCC B3 C2 AAAA E4 CCCC Non Unique Index Composite Unique Index 13 /57 Unique Index(Not Null) Index Fragmentation / Rebuild 14 /57 IndexType - Composite Composite Index CREATE INDEX문에 기술된 Key Column들의 순서가 중요 예: CREATE INDEX test_ind ON test (A, B) WHERE A=‘Value’ : efficient WHERE A=‘Value’ and B=‘Value’: efficient WHERE B=‘Value’ : less efficient 전화번호부 : FirstName + LastName Where LastName = ‘정진’ Where FirstName > ‘’ And LastName = ‘정진’ ?? Where FirstName in (Select FirstNameList from FirstNameArray) And LastName = ‘정진’ Index : 부서번호, 날짜, 사원이름 Where 날짜 between ‘20030301’ and ‘200303’ ?? 어떻게 수정 15 /57 IndexType - Composite Composite Index 보조 자료 17 page – 28 page 16 /57 IndexType - Cluster Clustered Index Clustered Index Leaf level은 데이터 row Akhtar Ganio … Akhtar … 유니크 인덱스 Martin Page 140 - Root 모든 데이터는 Martin 정렬되어 있음 - 테이블당 Smith 한 개만 가능 … Group by,Page Order 145 by, Where – Good Page 141 Akhtar 2334 Barr 5678 Con 2534 Funk 1334 Funk 1534 ... ... Page 100 ... ... ... ... ... ... Ganio 7678 Hall 8078 Jones 2434 Jones 5978 Jones 2634 ... ... Page 110 ... ... ... ... ... ... 17 /57 Insert, Update 는 보다 Harder Martin- 인서트시 1234 ... 경합 Smith 1434 ... 감소요인 Martin 7778 ... Smith 5778 ... Ota대량5878 7978 ... 범위...서치에 Smith 주로 사용 Phua - 인덱스 7878 ... 장점 + White 스캔 2234 장점 ... Rudd 6078 ... White 1634 ... ... ... ... ... ... ... 오라클의 IOT Page 120 Page 130 IndexType - Cluster Clustered Index(MSSQL) Clustered Index( A + B) ColB_Idx 컬럼B 컬럼A 컬럼B B1 A4 B3 B2 A2 B2 B2 A3 B2 B2 A7 B1 B3 A1 B2 B3 A10 B7 B7 A9 B3 컬럼A 컬럼B 컬럼C 컬럼D 컬럼E ROWID A1 B3 C2 D2 E3 AAAA A2 B2 C3 D1 E1 BBBB A3 B2 Null D3 E2 CCCC A4 B1 C1 D3 E4 DDDD A7 B2 C1 D11 E15 EEEE A9 B7 Null D24 E12 FFFF A10 B3 C3 D222 E1 GGGG 18 /57 IndexType - Covering Covering Index Emp Index : empno, ename Select count(*) from emp ? Select empno, ename from emp ? Select empno, count(*) from emp group by empno ? Select ename, sal from emp where empno = 7756 ? Oracle은 Covered Index가 안된다? - 오라클은 null 인 인덱스는 저장되지 않는다.. Non Clustered Index + Clustered Index 가 같이 있을때.. Clustered Index : ename Non Clustered Index : empno Non Clustered Index : deptno Select ename, empno from emp ? Select ename, deptno from emp where deptno = 10 ? Select ename, empno from emp where deptno = 10 ? 19 /57 IndexType Covering Update / Delete B Tree(MSSQL) Clustered Index ColA_Idx 컬럼A 컬럼B 컬럼C 컬럼D 컬럼E ROWID A1 B3 C2 D2 E3 AAAA null B2 C3 D1 E1 BBBB A1 B2 null D3 E2 DDDD A2 B1 C1 D3 E4 CCCC ColBD_idx 컬럼B 컬럼D 컬럼A B1 D3 A2…. Select 컬럼A from table A1…. B2 B2 C3 Null…. B3 C2 A1…. NonCluster Index 20 /57 Select 컬럼C from table Select 컬럼A, 컬럼B from table Where 컬럼D = D1 Index – Index Type Function Based Index CREATE TABLE 매출 ( productID int primary key 원가 int , 판매가 int , 이익 as (판매가-원가) ) insert into 매출 values (500,700) insert into 매출 values (600,850) insert into 매출 values (400,750) Go create index ind_매출 on 매출 (이익) ; Select * from 매출 Where 이익 < 10 ; Create index ind_매출 on 매출(판매가 – 원가) ; Select * from 매출 Where (판매가 – 원가) < 10 21 /57 Index – Bitmap Bitmap Index 성별_Bidx Id 성별 직급 근무지역 컬럼E ROWID Id1 남 사원 서울 E3 AAAA Id2 여 사원 부산 E1 BBBB Id3 남 사원 서울 E2 CCCC Id4 남 관리자 서울 E4 DDDD Id7 여 사원 부산 E15 EEEE Id9 남 관리자 광주 E12 FFFF id0 남 사원 서울 E1 GGGG 직급_Bidx 근무지역_Bidx M :1011011… 사 :1110101… 서 :1011001… F : 0100100… 관 : 0001010… 부 : 0100100… 남자이면서 직급이 관리자이고 광주에 사는 사람 M :1011011… 관 : 0001010… 광 : 0000010… 광 : 0000010… : 0000010… 22 /57 Index - Add Other Index Variations DESC Value – IBM, Oracle Function Base Index – Informix, Microsoft, Oracle, PostgreSQL Reverse Key – IBM, Oracle Hashes – Ingres, Informix, Oracle, PostgreSQL Full Text indexing = Microsoft, MySQL, Oracle Not Exact copy of column value Oracle refuse to Store Nulls Key Size Long Compress - Fron Compression - Back Compression - Composite Compression 23 /57 Join Join의 종류 Example Query SELECT a.FLD1, ..., b.FLD1,... FROM TAB2 b, TAB1 a WHERE a.KEY1 = b.KEY2 AND b.FLD2 like 'A%' AND a.FLD1 = '10' 24 /57 Join - Nested Loop Nested Loop 원리 for(each block in Table1) { /* Outer loop */ for(each block in Table2) { /* Inner loop */ for (each row in Table1) { for (each row in Table 2){ if(Table2 join column matches Table2 join column) pass else fail for (each row }in Table1) { /* Outer loop */ for (each row in Table 2){ /* inner loop */ } }if(Table2 join column matches Table2 join column) pass }else fail } } 25 /57 Join - Nested Loop Nested Loop 특징 선행 테이블의 처리범위가 일량을 결정(방향성) 선행 테이블의 값을 받아서 후행테이블의 범위가 결정된다(종속적) 주로 랜덤 억세스 방식으로 처리된다(랜덤억세스) 후행 테이블의 조인컬럼의 인덱스 유무 및 조건의 인덱스 참여정도에 따라 수행속도가 많이 차이난다 (연결고리 상태) 유연하고 벤더에서 구현이 쉬우므로 모든 DBMS에 의해 지원되며 대부분의 상황에서 기본 선택이다.. 이퀄 조인이 아닌 조인도 할 수 있다. 가장 많이 사용되는 조인 방식(90, 50) 26 /57 Join - Nested Loop Nested Loop 장/단점 부분 범위 처리를 하는데 유리하다 처리량이 작은 경우에 유리하다. – 랜덤 억세스가 많을 경우 Sort Merge, Hash Join으로 유도 선행 테이블의 결과를 받아야만 후행테이블의 처리 범위를 줄일수 있는 경우에 유리하다 선행 테이블의 처리 범위가 수행속도에 절대적 영향을 미치므로 최적의 조인순서가 될수 있도록 해야 한다. 선행테이블의 순서대로 나온다. 27 /57 OLTP에 적합 Join – Sort Merge Sort(Table1) Sort Merge Join Sort(Table2) Get first row(Table1) Get first row(Table2) For(;; until no more rows in tables){ For (each row in Table 2){ /* inner loop */ if(Table2 join column matches Table2 join column) pass else fail If (join column in Table1 < join column in Table2) Get next row(Table1) Else if(join column in Table1 > join column in Table2) Get next row(Table2) Else if(join column in Table1 = join column in Table2) pass get next row(Table1) get next row(Table2) } 전체적으로 안정되고 확장 가능성 충분히 고려 } 28 /57 Join – Sort Merge Sort Merge Join의 특징 상태 테이블로부터 결과값을 제공받지 않고, 자신에게 주어진 조건으로만 처리범위를 결정한다(독립적) 각자 Sort후 조인을 하게 되므로 부분범위 처리가 아닌 전체범위 처리를 하게 된다(전체범위 처리) 조인의 순서에는 상관없다. 인덱스가 아닌 컬럼도 Merge할 작업 대상을 줄이므로 중요한 의미를 지닌다 Ram이 얼마나 충분한가에 영향을 받는다. 테이블을 sequence scan 으로 한번만 읽는다. 주로 전체범위의 통계나 배치 처리에 많이 쓰인다.. 29 /57 Join – Sort Merge Sort Merge Join의 장/단점 처리량이 많거나 전체범위 처리시에 유리하다. 랜덤엑세스가 많은 NL은 불리 스스로 자신의 처리범위를 많이 줄일 수 있을때 유리하다. 연결고리 이상 상태에 영향을 받지 않으므로 연결고리 컬럼을 위한 인덱스를 생성하지 않고도 유용하게 사용할 수 있다. 처리할 데이터가 적은 온라인 어플리케이션에서는 NL이 유리한 경우가 많으므로 SM은 주의해서 사용한다. 양쪽 테이블에 같은 키로 Clustered 인덱스가 있을 경우 효율이 좋다. 30 /57 Join – Hash Join hash join Read rows = join column in Table1 Stability - 안정적인 레이아웃 Same design Different Contends Create Hash페이지 Table( Hash :Function(rows)) - 일관성 있는 정보 배치 - 제한된 색과 폰트를 사용한 타이포 그래픽의 은은함 고려 For(;; until no more rows in Table2) { If ( Hash Function( join column in Table2) Exist in Hash Table ) { Identity pass - Educative Identity : Active, Chic -> Bleu, Grey 계열 - User-centered Identity : Stable, Friendly -> Decent yellow 계열 } - Joyful : Fun Delight -> Orange 계열 Get Identity next row(Table2) 전체적으로 안정되고 세련된 이미지를 유지하며 편안한 분위기 연출 } 31 /57 Join – Hash Join hash join 의 특징 다른 테이블의 결과 값을 제공받지 않고, 자신에게 주어진 조건으로만 처리범위를 결정한다.(독립적) 해쉬 함수를 이용하므로 부분범위 처리를 할 수 없고 전체범위 처리 메모리 영영만으로 해쉬 테이블을 생성시 최적의 효과를 낼 수 있으므로 적은 테이블이 선행으로 온다(와야 한다.) 해쉬 함수를 이용하므로 결과값의 정렬을 보장할 수 없다. 테이블을 sequence scan 으로 한번만 읽는다. 대용량 데이터 베이스에서 필수 32 /57 Join – Hash Join hash join 의 특징 대량의 데이터 억세스, 배치처리, 풀 테이블 스캔 하면서 조인해야 할때 유리하다. 비용은 많이 들지만 수행속도를 보장해야 하는 작업에 유용하다(Parellel Query) 가능한 메모리 내에서 작업 가능하도록 init Parameter 정보를 변경하여 사용한다. (Has_area_size, Hash_multiblock_io_count 등) 이퀄 조인만 가능하다. 33 /57 Join – Outer Join Outer Join(NL Plan의 변화…Driving 테이블이 고정됨) select * from emp, dept where emp.deptno = dept.deptno select * from emp, dept where emp.deptno = dept.deptno(+) MSSQL 사용시 주의점(*, Left Outer Join은 다름) select count(*) from A , B where A.emp *= B.emp and A.ym = '200212‘ and B.ym='200212‘ and A.dept='40'; select count(*) from A left outer join B on A.emp = B.emp where A.ym = '200212‘ and B.ym='200212‘ and A.dept='40'; 34 /57 Join – Clustering Clustering 테이블의 건수가 많으면 느려진다… 그래서 여러 테이블로 쪼갠다. -> DB의 속력은 실제 읽는 I/O -> 관리상의 문제 -> 불편함 … 물리적 하드가 분리되지 않는 이상 빨라지지 않는다.(파티션 뷰) 필드를 옆으로 늘린다 -> DB의 I/O 단위는 블록 -> 인덱스 수가 늘어난다 -> Lock이 더 자주 걸린다. 기존 데이터를 삭제한다 -> 풀서치 쿼리는 HWM 까지 읽는다. 근데 느리데니깐… Why ? -> 읽은 row 건이 읽은 block 수를 보장해 주지 못한다 35 /57 Join – Clustering Clustering 보조 자료 114 page – 125 page 36 /57 부분범위 처리 부분범위 처리의 의미 보조 자료 28 – 42 page 참조… 37 /57 부분범위 처리 부분범위 처리의 예제 select columns..... from bulletin_tblc where bulletinCode = 'novision' select columns..... from bulletin_tblc where bulletinCode = 'novision' order by bulletinNo select columns..... from bulletin_tblc where bulletinCode = 'novision' and bulletinNo > 0 select columns..... from bulletin_tblc where bulletinCode = 'novision' order by bulletinNo desc select /*+ index_desc(bulletin_tblc bulletin_idx)*/ columns..... from bulletin_tblc where bulletinCode = 'novision' 38 /57 부분범위 처리 부분범위 처리의 예제 – 실행시간 – 3분 select category_id, parent_cat_id, name, lpad('-', level, '-') || name from category connect by prior category_id = parent_cat_id start with category_id in ( select category_id from category where parent_cat_id = 0 and category_use = 0 connect by category_id = prior parent_cat_id start with category_id in (select category_id from user_category_permission where user_id = 'novision' and (pm_id in ('AAAAA', 'SUBAA') or pm_id like 'C%' or pm_id like 'T0%' or pm_id like 'A0%') group by category_id) group by category_id) 39 /57 부분범위 처리 부분범위 처리의 예제 – 실행시간 – 0.3초 select category_id, parent_cat_id, name, alias, lpad('-', level, '-') || name from category connect by prior category_id = parent_cat_id start with category_id in ( select category_id from category x1 where parent_cat_id = 0 and category_use = 0 and category_id > 0 and Exists ( select 1 from user_category_permission x2 where Exists (select 1 from category where x2.category_id = category_id and rownum = 1 connect by prior category_id = parent_cat_id start with category_id = x1.category_id ) and x2.user_id = 'novision' and (x2.pm_id In ('AAAAA', 'SUBAA') or x2.pm_id like 'C%' or x2.pm_id like 'T0%' and x2.pm_id like 'A0%' ) and rownum = 1 ) group by category_id ) 40 /57 Lock Lock 자원은 남는데도 잠금으로 인해 느려짐 보조 자료2 참조… 41 /57 커서 Cursor의 종류 TSQL 커서(Ansi cursor) - Declare Cursor Open cursor Fetch ....... Close cursor 메타 데이타가 매번 전송. 업데이트 될수 있는 스크롤 가능 변경에 대한 민감성, 상대적 위치 지정과 절대적 위치 지정 모든 행 들이 개별적으로 요청 42 /57 커서 Cursor의 종류 API 서버 커서(OLE DB 공급자, ODBC 드라이버, DB-Library 프로그래밍) - 커서가 여러행을 가르킴 SQLFetchScroll(ODBC), IRowset::GetNextRows(OLEDB) 프로그래밍 환경에서 동작 메타 데이타는 한번만 클라이언트 커서 - V6에서 스크롤 가능 서버 커서 지원 집합 전체를 캐쉬 -> 로컬 버퍼링 메커니즘 무결성 데이터 관리 문제 인터넷 환경에 적합한 커서 타입 43 /57 커서 Cursor의 형식(ANSI : Scroll, Insersitive) Static - 읽기 전용 tempdb에 스냅샷 저장, Insensitive, Scroll 집계함수 사용시 정적 커서 키셋 커서 키 값을 tempdb에 저장 where balance > 10000 where cust_Id in (7, 12, 18, 24, 56) 고유 인덱스가 있어야 함 -> 없으면 Static 으로 동적커서 Fetch 동작 시에 Select문이 다시 적용 Order by 절이 있어야 함 인덱스가 존재하고 Order by에 사용되지 않으면 키셋 사용 인덱스가 없으면 Static 커서 사용 빠른 전진 전용 커서 - 트리거 걸려 있는 테이블은 키셋(상황조건적) - 분산쿼리는 키셋 방식으로 44 /57 DB를 잘한다는 것 DB를 잘한다는 것 집합에서… 원하는 집합을…. 원하는 집합형태로…. 45 /57 DB를 잘한다는 것 도움이 될 서적 / 강의 입문서 : 전문가로 가는 지름길 SQL Server 2000(개발자용) - 국내서, 정원혁 저 SQL Server 2000 For Developers - 온라인 강의(무료), 정원혁 SQL Server 2000의 뷰 / 프로시저 / 트리거 - 온라인 강의(무료), 정원혁 SQL Server 2000 세미나 SELECT, NULL, SET - 온라인 강의(무료), 정원혁 초급 : SQL Server 2000 Programming - 번역서, Robert Vieira 저 SQL Server 2000 Backup & Restore - 온라인 강의(무료), 정원혁 SQL Server 2000 - 트랜잭션과 잠금 - 온라인 강의, 정원혁 SQL Server 2000 Internal - 온라인강의, 정원혁 Transact SQL – 번역서, ken Henderson Oracle OLN 강의 – ION FTP 기본 : SQL Server 2000 포켓 컨설턴트(관리자용) - 국내서, 정원혁 저 SQL Server 2000 성능 튜닝 - 번역서, Edward Whalen 외 4명 공저 RDBMS 개론 및 SQL 실전정석 - 온라인 강의, 조광원 중급 : INSIDE MICROSOFT SQL SERVER 2000 - 번역서, Ron 대용량 데이타베이스 1, 2 - 이화식, 조광원 데이타베이스 설계와 구축 1,2 - 이춘식 대용량 데이타베이스(온라인 강의) - 조광원 실전데이터 모델링(온라인 강의) - 이화식 Expert One to One, 번역서, 톰 SQL Performance Tunning, 원서, Peter Gulutzan 46 /57 DB를 잘한다는 것 도움이 될 사이트 국내 http://www.en-core.com http://www.koug.net http://otn.oracle.co.kr http://www.oracle.co.kr:8880/bulletin/list.jsp 해외 http://asktom.oracle.com/ http://www.orafaq.org/faqscrpt.htm http://storacle.princeton.edu:9001/oracle8-doc/server.805/a58225/index.htm http://otn.oracle.co.kr/docs/Oracle816/index.htm MSSQL Books Online Help 47 /57 #첨부1 - Parameter 성능과 관련한 오라클 파라미터 설명 # Parameters DB_BLOCK_BUFFERS SHARED_POOL_SIZE SHARED_POOL_RESERVED_SIZE SORT_AREA_SIZE SORT_AREA_RETAINED_SIZE LOG_BUFFER DB_FILE_MULTIBLOCK_READ_COUNT HASH_MULTIBLOCK_IO_COUNT SORT_MULTIBLOCK_READ_COUNT #메모리 설정 데이타 베이스(1G) (block size * block size) + sga + redo buffer + (sort + hash/3) * 동시 접속자 수 614M + 512M + 10M + (2M + 4M / 3) * 10 = ? #Health Check 48 /57 #첨부2 - Table Option 성능과 관련한 Table Option CREATE TABLE (……..) TABLESPACE TableSpace_Name INITRANS 2 STORAGE ( INITIAL 1M NEXT 1M MINEXTENTS 10 MAXEXTENTS 1024 PCTFREE 10 PCTUSED 40 PCTINCREASE 0 FREELISTS 2 ) LOB(b) STORE AS demolob_seg ( TABLESPACE lob_tb STORAGE (INITIAL 6M NEXT 6M) CHUNK 4 PCTVERSION 20 NOCACHE NOLOGGING ENABLE STORAGE IN ROW INDEX demolob_idx ( TABLESPACE lob_tb STORAGE ( INITIAL 256k NEXT 256k ) ) ); 49 /57 #첨부2 - Tablesize TABLE SIZE 계산 공식 (Block Size 2K로 가정) SQL> SELECT GREATEST(4, ceil(ROW_COUNT / ( (round(((1958 - (initrans * 23) ) * ( (100 - PCTFREE) /100) ) / ADJ_ROW_SIZE) )) ) * BLOCK_SIZE) TableSize_Kbytes FROM dual; *. *. *. *. *. *. 한 개의 BLOCK에 Available 한 Bytes - 1958 각 initrans 는 23 Bytes PCT_FREE : Table 의 pctfree 값(default 10) ADJ_ROW_SIZE : 각 row 의 평균 SIZE 추정치 ROW_COUNT : table 의 row 의 갯수 BLOCK_SIZE : 1 block의 크기 (단위: K) 예) table 이름이 EMP 일 경우 ROW_COUNT : select count(*) from emp; ADJ_ROW_SIZE : analyze table emp compute statistics; (또는 건수가 매우 많을 때에는 compute 대신 estimate 사용) select avg_row_len from user_tables where table_name='EMP'; 50 /57 #첨부2 - Indexsize INDEX SIZE 계산 공식 SQL> SELECT GREATEST(4, (1.01) * ((ROW_COUNT / ((floor(((2048 - 113 - (initrans * 23)) * (1 - (PCTFREE/100))) / ((10 + uniqueness) + number_col_index + (total_col_length)))))) * DB_BLOCK_SIZE)) IndexSize_Kbytes FROM dual; *. *. *. *. *. *. *. *. 한 개의 block에 available 한 bytes ( 1935 or 2048 - 113 ) 각 initrans 는 23 Bytes ROW_COUNT : table 의 row 의 갯수 PCTFREE : Index 의 pctfree 값(default 10) number_col_index : Index 에서 column 의 수 total_col_length : Index 의 길이 추정치 uniqueness : 만일 unique index 이면 1, non-unique index 이면 0. DB_BLOCK_SIZE : 1 block의 크기 (단위: K) 51 /57 #첨부3 비슷하지만 다른 쿼리 select * from emp, dept where emp.deptno = dept.deptno and emp.deptno = 10 select * from emp, dept where emp.deptno = dept.deptno and dept.deptno = 10 52 /57 #첨부3 비슷하지만 다른 쿼리 product : 1,000 건 index : pid order : 1,000,000 건 index : pid + saleDate Select product.saleDate, count(*) From product, order Where order.saleDate like '2003%' and product.pid = order.pid group by saleDate Select order.pid, count(*) From product, order Where order.saleDate like '2003%' and product.pid = order.pid group by order.pid Select order.pid, cnt From product, ( select pid, count(*) cnt from order where saleDate like '2003%' group by pid) ordersum Where product.pid = ordersum.pid 53 /57 #첨부3 비슷하지만 다른 쿼리 product : 1,000 건 index : pid order : 1,000,000 건 index : pid + saleDate select product.pid, ordersum.qtysum from product, (select pid, sum(qty) qtysum from order group by pid) ordersum where product.pid = ordersum.pid select product.pid, ordersum.recentSaleDate from product, (select pid, max(saleDate) recentSaleDate from order group by pid) ordersum where product.pid = ordersum.pid select product.pid, (select /*+ index_desc(order order_pidsaledate_idx) */ saleDate from order where p.pid = order.pid and rownum = 1) recentSaleDate from product p 54 /57 @퀴즈1 퀴즈 1 판매테이블에는 구입자, 판매일, 수량 이렇게 세가지 정보가 저장되어 있는데. 원하는 정보는 연속 3일 이상 구매한 사람 가운데서 그 연속된 일자에 판매 수량이 5개 이상인 사람을 구하고 싶습니다. (구입자는 같은날 여러번 구매할 수 있읍니다. ) 연속된 일자란 11, 12, 13일 같은 날짜를 말합니다. (단 해당 달을 기준으로 하며 이전달부터 연속된 날짜는 인정하지 않는걸로 하겠습니다. ) create table 판매(userId varchar2(10), saleDay varchar2(8), qty int) Result…. 구입자 bleujin bleujin novision 일자 20030409 ~ 20030411[3일] 20030425 ~ 20030427[3일] 20030411 ~ 20030414[4일] 55 /18 수량 12 5 7 @퀴즈2 퀴즈 2 사용자테이블(아이디, 이름, 생년월일, 가입일, 등등등.…) 인덱스 : 사용자_idx - 아이디, 사용자이름_idx - 이름 게시판 테이블(게시판 번호, 등록아이디, 제목, 날짜, 내용, 기타 등등... ) 인덱스 : 게시판번호_idx - 게시판번호등록, 아이디_idx – 등록아이디 게시판의 검색 GUI 화면은 이렇습니다. 사용자 아이디 : ................. 사용자 이름 : ................. 제목 : .................... 내용 : ....................... 즉 사용자는 4가지 조건으로 찾을 수 있는데 한개만 적을수도 있도 4개 모두 적을 수도 있습니다. (and 조건) 또한 만약 사용자가 아이디칸에 bleu 라고 적는 것은 bleu로 시작하는 아이디가 적은 모든 글을 말합니다. (equal이 아니라 like 조건임..) 이름도 마찬가지 입니다. One SQL 이어야 하며 물론 동적 SQL 아니어야 함... 56 /57 CURSOR Static cursor Server(Database) Client(App Server or Middle ware) 결과 셋 3.request Fetch 4. send Data 1.exec Query 57 /57 2.load Data CURSOR Keyset cursor Server(Database) Client(App Server or Middle ware) 3.request Fetch 결과 셋 2.load Keys 5.send Data 1.exec Query 58 /57 4.lookup Data CURSOR Forward Only cursor Server(Database) Client(App Server or Middle ware) 1.exec Query 2.send Data 3.comfirm Packet 59 /57 CURSOR Dynamic cursor Server(Database) Client(App Server or Middle ware) 1.exec Query 2.send Data 3.comfirm Packet 60 /57 CURSOR DB간의 차이 Oracle MSSQL Load snapshot(동기적) Load block(비동기적) 61 /57