데이터베이스 - 인덱스(INDEX), 뷰(VIEW)

우리가 사용하는 DB는 RDBMS

데이터와 데이터베이스의 차이는 원하는 데이터를 검색할수있게 하는 차이점이있다.

 

인덱스(INDEX)

SELECT * FROM 학생 WHERE 이름 = '조조' -> FULL TABLE SCAN

SELECT * FROM 학생 WHERE 학번 = '003'  -> INDEX SCAN 

 - 학번같은 PRIMARY KEY는 INDEX가 만들어지며 INDEX에는 학번이

   정렬되어 들어가져있다. 

 - INDEX SCAN의 방식은 INDEX에 정렬된 데이터중 가운데에 번호가 맞는지

   질의하고 위에있는지 아래에있는지 확인한다.(한번 질의에 50%씩 줄어든다.)                                                                     - 행이 가지고있는 물리적인 위치를 ROWID라 한다.

         

PRIMARY KEY와 UNIQUE가 있으면 인덱스를 만들어야한다.

 

 

인덱스 종류
 - 고유 인덱스(Unique index) - 유니크에 입력된 값이 중복될수 없다.
 - 비 고유 인덱스(Non unique index) - 유니크에 입력된 값이 중복될수있다.

 

인덱스 생성
 - 자동 생성 
 . PK, UK 가 설정된 컬럼에 자동 생성된다.
 . 고유 인덱스(Unique index)가 생성된다.
 - 수동 생성 
 . CREATE INDEX 명령으로 생성한다.
 . 비 고유 인덱스(Non unique index)가 생성된다. 

 

인덱스 사례
 - 전체 데이터의 10%~15% 정도의 데이터를 검색하는 경우
 - WHERE 절이나 조인에 사용되는 컬럼
 - 데이터의 행이 매우 많은 경우
 - FK 컬럼!! - 조인할때 INDEX가 없으면 굉장히 느려질수있다.

 

 - PK/UK인덱스는 못지운다. / 제약조건 지우면 같이 삭제

 - PK/UK와 동일하게 INDEX 이름이 부여된다. 

 - 정렬은 우선순위가 중요하다.

 

 

인덱스 생성과 삭제

SQL> CREATE INDEX 인덱스
2 ON 테이블 (컬럼 | 함수 | 수식);

SQL> DROP INDEX 인덱스;

 

SQL> SELECT c.index_name, c.column_name, c.column_position, i.uniqueness
 2 FROM user_indexes I, user_ind_columns c
 3 WHERE c.index_name = i.index_name; 

SQL> SELECT index_name, column_expression 
 2 FROM user_ind_expressions;

 

예제 1. 다음 스크립트를 실행하고 인덱스를 조회한다. 

SQL> @c:\sql\school_con

SQL> SELECT i.table_name, i.index_name, c.column_name, c.column_position, i.uniqueness
 2 FROM user_indexes I, user_ind_columns c
 3 WHERE c.index_name = i.index_name
 4 AND i.table_name IN ('STUDENT', 'PROFESSOR', 'COURSE', 'SCORE')
 5 ORDER BY i.table_name;

TABLE_NAME   INDEX_NAME          COLUMN_NAME   COLUMN_POSITION UNIQUENES
------------ ------------------  -----------   --------------- ---------
COURSE       COURSE_CNO_PK       CNO                         1 UNIQUE
PROFESSOR    PROFESSOR_PNO_PK    PNO                         1 UNIQUE
SCORE        SCORE_SNO_CNO_PK    SNO                         1 UNIQUE
SCORE        SCORE_SNO_CNO_PK    CNO                         2 UNIQUE
STUDENT      STUDENT_SNO_PK      SNO                         1 UNIQUE

 

SQL> SELECT c.table_name, c.constraint_name, c.constraint_type, s.column_name
 2 FROM user_constraints c, user_cons_columns s
 3 WHERE c.constraint_name = s.constraint_name
 4 AND c.table_name IN ('STUDENT', 'PROFESSOR', 'COURSE', 'SCORE')
 5 ORDER BY c.table_name, c.constraint_name;

------------------------------------------------------------------------
**FOREIGN COLUMN INDEX는 직접 만들어줘야한다.
------------------------------------------------------------------------

TABLE_NAME      CONSTRAINT_NAME      C COLUMN_NAME
--------------- -------------------- - -----------
COURSE          COURSE_CNO_PK        P CNO
COURSE          COURSE_PNO_FK        R PNO
PROFESSOR       PROFESSOR_PNO_PK     P PNO
SCORE           SCORE_CNO_FK         R CNO
SCORE           SCORE_SNO_CNO_PK     P SNO
SCORE           SCORE_SNO_CNO_PK     P CNO
SCORE           SCORE_SNO_FK         R SNO
STUDENT         STUDENT_SNO_PK       P SNO

