2020년 11월 09일

업데이트: November 09, 2020

SEQUENCE

순차적으로 정수 값을 자동으로 생성하는 객체로 자동 번호 발생기 역할을 하는 객체이다.

/*
[작성법]
1) 생성
CREATE SEQUENCE 시퀀스명
[START WITH 정수] -- 처음 발생시킬 시작 값 지정, 생략 시 기본 값 1로 지정
[INCREMENT BY 정수] -- 다음 값에 대한 증가치, 생략 시 기본 값 1로 지정
[MAXVALUE 정수 | NOMAXVALUE]  -- 발생 시킬 최대값을 지정(10^27 -1), NOMAXVALUE 기본값
[MINVALUE 정수 | NOMINVALUE]  -- 발생시킬 최소값을 지정(-10^26), NOMINVALUE 기본값
[CYCLE | NOCYCLE]  -- 값 순환 여부를 지정, NOCYCLE 기본 값
[CACHE 바이트크기 | NOCACHE]  -- 캐쉬메모리크기 지정, 최소값 2바이트  -- NOCACHE 기본값 20바이트

* 시퀀스의 캐쉬메모리는 할당된 크기만큼 미리 다음 값을 생성해서 저장해둠
--> 다음 시퀀스 호출 시 저장된 값을 반환하므로 속도가 빠름(DB 속도 향상)
*/


-- 1. 시퀀스 생성
CREATE SEQUENCE SEQ_EMP_ID
START WITH 300
INCREMENT BY 5
MAXVALUE 310
NOMINVALUE
NOCYCLE
NOCACHE;


-- 사용자가 생성한 시퀀스 확인
SELECT * FROM USER_SEQUENCES;


-- 2. 시퀀스 사용
/*

[작성법]
1) 현재 생성된 시퀀스 값 반환
시퀀스명.CURRVAL

2) 시퀀스 값을 INCETMENT 값 만큼 증가시킨 값 반환
시퀀스AUD.NEXTVAL
*/

SELECT SEQ_EMP_ID.CURRVAL FROM DUAL;
-- CURRVAL는 마지막에 호출된 NEXTVAL의 값을 저장하는 변수같은 개념


--> NEXTVAL를 먼저 호출!
SELECT SEQ_EMP_ID.NEXTVAL FROM DUAL;  -- 초기값 300
SELECT SEQ_EMP_ID.CURRVAL FROM DUAL;  -- 현재값 300
SELECT SEQ_EMP_ID.NEXTVAL FROM DUAL;  -- 305
SELECT SEQ_EMP_ID.NEXTVAL FROM DUAL;  -- 310
SELECT SEQ_EMP_ID.NEXTVAL FROM DUAL; -- 에러(최대값 초과)

SELECT * FROM USER_SEQUENCES;

/*
시퀀스(CURRVAL, NEXTVAL) 사용 가능/불가능 SQL문

1) 사용 가능 
    - 메인쿼리(서브쿼리가 아닌 SELECT문)의 SELECT 절
    - INSERT문의 서브쿼리 SELECT절
    - INSERT문의 VALUES절
    - UPDATE문의 SET절
    
2) 사용 불가능
    - VIEW의 SELECT절
    - DISTINCT가 있는 SELECT절
    - GROUP BY, HAVING, ORDER BY절이 포함된 SELECT문
    - SELECT, DELETE, UPDATE문의 서브쿼리
    - CREATE TABLE, ALTER TABLE의 DEFAULT 값

*/

CREATE SEQUENCE SEQ_EID
START WITH 300  -- 시작값 300
--INCREMENT 1  -- 1씩 증가
MAXVALUE 10000-- 최대값 1만
--NOMINVALUE-- 최소값 없음
--NOCYCLE-- 반복 없음
CACHE 30 ;-- 캐시 30바이트


SELECT * FROM USER_SEQUENCES;

COMMIT;

INSERT INTO EMPLOYEE
VALUES(SEQ_EID.NEXTVAL, '홍길동', '001109-1234567', 'hogn-gd@kh.or.kr', '01012341234',
       'D2', 'J7', 'S1',
        6000000, 0.1, 200, SYSDATE, NULL, DEFAULT);


-- 홍길동 사번 확인
SELECT * FROM EMPLOYEE
WHERE EMP_NAME = '홍길동';  -- 300



INSERT INTO EMPLOYEE
VALUES(SEQ_EID.NEXTVAL, '홍길순', '001109-2234567', 'hong-gs@kh.or.kr', '01012345678', 
       'D2', 'J7', 'S1',
        6000000, 0.1, 200, SYSDATE, NULL, DEFAULT);

