IT CookBook, SQL Server 로배우는데이터베이스개론과실습 [ 강의교안이용안내 ] 본강의교안의저작권은한빛아카데미 에있습니다. 이자료를무단으로전제하거나배포할경우저작권법 136 조에의거하여최고 5 년이하의징역또는 5 천만원이하의벌금에처할수있고이를병과 ( 倂科 ) 할수도있습니다.
Chapter4. SQL 고급 SQL Server 로배우는데이터베이스개론과실습
1. 내장함수 2. 부속질의 3. 뷰 4. 인덱스
내장함수의의미를알아보고자주사용되는내장함수몇가지를직접실습해본다. 부속질의의의미와종류를알아보고직접실습해본다. 뷰의의미를알아보고, 뷰를직접생성, 수정, 삭제해본다. 데이터베이스의저장구조와인덱스의관계를알아보고, 인덱스를직접생성, 수정, 삭제해본다.
01. 내장함수 SQL 내장함수 NULL 값처리 TOP n 질의
01. 내장함수 SQL 에서는함수의개념을사용하는데수학의함수와마찬가지로특정값이나열의값을입력 받아그값을계산하여결과값을돌려준다. 그림 4-1 함수의원리 SQL 의함수는 DBMS 가제공하는내장함수 (built-in function) 와사용자가필요에따라직접 만드는사용자정의함수 (user-defined funtion) 로나뉜다.
1.1 SQL 내장함수 SQL 내장함수는상수나속성이름을입력값으로받아단일값을결과로반환한다. 모든내장 함수는최초에선언될때유효한입력값을받아야한다. 표 4-1 SQL Server 의내장함수 분류설명종류 환경설정함수 (Configuration Functions) 변환함수 (Conversion Functions) 커서함수 (Cursor Functions) 날짜와시간함수 (Date and Time Functions) 수학함수 (Mathematical Functions) 메타데이터함수 (Metadata Functions) 보안함수 (Security Functions) 문자열함수 (String Functions) 시스템함수 (System Functions) 시스템통계함수 (System Statistical Functions) 텍스트와이미지함수 (Text and Image Functions) DBMS 환경설정관련함수데이터타입변환함수커서관련함수날짜와시간관련함수수학함수데이터베이스와데이터베이스개체관련함수사용자와롤 (role) 에관한함수문자열관련함수 SQL 서버정보관련함수시스템통계관련함수문자열과이미지입력값관련함수 DATEFIRST, VERSION, SERVERNAME 등 CAST, CONVERT 등 CURSOR_ROWS, CURSOR_STATUS 등 SYSDATETIME, GETDATE, DAYADD 등 SIN, COS, ABS, ROUND, CEIL 등 OBJECT_NAME, FILE_ID, SCHEMA_NAME 등 CURRENT_USER, SUSER_ID, PERMISSIONS 등 CONCAT, SUBSTRING, LEN 등 HOST_NAME, ERROR_NUMBER 등 CONNECTIONS, CPU_BUSY 등 TEXTVALIS, TEXTPTR 등
1.1.1 수학함수 표 4-2 수학함수의종류 함수 설명 예 ABS( 숫자 ) 절대값계산 ABS(-4.5)=4.5 CEILING( 숫자 ) 숫자보다크거나같은최소의정수 CEILING(4.1)=5 FLOOR( 숫자 ) 숫자보다작거나같은최소의정수 FLOOR(4.1)=4 ROUND( 숫자, m) 숫자의반올림, m은반올림기준자릿수 ROUND(5.36, 1)=5.40 LOG( 숫자 ) 숫자의자연로그값을반환 LOG(10)=2.30259 POWER( 숫자, n) 숫자 n승값을계산 POWER(2, 3)=8 SQRT( 숫자 ) 숫자의제곱근값을계산 ( 숫자는양수 ) SQRT(9.0)=3.0 SIGN( 숫자 ) 숫자가음수면 -1, 0이면 0, 양수면 1 SIGN(3.45)=1
1.1.1 수학함수 ABS 함수 : 절댓값을구하는함수 질의 4-1 -78 과 +78 의절댓값을구하시오. SELECT ABS(-78), ABS(+78); ROUND 함수 : 반올림한값을구하는함수 질의 4-2 4.875 를소수첫째자리까지반올림한값을구하시오. SELECT ROUND(4.875, 1); 수학함수의연산질의 4-3 고객별평균주문금액을백원단위로반올림한값을구하시오. SELECT custid " 고객번호 ", ROUND(SUM(saleprice)/COUNT(*), -2) " 평균금액 " FROM Orders GROUP BY custid;
1.1.2 문자함수 표 4-3 문자함수의종류 문자함수 반환형 설명 사용예 CHAR(n) CHAR 정수아스키코드를문자로반환 CHAR(68)=D NCHAR(n) CHAR n 값의유니코드에대응하는문자를반환 NCHAR(68)=D CHARINDEX(str1, str2) INTEGER str2에서부분문자열 str1의시작위치를반환 CHARINDEX( System, Database System )=10 LEFT(str, n) VARCHAR str의왼쪽에서부터 n개문자열을반환 LEFT( abcdefg, 2)= ab RIGHT(str, n) VARCHAR str의오른쪽에서부터 n개문자열을반환 RIGHT( abcd, 2)= cd LEN(str) INTEGER str의문자열길이를반환 LEN( abcdefg )=7 LOWER(str) VARCHAR str을소문자로변환하여반환 LOWER( AbcD )= abcd UPPER(str) VARCHAR str을대문자로변환하여반환 UPPER( AbcD )= ABCD LTRIM(str) VARCHAR str의왼쪽공백을제거 LTRIM( two space here )= two space here RTRIM(str) VARCHAR str의오른쪽공백을제거 RTRIM( two space here )= two space here PATINDEX( %str1%, str2) INTEGER str2에서 str1 문자열의시작위치를반환 PATINDEX( %en_ure%, I ensure it )=3 REPLACE(str1, str2, str3) VARCHAR str1에서 str2를 str3로변환하여반환 REPLACE( abcdefg, cd, dc )= abdcefg REPLICATE(str, n) VARCHAR str을 n만큼반복 REPLICATE( a, 4)= aaaa REVERSE(str) VARCHAR str을역순으로출력 REVERSE( abcd )== dcba SPACE(n) VARCHAR n만큼의공백문자를반환 야구 +SPACE(5)+ 농구 = 야구농구 SUBSTRING(str, n, m) VARCHAR str에서 n번째부터 m개문자를반환 SUBSTRING( abcdefg, 3, 2)= cd ASCII(str) INTEGER str의제일왼쪽문자의아스키코드값을반환 ASCII( Data )=68 UNICODE(str) INTEGER str의제일왼쪽문자의유니코드값을반환 UNICODE( Data )=68
1.1.2 문자함수 REPLACE 함수 : 문자열을치환하는함수 질의 4-4 도서제목에야구가포함된도서를농구로변경한후도서목록을보이시오. SELECT FROM bookid, REPLACE(bookname, ' 야구 ', ' 농구 ') bookname, publisher, price Book;
1.1.2 문자함수 LEN 함수 : 글자의수를세어주는함수 ( 단위가바이트 (byte) 가아닌문자단위 ) 질의 4-5 굿스포츠에서출판한도서의제목과제목의글자수를확인하시오. SELECT bookname " 제목 ", LEN(bookname) " 길이 " FROM Book WHERE publisher=' 굿스포츠 '; SUBSTRING 함수 : 지정한길이만큼의문자열을반환하는함수 질의 4-6 마당서점의고객중에서같은성 ( 姓 ) 을가진사람이몇명이나되는지성별인원수를 구하시오. SELECT SUBSTRING(name, 1, 1) " 성 ", COUNT(*) " 인원 " FROM Customer GROUP BY SUBSTRING(name, 1, 1);
1.1.3 날짜함수 표 4-4 날짜함수의종류 날짜함수반환형설명사용예 SYSDATETIME() DATETIME2 SQL Server 가동작하는컴퓨터의날짜및시간을출력 GETDATE() DATETIME SQL Server 가동작하는컴퓨터의날짜및시간을출력 SYSDATETIME()=2013-03-01 16:39:47.8281250 GETDATE()=2013-03-01 16:42:43.257 DATENAME (datepart, date) VARCHAR date 값중 datepart 에표시된값을문자열로반환 DATENAME(YEAR, 2013-03-01 )= 2013 DATEPART (datepart, date) INTEGER date 값중 datepart 에표시된값을숫자로반환 DATEPART(YEAR, 2013-03-01 )=2013 DAY(date) INTEGER date 값중일을반환 DAY( 2013-03-01 )=1 MONTH(date) INTEGER date 값중월을반환 MONTH( 2013-03-01 )=3 YEAR(date) INTEGER date 값중년을반환 YEAR( 2013-03-01 )=2013 DATEDIFF(datepart, startdate, enddate) INTEGER datepar t 에지정된부분에대하여 startdate 와 enddate 를비교하여차이값을반환 DATEDIFF(MONTH, 2013-03-01, 2013-09-01 )=6 DATEADD(datepart, number, date) DATETIME datepart 에지정된부분에대하여 date 값에서 number 만큼더해서반환 DATEADD(DAY, 5, 2013-03-01 ) = 2013-03-06 00:00:00.000 ISDATE(expression) INTEGER 정상적인날짜값인지판단하여거짓이면 0, 정상이면 1 을반환 ISDATE( 2013-02-30 )=0
1.1.3 날짜함수 표 4-5 날짜함수에서 datepart 인자 depart 약어 datepart 약어 year yy.yyyy hour hh quarter qq.q minute mi.n month mm.m second ss.s dayofyear dy.y millisecond ms day dd.d microsecond mcs week wk.ww nanosecond ns weekday dw TZoffset tz
1.1.3 날짜함수 DATEADD 함수 : 입력된날짜에원하는날짜만큼더한날짜를반환하는함수 질의 4-7 마당서점은주문일로부터 10 일후매출을확정한다. 각주문의확정일자를구하시오. SELECT orderid " 주문번호 ", orderdate " 주문일 ", DATEADD(dd, 10, orderdate) " 확정 " FROM Orders;
1.1.3 날짜함수 SYSDATETIME 함수 : SQL Server 의현재시간을반환하는함수 질의 4-8 DBMS 서버에설정된현재시간과오늘날짜를확인하시오. SELECT SYSDATETIME(); /* 현재시간을알아본다. */ SELECT DAY(SYSDATETIME()) /* 오늘은몇일인가? */
1.2 NULL 값처리 NULL 값이란? 아직지정되지않은값이다. NULL 값은 0, ( 빈문자 ), ( 공백 ) 등과다른특별한값이다. NULL 값은비교연산자로비교가불가능하다. NULL 값의연산을수행하면결과역시 NULL 값으로반환된다. 집계함수를사용할때주의할점 NULL+ 숫자 연산의결과는 NULL이다. 집계함수계산시 NULL이포함된행은집계에서빠진다. 해당되는행이하나도없을경우 SUM, AVG 함수의결과는 NULL이되며, COUNT 함수의결과는 0이다.
1.2 NULL 값처리 NULL 값에대한연산과집계함수 Mybook bookid price 1 10000 2 20000 3 NULL SELECT FROM WHERE price+100 Mybook bookid=3; SELECT FROM SUM(price), AVG(price), COUNT(*), COUNT(price) Mybook; SELECT SUM(price), AVG(price), COUNT(*) FROM Mybook WHERE bookid >= 4;
1.2 NULL 값처리 NULL 값을확인하는방법 IS NULL, IS NOT NULL NULL 값을찾을때는 = 연산자가아닌 IS NULL 을사용하고, NULL 이아닌값을찾을때는 <> 연산자가아닌 IS NOT NULL 을사용한다. Mybook bookid price 1 10000 2 20000 3 NULL SELECT * FROM Mybook WHERE price IS NULL; SELECT * FROM Mybook WHERE price = '';
1.2 NULL 값처리 ISNULL() 함수 : NULL 값을다른값으로대치하여연산하거나다른값으로출력 ISNULL( 속성, 값 ) /* 속성값이 NULL 이면 ' 값 ' 으로대치한다 */ Customer 테이블의 2번고객인김연아의전화번호를 NULL 값으로변경해보자. UPDATE Customer SET phone=null WHERE custid=2; 질의 4-9 이름, 전화번호가포함된고객목록을보이시오. 단, 전화번호가없는고객은 연락처없음 으로표시한다. SELECT name " 이름 ", ISNULL(phone, ' 연락처없음 ') " 전화번호 " FROM Customer;
1.3 TOP n 질의 TOP n 질의는내장함수는아니지만자주사용되는문법이다. TOP n 은 SQL 실행 결과에서상위 n 개의행만반환한다. 자료를일부분씩확인하거나가공할때유용 하게활용할수있다. 질의 4-10 가나다순으로정리된고객목록에서고객번호, 이름, 전화번호를앞의두명만보이시오. SELECT TOP 2 custid, name, phone FROM Customer ORDER BY name;
02. 부속질의 스칼라부속질의 SELECT 부속질의 인라인뷰 FROM 부속질의 중첩질의 WHERE 부속질의
02 부속질의 부속질의 (subquery) 란? 하나의 SQL 문안에다른 SQL 문이중첩된nested 질의를말한다. 다른테이블에서가져온데이터로현재테이블에있는정보를찾거나가공할때사용한다. 일반적으로데이터가대량일경우데이터를모두합쳐서연산하는조인보다필요한데이터만찾아서공급해주는부속질의가성능이더좋다. 주질의 (main query) 와부속질의 (sub query) 로구성된다. 주질의는외부질의라고도하며부속질의는내부질의라고도한다. 주질의 (main query) 부속질의 (sub query) SELECT SUM(saleprice) FROM Orders WHERE custid = (SELECT custid FROM Customer WHERE name = ' 박지성 ') 그림 4-2 부속질의
02 부속질의 표 4-6 부속질의의종류 명칭위치영문및동의어설명 스칼라부속질의 SELECT 절 scalar subquery SELECT 절에서사용되며단일값을반환 하기때문에스칼라부속질의라고한다. 인라인뷰 FROM 절 inline view, table subquery FROM 절에서결과를뷰 (view) 형태로반 환하기때문에인라인뷰라고한다. 중첩질의 WHERE 절 nested subquery, predicate subquery WHERE 절에술어와같이사용되며결과 를한정시키기위해사용된다. 상관혹은 비상관형태다.
2.1 스칼라부속질의 SELECT 부속질의 스칼라부속질의 (scalar subquery) 란? SELECT 절에서사용되는부속질의로, 부속질의의결과값을단일행, 단일열의스칼라값으로반환한다. 스칼라부속질의는원칙적으로스칼라값이들어갈수있는모든곳에사용가능하며, 일반적으로 SELECT 문과 UPDATE SET 절에사용된다. 주질의와부속질의와의관계는상관 / 비상관모두가능하다. 주질의 스칼라부속질의 SELECT custid, FROM GROUP BY (SELECT name FROM Customer cs WHERE cs.custid=od.custid) Orders od custid,sum(saleprice) 그림 4-3 스칼라부속질의
2.1 스칼라부속질의 SELECT 부속질의 질의 4-11 마당서점의고객별판매액을보이시오. 단, 결과는고객이름과고객별판매액을출 력한다. SELECT FROM GROUP BY ( SELECT name FROM Customer cs WHERE cs.custid=od.custid ) "name", SUM(saleprice) "total" Orders od od.custid;
2.1 스칼라부속질의 SELECT 부속질의 SELECT custid, SUM(saleprice) totamt FROM Orders od GROUP BY custid ; SELECT FROM WHERE name Customer cs cs.custid = od.custid Custid 1 의이름은? SELECT (SELECT name name FROM FROM Customer cs cs WHERE WHERE cs.custid = od.custid) name, SUM(saleprice) totamt FROM Orders od GROUP BY od.custid ; 그림 4-4 마당서점의고객별판매액
2.1 스칼라부속질의 SELECT 부속질의 질의 4-12 Orders 테이블에각주문에맞는도서이름을입력하시오. UPDATE SET Orders bookname = ( SELECT bookname FROM Book WHERE Book.bookid=Orders.bookid );
2.2 인라인뷰 - FROM 부속질의 인라인뷰 (inline view) 란? FROM 절에서사용되는부속질의다. 테이블이름대신인라인뷰부속질의를사용하면보통의테이블과같은형태로사용할수있다. 부속질의결과반환되는데이터는다중행, 다중열이어도상관없다. 다만가상의테이블인뷰형태로제공되기때문에상관부속질의로사용될수는없다. 질의 4-13 고객번호가 2 이하인고객의판매액을보이시오. 단, 결과는고객이름과고객별판 매액을출력한다. SELECT cs.name, SUM(od.saleprice) "total" FROM (SELECT custid, name FROM Customer WHERE custid <= 2) cs, Orders od WHERE cs.custid=od.custid GROUP BY cs.name;
2.2 인라인뷰 - FROM 부속질의 주질의 SELECT cs.name, SUM(od.saleprice) FROM (SELECT custid, name FROM Customer WHERE custid <= 2) Orders od WHERE cs.custid = od.custid GROUP BY cs.name ; cs, 인라인뷰 그림 4-5 인라인뷰
2.3 중첩질의 WHERE 부속질의 중첩질의 (nested subquery) 는 WHERE 절에서사용되는부속질의다. WHERE 절은 보통데이터를선택하는조건혹은술어 (predicate) 와같이사용된다. 그래서중첩 질의를술어부속질의 (predicate subquery) 라고도부른다. 표 4-7 중첩질의연산자의종류 술어연산자반환행반환열상관 비교 =, >, <, >=, <=, <> 단일단일가능 집합 IN, NOT IN 다중단일가능 한정 (quantified) ALL, SOME(ANY) 다중단일가능 존재 EXISTS, NOT EXISTS 다중다중필수
2.3 중첩질의 WHERE 부속질의 비교연산자 부속질의가반드시단일행, 단일열을반환해야하며, 아닐경우질의를처리할수없다. 질의 4-14 평균주문금액이하의주문에대해서주문번호와금액을보이시오. SELECT FROM WHERE orderid, saleprice Orders saleprice <= (SELECT AVG(saleprice) FROM Orders); 질의 4-15 각고객의평균주문금액보다큰금액의주문내역에대해서주문번호, 고객번호, 금액을보이시오. SELECT FROM WHERE orderid, custid, saleprice Orders md saleprice > (SELECT AVG(saleprice) FROM Orders so WHERE md.custid=so.custid);
2.3 중첩질의 WHERE 부속질의 IN, NOT IN IN 연산자는주질의의속성값이부속질의에서제공한결과집합에있는지확인check하는역할을한다. IN 연산자는부속질의의결과다중행을가질수있다. 주질의는 WHERE 절에사용되는속성값을부속질의의결과집합과비교해하나라도있으면참이된다. NOT IN은이와반대로값이존재하지않으면참이된다. 질의 4-16 대한민국에거주하는고객에게판매한도서의총판매액을구하시오. SELECT FROM WHERE SUM(saleprice) "total" Orders custid IN (SELECT custid FROM Customer WHERE address LIKE '% 대한민국 %');
2.3 중첩질의 WHERE 부속질의 ALL, SOME(ANY) ALL은모두, SOME(ANY) 운어떠한 ( 최소한하나라도 ) 이라는의미를가진다. 구문구조 scalar_expression {= < >!= > >=!> < <=!<} {ALL SOME ANY} ( 부속질의 ) 질의 4-17 3 번고객이주문한도서의최고금액보다더비싼도서를구입한주문의주문번 호와금액을보이시오. SELECT FROM WHERE orderid, saleprice Orders saleprice > ALL (SELECT saleprice FROM Orders WHERE custid='3');
2.3 중첩질의 WHERE 부속질의 EXIST, NOT EXIST 데이터의존재유무를확인하는연산자다. 주질의에서부속질의로제공된속성의값을가지고부속질의에조건을만족하여값이존재하면참이되고, 주질의는해당행의데이터를출력한다. NOT EXIST 의경우이와반대로동작한다. 구문구조 WHERE [NOT] EXISTS ( 부속질의 ) 질의 4-18 EXISTS 연산자를사용하여대한민국에거주하는고객에게판매한도서의총판 매액을구하시오. SELECT SUM(saleprice) "total" FROM Orders od WHERE EXISTS (SELECT * FROM Customer cs WHERE address LIKE '% 대한민국 %' AND cs.custid=od.custid);
03. 뷰 뷰의생성 뷰의수정 뷰의삭제
03 뷰 뷰 (view) 는하나이상의테이블을합하여만든가상의테이블이다. 장점 편리성 : 미리정의된뷰를일반테이블처럼사용할수있기때문에편리하다. 또사용자가필요한정보만요구에맞게가공하여뷰로만들어쓸수있다. 재사용성 : 자주사용되는질의를뷰로미리정의해놓을수있다. 보안성 : 각사용자별로필요한데이터만선별하여보여줄수있다. 중요한질의의경우질의내용을암호화할수있다.
03 뷰 뷰 Vorders 뷰생성문 CREATE VIEW Vorders AS AS SELECT orderid, O.custid, name, O.bookid, B.bookname, saleprice, orderdate FROM Customer C, Orders O, Book B WHERE C.custid=O.custid and B.bookid=O.bookid 베이스릴레이션 Orders, Book, Customer O C B 그림 4-6 뷰
3.1 뷰의생성 기본문법 CREATE VIEW 뷰이름 [( 열이름 [,...n ])] AS SELECT 문 Book 테이블에서 축구 라는문구가포함된자료만보여주는뷰를만들어보자. SELECT * FROM Book WHERE bookname LIKE '% 축구 %'; 위 SELECT 문을이용해뷰정의문을작성하면다음과같다. CREATE VIEW vw_book AS SELECT * FROM Book WHERE bookname LIKE '% 축구 %';
3.1 뷰의생성 질의 4-19 주소에 대한민국 을포함하는고객들로구성된뷰를만들고조회하시오. 단, 뷰의이름은 vw_customer 로한다. CREATE VIEW vw_customer AS SELECT * FROM Customer WHERE address LIKE '% 대한민국 %'; < 결과확인 > SELECT * FROM vw_customer;
3.1 뷰의생성 질의 4-20 Orders 테이블에고객이름과도서이름을바로확인할수있는뷰를생성한후, 김연아 고객이구입한도서의주문번호, 도서이름, 주문액을보이시오. CREATE VIEW vw_orders (orderid, custid, name, bookid, bookname, saleprice, orderdate) AS SELECT od.orderid, od.custid, cs.name, od.bookid, bk.bookname, od.saleprice, od.orderdate FROM Orders od, Customer cs, Book bk WHERE od.custid =cs.custid AND od.bookid =bk.bookid; < 결과확인 > SELECT FROM WHERE orderid, bookname, saleprice vw_orders name=' 김연아 ';
3.2 뷰의수정 기본문법 ALTER VIEW 뷰이름 [( 열이름 [,...n ])] AS SELECT 문 질의 4-21 [ 질의 4-19] 에서생성한뷰 vw_customer 는주소가대한민국인고객을보여준다. 이뷰를영국을주소로가진고객으로변경하시오. phone 속성은필요없으므로포함시키지 마시오. ALTER VIEW vw_customer (custid, name, address) AS SELECT custid, name, address FROM Customer WHERE address LIKE '% 영국 %'; < 결과확인 > SELECT * FROM vw_customer;
3.3 뷰의삭제 기본문법 DROP VIEW 뷰이름 [,...n ]; 질의 4-21 앞서생성한뷰 vw_customer 를삭제하시오. DROP VIEW vw_customer; < 결과확인 > SELECT * FROM vw_customer;
04. 인덱스 데이터베이스의물리적저장 인덱스와 B-tree 인덱스의종류 인덱스의생성 인덱스의재구성과삭제
4.1 데이터베이스의물리적저장 중앙처리장치 OS TOOL CPU 사용자 UI SQL Server Management Studio 주기억장치 DBMS RAM SQL Server 보조기억장치 HDD, SSD 커널 파일시스템 데이터파일 주데이터파일 MDF 보조데이터파일 NDF 로그파일 LDF 그림 4-7 DBMS 와데이터파일
4.1 데이터베이스의물리적저장 실제데이터가저장되는곳은보조기억장치이다. 하드디스크, SSD, USB 메모리등 가장많이사용되는장치는하드디스크이다. 하드디스크는원형의플레이트 (plate) 로구성되어있고, 이플레이트는논리적으로트랙으로나뉘며트랙은다시몇개의섹터로나뉜다. 원형의플레이트는초당빠른속도로회전하고, 회전하는플레이트를하드디스크의액세스암 (arm) 과헤더 (header) 가접근하여원하는섹터에서데이터를가져온다. 하드디스크에저장된데이터를읽어오는데걸리는시간은모터 (motor) 에의해서분당회전하는속도 (RPM, Revolutions Per Minute), 데이터를읽을때액세스암이이동하는시간 (latency time), 주기억장치로읽어오는시간 (transfer time) 에영향을받는다. 액세스시간 (access time) 액세스시간 = 탐색시간 (seek time, 액세스헤드를트랙에이동시키는시간 ) + 회전지연시간 (rotational latency time, 섹터가액세스헤드에접근하는시간 ) + 데이터전송시간 (data transfer time, 데이터를주기억장치로읽어오는시간 )
4.1 데이터베이스의물리적저장 섹터 스핀들모터 트랙 액세스헤드 액세스암 그림 4-8 하드디스크의구조 DBMS가하드디스크에데이터를저장하고읽어올때컴퓨터시스템에서처리되는연산속도는빠른데, 디스크의액세스속도는느리기때문에문제가발생한다. 이러한속도문제를줄이기위해주기억장치에 DBMS가사용하는공간 (memory pool) 중일부를 DB 버퍼캐시 (buffer cache) 에자주사용하는데이터를저장하여작업한다.
4.1 데이터베이스의물리적저장 Memory Pool 버퍼캐시 Procedure Cache 로그캐시 Connection Context Worker Thread Lazy Writer Check Point Log Writer 데이터파일 (Data Files) (MDF,NDF) 로그 (log) 파일 (LDF) 그림 4-9 SQL Server 의내부구조
4.1 데이터베이스의물리적저장 표 4-8 SQL Server 의주요파일 파일 설명 데이터파일 주데이터파일 (*.mdf) 데이터베이스의시작정보를포함하며데이터베이스의나머지파일을가리킨다. 사용자데이터와개체를저장한다. 모든데이터베이스에는하나의주데이터파일이있으며, 파일확장명은.mdf 이다. 보조데이터파일 (*.ndf) 선택적으로사용하는사용자정의데이터파일이며사용자데이터를저장한다. 권장되는파일확장명은.ndf 이다. 트랜잭션로그파일 (*.jdf) 데이터베이스복구에사용되는로그정보를저장한다. 데이터베이스마다최소한하나의로그파일이있어야한다. 권장되는파일확장명은.ldf 이다.
4.2 인덱스와 B-tree 인덱스 (index, 색인 ) 란도서의색인이나사전과같이데이터를쉽고빠르게찾을수 있도록만든데이터구조이다. 루트노드 내부노드 B-tree 인덱스 리프노드 데이터테이블 그림 4-10 B-tree 의구조
4.2 인덱스와 B-tree 4 2 6 1 3 5 7 그림 4-11 B-tree 에서검색예 인덱스의특징 인덱스는테이블에서한개이상의속성을이용하여생성한다. 빠른검색과함께효율적인레코드접근이가능하다. 순서대로정렬된속성과데이터의위치만보유하므로테이블보다작은공간을차지한다. 저장된값들은테이블의부분집합이된다. 일반적으로 B-tree 형태의구조를가진다. 데이터의수정, 삭제등의변경이발생하면인덱스의재구성이필요하다.
4.3.1 클러스터인덱스 클러스터인덱스는연속된키값의레코드를묶어서같은블록에저장하는방법이다. 테이블당하나만생성할수있고, 테이블의데이터가키값에따라정렬되어있기때문에손쉽게검색할수있다. 리프노드 Page 1 1 축구의역사 굿스포츠 7000 2 2030 축구아는여자 나무수 13000 3 축구의이해 대한미디어 22000 루트노드 key page 1 1 4 2 7 3 10 4 검색 Page 2 4 골프바이블 대한미디어 35000 5 피겨교본 굿스포츠 8000 6 역도단계별기술 굿스포츠 6000 Page 3 7 야구의추억 이상미디어 20000 8 야구를부탁해 이상미디어 13000 9 올림픽이야기 삼성당 7500 Page 4 10 Olympic Champions Pearson 13000 empty empty 그림 4-12 클러스터인덱스
4.3.2 비클러스터인덱스 비클러스터인덱스는테이블과별도로구성되어, 테이블당여러개를생성할수있다. 그러나 테이블과인덱스가별도의페이지에저장되므로클러스터인덱스에비해많은저장공간이요구 된다. INDEX page 1 TABLE 10 Olympic Champions Pearson 13000 루트노드 key node 1 1 6 2 그림 4-13 비클러스터인덱스 리프노드 index page 1 1 1-3 2 3-3 3 3-2 4 2-1 5 4-1 index page 2 6 2-3 7 1-2 8 3-1 9 4-2 10 1-1 7 야구의추억 이상미디어 20000 1 축구의역사 굿스포츠 7000 page2 4 골프바이블 대한미디어 35000 empty 6 역도단계별기술 굿스포츠 6000 page 3 8 야구를부탁해 이상미디어 13000 3 축구의이해 대한미디어 22000 2 2030 축구아는여자 나무수 13000 page 4 5 피겨교본 굿스포츠 8000 9 올림픽이야기 삼성당 7500 empty
4.4 인덱스의생성 인덱스생성시고려사항 인덱스는 WHERE 절에자주사용되는속성이어야한다. 인덱스는조인에자주사용되는속성이어야한다. 단일테이블에인덱스가많으면속도가느려질수있다 ( 테이블당 4~5개정도권장 ). 속성이가공되는경우사용하지않는다. 속성의선택도가낮을때유리하다 ( 속성의모든값이다른경우 ). 인덱스의생성문법 CREATE [UNIQUE] [CLUSTERED NONCLUSTERED] INDEX 인덱스이름 ON 테이블이름 ( 속성이름 [ASC DESC] [,...n ])
4.4 인덱스의생성 질의 4-23 Book 테이블의 bookname 열을대상으로비클러스터인덱스 ix_book 을생성하시 오. CREATE INDEX ix_book ON Book (bookname); 질의 4-24 Customer 테이블의 name 열을대상으로클러스터인덱스 cix_customer 를생성 하시오. CREATE CLUSTERED INDEX cix_customer ON Customer (name);
4.4 인덱스의생성 생성된인덱스는개체탐색기의해당테이블의인덱스를통해확인할수있다.
4.5 인덱스의재구성과삭제 인덱스의재구성은 ALTER INDEX 명령을사용한다. 생성문법 ALTER INDEX { 인덱스이름 ALL} ON 테이블이름 {REBUILD DISABLE REORGANIZE}; 질의 4-25 비클러스터인덱스인 ix_book 을재생성하시오. ALTER INDEX ix_book ON Book REBUILD;
4.5 인덱스의재구성과삭제 삭제문법 DROP INDEX 인덱스이름 ON 테이블이름 ; 질의 4-26 비클러스터인덱스인 ix_book 을삭제하시오. DROP INDEX ix_book ON Book;
요약 1. 내장함수 2. 부속질의 3. 뷰 4. 인덱스 5. B-tree 6. SQL Server 인덱스의종류
SQL Server 로배우는데이터베이스개론과실습