[CH13]객체종류_데이터사전, 인덱스(INDEX), 뷰(VIEW)

 

※ 오라클 데이터베이스 ※

-사용자 테이블(user table) : EMP, DEPT 등등
-데이터 사전(data dictionary) : 데이터베이스를 구성하고 운영하는데 필요한 모든 정보를 저장하는 특수한 테이블
 데이터베이스가 생성되는 시점에 자동으로 생성. 사전뷰를 통해 정보 열람.

-접두어_테이블명(항상 복수형)

USER_xxxx	현재 데이터베이스에 접속한 사용자가 소유한 객체 정보
ALL_xxxx	현재 데이터베이스에 접속한 사용자가 소유한 객체, 다른 사용자가 소유한 객체 중 사용허가를 받은 객체
DBA_xxxx	데이터베이스 관리 권한을 가진 사용자가 조회 가능.    SYSTEM계정에서 가능
V$_xxxx	데이터베이스 성능 관련 정보

(조회하는 방법 예시) : 복수형!!

--테이블 조회
SELECT * FROM USER_TABLES;

--인덱스 조회
SELECT * FROM USER_INDEXES;

--뷰 조회
SELECT * FROM USER_VIEWS;

--시퀀스 조회
SELECT * FROM USER_SEQUENCES;

 

1. 데이터사전

-SCOTT 계정이 가지고 있는 객체 정보 살펴보기

--USER_  : 사용자가 소유한 객체정보
SELECT TABLE_NAME
  FROM USER_TABLES;

-ALL_  :  사용 가능한 모든 객체 정보
SELECT OWNER, TABLE_NAME
  FROM ALL_TABLES;

-DBA_  :  데이터베이스 관리 권한을 가진 사용자가 조회 가능
--일반 계정은 조회 불가능. 시스템 계정 접속해 거기서 조회
SELECT *
  FROM DBA_TABLES;

 

※ 시스템 계정 접속 방법 ※

[+]  → Name : SYSTEM  → 사용자 이름 및 비밀번호 : SYSTEM, ORACLE → 테스트 : 상태 '접속' 확인 → 접속

 

2. 인덱스(INDEX)

빠른 검색 향상을 위해 '테이블 열'에 사용하는 객체. 열이 기본키(PK) 또는 고유키일 경우 자동 생성.

데이터 조회를 반드시 빠르게 한다는 보장은 없음. 무분별한 생성은 오히려 성능 저하 원인

 

-INDEX 및 INDEX_COLUMN 정보 알아보기

--SCOTT 계정이 소유한 INDEX 정보 알아보기
SELECT *
  FROM USER_INDEXES;

--SCOTT 계정이 소유한 INDEX_COLUMN 정보 알아보기
SELECT *
  FROM USER_IND_COLUMNS;

 

-인덱스 생성

CREATE INDEX 인덱스 이름
    ON 테이블이름(열 이름1 ASC(or DESC), 열 이름2 ASC(or DESC)... );

(예제)

CREATE INDEX IDX_EMP_SAL
    ON EMP(SAL);    --EMP테이블의 SAL열의 인덱스 지정. IDX_EMP_SAL이라는 이름으로.

 

-인덱스 삭제

DROP INDEX IDX_EMP_SAL;

 

3. 뷰(VIEW)

하나 이상의 테이블을 조회하는 SELECT문을 저장한 객체. 가상테이블(virtual table).

└편리성 : SELECT문 복잡도 완화

└보안성 : 테이블의 일부 데이터만 노출. 상대는 뷰로 받은 내용만 확인할 수 있음.

CREATE [OR REPLACE_생략가능_만들어진 뷰가 있다면 대체] [FORCE_테이블 없어도 뷰 생성 | NOFORCE_테이블 존재할 때만 뷰 생성] VIEW 뷰 이름 (열 이름1, 열 이름2, ...)
    AS (저장할 SELECT문)
  [WITH CHECK OPTION_생략가 [CONSTRAINT 제약조건]]
  [WITH READ ONLY [CONSTRAINT 제약조건]];

-뷰 생성

(예제)

※ 일반 계정(SCOTT)은 뷰 생성 권한 없음 → 시스템 계정에서 뷰 생성 권한 부여 → 생성 가능

--시스템 계정에서 뷰 생성 권한 부여하기(시스템 계정)
GRANT CREATE VIEW TO SCOTT;

CREATE VIEW VW_EMP20
    AS (SELECT EMPNO, ENAME, JOB, DEPTNO
          FROM EMP
         WHERE DEPTNO = 20);
         
--생성한 뷰 확인_데이터사전
SELECT *
  FROM USER_VIEWS;

--뷰 조회
SELECT *
FROM VW_EMP20;

 

-뷰 삭제 : 데이터나 테이블은 그대로 존재

DROP VIEW VW_EMP20;

--데이터사전으로 유저가 가진 뷰 조회
SELECT * FROM USER_VIEWS; 

--테이블 조회로 테이블 및 데이터 그대로인 것 확인 가능
SELECT * FROM EMP;

 

※ 인라인 뷰를 이용한 TOP-N SQL문 ※

