SQL - 뷰, 시퀀스, 인덱스, 사용자 관리

🎯 SQL 오라클 객체의 뷰, 시퀀스, 인덱스와 사용자 관리를 알아본다.

 

 

 

 

뷰 (VIEW)

 

기존의 테이블에서 필요한 것만 뽑아서 하나의 가상 테이블을 다시 만드는 것.

뷰에서 참조하는 원본테이블을 기본테이블(base table)이라고 한다.

10번 부서의 데이터를 자주 사용한다면 매번 셀렉하기보다 그냥 따로 뷰를 만들어두면 사용하기 편하다.

보안을 위해 데이터베이스에서 선택적으로 데이터를 보여줄 수 있기 때문에 접근을 제한할 수 있다. 

검색 쿼리를 단순하게 만들수 있다. 매번 만들기 복잡한 조인문을 뷰로 만들어놓으면 쉽게 사용할 수 있다.

 

GRANT CREATE VIEW TO SCOTT;

 

먼저 SYS계정에서 스콧계정에 뷰를 만들 수 있는 권한을 부여해준다.

 

CREATE VIEW EMP_VIEW
AS
SELECT EMPNO, ENAME, SAL, HIREDATE
FROM EMP
WHERE DEPTNO = 10;


SELECT * FROM EMP_VIEW;



CREATE VIEW DEPT_VIEW
AS
SELECT DEPTNO NO, DNAME NAME
FROM DEPT;


SELECT VIEW_NAME
FROM USER_VIEWS;

 

원하는 부분만 뽑아 뷰를 생성한다.

 

한번 뷰를 만들어놓으면 영구 저장됨.

 

사원정보를 일반사용자가 검색할 때, 급여 정보는 매우 민감하기 때문에 셀렉할 때 뷰를 통해 출력되지 않도록 처리할 수 있다. 테이블을 SELECT할 경우, 행을 제한할 순 있지만 특정 컬럼을 제한하는 방법은 없기 때문에 이럴 땐 뷰를 유용하게 사용할 수 있다.

 

월급컬럼을 제외한 나머지 컬럼으로 구성된 뷰 만들기


CREATE VIEW EMP_VIEW4
AS
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, COMM, DEPTNO
FROM EMP;

SELECT * FROM EMP_VIEW4;

 

USER_VIEWS 데이터사전의 TEXT 컬럼을 확인해보면 뷰를 정의할 때 썼던 서브쿼리만 문자열 형태로 저장되어있다.

뷰는 테이블처럼 디스크 공간을 할당해 실제 데이터를 저장하고 있는 게 아니다.

 

 

뷰 수정

안에 들어있는 자료를 수정한다는 게 아니라 뷰의 구조를 바꾸는 것.

CREATE OR REPLACE 문을 이용한다.

기존에 뷰가 있다면 REPLACE로 덮어쓰기가 되고, 없었으면 CREATE로 새로 생성된다.

 

CREATE OR REPLACE VIEW DEPT_VIEW
AS
SELECT DEPTNO NO, DNAME NAME, LOC
FROM DEPT;

DESC DEPT_VIEW;

 

 

뷰 종류

 

단순뷰 : 하나의 기본 테이블에 대해 정의한 뷰

단순뷰에서 DML문을 실행할 수 있는데, 결과가 실제로 기본 테이블에 반영된다.

뷰이름 뒤에 컬럼 별칭을 지정할 수 있으며 뷰 생성시 함수를 사용하는 경우에는 반드시 컬럼 별칭을 지정해야 한다.

단순 뷰가 그룹함수, GROUP BY, DISTINCT를 포함하는 경우엔 DML 작업 불가능.

 

복합뷰 : 두개 이상의 기본테이블에 대해서 정의한 뷰

매번 조인할 필요 없이 뷰를 사용할 수 있다. 조인 쿼리문을 뷰로 생성한 게 복합뷰다.

 

CREATE VIEW EMP_DEPT_VIW
AS
SELECT EMPNO, ENAME, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND DEPT.DEPTNO = 30;