8 개의 행이 선택되었습니다.

 

예제 2. 테이블의 제약 조건과 관련된 필요한 인덱스를 추가한다. 

SQL> CREATE INDEX course_pno_fk
 2 ON course (pno);

인덱스가 생성되었습니다. 


SQL> CREATE INDEX score_cno_fk
 2 ON score (cno);

인덱스가 생성되었습니다.

 

SQL> SELECT i.table_name, i.index_name, c.column_name, c.column_position, i.uniqueness
 2 FROM user_indexes i, user_ind_columns c
 3 WHERE c.index_name = i.index_name
 4 AND i.index_name LIKE '%FK' 
 5 ORDER BY c.table_name;

TABLE_NAME INDEX_NAME           COLUMN_NAME    COLUMN_POSITION UNIQUENES
---------- -------------------- -------------- --------------- ---------
COURSE     COURSE_PNO_FK        PNO                          1 NONUNIQUE
SCORE      SCORE_CNO_FK         CNO                          1 NONUNIQUE

 

예제 3. 다양한 인덱스를 생성하고 조회한다. 

SQL> CREATE INDEX student_sname_indx
 2 ON student (sname);
 
인덱스가 생성되었습니다. 


SQL> CREATE INDEX student_major_sname_indx
 2 ON student (major, sname);

인덱스가 생성되었습니다.. 


SQL> CREATE INDEX student_coavr_indx
 2 ON student (avr*4.5/4.0);

인덱스가 생성되었습니다.

 

SQL> SELECT c.index_name, c.column_name, c.column_position, i.uniqueness
 2 FROM user_indexes I, user_ind_columns c
 3 WHERE c.index_name = i.index_name
 4 AND c.table_name = 'STUDENT'
 5 ORDER BY c.index_name, c.column_position;

INDEX_NAME                     COLUMN_NAME    COLUMN_POSITION UNIQUENES
------------------------------ -------------- --------------- ---------
STUDENT_COAVR_INDX             SYS_NC00007$                 1 NONUNIQUE
STUDENT_MAJOR_SNAME_INDX       MAJOR                        1 NONUNIQUE
STUDENT_MAJOR_SNAME_INDX       SNAME                        2 NONUNIQUE
STUDENT_SNAME_INDX             SNAME                        1 NONUNIQUE
STUDENT_SNO_PK                 SNO                          1 UNIQUE

 

SQL> SELECT index_name, column_expression 
 2 FROM user_ind_expressions 
 3 WHERE index_name = 'STUDENT_COAVR_INDX';

INDEX_NAME                     COLUMN_EXPRESSION
------------------------------ -------------------
STUDENT_COAVR_INDX             "AVR"*4.5/4.0

 

예제 4. Student 테이블의 인덱스를 삭제한다. 

SQL> SELECT c.index_name, c.column_name, c.column_position, i.uniqueness
 2 FROM user_indexes I, user_ind_columns c
 3 WHERE c.index_name = i.index_name
 4 AND c.table_name = 'STUDENT';

INDEX_NAME                     COLUMN_NAME    COLUMN_POSITION UNIQUENES
------------------------------ -------------- --------------- ---------
STUDENT_COAVR_INDX             SYS_NC00007$                 1 NONUNIQUE
STUDENT_MAJOR_SNAME_INDX       MAJOR                        1 NONUNIQUE
STUDENT_MAJOR_SNAME_INDX       SNAME                        2 NONUNIQUE
STUDENT_SNAME_INDX             SNAME                        1 NONUNIQUE
STUDENT_SNO_PK                 SNO                          1 UNIQUE

 

SQL> DROP INDEX student_major_sname_indx; 

인덱스가 삭제되었습니다. 


SQL> DROP INDEX student_sno_pk; 
DROP INDEX student_sno_pk
 *
1행에 오류:
ORA-02429: 고유/기본 키 적용을 위한 인덱스를 삭제할 수 없습니다.

 

SQL> SELECT c.index_name, c.column_name, c.column_position, i.uniqueness
 2 FROM user_indexes I, user_ind_columns c
 3 WHERE c.index_name = i.index_name
 4 AND c.table_name = 'STUDENT'; 

INDEX_NAME           COLUMN_NAME    COLUMN_POSITION UNIQUENES
-------------------- -------------- --------------- ---------
STUDENT_SNAME_INDX   SNAME                        1 NONUNIQUE
STUDENT_SNO_PK       SNO                          1 UNIQUE
STUDENT_COAVR_INDX   SYS_NC00007$                 1 NONUNIQUE


SQL> ALTER TABLE student 2 DISABLE CONSTRAINT student_sno_pk CASCADE; 

