[SQL] Oracle, 객체 종류

데이터 사전

: 데이터베이스를 위한 데이터를 저장

ex. 데이터베이스 메모리, 성능, 사용자, 권한, 객체 등의 데이터가 저장되어 있다.

 

• 직접 접근 X
• SELECT문으로 정보열람 O(VIEW)

 

SELECT * FROM DICT;
--사용 가능한 데이터 사전 조회

 

-USER_접두어

:현재 접속한 사용자가 소유한 객체 정보

SELECT TABLE_NAME FROM USER_TABLES;

-ALL_접두어

: 현재 사용자의 사용이 허락되어 있는 객체 정보(소유권이 포함)

ex. ALL_TABLES

 

-DBA_접두어

: 데이터베이스 관리 권한을 가진 사용자만 조회가능

ex. DBA_TABLES, DBA_USERS: SYS계정으로 접근 시 조회 가능

 

---

인덱스

: 검색 성능 향상을 위해 테이블 열에 사용하는 객체

그러나 무분별한 인덱스 생성은 좋지 않다.

Table Full Scan, Index Scan 방식이 있다. 

 

-인덱스 조회

SELECT *
FROM USER_IND_COLUMNS;

 

-인덱스 생성

종류: 단일 인덱스, 고유 인덱스, 복합 인덱스, 함수 기반 인덱스...

CREATE INDEX IDX_EMP_SAL
    ON EMP(SAL);

 

-인덱스 삭제

DROP INDEX IDX_EMP_SAL;

---

뷰

: 가상테이블

사용 목적: 편리성과 보안성(테이블의 특정 열을 노출하고 싶지 않을 경우)

 

-뷰 생성

: SCOTT 계정은 뷰 생성 권한이 없다. 따라서 권한 부여가 필요함

SYSTEM계정으로 로그인 후 권한 부여

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;

 

-인라인 뷰, ROWNUM

SELECT ROWNUM, E.*
FROM (SELECT * FROM EMP E ORDER BY SAL DESC) E
WHERE ROWNUM <=3;

 

-인라인 뷰와 서브쿼리의 차이

• 인라인 뷰: SELECT문의 결과를 FROM절에서 사용하고 싶을 때
• 서브쿼리: SELECT문의 결과를 WHERE절에서 사용하고 싶을 때 

---

시퀀스

: 특정 규칙에 맞는 연속 숫자를 생성하는 객체

 

-시퀀스 생성

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;
--CACHE: 시퀀스가 생성할 번호를 메모리에 미리할당해 놓은 수

 

-시퀀스 사용

INSERT INTO DEPT_SEQUENCE(DEPTNO, DNAME, LOC)
VALUES(SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE', 'SEOUL');

SELECT * FROM DEPT_SEQUENCE ORDER BY DEPTNO;

 

-시퀀스 수정

ALTER SEQUENCE SEQ_DEPT_SEQUENCE
    INCREMENT BY 3
    MAXVALUE 99
    CYCLE;

SELECT * FROM DEPT_SEQUENCE ORDER BY DEPTNO;

 

-시퀀스 삭제

DROP SEQUENCE SEQ_DEPT_SEQUENCE;

---

동의어

: 객체 이름 대신 사용할 수 있는 다른 이름을 부여하는 객체

• 생성된 동의어는 다양한 SQL문에서 사용가능
• 일회성이 아니다
• 권한 부여가 필요

SYSTEM 계정으로 SCOTT계정에 권한 부여

- 동의어 생성

CREATE SYNONYM E
    FOR EMP;
    
SELECT * FROM E;

 

-동의어 삭제

DROP SYNONYM E;

---

-종합예제

--1
CREATE TABLE EMPIDX
AS SELECT * FROM EMP;


CREATE INDEX IDX_EMPIDX_EMPNO
    ON EMPIDX(EMPNO);
    
SELECT *
FROM USER_IND_COLUMNS;

--2
CREATE OR REPLACE VIEW EMPIDX_OVER15K
AS (SELECT EMPNO, ENAME, JOB, DEPTNO, SAL, NVL2(COMM, 'O', 'X') AS COMM FROM EMP WHERE SAL>1500);

SELECT * FROM EMPIDX_OVER15K;

--3
CREATE TABLE DEPTSEQ
AS (SELECT * FROM DEPT);

CREATE SEQUENCE DEPT_SEQUENCE
    INCREMENT BY 1
    START WITH 1
    MAXVALUE 99
    MINVALUE 1
    NOCYCLE
    NOCACHE;
    
INSERT INTO DEPTSEQ(DEPTNO, DNAME, LOC)
VALUES(DEPT_SEQUENCE.NEXTVAL, 'DATABASE', 'SEOUL');

SELECT * FROM DEPTSEQ;