오라클 객체 종류

객체 종류 설명
테이블(Table) 데이터를 저장하기 위한 가장 기본적인 논리적 데이터 저장 구조
행과 열로 구성됨
시퀀스(Sequence) 일련번호를 생성하는 객체. 숫자 데이터를 일정한 방향으로 증가하도록 만들어주는 메커니즘
→ Primary Key(PK)에서 자주 쓰임
뷰(View) - 가상 테이블
- 실제 데이터를 저장하는 데이터 저장 장치가 아니라 하나 또는 그 이상의 테이블의 데이터를 참조하여 단순히 보여주는 역할만 수행하는 객체
인덱스(Index) 테이블의 검색 효율을 높이기 위해 테이블 내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조 (ROWID)
시노님(Synonym) 데이터베이스 객체에 대한 별칭을 부여하는 객체

 

Sequence(시퀀스)

  • 일련번호를 생성하는 객체
  • 숫자 데이터를 일정한 방향으로 증가하도록 만들어주는 메커니즘
  • Primary Key(PK)에서 자주 쓰임
  • 시퀀스는 테이블과는 독립적으로 저장되고 생성된다. 따라서 하나의 시퀀스를 여러 테이블에서 쓸 수 있다.
CREATE SEQUENCE 시퀀스명
[INCREMENT BY n] --n만큼 증가
[START WITH n] --시작값으로 n을 설정
[ {MAXVALUE n | NOMAXVALUE} ] --최댓값으로 n을 설정 (default = 9999999999999999999999999999)
[ {MINVALUE n | NOMINVALUE} ] --최솟값으로 n을 설정
[ {CYCLE | NOCYCLE} ] --1부터 다시 사이클 | maxvalue에 도달하면 끝  (default = NOCYCLE)
[ {CACHE | NOCACHE} ] --cache를 쓰겠다 | 쓰지 않겠다 (default = NOCACHE)

 

CACHE: 메모리에 시퀀스 값을 미리 할당한다.

CACHE 10이 설정되어있을 경우, Sequence 번호를 한번에 10개씩 메모리에 올려놓고 작업을 한다. 만일 메모리에 21~30번까지 시퀀스 번호를 올려놓았다고 가정할 때, DB를 재시작하면 메모리에 있던 21~30번은 삭제되고 31~40번까지의 시퀀스 번호가 새로 올라가기 때문에 21번~30번의 시퀀스 번호가 존재하지 않을 수 있다.

 

NOCACHE: 메모리에 시퀀스 값을 미리 할당하지 않는다.

 

시퀀스 생성

--dept의 pk로 사용되는 deptno 값으로 사용할 시퀀스를 만들어보자.
CREATE SEQUENCE dept_deptno_seq
START WITH 60 --60부터 시작
INCREMENT BY 10 --10씩 증가
MAXVALUE 99 --99까지
MINVALUE 60 --60부터
NOCACHE
NOCYCLE;

 

데이터 사전 조회

SELECT * FROM user_sequences WHERE sequence_name='DEPT_DEPTNO_SEQ';
SELECT * FROM user_objects WHERE object_type='SEQUENCE';

 

 

시퀀스 사용법

CURRVAL: 시퀀스의 현재값 (맨 처음에는 확인 불가. NEXTVAL 1회 실행 후 확인 가능)

SELECT dept_deptno_seq.CURRVAL FROM dual;
--현재 시퀀스값 확인

 

NEXTVAL: 시퀀스의 다음값 (CURRVAL + INCREMENT BY n)

SELECT dept_deptno_seq.NEXTVAL FROM dual;
--다음 시퀀스값 확인

예제

dept 테이블

 

dept 테이블의 PK로 사용되는 deptno 값으로 사용할 시퀀스를 만들어보자.

CREATE SEQUENCE dept_deptno_seq
START WITH 60
INCREMENT BY 10
MAXVALUE 99
MINVALUE 60
NOCACHE
NOCYCLE;

 

