1. 내장함수 2. 부속질의 3. 뷰 4. 인덱스

Transcription

1 Chapter 04 SQL 고급... 오라클로배우는데이터베이스개론과실습

2 1. 내장함수 2. 부속질의 3. 뷰 4. 인덱스

3 내장함수의의미를알아보고자주사용되는내장함수몇가지를직접실습해본다. 부속질의의의미와종류를알아보고직접실습해본다. 뷰의의미를알아보고, 뷰를직접생성, 수정, 삭제해본다. 데이터베이스의저장구조와인덱스의관계를알아보고, 인덱스를직접생성, 수정, 삭제해본다.

4 01. 내장함수 SQL 내장함수 NULL 값처리 ROWNUM

5 01. 내장함수 SQL 에서는함수의개념을사용하는데수학의함수와마찬가지로특정값이나열의값을입력 받아그값을계산하여결과값을돌려줌 그림 4-1 함수의원리 SQL 의함수는 DBMS 가제공하는내장함수 (built-i fuctio) 와사용자가필요에따라직접 만드는사용자정의함수 (user-defied fuctio) 로나뉨

6 1. SQL 내장함수 SQL 내장함수는상수나속성이름을입력값으로받아단일값을결과로반환함 모든내장함수는최초에선언될때유효한입력값을받아야함 표 4-1 오라클에서제공하는주요내장함수 분류설명종류 단일행함수 숫자함수문자함수 ( 문자반환 ) 문자함수 ( 숫자반환 ) 날짜 / 시간함수변환함수인코딩과디코딩 NULL 관련함수 ABS, CEIL, COS, EXP, FLOOR, LN, LOG, MOD, POWER, ROUND(umber), SIGN, TRUNC(umber) CHR, CONCAT, LOWER, LPAD, LTRIM, STR, REPLACE, RPAD, RTRIM, SUBSTR, TRIM, UPPER ASCII, INSTR, LENGTH ADD_MONTHS, LAST_DAY, NEXT_DAY, ROUND(date), SYSDATE, TO_CHAR(datetime) ASCIISTR, CONVERT, TO_BINARY_DOUBLE, TO_BINARY_FLOAT, TO_CHAR(character), TO_CHAR(datetime), TO_CHAR(umber), TO_DATE, TO_NUMBER DECODE, DUMP, VSIZE COALESCE, NULLIF, NVL 집계함수 분석함수 AVG, COUNT, CUME_DIST, FIRST, LAST, MAX, MEDIAN, MIN, PERCENT_RANK, PERCENTILE_CONT, SUM AVG, CORR, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAST_VALUE, LEAD, MAX, MIN, RANK, SUM

7 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)= POWER( 숫자, ) 숫자 제곱값을계산 POWER(2, 3)=8 SQRT( 숫자 ) 숫자의제곱근값을계산 ( 숫자는양수 ) SQRT(9.0)=3.0 SIGN( 숫자 ) 숫자가음수면 -1, 0이면 0, 양수면 1 SIGN(3.45)=1

8 1.1 수학함수 ABS 함수 : 절댓값을구하는함수 질의 과 +78 의절댓값을구하시오. SELECT ABS(-78), ABS(+78); FROM Dual; ROUND 함수 : 반올림한값을구하는함수 질의 를소수첫째자리까지반올림한값을구하시오. SELECT ROUND(4.875, 1); FROM Dual; 숫자함수의연산 질의 4-3 고객별평균주문금액을백원단위로반올림한값을구하시오. SELECT custid " 고객번호 ", ROUND(SUM(saleprice)/COUNT(*), -2) " 평균금액 " FROM Orders GROUP BY custid;