-- 홍길순 사번 확인
SELECT * FROM EMPLOYEE
WHERE EMP_NAME = '홍길순';  -- 301


/*
3. 시퀀스 변경
[작성법]
ALTER SEQUENCE 시퀀스 명
[INCREMENT BY 정수] 
[MAXVALUE 정수 | NOMAXVALUE] 
[MINVALUE 정수 | NOMINVALUE] 
[CYCLE | NOCYCLE] 
[CACHE 바이트크기 | NOCACHE] ;

--> START WITH는 변경 불가능

* 만약 START WITH를 변경하고 싶다면
   DROP SEQUENCE 시퀀스명; --> 삭제 진행 후
   
   CREATE SEQUENCE 시퀀스명; ---> 다시 생성


*/

ALTER SEQUENCE SEQ_EMP_ID
INCREMENT BY 10
MAXVALUE 400
MINVALUE 200
CYCLE ;


SELECT SEQ_EMP_ID.CURRVAL FROM DUAL;
SELECT SEQ_EMP_ID.NEXTVAL FROM DUAL;

INDEX

SQL 명령문의 처리 속도를 향상시키기 위해서 컬럼에 대해 생성하는 오라클 객체로 내부 구조는 B*트리 형식으로 구성되어있다.

장점

검색 속도가 빨라지고 시스템에 걸리는 부하를 줄여 시스템 전체 성능을 향상시킨다.

단점

인덱스를 위한 추가 저장 공간이 필요하고 인덱스를 생성하는데 시간이 걸린다. 데이터의 변경 작업(INSERT/UPDATE/DELETE)이 자주 일어날 경우엔 오히려 성능이 저하되니 주의해야한다.

/* 인덱스 생성 방법
CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(컬럼명 | 함수명 | 계산식, ....);
*/

-- 인덱스 구조
---> ROWID : DB 내 데이터 공유 주소
SELECT ROWID, EMP_ID, EMP_NAME
FROM EMPLOYEE;
-- AAAE5e  AAB  AAALC5   AAA
-- 1 ~  6 : 데이터 오브젝트 번호
-- 7 ~ 9 : 파일 번호
-- 10 ~ 15 : BLOCK 번호
-- 16 ~ 18 : ROW 번호

-- 데이터베이스는 저장 순서가 지켜지지 않음  --> 그래서 ORDER BY 절을 사용함

-- 테이블 제약 조건 중 PK로 지정된 컬럼은 자동으로 INDEX 생성 (★★★★★)

-- 인덱스 활용 방법

-- 1) 인덱스를 활용하지 않은 SELECT문
SELECT EMP_ID, EMP_NAME
FROM EMPLOYEE; -- 0.007초

-- 2) 인덱스를 활용한 SELECT문
SELECT EMP_ID, EMP_NAME
FROM EMPLOYEE
WHERE EMP_ID > 0;  -- 0.005초
-- WHERE절에 인덱스가 설정된 컬럼이 포함되면 됨
--> 데이터가 26개 밖에 없어서 속도 차이 안 보임





-- 인덱스 테스트용 테이블 생성
CREATE TABLE BOARD(
    BOARD_NO NUMBER PRIMARY KEY,
    BOARD_TITLE VARCHAR2(300) NOT NULL,
    BOARD_CONTENT CLOB NOT NULL,
    BOARD_COUNT NUMBER DEFAULT 0,
    BOARD_CREATE_DT DATE DEFAULT SYSDATE,
    BOARD_MODIFY_DT DATE DEFAULT SYSDATE,
    BOARD_STATUS CHAR(1) DEFAULT 'Y' CHECK(BOARD_STATUS IN ('Y','B','N')),
    BOARD_WRITER NUMBER NOT NULL,
    BOARD_CATEGORY NUMBER NOT NULL,
    BOARD_TYPE NUMBER NOT NULL
);

CREATE SEQUENCE SEQ_BNO
START WITH 1
INCREMENT BY 1
MAXVALUE 10000000
NOCYCLE
NOCACHE;


/* PL/SQL (Procedural Language extension to SQL)
    - 오라클 자체에 내장되어있는 절차적 언어
    - SQL 문장 내에서 변수의 정의, 조건처리(IF), 반복처리(FOR, WHILE, LOOP) 등을 지원하여
       SQL의 단점을 보완한 언어
*/

    BEGIN 
    FOR N IN 1..1000000 LOOP
    
    INSERT INTO BOARD
    VALUES(SEQ_BNO.NEXTVAL, N || '번째 게시글',
                     N || '번째 게시글의 내용입니다.',
                     DEFAULT, DEFAULT, DEFAULT, DEFAULT, 1,
                     CEIL(DBMS_RANDOM.VALUE(0, 6) ) * 10, 1);
    
    END LOOP;
    COMMIT;