SELECT * FROM EMP_DEPT_VIEW;


-- 복잡한 조인문을 뷰로 만들어 계속 활용할 수 있다

 

 

SELECT * FROM EMP_VIEW;

DELETE FROM EMP_VIEW
WHERE EMPNO= 7782;

SELECT * FROM EMP_VIEW;

ROLLBACK;


UPDATE EMP_VIEW
SET ENAME='MILLY' WHERE ENAME = 'CLARK';

ROLLBACK;

 

 

뷰의 제약조건

 

WITH CHECK OPTION

WHERE 조건을 만족하는 데이터만 INSERT와 UPDATE 가능

WITH READ ONLY

읽기전용으로 자료 검색만 가능하게 설정.

DML작업은 불가능한 상태

 

 

뷰 제거

 

DROP VIEW 뷰이름;

 

기본 테이블에 아무런 영향을 주지 않는다.

 

 

인라인뷰

 

서브쿼리는 보통 WHERE절에서 사용되는데 이건 FROM 절에 사용하는 형태다. 프롬절엔 테이블명이 와야하지만 서브쿼리가 하나의 가상테이블 형태로 사용된다. FROM 절에서 참조하는 테이블 크기가 클 경우 필요한 행과 컬럼만으로 구성된 집합을 재정의하여 쿼리문을 효율적으로 사용할 수 있다.

 

SELECT E.DEPTNO, TOTAL_SUM, TOTAL_AVG, CNT
FROM (SELECT DEPTNO, SUM(SAL) TOTAL_SUM, AVG(SAL) TOTAL_AVG, COUNT(*) CNT
FROM EMP
GROUP BY DEPTNO) E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

-- 서브쿼리 포스트의 인라인뷰 참고

 

 

 

시퀀스 SEQUENCE

 

무수히 많은 게시물 중 원하는 글을 찾고싶을 때, 글제목은 중복가능성이 있어서 글 번호로 찾는게 좋다. 보통 글번호에 기본키 제약조건을 붙인다.

시퀀스는 호출될 때마다 자동으로 고유한 숫자로 특정 컬럼값을 넘버링한다.

시퀀스에는 여러가지 옵션을 줄 수 있다.

 

CREATE SEQUENCE 시퀀스명
[START WITH N]
[INCREMENT BY N]
[MAXVALUE N]
[MINVALUE N]
[CYCLE]
[CACHE]

 

시퀀스를 만들면 데이터를 생성할 때마다 자동으로 넘버링이 실행된다.

번호는 스타트부터 시작해서 인크리먼트 값만큼 증가하며 맥스밸류까지 도달한다. 맥스밸류를 넘어서는 순간 싸이클이 실행되어 민밸류에서 다시 맥스까지 간다.

사이클 돌았을 때 찍는 최댓값이 MAXVALUE 이며, 싸이클을 새로 시작할 때의 시작값이 MINVALUE 이다.

 

CREATE SEQUENCE EMP_SEQ
INCREMENT BY 1
START WITH 100
MAXVALUE 9999
NOCACHE
NOCYCLE;


SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY
FROM USER_SEQUENCES;


-- 사이클 없이 100번부터 9999번까지의 시퀀스를 실행한다

 

 

시퀀스 사용

시퀀스를 사용하기 위해서는 반드시 시퀀스 객채를 호출해야 한다.

 

1) 시퀀스명.NEXTVAL

지정된 시퀀스에서 순차적인 시퀀스 번호를 추출할 때 사용한다.

넥스트바를 사용하는 순간 값이 하나씩 증가되니까 데이터를 두번연속 INSTERT하지 않게 주의할 것.

 

2) 시퀀스명.CURRVAL

사용자가 방금 추출한 시퀀스 번호를 참조할 때 사용한다.

반드시 NEXTVAL로 번호를 추출한 후에 사용해야 한다.

 

