오라클 - 객체 종류

 - 오라클 데이터 베이스는 데이터 보관 및 관리를 위한 여러 기능 과 저장 공간을 객체를 통해 제공함

 - 테이블은 SQL문과 더불어 오라클에서 가장 많이 사용하는 객체 중 하나 

 - 테이블 외 데이터 사전, 인덱스, 뷰, 시퀀스, 동의어 등 사용빈도가 높은 객체를 살펴보자 

 

1. 데이터 사전 (테이블 객체)

 

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

 - 데이터 사전은 데이터베이스를 구성하고 운영하는 데 필요한 모든 정보를 저장하는 특수 테이블 

   데이터베이스가 생성되는 시점에 자동으로 만들어 진다.

 - 데이터 사전에는 메모리,성능,사용자,권한,객체 등 운영에 중요한 데이터가 보관되어 있다.

 - 데이터 사전 뷰를 통해 정보를 열람해 보자 

 

 * 열람할 수 있는 데이터 사전 보기 

SELECT * FROM DICT;

 

 > USER_XXXX : 현재 데이터 베이스에 접속한 사용자가 소유한 객체 정보 

SELECT * FROM USER_TABLES;

 

> ALL_XXXX : 현재 접속한 사용자 소유 + 사용 허가 받은 객체 

SELECT * FROM ALL_TABLES;

 

>DBA_XXX: SYS,SYSTEM 권한을 가진 사용자만 열람가능

> V$_XXXX: 데이터 성능 관련 정보

 

 

2. 인덱스 

 

 - DB에서 검색 성능 향상 시키기 위해 테이블 열에 사용하는 객체 

 - 테이블에 보관된 특정 행 데이터 주소, 위치 정보를 목록으로 만들어 놓은 것 

 - 인덱스는 테이블의 여러 열을 여러 분석을 통해 선정하여 설정 가능하다.

 - 인덱스 사용 여부에 따라 검색 방식을 TABLE FULL SCAN, INDEX SCAN으로 구분합니다.

 - 인덱스도 객체이므로 소유 사용자와 사용 권한이 존재합니다. 

 - 인덱스 정보는 USER_INDEXS, USER_IND_COLUMNS의 사전을 사용하여 보자 

 

* 기본키와 고유키는 인덱스가 자동 생성됨

 

-----------------------인덱스 강의 자료 --------------------------------- (195p)

데이터의 빠른 검색을 위해 사용하는 색인 기술 (인덱스)

- B-tree자료 구조 이용하여 검색 속도 향상

- B-tree(Balanced Tree)란 이진트리의 변형된 알고리즘으로, 데이터를 빠르게 찾을 수 있도록 

  트리구조에 정렬한 상태로 보관하는 방식

 