9 1.2 문자함수 표 4-3 문자함수의종류 반환구부함수설명 CHR(k) 정수아스키코드를문자로반환 CHR(68) = D CONCAT(s1,s2) 두문자열을연결 CONCAT( 마당, 서점 ) = 마당서점 문자값반환함수 s : 문자열 c : 문자 : 정수 k : 정수 INITCAP(s) LOWER(s) LPAD(s,,c) LTRIM(s1,s2) REPLACE(s1,s2,s3) RPAD(s,,c) RTRIM(s1,s2) SUBSTR(s,,k) TRIM(c FROM s) UPPER(s) 문자열의첫번째알파벳을대문자로변환 INITCAP( the soap ) = The Soap 대상문자열을모두소문자로변환 LOWER( MR. SCOTT ) = mr. scott 대상문자열의왼쪽부터지정한자리수까지지정한문자로채움 ( 예 ) LPAD( Page 1, 10, * ) = ****Page 1 대상문자열의왼쪽부터지정한문자들을제거 ( 예 ) LTRIM( <= =>BROWNING<= =>, <>= ) = BROWNING<= => 대상문자열의지정한문자를원하는문자로변경 ( 예 ) REPLACE( JACK ad JUE, J, BL ) = BLACK ad BLUE 대상문자열의오른쪽부터지정한자리수까지지정한문자로채움 ( 예 ) RPAD( AbC, 5, * ) = AbC** 대상문자열의오른쪽부터지정한문자들을제거 ( 예 ) RTRIM( <= =>BROWNING<= =>, <>= ) = <= =>BROWNING 대상문자열의지정된자리에서부터지정된길이만큼잘라서반환 ( 예 ) SUBSTR( ABCDEFG, 3, 4) = CDEF 대상문자열의양쪽에서지정된문자를삭제 ( 문자열만넣으면기본값으로공백제거 ) ( 예 ) TRIM( = FROM = =>BROWNING<= = ) = >BROWNING< 대상문자열을모두대문자로변환 ( 예 ) UPPER( mr. scott ) = MR. SCOTT ASCII(c) 대상알파벳문자의아스키코드값을반환 ( 예 ) ASCII( D ) = 68 숫자값반환함수 INSTR(s1,s2,,k) 문자열중 번째문자부터시작하여찾고자하는문자열 s2 가 k 번째나타나는문자. 열위치반환, 예제에서 3 번째부터 OR 가 2 번째나타나는자리수 ( 예 ) INSTR( CORPORATE FLOOR, OR, 3, 2) = 14 LENGTH(s) 대상문자열의글자수를반환 ( 예 )LENGTH( CANDIDE ) = 7

10 1.2 문자함수 REPLACE : 문자열을치환하는함수 질의 4-4 도서제목에야구가포함된도서를농구로변경한후도서목록을보이시오. SELECT FROM bookid, REPLACE(bookame, ' 야구 ', ' 농구 ') bookame, publisher, price Book;

11 1.2 문자함수 LENGHT : 글자의수를세어주는함수 ( 단위가바이트 (byte) 가아닌문자단위 ) 질의 4-5 굿스포츠에서출판한도서의제목과제목의글자수를확인하시오. SELECT bookame " 제목 ", LENGHT(bookame) " 글자수, LENGTH(bookame) 바이트수 FROM Book WHERE publisher=' 굿스포츠 '; SUBSTR : 지정한길이만큼의문자열을반환하는함수 질의 4-6 마당서점의고객중에서같은성 ( 姓 ) 을가진사람이몇명이나되는지성별인원수를구하시오. SELECT SUBSTR(ame, 1, 1) " 성 ", COUNT(*) " 인원 " FROM Customer GROUP BY SUBSTR(ame, 1, 1);

