SQL - 객체 종류

데이터 사전

오라클 데이터베이스 테이블은 사용자 테이블과 데이터 사전으로 나뉜다.

• 사용자 테이블: 데이터베이스를 통해 관리할 데이터를 저장하는 테이블
• 데이터 사전: 데이터베이스를 구성하고 운영하는 데 필요한 모든 정보를 저장하는 특수한 테이블로 데이터베이스가 생성되는 시점에 자동으로 만들어진다.

데이터 사전에는 데이터베이스 메모리·성능·사용자·권한·객체 등 데이터베이스 운영에 중요한 데이터가 보관되어 있다.
따라서 오라클 데이터베이스는 사용자가 데이터 사전 정보에 직접 접근하거나 작업하는 것을 허용하지 않는다.
그 대신 데이터 사전 뷰를 제공하여 SELECT문으로 정보 열람을 할 수 있게 해 두었다.

데이터 사전 뷰는 용도에 따라 접두어를 지정하여 분류한다.

• USER_XXXX: 현재 데이터베이스에 접속한 사용자가 소유한 객체 정보
• ALL_XXXX: 현재 데이터베이스에 접속한 사용자가 소유한 객체 또는 다른 사용자가 소유한 객체 중 사용 허가를 받은 객체, 즉 사용 가능한 모든 객체 정보
• DBA_XXXX: 데이터베이스 관리를 위한 정보(데이터베이스 관리 권한을 가진 SYSTEM, SYS 사용자만 열람 가능)
• V$_XXXX: 데이터베이스 성능 관련 정보(X$_XXXX 테이블의 뷰)

-- SCOTT 계정에서 사용 가능한 데이터 사전
SELECT * FROM DICT;
SELECT * FROM DICTIONARY;

-- USER_ 접두어를 가진 데이터 사전
SELECT TABLE_NAME FROM USER_TABLES;

-- ALL_ 접두어를 가진 데이터 사전
SELECT OWNER, TABLE_NAME FROM ALL_TABLES;

-- DBA_ 접두어를 가진 데이터 사전(SYSTEM 계정)
SELECT * FROM DBA_TABLES;

-- DBA_USERS 사용
SELECT *
FROM DBA_USERS
WHERE USERNAME = 'SCOTT';

인덱스

인덱스: 오라클 데이터베이스에서 데이터 검색 성능의 향상을 위해 테이블 옆에 사용하는 객체

• Table Full Scan: 테이블 데이터를 처음부터 끝까지 검색하여 원하는 데이터를 찾는 방식
• Index Scan: 인덱스를 통해 데이터를 찾는 방식

-- 인덱스
-- SCOTT 계정이 소유한 인덱스 정보
SELECT * FROM USER_INDEXES;
-- SCOTT 계정이 소유한 인덱스 컬럼 정보
SELECT * FROM USER_IND_COLUMNS;

-- 인덱스 생성
CREATE INDEX IDX_EMP_SAL ON EMP(SAL);
SELECT * FROM USER_IND_COLUMNS;

-- 인덱스 삭제
DROP INDEX IDX_EMP_SAL;

뷰

뷰: 하나 이상의 테이블을 조회하는 SELECT문을 저장한 객체
SELECT문을 저장하기 때문에 물리적 데이터를 따로 저장하지는 않는다.
따라서 뷰를 SELECT문의 FROM절에 사용하면 특정 테이블을 조회하는 것과 같은 효과를 얻을 수 있다.

뷰의 목적은 다음과 같다.

• 편리성: 여러 SQL문에서 자주 활용하는 SELECT문을 뷰로 저장해 놓은 후 다른 SQL문에서 활용하면
  전체 SQL문의 복잡도를 완화하고 본래 목적의 메인 쿼리에 집중할 수 있어 편리하다.
• 보안성: 테이블의 일부 데이터 또는 조인이나 여러 함수 등으로 가공을 거친 데이터만 SELECT하는
  뷰 열람 권한을 제공하는 것이 불필요한 데이터 노출을 막을 수 있기 때문에 안전하다.

-- 뷰 생성
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;

인라인 뷰를 사용한 TOP-N SQL문

CREATE문을 통해 객체로 만들어지는 뷰 외에 SQL문에서 일회성으로 만들어서 사용하는 뷰를 인라인 뷰라고 한다.
SELECT문에서 사용되는 서브쿼리, WITH절에서 미리 이름을 정의해 두고 사용하는 SELECT문 등이 이에 해당한다.

-- 의사 열: 데이터가 저장되는 실제 테이블에 존재하지는 않지만
-- 특정 목적을 위해 테이블에 저장되어 있는 열처럼 사용 가능한 특수 열

-- ROWNUM
SELECT ROWNUM, E.* FROM EMP E ORDER BY SAL DESC;

-- 인라인 뷰(서브쿼리)
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 추출(서브쿼리)
SELECT ROWNUM, E.*
FROM (SELECT * FROM EMP E 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;

시퀀스

시퀀스: 오라클 데이터베이스에서 특정 규칙에 맞는 연속 숫자를 생성하는 객체

-- 시퀀스
CREATE TABLE DEPT_SEQUENCE AS SELECT * FROM DEPT WHERE 1<>1;

CREATE SEQUENCE SEQ_DEPT_SEQUENCE INCREMENT BY 10 START WITH 10 MAXVALUE 90 MINVALUE 0 NOCYCLE CACHE 2;

-- NEXTVAL: 다음 번호를 생성
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 SEQUENCE SEQ_DEPT_SEQUENCE INCREMENT BY 3 MAXVALUE 99 CYCLE;

INSERT INTO DEPT_SEQUENCE (DEPTNO, DNAME, LOC)
VALUES (SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE', 'SEOUL');
SELECT * FROM DEPT_SEQUENCE ORDER BY DEPTNO;

-- 시퀀스 삭제(시퀀스를 사용하여 추가된 데이터는 삭제되지 않음)
DROP SEQUENCE SEQ_DEPT_SEQUENCE;

동의어

동의어: 테이블·뷰·시퀀스 등 객체 이름 대신 사용할 수 있는 다른 이름을 부여하는 객체, 주로 테이블 이름이 너무 길어 불편할 때 좀더 간단하고 짧은 이름을 하나 더 만들어 주기 위해 사용

-- 동의어
CREATE SYNONYM E FOR EMP;
SELECT * FROM E;

-- 동의어 삭제
DROP SYNONYM E;