END;
/

-- 인덱스 활용 X
SELECT * FROM BOARD
WHERE BOARD_TITLE = '500000번째 게시글'; -- 1.714초



-- 인덱스 활용 O
SELECT * FROM BOARD
WHERE BOARD_NO = 500000; -- 0.003초

인덱스 종류

고유 인덱스(UNIQUE INDEX)
비고유 인덱스(NON UNIQUE INDEX)
단일 인덱스(SLNGLE INDEX)
- 한 개의 컬럼으로 구성된 인덱스
결합 인덱스(COMPOSITE INDEX)
- 두 개 이상의 컬럼으로 구성된 인덱스
함수 기반 인덱스(FUNCTION BASED INDEX)
- SELECT절이나 WHERE절에 작성된 산술 또는 함수식에 부여하는 인덱스

-- 1. 고유 인덱스 (UNIQUE INDEX)
/* 
    - 고유 인덱스가 설정된 컬럼은 중복값 포함 불가 (UNIQUE 제약 조건 상태)
        --> 중복값이 이미 포함된 컬럼에는 고유 인덱스 생성 불가
    - 테이블 생성 시 또는 제약 조건 추가 SQL에서 PRIMARY KEY, UNIQUE 제약 조건이 설정되는 경우
      별도 INDEX가 컬럼에 없다면 자동으로 INDEX가 생성됨.
      
      * PK를 이용하여 검색하는 경우 성능 향상에 큰 효과가 있다.
         
*/

-- BOARD 테이블의 BOARD_TITLE 컬럼에 고유 인덱스 생성
CREATE UNIQUE INDEX IDX_BOARD_TITLE
ON BOARD(BOARD_TITLE);
-- ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found -->중복 에러   

-- 사용자가 생성한 인덱스 조회
SELECT INDEX_NAME, TABLE_NAME, UNIQUENESS
FROM USER_INDEXES
WHERE TABLE_NAME = 'BOARD';

SELECT * FROM BOARD
WHERE BOARD_TITLE = '500000번째 게시글';



-- 고유 인덱스 지정으로 인해 BOARD_TITLE 컬럼에 UNIQUE 제약조건이 생김.
 INSERT INTO BOARD
        VALUES(SEQ_BNO.NEXTVAL,  '500000번째 게시글',
                    '500000번째 게시글의 내용입니다.',
                    DEFAULT, DEFAULT, DEFAULT, DEFAULT, 1,
                    50, 1 );
-- ORA-00001: unique constraint (KH.IDX_BOARD_TITLE) violated

-- 인덱스 삭제
DROP INDEX IDX_BOARD_TITLE;


-- 2. 비고유 인덱스(NON UNIQUE INDEX)
-- 컬럼에 중복 값이 있지만 빈번하게 조회되는 컬럼을 대상으로 생성
-- 주로 조회 성능 향상을 위해 사용

CREATE INDEX IDX_BOARD_TITLE_NU
ON BOARD(BOARD_TITLE);

SELECT * FROM BOARD
WHERE BOARD_TITLE = '500000번째 게시글';

CREATE INDEX IDX_BOARD_CT_NU
ON BOARD(BOARD_CATEGORY);

SELECT * FROM BOARD
WHERE BOARD_CATEGORY = 30;

INDEX REBUILD(인덱스 재구성)

DML 작업(특히 DELETE) 명령을 수행한 경우, 해당 인덱스 내에서 엔트리가 논리적으로만 제거되고 실제 엔트리는 그냥 남아있게 된다. 인덱스가 필요 없는 공간을 차지하고 있기 때문에 인덱스를 재생성할 필요가 있다.

ALTER INDEX IDX_BOARD_TITLE_NU REBUILD;

-- 인덱스 삭제
DROP INDEX IDX_BOARD_TITLE_NU;


-- 따라서 인덱스는 4만개 이상정도의 데이터에서 효율적이고 
-- 반대로 적은 데이터의 양에서는 오히려 비효율적이다. 
-- DML을 많이 실행하지 않은 단지 조회목적의 테이블에서는 효율적이지만 
-- DML을 실행하면 다시 인덱스들을 조정해야되는 시간이 더 걸린다. --> 비효율적