-인덱스를 생성하는 순간 데이터가 정렬되고, 인덱스가 생성됨 

 ex (age [30,20,10,50,40] -> index -> age[정렬] 10(3),20(2),30(1),40(5),50(4)

  위와 같이 값과 인덱스 저장해서 조회시에  정렬된 age를 기반으로 빠르게 값을 찾아서 리턴

  이때 기존 age테이블에 새로운 데이터가 추가되거나, 삭제되거나 변경이 발생할 때 다시 정렬하는 과정이 필요하므로, 속  도가 느려지는 단점이 있다. 

 

- 인덱스 생성 

CREATE INDEX 인덱스명 ON 테이블명 (컬럼);

- 인덱스 실행 전 실행 계획 출력 

EXPLAIN PLAN FOR select * FROM emp WHERE ENAME = 'SMITH';
SELECT * FROM TABLE(dbms_xplan.display);
//각각 실행

  > 실행결과 TABLE ACCESS FULL / EMP/COST(3) -> 인덱스가 없기 때문에  테이블을 모두 SCAN해서 찾는다.

 

- 인덱스 생성 후 실행 계획 출력

CREATE INDEX empindex ON emp (ename);

EXPLAIN PLAN FOR SELECT * FROM EMP WHERE ENAME ='SMITH';
SELECT * FROM TABLE(dbms_xplan.display);

 > 인덱스 생성후 실행하면, INDEX RANGE SCAN / EMPINDEX 으로 변경되고 COST 1로 바뀌어 있다!

  

**인덱스를 사용해야할 컬럼은?

  -> WHERE 조건에서 자주 사용되는 컬럼, 조인 시 조건에 사용되는 컬럼

  -> WHERE절에 두 개 이상의 컬럼이 포함되는 조건인 경우 복합 인덱스 화 (ename, job)

  -> 복합 인덱스인 경우 조회 조건에 모든 컬럼이 동시에 사용되도록 지정 ( > WHERE절에 동시에 ename과 job을 사용해야 인덱스 적용됨)

  -> 주로 숫자, 날짜, char 타입에 적용한다. ( 가번문자열은 미적용 가능성이 높음) 

    

 

 ** 주의 사항 

 -> PRIMARY KEY, UNIQUE KEY 제약조건에서는 인덱스 자동 생성

 -> 해당 칼럼을 가공하기 전 상태에서 조건 지정 

 -> 인덱스 여부까지 데이터 모델링 시 고려할 필요가 있다.

 -> LIKE 연산은 인덱스 미적용 가능성이 높다 (% 앞뒤로 포함된 경우)  

 

- 인덱스 스캔범위 비교 (217p) 

 > 복합인덱스를 사용한다고 하더라도, WHERE 절 범위 지정 순서에 따라 인덱스 스캔범위 차이 있을 수 있다.

 

- 인덱스 설계 전략 (218p) 

 

-인덱스 외에 DB성능을 높이는 다른 방법 간단하게 살펴보기 

 > 정규화와 반정규화

    원래 테이블의 중복데이터 제거 등등을 위해 테이블 분리 -> 정규화

    데이터 조회시 조인등에 의한 성능 저하를 막기 위해 분리된 테이블을 다시 합침 -> 반정규화

 > 통계용 테이블 생성

--------------------------------------------------------------------------------------------------------------------------------------------------------

 

2.1 인덱스 생성 

 

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

 - CREATE문을 사용하여 index를 만들 수 있습니다. (정렬 순서도 정할 수도 있다 - 기본 오름차순) 

 - 인덱스가 걸린 SAL열을 WHERE의 검색 조건으로하여 EMP 테이블을 조회하면 출력 속도가 빨라질 것으로 예상

 - 인덱스를 설정하는 것은 데이터 구조 및 데이터 분포도 등 여러 조건을 고려하여 이루어져야 합니다. 

   (무조건 속도가 빨리진다고 보장할 수 없다)

 

*목적에 따라 여러 방식으로 생성할 수 있는 인덱스 종류들이 있는데, 추후에 꼭 알아보자

 

2.2 인덱스 삭제 

DROP INDEX 인덱스 이름;

 

- 인덱스는 데이터 접근 및 검색 속도 향상을 위해 사용하는 객체이지만, 꼭 좋은 결과로 이어지는 것은 아니다.

- 정확한 데이터 분석에 기반을 두고 인덱스를 생성해야한다. 

- 생성과 사용에 관련된 내용은 SQL튜닝 관련 서적을 찾아봅시다! 

 

3. 뷰

 

 - 가상 테이블을 의미 한다. 특정 SELECT문을 저장한 객체이다. (뷰를 생성할 때 수행한 SELECT문을 저장)

 - SELECT문을 저장하기 때문에 물리적 데이터를 따로 저장하지 않습니다. 

 - 뷰를 FROM절에 사용하면 특정 테이블을 조회하는 것과 같습니다! (서브쿼리와 같아!)

 

3.1 뷰의 사용 목적(편리성)

 

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

 - 보안성: 테이블 특정 열을 노출하고 싶지 않은 경우

                 (그냥 테이블 불러다가 쓰면 안되는 민감한 정보가 포함되어 있는 경우..!)

 

ex)  5개의 테이블을 조인해서 결과를 얻어야할 때 -> 테이블을 새로 짜면 좋지 않을까..?라는 생각이 들 때 

                                                                               -> 하지만 테이블을 새로 만드는 것은 비효율적

                                                                               -> 5개 테이블 join한 결과를 view로 만들어서 활용

      

*하지만 뷰는 새로운 데이터가 추가되었을 때 실시간으로 뷰에 반영되지 않음

-> 데이터 추가시 마다 view 새로 생성해야함 따라서 특별한 목적 외 엄청 자주 사용하지 않는다고함 

 

3.2 뷰 생성

 

 - CREATE문으로 생성할 수 있다. (뷰 생성할 수 있는 권한이 있어야함)

--sys계정
grant create view to testuser;

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 뷰이름(열이름,열이름..)
AS (저장할 SELECT 문)
[WITH CHECK OPTION [CONSTRAINT 제약조건]]
[WITH READ ONLY[CONSTRAINT 제약조건]];

OR REPLACE	이름 겹칠 때 다른 이름(선택)
FORCE	뷰가 저장할 SELECT문의 기반 테이블이 존재하지 않아도 만들기 강제(선택)
NOFORCE	기반 테이블 존재시에만 생성(기본값)
뷰이름	생성할 뷰 이름
열 이름	SELECT문에 명시된 이름 대신 사용할 열 이름 지정 (생략가능)
저장할 SELECT	생성할 뷰에 저장할 SELECT문 지정
WITH CHECK OPTION	지정한 제약조건 만족하는 데이터에 한해 DML작업이 가능하도록 뷰 생성
WITH READ ONLY	뷰의 열람, 즉 SELECT만 가능하도록 뷰 생성

CREATE VIEW VW_EMP20
AS (SELECT EMPNO, ENAME, JOB, DEOTNO FROM EMP WHERE DEPTNO = 20);

