[DB] sequence, index

* sequence

- 증가하는 번호를 관리하는 객체(연속적인 일련번호를 만들어주는 기능이다.)

- 메모리에 정해진 개수(서버가 다운되면 사라진다.)의 수를 올려놓고 사용한다.

- 번호를 얻을 때에는 nextval, currval이라는 가상 컬럼(pseudo column)을 사용한다.

nextval : 다음 번호를 얻는 일을 한다. 다음 번호가 없다면 하드 디스크에서 번호를 메모리(서버 인스턴스)로 적재한다. 한번 실행할때마다 다음 번호를 얻는데 쿼리문이 실패해도 번호는 다음번호를 얻는다.

currval : 메모리에 올라와있는 시퀀스의 현재번호를 얻는다. 따라서 nextval이 먼저 수행되고 나서 수행되어야 한다.

- user_sequences 테이블에서 확인할 수 있다.

- 문법

시퀀스 생성

create sequence 시퀀스명

increment by 증가값(기본값 1)

start with 시작값(기본값 1)

maxvalue 최댓값

minvalue cycle일 경우 새로 시작되는 값

cache  메모리에 올려놓을 수의 개수(시퀀스 생성 속도를 개선하기 위해 캐싱 여부 지정)(기본값 20)

cycle 순환 반복 여부(기본값 nocycle)

시퀀스 삭제

drop sequence 시퀀스명

- why? cache를 사용하는가? 예를들어 선착순 1천명에게 판매하는 이벤트를 한다고 가정하자. 주문을 받을 경우 1초에도 아주 많은 주문이 들어올 것이다. 이때 주문 테이블의 주문번호를 sequence로 생성할 경우 sequence에서 번호를 생성하는 시간이 걸리므로(아주 짧은 시간이라 하더라도) sequence 번호를 기다리는 대기(wait)현상이 발생할 것이고 이로 인해 성능이 저하된다. 이런 경우를 개선하기 위해 sequence번호를 미리 메모리상에 만들어두고(cache) sequence 번호 요청이 들어오면 즉시 번호를 할당하게 된다.

[참고] 속도 cpu > ---(cache)--- > HDD

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

-- 시퀀스

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

 

-- *시퀀스를 사용 안했던 기존의 쿼리

-- 문) cp_emp테이블에 사원정보 추가하라. empno, ename, hiredate

-- 사원번호는 가장 마지막 번호의 다음 번호로 추가하라

create table cp_emp4 as (select empno, ename, hiredate from emp);

insert into cp_emp4(empno, ename, hiredate) values((select max(empno) from cp_emp4)+1,'최민호',sysdate);

select * from cp_emp4;

-- 이런 쿼리를 시퀀스를 사용하여 번호를 매겨 나타낼수 있다.

 

 

-- *시퀀스 생성

create sequence seq_test

increment by 1

start with 1

maxvalue 999999;

 

 

-- *currval, nextval

-- *nextval을 해주지 않은 상태에서 맨 처음 currval을 실행하면 아무 값도 없다.

select seq_test.currval from dual;

-- └error msg : 시퀀스 SEQ_TEST.CURRVAL은 이 세션에서는 정의 되어 있지 않습니다

 

 

-- *메모리에 번호를 올리기 혹은 다음 번호로 증가, 이동하기

select seq_test.nextval from dual;

-- └출력값 : 1

-- *nextval을 실행한 후 currval 실행하기

select seq_test.currval from dual;

-- └출력값 : 1

/*

여기까지 실행한 후 user_sequences테이블을 조회하면

min_value 1 / max_value 999999 / increment_by 1 / ctcle_flag n / order_flag n / cache_size 20 / last_number 21

*/

 

 

select seq_test.nextval from dual;

select * from user_sequences;

/*

위의 두 쿼리를 20번 반복(cache size만큼 반복) 후 user_sequences테이블을 조회하면

min_value 1 / max_value 999999 / increment_by 1 / ctcle_flag n / order_flag n / cache_size 20 / last_number 41

★준비된 캐시 용량을 넘어서면 size+last_number =+ increment*cache 계산하여 늘려준다.★

★이거 잘 모르겠다 강사님한테 물어보기!!!!★

 

이후 재부팅을 하는 등 서버어플리케이션을 죽이고 다시 user_sequences테이블을 확인해보면 시퀀스의 값이 last_number로 바뀌어져 있는 현상을 확인 할 수 있다.

└why? 메모리에서 작업하였으므로 해당 값은 사라진다.(메모리의 휘발성)

*/

-- ~재부팅~

select seq_test.nextval from dual;

-- └출력값 : 41

 

 

-- *위 예제 복습

create sequence seq_jumun2_no

increment by 1

start with 100

maxvalue 105

cache 2;

 

select seq_jumun2_no.nextval from dual;