INSERT INTO dept(deptno, dname, loc)
VALUES(dept_deptno_seq.NEXTVAL, '홍보부', '인천'); --60

INSERT INTO dept(deptno, dname, loc)
VALUES(dept_deptno_seq.NEXTVAL, '기획부', '수원'); --70

INSERT INTO dept(deptno, dname, loc)
VALUES(dept_deptno_seq.NEXTVAL, '영업부1', '수원'); --80

INSERT INTO dept(deptno, dname, loc)
VALUES(dept_deptno_seq.NEXTVAL, '영업부2', '수원'); --90

INSERT INTO dept(deptno, dname, loc)
VALUES(dept_deptno_seq.NEXTVAL, '영업부3', '수원');
--ORA-08004: sequence DEPT_DEPTNO_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

SELECT dept_deptno_seq.CURRVAL FROM dual;

 

 

시퀀스 수정

ALTER SEQUENCE 시퀀스명
INCREMENT BY n
MINVALUE n
MAXVALUE n
CYCLE | NOCYCLE
CACHE n | NOCACHE;

 

[주의] START WITH(초기값)은 변경할 수 없다.

초기값을 변경하려면 시퀀스를 지우고 다시 만들어야 한다.

ALTER SEQUENCE DEPT_DEPTNO_SEQ
START WITH 7;
--오류 보고 -
--ORA-02283: cannot alter starting sequence number

 

 

시퀀스 삭제

DROP SEQUENCE 시퀀스명;
--dept_deptno.seq를 삭제하세요
DROP SEQUENCE dept_deptno_seq;

 

 

View(뷰)

  • 하나의 가상 테이블 또는 테이블에 대한 데이터베이스 관리 시스템에 저장된 SELECT 구문
  • 실제 데이터를 저장하는 데이터 저장 장치가 아니라, 하나 또는 그 이상의 테이블의 데이터를 참조하여 단순히 보여주는 역할만 수행한다.
  • 데이터를 보거나 변경할 수 있는 창과 같다.
  • [장점]
    • 데이터의 복잡성을 감소시킴
    • 복잡한 질의를 감소시킴
    • 기본 테이블의 데이터를 다양한 관점으로 보여줌

 

뷰 생성

CREATE [OR REPLACE] VIEW 뷰이름 --뷰를 생성하거나 기존의 뷰에 새로운 뷰를 덮어씌울 경우(or replace)
AS
SELECT 컬럼명1, 컬럼명2 ... FROM 뷰에 사용할 테이블명 WHERE 조건;

 

예시

--emp 테이블에서 부서번호가 20인 사원들의 정보를 뷰(view)로 만드세요.
--뷰 이름: emp20_view
CREATE OR REPLACE VIEW emp20_view
AS
SELECT * FROM emp WHERE deptno = 20;

emp20_view

--emp테이블에서 30번 부서만 EMPNO를 EMP_NO로, ENAME을 NAME으로,
--SAL를 SALARY로, DEPTNO를 DNO로 바꾸어 EMP30_VIEW를 생성하세요.
CREATE OR REPLACE VIEW emp30_view
AS
SELECT empno AS emp_no, ename AS name, sal AS salary, deptno AS dno
FROM emp
WHERE deptno = 30;

emp30_view

--원테이블(emp)에서 WARD가 10번 부서로 부서이동을 하면 VIEW는 어떻게 될까?
UPDATE emp SET deptno = 10 WHERE ename = 'WARD';
SELECT * FROM emp30_view; --WARD가 VIEW에서 빠짐
--VIEW를 수정하면 어떻게 될까?
UPDATE emp30_view SET salary=3000 WHERE name = 'BLAKE'; --원테이블에서도 BLAKE의 급여가 변경됨

WARD가 빠짐
emp30_view: salary 3000으로 수정
emp: sal이 3000으로 수정됨 (원래 2850이었음)

 

 