테이블이 변경되었습니다.

 

SQL> SELECT c.index_name, c.column_name, c.column_position, i.uniqueness
 2 FROM user_indexes I, user_ind_columns c
 3 WHERE c.index_name = i.index_name
 4 AND c.table_name = 'STUDENT';

INDEX_NAME           COLUMN_NAME    COLUMN_POSITION UNIQUENES
-------------------- -------------- --------------- ---------
STUDENT_SNAME_INDX   SNAME                        1 NONUNIQUE
STUDENT_COAVR_INDX   SYS_NC00007$                 1 NONUNIQUE


SQL> ALTER TABLE student 
2 ENABLE CONSTRAINT student_sno_pk;

테이블이 변경되었습니다.

 

SQL> SELECT c.index_name, c.column_name, c.column_position, i.uniqueness
 2 FROM user_indexes I, user_ind_columns c
 3 WHERE c.index_name = i.index_name
 4 AND c.table_name = 'STUDENT'; 

INDEX_NAME           COLUMN_NAME    COLUMN_POSITION UNIQUENES
-------------------- -------------- --------------- ---------
STUDENT_SNAME_INDX   SNAME                        1 NONUNIQUE
STUDENT_SNO_PK       SNO                          1 UNIQUE
STUDENT_COAVR_INDX   SYS_NC00007$                 1 NONUNIQUE

 

뷰(VIEW)

 - 뷰의 목적은 검색하는데 의미를 둔다.
뷰의 종류
 - 단순 뷰(simple view)
  . 단일 table로부터 만들어진 view
  . DML문장 수행이 가능하다.
 - 복합 뷰(complex view)
  . 다중 table로부터 만들어진 view
  . 함수나 수식 등을 포함한다.
  . DML문장 수행이 불가능하다. 

 

뷰 생성과 확인

SQL> CREATE [OR REPLACE][FORCE | NOFORCE] VIEW 뷰 (컬럼 ... )
 2 AS (SELECT 문장 : sub query)
 3 [WITH CHECK OPTION [CONSTRAINT constrant_name]]
 4 [WITH READ ONLY [CONSTRAINT constrant_name]];

SQL> SELECT view_name, text FROM user_views;

SQL> SET LONG 1000

 

예제 1. 접속 계정에 뷰를 생성할 수 있는 권한을 할당한다. 이번 예제는 반드시 관리자로 접속한다. 

C:\...\>sqlplus / as sysdba

SQL> GRANT create view TO st; 

권한이 부여되었습니다.

 

예제 2. 각 일반화학 과목의 학과별 기말고사 평균을 검색하고 뷰로 생성한다.

SQL> SELECT c.cno, cname, major, ROUND(AVG(result))
 2 FROM student s, course c, score r 
 3 WHERE s.sno=r.sno AND r.cno=c.cno
 4 AND cname='일반화학' 5 GROUP BY c.cno, cname, major;

CNO      CNAME          MAJOR  ROUND(AVG(RESULT))
-------- -------------- ------ ------------------
1212 일반화학 유공 68
1212 일반화학 생물 64
1212 일반화학 물리 76
1212 일반화학 식영 69
1212 일반화학 화학 70



SQL> CREATE OR REPLACE VIEW ma_result (과목번호, 과목명, 학과, 기말고사평균)
 2 AS SELECT c.cno, cname, major, ROUND(AVG(result)) 
 3 FROM student s, course c, score r 
 4 WHERE s.sno=r.sno AND r.cno=c.cno
 5 AND cname='일반화학' 

 6 GROUP BY c.cno, cname, major; 
뷰가 생성되었습니다.

 

SQL> SET LONG 1000
SQL> SELECT view_name, text FROM user_views;

 

VIEW_NAME TEXT
-------------------- ----------------------------------------------
MA_RESULT SELECT c.cno, cname, major, ROUND(AVG(result))
 FROM student s, course c, score r
 WHERE s.sno=r.sno AND r.cno=c.cno
 AND cname='일반화학'
 GROUP BY c.cno, cname, major

 

SQL> SELECT * FROM tab
 2 WHERE tabtype = 'VIEW';

 

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
MA_RESULT VIEW
SQL> DESC ma_result; 
이름 널? 유형
-------------------------- -------- ------------- 

과목번호 VARCHAR2(8)
과목명 VARCHAR2(14)
학과 VARCHAR2(10)
기말고사평균 NUMBER

 

SQL> SELECT * FROM ma_result; 
과목번호 과목명 학과 기말고사평균
-------- -------- ---------- ------------
1212 일반화학 유공 68

1212 일반화학 생물 64
1212 일반화학 물리 76
1212 일반화학 식영 69
1212 일반화학 화학 70
 