12 1.3 날짜ㆍ시간함수 표 4-4 날짜ㆍ시간함수의종류 함수반환형설명 TO_DATE(char, datetime) DATE 문자형 (CHAR) 데이터를날짜형 (DATE) 으로반환 TO_DATE( , yyyy-mm-dd ) = TO_CHAR(date, datetime) CHAR 날짜형 (DATE) 데이터를문자열 (VARCHAR2) 로반환 TO_CHAR(TO_DATE( , yyyy-mm-dd ), yyyymmdd ) = ADD_MONTHS(date, 숫자 ) DATE date 형의날짜에서지정한달만큼더함 (1 : 다음달, -1 : 이전달 ) ADD_MONTHS(TO_DATE( , yyyy-mm-dd ), 12) = LAST_DAY(date) DATE date 형의날짜에서달의마지막날을반환 LAST_DAY(TO_DATE( , yyyy-mm-dd )) = SYSDATE DATE DBMS 시스템상의오늘날짜를반환하는인자없는함수 SYSDATE = 14/04/20

13 1.3 날짜함수 표 4-5 datetime 의주요인자 인자 설명 인자 설명 d 요일순서 (1~7, 월 =1) mi 분 (0~59) day 요일 ( 월요일 ~ 일요일 ) dy 요일의약자 ( 월 ~ 일 ) dd 1 달중날짜 (1~31) mm mo moth 월순서 (01~12, Jauary=01) 월이름약어 (Ja~Dec) 월이름 (Jauary~December) ddd 1 년중날짜 (1~366) ss 초 (0~59) hh, hh12 12 시간 (1~12) hh24 24 시간 (0~23) yyyy yyy, yy, y 4 자리연도 4 자리연도의마지막 3, 2, 1 자리

14 1.3 날짜함수 질의 4-7 마당서점은주문일로부터 10 일후매출을확정한다. 각주문의확정일자를구하시오. SELECT orderid " 주문번호 ", orderdate " 주문일 ", orderdate+10 " 확정 " FROM Orders;

15 1.3 날짜함수 TO_DATE : 문자형으로저장된날짜를날짜형으로변환하는함수 TO_CHAR : 날짜형을문자형으로변환하는함수 질의 4-8 마당서점이 2014 년 7 월 7 일에주문받은도서의주문번호, 주문일, 고객번호, 도서번호를모두보이시오. 단주문일은 yyyy-mm-dd 요일 형태로표시한다. SELECT orderid " 주문번호 ", TO_CHAR(orderdate, 'yyyy-mm-dd dy') " 주문일 ", custid " 고객번호 ", bookid " 도서번호 " FROM Orders WHERE orderdate=to_date(' ', 'yyyymmdd');

16 1.3 날짜함수 SYSDATETIME : 오라클의현재날짜와시간을반환하는함수 SYSTIMESTAMP : 현재날짜, 시간과함께초이하의시간과서버의 TIMEZONE 까 지출력함 질의 4-8 DBMS 서버에설정된현재시간과오늘날짜를확인하시오. SELECT SYSDATE, TO_CHAR(SYSDATE, 'yyyy/mm/dd dy hh24:mi:ss') "SYSDATE_1" FROM Dual;

17 1. 다음내장함수의결과를적으시오. ABS(-15) CEIL(15.7) COS( ) FLOOR(15.7) LOG(10,100) MOD(11,4) POWER(3,2) ROUND(15.7) SIGN(-15) TRUNC(15.7) CHR(67) CONCAT('HAPPY ', 'Birthday') LOWER('Birthday') LPAD('Page 1', 15, '*.') LTRIM('Page 1', 'ae') REPLACE('JACK', 'J', 'BL') RPAD('Page 1', 15, '*.') RTRIM('Page 1', 'ae') SUBSTR('ABCDEFG', 3, 4) TRIM(LEADING 0 FROM '00AA00') UPPER('Birthday') ASCII('A') INSTR('CORPORATE FLOOR','OR', 3, 2) LENGTH('Birthday') ADD_MONTHS('14/05/21', 1) LAST_DAY(SYSDATE) NEXT_DAY(SYSDATE) ROUND(SYSDATE) SYSDATE TO_CHAR(SYSDATE) TO_CHAR(123) TO_DATE(' ', 'DD MM YYYY') TO_NUMBER('12.3') DECODE(1,1,'aa','bb') NULLIF(123, 345) NVL(NULL, 123)