WITH READ ONLY

VIEW를 수정하면 원테이블도 수정되고, 원테이블을 수정하면 VIEW도 수정된다.

 

만약 VIEW를 수정하지 못하게 하려면 VIEW를 생성할 때 WITH READ ONLY (읽기전용) 옵션을 준다.

CREATE [OR REPLACE] VIEW 뷰이름 --뷰를 생성하거나 기존의 뷰에 새로운 뷰를 덮어씌울 경우(or replace)
AS
SELECT 컬럼명1, 컬럼명2 ... FROM 뷰에 사용할 테이블명 WHERE 조건
WITH READ ONLY;

 

 

[예제]

--고객 테이블의 고객 정보 중 나이가 19세 이상인 고객의 정보를 확인하는 뷰를 만들어보세요.
--단, 뷰의 이름은 MEMBER_19VIEW로 하고, 덮어쓰기 가능 및 읽기전용으로 만드세요.
CREATE OR REPLACE VIEW member_19view
AS
SELECT * FROM member WHERE age >= 19
WITH READ ONLY;

 

=> 원테이블 변경 가능(뷰에도 영향 미침), 뷰는 변경 불가능

--원 테이블 변경: member에서 아이디가 id1인 회원의 나이를 17세로 수정하세요
UPDATE member SET age=17 WHERE userid='id1';
SELECT * FROM member_19view ORDER BY AGE; --id1인 회원이 뷰에서도 제거됨

--member_19view에서 아이디가 'id3'인 회원의 마일리지를 500점 추가하세요
UPDATE member_19view SET mileage = mileage + 500 WHERE userid='id3';
--SQL 오류: ORA-42399: cannot perform a DML operation on a read-only view

 

 

테이블들을 조인한 뷰는 읽기전용

--카테고리, 상품, 공급업체를 JOIN한 VIEW를 만드세요 (뷰 이름: prod_view)
CREATE OR REPLACE VIEW prod_view
AS
SELECT * FROM CATEGORY C JOIN PRODUCTS P
ON c.category_code = p.category_fk
JOIN SUPPLY_COMP S
ON p.ep_code_fk = s.ep_code;

SELECT category_name, products_name, output_price, ep_name FROM prod_view;

 

3개의 관련된 테이블들을 조인하여 기본 테이블의 데이터를 다양한 관점으로 보여줄 수 있게 되었고, 코드도 훨씬 간결해졌다.

 

한편, JOIN문으로 생성된 뷰는 읽기 전용으로만 사용이 가능하다. 뷰를 변경하려고 시도하면 오류가 난다.

UPDATE prod_view SET category_name='텔레비전' WHERE cnum=2;

 

 

WITH CHECK OPTION

  • WHERE절의 조건을 엄격하게 유지하도록 제한함
  • WHERE절에 사용된 컬럼값을 변경하지 못한다.
CREATE [OR REPLACE] VIEW 뷰이름 --뷰를 생성하거나 기존의 뷰에 새로운 뷰를 덮어씌울 경우(or replace)
AS
SELECT 컬럼명1, 컬럼명2 ... 
FROM 뷰에 사용할 테이블명 
WHERE 조건
WITH CHECK OPTION;

 

[예제]

CREATE OR REPLACE VIEW emp20vw
AS
SELECT * FROM emp
WHERE deptno=20 --deptno=20에서 다른 부서로 이동 불가능
WITH CHECK OPTION CONSTRAINT emp20vw_ck;

emp20vw

--deptno=20인 직원의 정보를 변경하자.
UPDATE emp20vw SET sal=sal+500 WHERE empno=7369; --sal은 변경 가능
UPDATE emp20vw SET deptno=30 WHERE empno=7369; --deptno는 변경 불가능
--ORA-01402: view WITH CHECK OPTION where-clause violation

 

 

데이터 사전 조회

  • user_view
  • user_objects;