예제 3. WITH CHECK OPTION을 이용 뷰를 생성해 보자. 

SQL> CREATE VIEW st_ch 
 2 AS SELECT sno, sname, syear, avr 
 3 FROM student 4 WHERE syear = 1; 
뷰가 생성되었습니다. 

 

SQL> INSERT INTO st_ch VALUES ('000001', '시현', 2, 4.0); 
1 개의 행이 만들어졌습니다. 

 

SQL> SELECT * FROM st_ch WHERE sname = '시현'; 
선택된 레코드가 없습니다.       ← st_ch 뷰를 통해 입력했으나 st_ch 뷰에서 검색되지 않고 기반 테이
                                                      블인 student 테이블에서만 검색된다.

 

SQL> SELECT * FROM student WHERE sname = '시현';
SNO SNAME SEX SYEAR MAJOR AVR
------ ---------- --- ---------- ---------- ----------
000001 시현 2 4

 

SQL> ROLLBACK;
롤백이 완료되었습니다. 

 

SQL> CREATE OR REPLACE VIEW st_ch 
 2 AS SELECT sno, sname, syear, avr 
 3 FROM student 

 4 WHERE syear = 1
 5 WITH CHECK OPTION CONSTRAINT view_st_ch_ck; - 해당 뷰에 맞지않는 insert를 차단한다.
뷰가 생성되었습니다. 

 

SQL> INSERT INTO st_ch VALUES ('000001', '시현', 2, 4.0); 
INSERT INTO st_ch VALUES ('000001', '시현', 2, 4.0)      ← 뷰를 통해 검색되지 않는 데이터는 입력 할 수 없다.
 *
1행에 오류:
ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다.

 

실습

1. 사원의 년봉을 검색할 수 있는 뷰를 생성한다. 

    CREATE OR REPLACE VIEW SEARCH (사번, 이름, 연봉)

    AS SELECT ENO, ENAME, SAL*12+NVL(COMM, 0) FROM EMP;

 

2. 학생의 학점 4.5 만점으로 환산된 정보를 검색할 수 있는 뷰를 생성한다. 

   CREATE OR REPLACE VIEW CHANGEAVR (학번, 이름, 학점)

   AS SELECT SNO, SNAME, AVR*4.5/4.0 FROM STUDENT;

 

3. 각 과목별 평균 점수를 검색할 수 있는 뷰를 생성한다. 

    CREATE OR REPLACE VIEW AVGRESULT (과목번호, 과목명, 평균점수)

    AS SELECT c.cno, cname, TO_CHAR(AVG(result), '00.99')

    FROM score r, course c

    WHERE r.cno=c.cno

    GROUP BY c.cno, cname;

 

4. 각 직원과 관리자의 이름을 검색할 수 있는 뷰를 생성한다. 

   CREATE OR REPLACE VIEW ENOMGR (사번, 이름, 사수사번, 사수이름)

   AS SELECT E1.ENO, E1.ENAME, E2.ENO, E2.ENAME

   FROM EMP E1, EMP E2

   WHERE E1.MGR = E2.ENO;

 

5. 각 과목별로 평가 점수가 F인 학생을 검색할 뷰를 생성한다. 

  CREATE OR REPLACE VIEW f_result (과번, 과목명, 학번, 이름, 평가점수)

  AS SELECT c.cno, cname, s.sno, sname, grade

  FROM student s, score r, course c, scgrade

  WHERE s.sno = r.sno and r.cno = c.cno

  AND result between loscore and hiscore

  AND grade = 'F'

  ORDER BY cname;

 

6. 각 과목별로 기말고사가 일등인 학생의 명단을 검색할 뷰를 생성한다. 

  CREATE OR REPLACE VIEW course_first (과번, 과목명, 학번, 일등학생이름, 점수)
   AS SELECT c.cno, cname, s.sno, sname, result
  FROM student s, score r, course c
  WHERE s.sno = r.sno and r.cno = c.cno
  AND (c.cno, result) IN (SELECT c.cno, MAX(result)
                              FROM score r, course c
                              WHERE r.cno = c.cno
                              GROUP BY c.cno);

 

7. 물리학과 교수의 과목을 수강하는 학생의 명단을 검색할 뷰를 생성한다.

    CREATE OR REPLACE VIEW ST_NAME (학번, 이름, 교수이름, 과목)

    AS SELECT S.SNO, S.SNAME, P.PNAME, C.CNAME

    FROM STUDENT S, SCORE R, COURSE C, PROFESSOR P

    WHERE S.SNO = R.SNO AND C.CNO = R.CNO AND C.PNO = P.PNO

    AND P.SECTION = '물리' ORDER BY PNAME, CNAME;