18 2. NULL 값처리 NULL 값이란? 아직지정되지않은값 NULL 값은 0, ( 빈문자 ), ( 공백 ) 등과다른특별한값 NULL 값은비교연산자로비교가불가능함 NULL 값의연산을수행하면결과역시 NULL 값으로반환됨 집계함수를사용할때주의할점 NULL+ 숫자 연산의결과는 NULL 집계함수계산시 NULL이포함된행은집계에서빠짐 해당되는행이하나도없을경우 SUM, AVG 함수의결과는 NULL이되며, COUNT 함수의결과는 0

19 2. NULL 값처리 NULL 값에대한연산과집계함수 (* Mybook 테이블생성은웹페이지스크립트참조 ) Mybook bookid price 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;

20 2. NULL 값처리 NULL 값을확인하는방법 IS NULL, IS NOT NULL NULL 값을찾을때는 = 연산자가아닌 IS NULL 을사용, NULL 이아닌값을찾을때는 <> 연산자가아닌 IS NOT NULL 을사용함 Mybook bookid price NULL SELECT * FROM Mybook WHERE price IS NULL; SELECT * FROM Mybook WHERE price = '';

21 2. NULL 값처리 NVL : NULL 값을다른값으로대치하여연산하거나다른값으로출력 NVL( 속성, 값 ) /* 속성값이 NULL 이면 ' 값 ' 으로대치한다 */ 질의 4-10 이름, 전화번호가포함된고객목록을보이시오. 단, 전화번호가없는고객은 연락처없음 으로표시한다. SELECT ame " 이름 ", NVL(phoe, ' 연락처없음 ') " 전화번호 " FROM Customer;

22 3. ROWNUM 내장함수는아니지만자주사용되는문법 오라클에서내부적으로생성되는가상컬럼으로 SQL 조회결과의순번을나타냄 자료를일부분만확인하여처리할때유용 질의 4-11 고객목록에서고객번호, 이름, 전화번호를앞의두명만보이시오. SELECT ROWNUM 순번, custid, ame, phoe FROM Customer WHERE ROWNUM <=2;

23 2. Mybook 테이블을생성하고 NULL 에관한다음 SQL 문에답하시오. 질의의결과를 보면서 NULL 에대한개념을정리해보시오.

24 3. ROWNUM 에관한다음 SQL 문에답하시오. 데이터는마당서점데이터베이스를 이용한다.

25 02. 부속질의 스칼라부속질의 SELECT 부속질의 인라인뷰 FROM 부속질의 중첩질의 WHERE 부속질의

26 02 부속질의 부속질의 (subquery) 란? 하나의 SQL 문안에다른 SQL 문이중첩된 ested 질의를말함 다른테이블에서가져온데이터로현재테이블에있는정보를찾거나가공할때사용함 보통데이터가대량일때데이터를모두합쳐서연산하는조인보다필요한데이터만찾아서공급해주는부속질의가성능이더좋음 주질의 (mai query, 외부질의 ) 와부속질의 (sub query, 내부질의 ) 로구성됨 주질의 (mai query) 부속질의 (sub query) SELECT SUM(saleprice) FROM Orders WHERE custid = (SELECT custid FROM Customer WHERE ame = ' 박지성 ') 그림 4-2 부속질의

27 02 부속질의 표 4-6 부속질의의종류 명칭위치영문및동의어설명 스칼라부속질의 SELECT 절 scalar subquery SELECT 절에서사용되며단일값을반환 하기때문에스칼라부속질의라고함 인라인뷰 FROM 절 ilie view, table subquery FROM 절에서결과를뷰 (view) 형태로반 환하기때문에인라인뷰라고함 중첩질의 WHERE 절 ested subquery, predicate subquery WHERE 절에술어와같이사용되며결과 를한정시키기위해사용됨 상관혹은비상관형태.