SELECT * FROM user_views WHERE view_name = 'EMP20VW';
SELECT text FROM user_views WHERE view_name = 'EMP20VW';
SELECT * FROM user_objects WHERE object_name = 'EMP20VW';

 

 

뷰 삭제

DROP VIEW 뷰이름
--emp20vw를 삭제하고, 데이터 사전에서 조회해보세요.
DROP VIEW emp20vw;
SELECT * FROM user_views WHERE view_name = 'EMP20VW';

삭제됨

 

Index(인덱스)

  • 테이블 값에 빠르게 접근할 수 있도록 하는 데이터베이스 객체
  • 보다 빨리 데이터를 검색할 수 있도록 도와주는 선택적 구조
  • 일반 사전에서 ABCD 순으로 정렬되면 단어를 찾을 때 검색이 용이하듯, SQL에서는 인덱스를 이용하여 데이터를 신속하게 검색하도록 한다.
  • 특정 테이블에서 인덱스를 이용하면 컬럼의 자료가 내부에서 순차적으로 정렬되어 자료 검색시 정렬된 값을 이용해 자료를 검색할 수 있다.
  • 인덱스를 최적으로 구성하여 사용하면 데이터 파일에 대한 I/O를 줄일 수 있고, 성능을 향상시킬 수 있다.
  • 그러나 테이블에 과도한 인덱스 사용한 테이블을 입력, 수정, 삭제할 때 오히려 많은 과부하를 발생시킬 수 있다. 따라서 언제, 어디에 인덱스를 만들지 잘 고려해서 사용해야 한다.

인덱싱할 테이블 선택

  • 쿼리가 적은 양의 결과를 가져오는 경우 테이블에 인덱스를 인덱스를 사용한다.
    (테이블 전체 행의 10~15% 미만이 검색 대상이 되는 경우)
  • 수정이 많은 테이블에서는 인덱싱을 하지 않는다.
    WHERE절이 자주 사용되는 테이블에 대해 인덱스를 생성한다.

인덱싱할 컬럼의 결정

  • WHERE절에서 가장 빈번하게 지정되는 컬럼을 선택한다.
  • 유일한 값을 갖는 컬럼을 인덱스로 사용한다.
  • 두 개 이상의 테이블을 조인하기 위해 사용되는 컬럼을 인덱싱한다.
  • 자주 수정되는 컬럼은 인덱싱을 하지 않는다.

→ PK, FK가 이러한 조건들에 가장 만족한다.

 

 

INDEX 생성

  1. 자동 생성
    PK나 UNIQUE 제약 조건을 정의하면 UNIQUE 인덱스가 자동적으로 생성된다.
  2. 사용자가 생성
    column에 UNIQUE 인덱스 또는 NON-UNIQUE 인덱스를 생성한다.
    * unique index: 지정된 열의 값이 고유함을 보장
    * non-unique index: 지정된 열의 값에 중복을 허용
CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명 (컬럼명)
--emp테이블의 사원명(ename)을 인덱싱하세요.
CREATE INDEX emp_ename_indx ON emp (ename); --NON-UNIQUE 인덱스

 

데이터 사전에서 조회

  1. user_objects
  2. user_indexes
  3. user_ind_columns
--[1] user_objects
SELECT * FROM user_objects
WHERE object_type='INDEX' AND object_name='EMP_ENAME_INDX';
--[2] user_indexes
SELECT * FROM user_indexes
WHERE index_name = 'EMP_ENAME_INDX';
--[3] user_ind_columns
SELECT * FROM user_ind_columns
WHERE table_name ='EMP';

 

 

검색속도 차이 비교

색인 전: 0.003초

SELECT * FROM emp WHERE ename LIKE '%E%';

 

색인 후: 0.001초 >> 검색 속도가 빨라졌다.

CREATE INDEX emp_ename_indx ON emp (ename);
SELECT * FROM emp WHERE ename LIKE '%E%';

 