SELECT * FROM USER_VIEWS; -- 뷰 정보 확인 가능

 

3.3 뷰 삭제 

DROP VIEW 뷰이름

 

 

* 뷰와 데이터 조작어 

  의외로 뷰에 데이터 삽입-수정-삭제 같은 데이터 조작어 사용이 가능하다! 근데 잘은 안한다! 

  데이터를 따로 저장하는 것이 허용되는 구체화뷰도 있다.

 

4. 시퀀스 (규칙에 따라 순번을 생성)

 

 4.1 시퀀스란? 

 

  - 특정 규칙에 맞는 연속 숫자를 생성하는 객체입니다. (대기 순번표)

  - 단지 연속하는 새로운 번호를 만든다면, MAX함수 +1 을 활용할 수도 있다.

SELECT MAX(글번호) +1 FROM 게시판 테이블

 -> 이 방법은 쉽지만 동시에 SELECT문을 요구했을 경우, 데이터를 찾고 더하는 과정이 아쉽다.

 -> 시퀀스는 이런점을 보완해 줄 수 있다.

 

4.2 시퀀스 생성 

CREATE SEQUENCE 시퀀스명 
[INCREMENT BY n]
[START WITH n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE]

INCREMENT N	시퀀스 생성할 번호의 증가값 (기본값 1)
START WITH	시퀀스 생성 번호 시작 값
MAXVALUE n	생성할 번호 최대값 지정, 시작값과 최소값 사이,   NOMAXVALUE일 경우 오름차순 10^27, 내림차순 -1 (선택)
MINVALUE n	MAX와 반대 NOMINVALUE일 경우 오름차순 1 내림차순 -10^26 (선택)
CYCLE	최대값 도달시 다시 시작값으로
CACHE	시퀀스가 생성할 번호를 메모리에 미리 할댕해 놓은 수 지정 (생략시 기본 20 (선택))
SEQUENCE	시퀀스 이름 설정

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

 

4.3 시퀀스 사용

--시퀀스 이용하여 업데이트할 테이블 생성
CREATE TABLE DEPT_SEQUENCE 
AS SELECT * FROM DEPT WHERE 1<>1;

- sql-developer 이용하여 시퀀스 생성

시퀀스 명  SEQ_DEPT_SEQUENCE로 바꿨음

-- currval을 하고 싶다면 먼저 nextval을 해야함 
SELECT SEQ_DEPT_SEQUENCE.nextval from dual;

SELECT SEQ_DEPT_SEQUENCE.currval from dual;

 

 - 생성된 시퀀스 사용시에는 시퀀스명.CURRVAL 혹은 시퀀스명.NEXTVAL을 사용할 수 있다.

   *CURRVAL은 시퀀스 생성 후 바로 사용시 오류 

 

--시퀀스를 이용하여 테이블에 값 삽입
INSERT INTO DEPT_SEQUENCE(DEPTNO, DNAME,LOC)
VALUES(SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE','SEOUL');

SELECT * FROM DEPT_SEQUENCEl

 

4.4 시퀀스 수정 및 삭제

 

 - ALTER 명령어를 통해 시퀀스 수정하고 DROP를 통해 삭제할 수 있다.

 - 시퀀스 수정 몇가지 옵션을 수정할 수 있는데 START WITH은 변경 불가하다.

ALTER SEQUENCE SEQ_DEPT_SEQUENCE
INCREMENT BY 3
MAXVALUE 99
CYCLE;

 -> 시퀀스 삭제 

DROP SEQUENCE SEQ_DEPT_SEQUENCE;

 - 시퀀스를 삭제해도 시퀀스를 사용하여 추가된 데이터는 삭제되지 않는다.

 

 

5. 공식 별칭을 지정하는 동의어 

 

5.1 동의어란 

 

 - 테이블,뷰,시퀀스 등 객체 이름 대신 사용할 수 있는 다른 이름을 부여하는 객체이다. 

 - 주로 테이블 명이 너무 길어서  불편할 때 사용한다. (별칭 하나 더 생성)

CREATE [PUBLIC] SYNONYM 동의어 이름
FOR [사용자.][객체명];

PUBLIC	동의어를 모든 사용자가 사용할 수 있도록 함. 생략시 동의어 생성한 사용자만 사용자만 가능
동의어 이름	필수
사용자.	본래 객체 소유자 지정, 생략 시 현재 접속한 사용자(선택)
객체명	동의어 생성 대상 객체(필수)

 -> 이렇게 사용한 동의어는 다양한 SQL에서 사용할 수 있다.

 -> SELECT문 SELCT, FROM에서 사용한 별칭과 다르게 일회성이 아니다.

 

*열 별칭을 만들기 위한 권한 설정해줘야함! 

 

--생성
CREATE SYNONYM E FOR EMP;
--삭제
DROP SYNONYM E;