28 1. 스칼라부속질의 SELECT 부속질의 스칼라부속질의 (scalar subquery) 란? SELECT 절에서사용되는부속질의로, 부속질의의결과값을단일행, 단일열의스칼라값으로반환함 스칼라부속질의는원칙적으로스칼라값이들어갈수있는모든곳에사용가능하며, 일반적으로 SELECT 문과 UPDATE SET 절에사용됨. 주질의와부속질의와의관계는상관 / 비상관모두가능함. 주질의 SELECT custid, FROM GROUP BY Orders od custid 스칼라부속질의 (SELECT ame FROM Customer cs WHERE cs.custid=od.custid), SUM(saleprice) 그림 4-3 스칼라부속질의

29 1. 스칼라부속질의 SELECT 부속질의 질의 4-12 마당서점의고객별판매액을보이시오 ( 결과는고객이름과고객별판매액을출력 ). SELECT FROM GROUP BY ( SELECT ame FROM Customer cs WHERE cs.custid=od.custid ) "ame", SUM(saleprice) "total" Orders od od.custid;

30 1. 스칼라부속질의 SELECT 부속질의 SELECT custid, SUM(saleprice) total FROM Orders od GROUP BY custid ; SELECT FROM WHERE ame Customer cs cs.custid = od.custid Custid 1 의이름은? SELECT SELECT ame FROM Customer Customer cs cs WHERE cs.custid cs.custid = od.custid) = ame, SUM(saleprice) total FROM Orders od GROUP BY od.custid ; 그림 4-4 마당서점의고객별판매액

31 1. 스칼라부속질의 SELECT 부속질의 질의 4-12 Orders 테이블에각주문에맞는도서이름을입력하시오. UPDATE SET Orders bookame = ( SELECT bookame FROM Book WHERE Book.bookid=Orders.bookid );

32 2. 인라인뷰 - FROM 부속질의 인라인뷰 (ilie view) FROM 절에서사용되는부속질의 테이블이름대신인라인뷰부속질의를사용하면보통의테이블과같은형태로사용가능 부속질의결과반환되는데이터는다중행, 다중열이어도상관없음 다만가상의테이블인뷰형태로제공되어상관부속질의로사용될수는없음 질의 4-14 고객번호가 2 이하인고객의판매액을보이시오 ( 결과는고객이름과고객별판매액 출력 ) SELECT cs.ame, SUM(od.saleprice) "total" FROM (SELECT custid, ame FROM Customer WHERE custid <= 2) cs, Orders od WHERE cs.custid=od.custid GROUP BY cs.ame;

33 2. 인라인뷰 - FROM 부속질의 주질의 SELECT cs.ame, SUM(od.saleprice) total FROM (SELECT custid, ame FROM Customer WHERE custid <= 2) Orders od WHERE cs.custid = od.custid GROUP BY cs.ame ; 그림 4-5 인라인뷰 cs, 인라인뷰

34 3. 중첩질의 WHERE 부속질의 중첩질의 (ested subquery) 는 WHERE 절에서사용되는부속질의 WHERE 절은보통데이터를선택하는조건혹은술어 (predicate) 와같이사용됨 그래서중첩질의를술어부속질의 (predicate subquery) 라고도함 표 4-7 중첩질의연산자의종류 술어연산자반환행반환열상관 비교 =, >, <, >=, <=, <> 단일단일가능 집합 IN, NOT IN 다중단일가능 한정 (quatified) ALL, SOME(ANY) 다중단일가능 존재 EXISTS, NOT EXISTS 다중다중필수