INSERT INTO EMP
VALUES (EMP_SEQ.NEXTVAL, '홍길동', '인사', NULL, SYSDATE,
2500, 300, 40);


SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE DEPTNO = 40;


SELECT EMP_SEQ.CURRVAL FROM DUAL;

 

 

시퀀스 삭제

DROP SEQUENCE EMP_SEQ;

시퀀스를 초기화하려면 드랍하고 다시 만들어야되므로 생성 시 주의해야 한다.

 

 

시퀀스 변경

 

ALTER SEQUENCE EMP_SEQ
INCREMENT BY 2
MAXVALUE 10000
NOCACHE
NOCYCLE;

 

시퀀스의 옵션을 변경할 경우 다음 시퀀스 번호부터 변경사항이 적용되어 출력된다. 

START WITH 옵션은 변경이 불가능하며 MAXVALUE 값은 현재 시퀀스 번호보다 큰 번호로 지정해야 한다.

 

시퀀스 변경 결과, 번호가 2씩 증가

 

 

CREATE SEQUENCE DEPT_DEPTNO_SEQ
START WITH 10
INCREMENT BY 10
MAXVALUE 100
MINVALUE 5
CYCLE 
NOCACHE;


SELECT DEPT_DEPTNO_SEQ.NEXTVAL,
DEPT_DEPTNO_SEQ.CURRVAL
FROM DUAL;


-- 증가값에 마이너스도 사용 가능하다.
-- 100부터 0까지 -10씩 떨어지고 사이클은 150부터 시작.

CREATE SEQUENCE DEPT_DEPTNO_SEQ
START WITH 100
INCREMENT BY -10
MAXVALUE 150
MINVALUE 0
CYCLE 
NOCACHE;

 

 

 

인덱스 INDEX

 

0~10000까지의 번호 중에서 9999를 찾을 때 어떤 검색 방법이 더 빠를까? 0부터 시작하는것보단 뒤에서 한 번 빨리 찾는 게 더 쉽다. 

인덱스는 데이터의 실제 저장위치인 ROWID 고유번호를 관리하기 때문에 빠른 검색이 가능하다. 일단 숫자를 반으로 뚝 잘라서 가지치기 하며 숫자를 찾는 게 트리인덱스 방법이다.

인덱스 없이 데이터를 검색하는 걸 풀스캔이라고 한다. 행이 3개밖에 없는 경우엔 풀스캔이 빠르다.

 

 

인덱스 생성

 

유니크,PK 제약조건 지정 시 유니크 인덱스가 자동생성된다.

수동생성은 한개 컬럼 또는 여러 컬럼(복합 인덱스)을 이용할 수 있다.

 

CREATE [UNIQUE] INDEX 인덱스명
ON 테이블(컬럼[,컬럼]);

 

 

인덱스를 사용해야 하는 경우

• 테이블에 데이터가 많을 때
• 컬럼값의 범위가 넓을 때
• WHERE절이나 JOIN 조건에 사용되는 컬럼
• 검색결과가 전체 데이터의 약 2 ~ 4% 이내를 검색하는 경우
• NULL 값이 많은 컬럼 ( NULL 값은 인덱스에 포함되지 않기 때문에 인덱스 크기가 감소)

 

인덱스 안통하고 풀스캔 사용하는 경우

• 테이블의 데이터가 적을 때
• WHERE문에 해당 컬럼이 자주 사용되지 않을때
• 검색 결과가 전체데이터의 10~15% 이상을 검색하는 경우
• 테이블에 DML작업이 많을 때
• 인덱스가 적용된 컬럼이 NOT연산자, IS NULL,함수와 같이 사용되는 경우

 

 

인덱스 삭제

DROP INDEX 인덱스명;

 

 

 

사용자관리

 

다수의 사용자들이 데이터베이스에 접근하는 경우 보안을 위해 인증과 권한 기능을 사용한다.

인증(Authentication)이란 시스템 수준에서 접근과 사용을 관리하는 것이고, 권한(Authorization)이란 데이터베이스 객체 사용을 관리하는 것이다.

 

 