/* 인덱스 재구성(INDEX REBUILD) 추가 설명

    테이블에 데이터가 추가, 수정, 삭제(DML)이 일어나면 
    해당 테이블에 존재하는 인덱스에도 추가, 수정, 삭제가 일어남
    
    그런데 DML 명령을 수행하면 트리의 불균형이 나타나게 됨
        - INSERT : 이진트리에 공간이 없으면 필요한 위치에 새로운 공간이 추가되고
                   저장된 값들이 다시 정렬됨 
                   -> 새로운 공간 추가 시 이진트리 한쪽으로만 추가되어 불균형 발생 가능
                   
        - DELETE : 데이터가 삭제되면, 삭제된 데이터와 연결된 인덱스에서도 저장된 데이터가 논리적으로만 삭제되고
                   데이터가 저장되어 있던 물리적 공간은 계속 남아있게됨 
                   -> 저장 공간 낭비
                   
        - UPDATE : INSERT와 DELETE가 동시에 수행됨
            
*/    

SYNONYM(동의어)

사용자가 다른 사용자의 객체를 참조할 때 [사용자ID].[테이블명]으로 표시하는데 이처럼 길게 표현되는 것을 동의어로 설정 후 간단히 사용할 수 있는 객체이다.

비공개 동의어

객체에 대한 접근 권한을 부여 받은 사용자가 정의한 동의어로 해당 사용자만 사용이 가능하다.

--1.  비공개 동의어
-- 객체에 대한 접근 권한을 부여받은 사용자가 정의한 동의어로
-- 해당 사용자만 사용 가능

 
 -- SQL DEVELOPER(kh)
 -- sample 계정에 kh의 BOARD 테이블 조회 권한 부여
 GRANT SELECT ON kh.BOARD TO sample;
 -- Grant을(를) 성공했습니다.
 
-- SQLPLUS(sample)
-- kh.BOARD 테이블 조회
SELECT * FROM kh.BOARD
WHERE ROWNUM <= 100;

 
 -- sample 계정이 kh.BOARD 테이블을 동의어로 KB SYNONYM 객체를 생성
CREATE SYNONYM KB FOR kh.BOARD;
-- ORA-01031: insufficient privileges ---> SYNONYM 생성 권한 없음

--SQL DEVELOPER(sys as sysdba)
-- sample 계정에 SYNONYM 생성 권한 부여
GRANT CREATE SYNONYM TO sample;
 -- Grant을(를) 성공했습니다
 
 
 
 -- SQLPLUS(sample)에서 SYSNONYM  생성 구문 다시 실행
 CREATE SYNONYM KB FOR kh.BOARD;
-- 생성 됨

-- KB로 조회로 가능한지 확인
SELECT * FROM KB
WHERE ROWNUM <= 100;  --> 조회 성공

-- KB SYNONYM 삭제


-- DROP SYSNONYM  생성 후 SYS계정으로 KB조회
SELECT * FROM KB
WHERE ROWNUM <= 100;
--> 관리자여도 비공개 동의ㅇ는 접근할 수 없음

 
 SELECT * FROM  kb.BOARD
WHERE ROWNUM <= 100;
--> 관리자는 모든 객체 접근 권한이 있기 때문에
-- 별도 GRANT 없이도 사용자명.객체명으로 접근이 가능하다.

공개 동의어

권한을 주는 사용자(DBA)가 정의한 동의어로 모든 사용자가 사용 가능(PUBLIC)하다. EX) DAUL

 -- 2. 공개 동의어
 -- 모든 권한을 주는 사용자(DBA)가 정의한 동의어로
 -- 지정된 동의어는 모든 사용자가 사용할 수 있음
-- 대표적인 예시 : DUAL

-- SQL DEVELOPER(sys as sysdba)
--KH 계정의 DEPARMENT 테이블의 공개 동의어를 DEPT로 지정
CREATE PUBLIC SYNONYM DEPT FOR kh.DEPARTMENT;

-- 기존 동의어 없이 조회하는 경우
SELECT * FROM kh.DEPARTMENT;

-- 동의어 지정 후(sys as sysdba, kh)
SELECT * FROM DEPT;
 -- ORA-00942: table or view does not exist
 ---> SELECT 권한이 없어서 조회 불가
 
 -- SQL DEVELOPER(kh)
 -- sample 계정에 khDEPARTMENT to sample;
 GRANT SELECT ON kh.DEPARTMENT TO sample;
 -- Grant을(를) 성공했습니다.
 --> SQLPULS(sample)에서 다시 edpt 조회 ---> 조회 성공

Twitter Google+ LinkedIn

techhan

2020년 11월 09일

SEQUENCE

INDEX

장점

단점

인덱스 종류

INDEX REBUILD(인덱스 재구성)

SYNONYM(동의어)

비공개 동의어

공개 동의어

공유하기

댓글남기기

관련 글 더보기

[정올] 1658_최대공약수와 최소공배수 (JAVA)

[정올] 2809_약수 (JAVA)

[정올] 1402_약수 구하기 (JAVA)

[정올] 1071_약수와 배수 (JAVA)