35 3. 중첩질의 WHERE 부속질의 비교연산자 부속질의가반드시단일행, 단일열을반환해야하며, 아닐경우질의를처리할수없음. 질의 4-15 평균주문금액이하의주문에대해서주문번호와금액을보이시오. SELECT FROM WHERE orderid, saleprice Orders saleprice <= (SELECT AVG(saleprice) FROM Orders); 질의 4-16 각고객의평균주문금액보다큰금액의주문내역에대해서주문번호, 고객번호, 금액을보이시오. SELECT FROM WHERE orderid, custid, saleprice Orders od saleprice > (SELECT AVG(saleprice) FROM Orders so WHERE od.custid=so.custid);

36 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 '% 대한민국 %');

37 3. 중첩질의 WHERE 부속질의 ALL, SOME(ANY) ALL은모두, SOME(ANY) 은어떠한 ( 최소한하나라도 ) 이라는의미를가짐 구문구조 scalar_expressio { 비교연산자 (= < >!= > >=!> < <=!<)} {ALL SOME ANY} ( 부속질의 ) 질의 번고객이주문한도서의최고금액보다더비싼도서를구입한주문의주문번 호와금액을보이시오. SELECT FROM WHERE orderid, saleprice Orders saleprice > ALL (SELECT saleprice FROM Orders WHERE custid='3');

38 3. 중첩질의 WHERE 부속질의 EXISTS, NOT EXISTS 데이터의존재유무를확인하는연산자 주질의에서부속질의로제공된속성의값을가지고부속질의에조건을만족하여값이존재하면참이되고, 주질의는해당행의데이터를출력함 NOT EXIST 의경우이와반대로동작함 구문구조 WHERE [NOT] EXISTS ( 부속질의 ) 질의 4-19 EXISTS 연산자로대한민국에거주하는고객에게판매한도서의총판매액을구 하시오. SELECT SUM(saleprice) "total" FROM Orders od WHERE EXISTS (SELECT * FROM Customer cs WHERE address LIKE '% 대한민국 %' AND cs.custid=od.custid);

39 연습문제풀이 4. 부속질의에관한다음 SQL 문을수행해보고어떤질의에대한답인지설명하시오.

40 03. 뷰 뷰의생성 뷰의수정 뷰의삭제

41 03 뷰 뷰 (view) 는하나이상의테이블을합하여만든가상의테이블 장점 편리성 : 미리정의된뷰를일반테이블처럼사용할수있기때문에편리함. 또사용자가필요한정보만요구에맞게가공하여뷰로만들어쓸수있음 재사용성 : 자주사용되는질의를뷰로미리정의해놓을수있음 보안성 : 각사용자별로필요한데이터만선별하여보여줄수있음. 중요한질의의경우질의내용을암호화할수있음

42 03 뷰 그림 4-6 뷰

43 1. 뷰의생성 기본문법 CREATE VIEW 뷰이름 [( 열이름 [,... ])] AS SELECT 문 Book 테이블에서 축구 라는문구가포함된자료만보여주는뷰 SELECT * FROM Book WHERE bookame LIKE '% 축구 %'; 위 SELECT 문을이용해작성한뷰정의문 CREATE VIEW vw_book AS SELECT * FROM Book WHERE bookame LIKE '% 축구 %';

44 1. 뷰의생성 질의 4-20 주소에 대한민국 을포함하는고객들로구성된뷰를만들고조회하시오. 단, 뷰의이름은 vw_customer 로한다. CREATE VIEW vw_customer AS SELECT * FROM Customer WHERE address LIKE '% 대한민국 %'; < 결과확인 > SELECT * FROM vw_customer;

45 1. 뷰의생성 질의 4-21 Orders 테이블에고객이름과도서이름을바로확인할수있는뷰를생성한후, 김연아 고객이구입한도서의주문번호, 도서이름, 주문액을보이시오. CREATE VIEW vw_orders (orderid, custid, ame, bookid, bookame, saleprice, orderdate) AS SELECT od.orderid, od.custid, cs.ame, od.bookid, bk.bookame, 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, bookame, saleprice vw_orders ame=' 김연아 ';