-ROWNUM : 테이블에 저장된 행이 조회된 순서대로 매겨진 일련번호(게시글에 넘버 매길 때 사용.)

 

-ROWNUM 조회 및 정렬

--ROWNUM을 추가로 조회(or 출력)
SELECT ROWNUM, E.*
  FROM EMP E;

--EMP 테이블을 SAL 열 기준으로 정렬
SELECT ROWNUM, E.*
  FROM EMP E
 ORDER BY SAL DESC;
		--이미 매겨진 ROWNUM. SAL연봉순서대로 정렬. ROWNUM 번호는 SAL연봉 순서로 정렬됨

 

-SAL 정렬에 맞게 ROWNUM 순번 매기기

--인라인 뷰(서브쿼리)
SELECT ROWNUM, E.*
  FROM (SELECT *
          FROM EMP E
         ORDER BY SAL DESC) E;

--인라인 뷰(WITH절)
WITH E AS (SELECT * FROM EMP ORDER BY SAL DESC)
SELECT ROWNUM, E.*
  FROM E;

 

-인라인 뷰로 TOP-N 추출 : 위에서부터 몇 번째까지 추출하는 거

--인라인 뷰로 TOP-N 추출하기(서브쿼리 사용)
SELECT ROWNUM, E.*
  FROM (SELECT *
          FROM EMP
        ORDER BY SAL DESC) E
 WHERE ROWNUM <= 3;
 
 --인라인 뷰로 TOP-N 추출하기(WITH 사용
WITH E AS (SELECT * FROM EMP ORDER BY SAL DESC)
SELECT ROWNUM, E.*
  FROM E
 WHERE ROWNUM <= 3;

 

4. 시퀀스

번호 생성기

-시퀀스 양식

CREATE SEQUENCE 시퀀스 이름
INCREMENT BY   증가값
START WITH  시작위치
MAXVALUE n | MAXVALUE  마지막 값이 어떤 값인지 나타냄
MINVALUE n | MINVALUE  시퀀스에서 생성할 최소값 지정
CYCLE | NOCYCLE  시퀀스 반복 여부.
CACHE n | NOCACHE  메모리에 미리 할당할 것인지 여부

└CYCLE : 노사이클일 경우, 최대값 넘어가는 것 허용하지 않음. 최대값 넘어가게 인서트하면 오류 발생.

              사이클일 경우, 최대값 넘어가면 최소값부터 다시 부여

└CACHE : 미리 할당 시 속도 빠름. 동시 사용자가 많을 경우 유리. 최대 지정값(MAXVALUE - MINVALUE)

└START WITH : '시퀀스 이름.NEXTVAL'는 다음 번호 생성

 

-시퀀스 생성 및 확인, 데이터 입력

--시퀀스 생성
CREATE SEQUENCE SEQ_DEPT_SEQUENCE
INCREMENT BY 10
START WITH 10
MAXVALUE 90
MINVALUE 0
NOCYCLE
CACHE 2;

--생성한 시퀀스 확인(데이터사전)
SELECT *
  FROM USER_SEQUENCES;
  
--시퀀스에서 생성한 순번을 사용한 INSERT문 실행
INSERT INTO DEPT_SEQUENCE (DEPTNO, DNAME, LOC)
 VALUES(SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE', 'SEOUL');
 
--테이블 조회
SELECT * FROM DEPT_SEQUENCE ORDER BY DEPTNO;

--가장 마지막으로 생선된 시퀀스 확인  :  CURRVAL
SELECT SEQ_DEPT_SEQUENCE.CURRVAL
  FROM DUAL;

 

-시퀀스 수정 : ALTER

ALTER SEQUENCE SEQ_DEPT_SEQUENCE
    INCREMENT BY 3
    MAXVALUE 99
    CYCLE;

 

-시퀀스 삭제 : DROP

DROP SEQUENCE SEQ_DEPT_SEQUENCE;

--삭제되었는지 확인
SELECT * FROM USER_SEQUENCES;

 

5. 동의어

공식 별칭 지정.

테이블, 뷰, 시퀀스 등 객체 이름 대신 사용할 수 있는 다른 이름

CREATE [PUBLIC] SYNONYM 동의어 이름
FOR [사용자._생략가][객체 이름];

※ 일반 계정(SCOTT)은 뷰 생성 권한 없음 → 시스템 계정에서 뷰 생성 권한 부여 → 생성 가능

--시스템 계정에서 동의어 생성 권한 부여하기(시스템 계정)
GRANT CREATE SYNONYM TO SCOTT;
GRANT CREATE PUBLIC SYNONYM TO SCOTT;

 

-동의어 생성, 확인, 삭제

--생성
CREATE SYNONYM E
    FOR EMP;
    
--데이터사전뷰 확인
SELECT * FROM USER_SYNONYMS;

--동의어 지정한 테이블, 기존 테이블 조회하면 같은 거 나옴
SELECT * FROM E;
SELECT * FROM EMP;

--삭제 : 데이터와 테이블에 아무런 영향을 주지 않음.
DROP SYNONYM E;