사용자 생성

사용자 생성은 반드시 데이터베이스관리자(DBA) SYS 계정에서만 가능하다.

 

CREATE USER 계정
IDENTIFIED BY 비밀번호;



CREATE USER USER01 --유저명
IDENTIFIED BY ORACLE; --패스워드

 

이 로그인 패스워드만 맞으면 인증 허용한다.

계정을 생성했다고 해서 접속 후 바로 모든 작업을 할 수 있는게 아니다. 개별적인 인증과 권한이 필요하다.

 

 

비밀번호 변경

ALTER USER USER01
IDENTIFIED BY user01;

 

 

권한 생성

 

시스템권한 : CREATE USER, DROP USER, DROP ANY TABLE ...

사용자의 데이터베이스 접근권한 : CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW ... (접속권한, 테이블사용할수있는 권한)

 

 

GRANT CREATE SESSION, CREATE TABLE
TO USER01;

--  USER01 테이블에 오라클 접속권한과 테이블 생성권한을 부여

 

 

새 접속 생성

 

USER01 로 접속해서 가진 권한 보기

 

 

시스템 권한 회수

REVOKE CREATE SESSION
FROM USER01;

 

 

객체 권한

테이블, 뷰, 시퀀스, 프로시저 등을 사용할 수 있는 권한

 

-- 스콧이 USER01에게 테이블 중 DEPT만 공유했음
GRANT SELECT
ON DEPT
TO USER01;

 

DEPT불러올때 앞에 스키마 붙여줘야함. 스콧꺼라

 

-- SCOTT 계정에서 USER01계정에 권한 부여 

GRANT UPDATE(DNAME)
ON DEPT
TO USER01;

-- USER01 계정에서 데이터 수정

UPDATE SCOTT.DEPT
SET DNAME='인사'
WHERE DEPTNO = 40;

 

 

WITH GRANT OPTION

권한을 부여 받은 사람이 부여 받은 권한을 다른 사용자에게 다시 부여할 수 있는 방법

 

 

객체권한 회수

REVOKE 객체권한
ON 객체명
FROM 사용자계정|ROLE|PUBLIC
[CASCADE CONSTRAINTS];

 

 

롤

인터넷 카페를 관리할 때 관리자 권한을 주면 읽기, 쓰기, 회원관리 다 가능하다. 롤을 주는 것이다. 롤은 권한의 묶음이다. 부여된 롤은 활성화 비활성화가 가능하다.

 

오라클엔 기본적으로 제공되는 사전정의된(BUILT-IN)롤이 있다.

CONNECT 접속권한 롤

RESOURCE 테이블 사용권한 롤

DBA는 시스의 권한이라 일반 사용자에게 주면 안된다.

 

SELECT role
FROM dba_roles;

SYS 계정 접속해서 dba roles 데이터 사전을 통해서 빌트인 롤의 목록을 확인할 수있다.

GRANT connect, resource
To 계정명

이 두가지 롤을 부여하는게 가장 일반적이다.

 

 

사용자 롤 생성 및 사용

 

-- 사용자 계정생성
CREATE USER user02
IDENTIFIED BY user02


-- 롤 생성

--SYS
CREATE ROLE clerk;


-- 시스템 권한 부여

--SYS
GRANT CREATE SESSION, CREATE TABLE
TO clerk;

GRANT SELECT
ON SCOTT.DEPT
TO CLERK;


-- 사용자에게 clerk 롤 부여
--SYS
GRANT CLERK
TO user03;

 

롤은 동적으로 권한을 관리할 수 있다. 롤에 새로운 권한을 부여하면 자동으로 그 롤을 가진 사용자에게도 새로운 권한 부여된다. 회수도 마찬가지다.

 

-- 새 접속 생성 후 sys계정에서

create user 계정 IDENTIFIED by 비밀번호;
alter user 계정 account unlock;
grant connect, resource to 계정;

-- 요 세문장은 기억해두기