46 2. 뷰의수정 기본문법 CREATE OF REPLACE VIEW 뷰이름 [( 열이름 [,... ])] AS SELECT 문 질의 4-22 [ 질의 4-20] 에서생성한뷰 vw_customer 는주소가대한민국인고객을보여준다. 이뷰를영국을주소로가진고객으로변경하시오. phoe 속성은필요없으므로포함시키지 마시오. CREATE OR REPLACE VIEW vw_customer (custid, ame, address) AS SELECT custid, ame, address FROM Customer WHERE address LIKE '% 영국 %'; < 결과확인 > SELECT * FROM vw_customer;

47 3. 뷰의삭제 기본문법 DROP VIEW 뷰이름 [,... ]; 질의 4-23 앞서생성한뷰 vw_customer 를삭제하시오. DROP VIEW vw_customer; < 결과확인 > SELECT * FROM vw_customer;

48 6. 다음에해당하는뷰를작성하시오. 데이터베이스는마당서점데이터베이스를이용한다. (1) 판매가격이 20,000원이상인도서의도서번호, 도서이름, 고객이름, 출판사, 판매가격을보여주는 highorders 뷰를생성하시오. (2) 생성한뷰를이용하여판매된도서의이름과고객의이름을출력하는 SQL 문을작성하시오. (3) highorders 뷰를변경하고자한다. 판매가격속성을삭제하는명령을수행하시오. 삭제후 (2) 번 SQL 문을다시수행하시오.

49 04. 인덱스 데이터베이스의물리적저장인덱스와 B-tree 오라클인덱스인덱스의생성인덱스의재구성과삭제

50 1. 데이터베이스의물리적저장 중앙처리장치 OS TOOL CPU 사용자 UI SQL Developer / SQL PLUS 오라클클라이언트 주기억장치 DBMS RAM 오라클 INSTANCE 보조기억장치 HDD, SSD 커널 파일시스템 DB FIELS 데이터파일 컨트롤파일 데이터베이스 리두로그파일 그림 4-7 DBMS 와데이터파일

51 1. 데이터베이스의물리적저장 실제데이터가저장되는곳은보조기억장치 하드디스크, SSD, USB 메모리등 가장많이사용되는장치는하드디스크 하드디스크는원형의플레이트 (plate) 로구성되어있고, 이플레이트는논리적으로트랙으로나뉘며트랙은다시몇개의섹터로나뉨 원형의플레이트는초당빠른속도로회전하고, 회전하는플레이트를하드디스크의액세스암 (arm) 과헤더 (header) 가접근하여원하는섹터에서데이터를가져옴 하드디스크에저장된데이터를읽어오는데걸리는시간은모터 (motor) 에의해서분당회전하는속도 (RPM, Revolutios Per Miute), 데이터를읽을때액세스암이이동하는시간 (latecy time), 주기억장치로읽어오는시간 (trasfer time) 에영향을받음

52 1. 데이터베이스의물리적저장 섹터 스핀들모터 트랙 액세스헤드 액세스암 그림 4-8 하드디스크의구조액세스시간 (access time) 액세스시간 = 탐색시간 (seek time, 액세스헤드를트랙에이동시키는시간 ) + 회전지연시간 (rotatioal latecy time, 섹터가액세스헤드에접근하는시간 ) + 데이터전송시간 (data trasfer time, 데이터를주기억장치로읽어오는시간 )

53 1. 데이터베이스의물리적저장 그림 4-9 오라클의내부구조

54 1. 데이터베이스의물리적저장 표 4-8 오라클의주요파일 파일 설명 데이터파일 운영체제상에물리적으로존재 사용자데이터와개체를저장 테이블과인덱스로구성. 온라인리두로그 컨트롤파일 데이터의모든변경사항을기록 데이터베이스복구에사용되는로그정보저장 최소두개의온라인리두로그파일그룹을가짐 오라클이필요로하는다른파일들 ( 데이터파일, 로그파일등 ) 의위치정보를저장 데이터베이스구조등의변경사항이있을때자동으로업데이트됨 오라클 DB의마운트, 오픈의필수파일 복구시동기화정보저장