인덱스 삭제

  • 인덱스 수정은 없다. 인덱스를 고치고 싶다면 삭제하고 다시 생성하는 방법밖에 없다.
DROP INDEX 인덱스명;
--emp_ename_indx를 삭제하세요.
DROP INDEX emp_ename_indx;

SELECT * FROM user_indexes
WHERE index_name = 'EMP_ENAME_INDX'; --검색 결과 없음

 

 

Synonym(동의어)

  • 오라클 객체(테이블, 뷰, 시퀀스, 프로시저 등)에 대한 별칭(alias)
  • 즉, 객체에 대한 참조를 의미한다.
  • 다른 유저의 객체를 참조할 때 많이 사용한다.
  • 동의어 사용시 장점
    1. 객체의 긴 이름을 간편한 이름으로 참조하여 SQL코딩을 단순화할 수 있다.
    2. 객체를 참조하는 사용자의 오브젝트를 감출 수 있어 보안을 유지할 수 있다.

 

동의어 생성

CREATE [PUBLIC] SYNONYM 시노님명 FOR 객체명(스키마.테이블명);
CREATE SYNONYM A FOR mystar.note;
--mystar유저의 note테이블에 대한 시노님 만들기 (시노님명: A)
Microsoft Windows [Version 10.0.22621.3155]
(c) Microsoft Corporation. All rights reserved.

C:\Users\user>sqlplus system/oracle --오라클에 접근

SQL*Plus: Release 11.2.0.2.0 Production on 목 2월 22 09:47:47 2024

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> show user
USER is "SYSTEM"
SQL> CREATE USER mystar
  2  identified by mystar; //mystar의 비밀번호: mystar

User created.

SQL> conn mystar/mystar
ERROR:
ORA-01045: user MYSTAR lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> show user
USER is ""
SQL> conn system/oracle
Connected.
SQL> grant connect.resource to mystar;
grant connect.resource to mystar
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege


SQL> grant connect, resource to mystar;

Grant succeeded.

SQL> conn mystar/mystar
Connected.
SQL> show user
USER is "MYSTAR"
SQL> create table note(
  2  no number(2),
  3  msg varchar2(30)
  4  );

Table created.

SQL> desc note;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NO                                                 NUMBER(2)
 MSG                                                VARCHAR2(30)

SQL> insert into note values(1, '안녕하세요');

1 row created.

SQL> insert into note values(2, '반가워요~');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from note;

        NO MSG
---------- ------------------------------------------------------------
         1 안녕하세요
         2 반가워요~

SQL> show user
USER is "MYSTAR"
SQL> grant all on note to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> select * from mystar.note;

        NO MSG
---------- ------------------------------------------------------------
         1 안녕하세요
         2 반가워요~

SQL> show user
USER is "SCOTT"
SQL> insert into mystar.note values(3, '저는 스콧이에요');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mystar.note;

        NO MSG
---------- ------------------------------------------------------------
         1 안녕하세요
         2 반가워요~
         3 저는 스콧이에요

SQL> show user
USER is "SCOTT"
SQL> create synonym A for mystar.note;
create synonym A for mystar.note
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> conn system/oracle
Connected.
SQL> grant create synonym to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> show user
USER is "SCOTT"
SQL> create synonym A for mystar.note;

Synonym created.

SQL> select * from A;

        NO MSG
---------- ------------------------------------------------------------
         1 안녕하세요
         2 반가워요~
         3 저는 스콧이에요

SQL> update A set msg='동의어를 생성했어요' where no=1;

1 row updated.

SQL> select * from A;

        NO MSG
---------- ------------------------------------------------------------
         1 동의어를 생성했어요
         2 반가워요~
         3 저는 스콧이에요

SQL> commit;

Commit complete.

 

동의어 삭제

DROP SYNONYM 시노님명;
저작자표시 비영리 변경금지

+ Recent posts

티스토리툴바