select seq_jumun2_no.currval from dual;

select * from user_sequences;

-- 최초 last_number 100

-- nextval을 사용하여 값이 100임을 출력한 후 user_sequences테이블을 확인하면 last_number이 102로 증가한것을 확인할 수 있다.

-- nextval을 사용하여 값을 102로 만든 후 user_sequences테이블을 확인하면 last_number가 104로 증가한것을 확인할 수 있다.

/*

또한 nextval을 이용해 값은 105로 만든 후 한번더 nextval을 실행하면 에러가 난다

error msg : 시퀀스 SEQ_JUMUN2_NO.NEXTVAL exceeds MAXVALUE은 사례로 될 수 없습니다

cycle의 기본값은 nocycle이므로 maxvalue를 초과하면 에러를 발생한다.

*/

 

 

-- *cycle 확인 예제

create sequence seq_jumun3_no

increment by 1

start with 100

maxvalue 105

cache 2

cycle;

 

select seq_jumun3_no.nextval from dual;

select seq_jumun3_no.currval from dual;

select * from user_sequences;

/*

위 seq_jumun2_no는 105에서 nextval를 실행하면 에러가 발생하지만 seq_jumun3_no는 cycle로 설정해주었기 때문에

minvalue값으로 전환된다. 위에서는 minvalue값을 따로 설정해주지 않았기 때문에 1이 출력된다.

*/

 

 

-- *시퀀스를 이용하여 데이터 추가하기

create table cp_emp5 as (select empno, ename, deptno, sal, hiredate from emp);

select * from cp_emp5;

-- 서브쿼리를 이용하여 추가하기

insert into cp_emp5(empno, ename, deptno, sal, hiredate) values ((select max(empno) from emp), '이진기', 10, 3960, sysdate);

-- 시퀀스를 이용하여 추가하기

insert into cp_emp5(empno, ename, deptno, sal, hiredate) values (seq_test.nextval, '이진기', 10, 3960, sysdate);

 

 

-- *시퀀스 번호 건너뜀 확인하기

-- 1. 반복

select seq_test.nextval from dual;

-- └3번 반복 후 출력값 45

insert into cp_emp5(empno, ename, deptno, sal, hiredate) values (seq_test.nextval, '김종현', 20, 4000, sysdate);

-- └출력값 46

 

-- 2. insert 실패

insert into cp_emp5(empno, ename, deptno, sal, hiredate) values (seq_test.nextval, '김종현', 200, 4000, sysdate);

-- └부서번호를 범위를 벗어난 값을 입력하여 insert문이 실패하였다.

insert into cp_emp5(empno, ename, deptno, sal, hiredate) values (seq_test.nextval, '최민호', 20, 4000, sysdate);

select * from cp_emp5;

-- └예상으로 empno는 위 insert문이 실패하였으므로 46의 다음인 47이 출력될것 같은데 실제 출력되는 값은 48이다.

-- nextval을 포함한 쿼리문이 실패해도 값은 증가한다.

 

 

-- *문자열이면서 증가하는 수를 구하라(시퀀스를 이용하여 문자열로 된 고정된 자리수 출력)

select 'item_' || lpad(seq_test.nextval,10,0) result from dual;

-- └lpad(),rpad()함수를 사용하여 출력한다.

 

 

 

-- 문) 10에서 10000000까지 10씩 증가하는 시퀀스를 생성하라. 번호를 모두 소진한 경우에는 반복시키지 않는다.

create sequence seq_test1

increment by 10

start with 10

maxvalue 10000000

cache 100

nocycle;

 

 

-- 시퀀스 삭제

drop sequence seq_test;

 

 

* index

- 많은 양의 레코드를 빠르게 검색하기 위해서 사용한다.

- 레코드가 적다면 검색 속도가 느려진다.

- 테이블 셍성시 primary key, unique를 설정하면 자동으로 인덱스가 생성된다.

- user_indexs 테이블에서 생성된 인덱스를 확인할 수 있다.

- unique index(컬럼의 값이 유일한 경우), non unique index(값이 유일하지 않는 컬럼에 인덱스를 걸고자 할 때), bitmap indax(컬럼의 값이 독특한 형상일 때 ex. 코드값), composit index(여러개의 컬럼이 합쳐져서 인덱스로 사용될 때)를 제공한다.

- 인덱스로 설정할 컬럼은 조회시 where절에서 자주 사용될 컬럼, 값의 변화(update)가 빈번하게 발생하지 않는 컬럼으로 설정한다.

- 문법(non unique, composit index는 따로 명시하지 않는다.)

생성

create [unique|ditmap] index 인덱스명on 테이블명(컬럼명);

삭제

drop index 인덱스명;

- 조회시 힌트(/*+ 인덱스명 */)를 사용한다.

select /*+인덱스명*/ 컬럼명,,,

...