55 2. 인덱스와 B-tree 인덱스 (idex, 색인 ) 란도서의색인이나사전과같이데이터를쉽고빠르게찾을수 있도록만든데이터구조 그림 4-10 B-tree 의구조

56 2. 인덱스와 B-tree 그림 4-11 B-tree 에서검색예 인덱스의특징 인덱스는테이블에서한개이상의속성을이용하여생성함 빠른검색과함께효율적인레코드접근이가능함 순서대로정렬된속성과데이터의위치만보유하므로테이블보다작은공간을차지함 저장된값들은테이블의부분집합이됨 일반적으로 B-tree 형태의구조를가짐 데이터의수정, 삭제등의변경이발생하면인덱스의재구성이필요함

57 3.1 오라클 B-tree 인덱스 오라클인덱스는 B-tree 를변형하여사용하며명칭은 B-tree 로동일한이름으로부름 그림 4-12 인덱스의예

58 3.2 오라클인덱스의종류 표 4-9 오라클인덱스의종류

59 4. 인덱스의생성 인덱스생성시고려사항 인덱스는 WHERE 절에자주사용되는속성이어야함 인덱스는조인에자주사용되는속성이어야함 단일테이블에인덱스가많으면속도가느려질수있음 ( 테이블당 4~5개정도권장 ) 속성이가공되는경우사용하지않음 속성의선택도가낮을때유리함 ( 속성의모든값이다른경우 ) 인덱스의생성문법 CREATE [REVERSE] [UNIQUE] INDEX 인덱스이름 ] ON 테이블이름 ( 컬럼 [ASC DESC] [{, 컬럼 [ASC DESC]} ])[;]

60 4. 인덱스의생성 질의 4-24 Book 테이블의 bookame 열을대상으로비클러스터인덱스 ix_book 을생성하라. CREATE INDEX ix_book ON Book (bookame); 질의 4-25 Customer 테이블의 ame 열을대상으로클러스터인덱스 cix_customer 를생성 하시오. CREATE INDEX ix_book2 ON Book(publisher, price);

61 4. 인덱스의생성 표 4-14 생성된인덱스확인및실행계획

62 5. 인덱스의재구성과삭제 인덱스의재구성은 ALTER INDEX 명령을사용함 생성문법 ALTER [REVERSE] [UNIQUE] INDEX 인덱스이름 [ON {ONLY} 테이블이름 { 컬럼이름 [{, 컬럼이름 } ])] REBUILD[;] 질의 4-26 인덱스 ix_book 을재생성하시오. ALTER INDEX ix_book REBUILD; 삭제문법 DROP INDEX 인덱스이름 질의 4-27 인덱스 ix_book 을삭제하시오. DROP INDEX ix_book;

63 13. [ 마당서점데이터베이스인덱스 ] 마당서점데이터베이스에서다음 SQL 문을수행하고데이터베이스가인덱스를사용하는과정을확인하시오. (1) 다음 SQL 문을수행해본다. SELECT ame FROM Customer WHERE ame LIKE ' 박세리 '; (2) 실행계획을살펴본다. 실행계획은 [F10] 키를누른후 [ 계획설명 ] 탭을선택하면표시된다. (3) Customer 테이블에 ame으로인덱스를생성하시오. 생성후 (1) 번의질의를다시수행하고실행계획을살펴보시오. (4) 같은질의에대한두가지실행계획을비교해보시오. (5) (3) 번에서생성한인덱스를삭제하시오.

64 요약 1. 내장함수 2. 부속질의 3. 뷰 4. 인덱스 5. B-tree 6. 오라클인덱스의종류