[ORACLE]시퀀스 인덱스

시퀀스

테이블 내의 유일한 숫자를 자동으로 생성

기본 키가 유일한 값을 갖도록 하여 사용자가 직접 생성하는 부담감을 줄임

create sequence 시퀀스명
[start with 시퀀스 시작숫자]--시작숫자의 기본값은 증가할 떄 minvalue, 감소할 때 maxvalue
[increment by 증감숫자]--양수면 증가 음수면 감소(기본값 :1)
[minvalue 최소값 | nominvalue(기본값)]--nominvalue(기본값) : 증일 때 1, 감소일때 10의-26
								  --minvalue 최소값: 최소값 설정, 시작숫자와 같거나 작아야 하고 maxvalue보다 작아야함

[maxvalue 최대값 | nomaxvalue(기본값)]--maxvalue 최대값: 최대값 설정, 시작숫자와 같거나 커야 minvalue(최소값)보다 커야함
								   --nomaxvalue(기본값) : 증가일때 10의 27까지
[cycle | nocycle(기본값)] --cycle:최대값 까지 증가하다가 최소값으로 다시 시작 
						--nocycle: 최대값 까지 증가하다가 또 다시 시퀀스를 발급받으려면 에러 발생
[cache n | nocache] 	--cache n : 메모리상에 시퀀스 값을 미리 할당(기본값은 20)						
						--nocache : 메모리상에 시퀀스 값을 미리 할당하지 않음(관리X)
						
[order | noorder(기본값)]--order: 병렬서버를 사용할 경우 요청 순서에 따라 정확하게 시퀀스를 생성하기를 원할 떄 order로 지정
						--       단일서버일 경우 이 옵션과 관계없이 정확히 요청 순서에 따라 시퀀스 생성

시퀀스 생성

create SEQUENCE sample_test;--옵션은 기본값으로 들어감

데이터사전에서 시퀀스조회

select * from user_sequences;

CURRVAL  NEXTVAL

nextval 먼저 사용하고 currval 사용

select sample_seq.nextval from dual; 

select sample_seq.currval from dual;

 

기본키에 접목시킬 시퀀스 생성

create sequence dno_seq
increment by 10
start with 10;--nocycle이 기본값(10->20->...90)

insert into dept12 values(dno_seq.nextval,'ACCOUNTING','NEW YORK');--10
insert into dept12 values(dno_seq.nextval, 'RESEARCH', 'DALLAS');--20
insert into dept12 values(dno_seq.nextval, 'SALES', 'CHICAGO');--30
insert into dept12 values(dno_seq.nextval, 'OPERATIONS', 'BOSTON');--40

create sequence sampl_seq
start with 10
increment by 3
maxvalue 20
cycle
nocache;

select sampl_seq.nextval, sampl_seq.currval from dual;--10 10
select sampl_seq.nextval, sampl_seq.currval from dual;--13 13
select sampl_seq.nextval, sampl_seq.currval from dual;--16 16
select sampl_seq.nextval, sampl_seq.currval from dual;--19 19
select sampl_seq.nextval, sampl_seq.currval from dual;--1 1 (cycle 옵션때문에 다시 최소값으로 )
select sampl_seq.nextval, sampl_seq.currval from dual;--4 4

시퀀스 수정

start with 시작숫자는 수정불가

ALTER sequence dno_seq
maxvalue 50;

시퀀스제거

drop sequence dno_seq;

 

 

인덱스

DB 테이블에 대한 검색속도 향상시켜주는 자료 구조

사용자의 필요에 의해서 직접 생성할 수도 있고,
데이터 무결성을 확인하기 위해서 수시로 데이터를 검색하는 용도로 사용되는

'기본키'나 '유일키(unique)'는 index 자동생성됨

USER_indexES 또는 USER_IND_columnS(컬럼이름까지 검색가능) 데이터 사전에서 INDEX객체 확인가능

계획성 없이 너무 많은 인덱스를 지정하면 오히려 성능저하시킬 수 있다.

 index 생성 전략
 1.조건절에 자주 등장하는 컬럼
 2.항상 =으로 비교되는 컬럼
 3.중복되는 데이터가 최소한인 컬럼
 4.order by 절에서 자주 사용되는 컬럼
 5.조인 조건으로 자주 사용되는 컬럼

index 사용해야 하는 경우

• 테이블의 행 수 많을 때
• where문에 해당 컬럼이 많이 사용될 때 
• 검색결과가 전체 데이터의 2~4% 정도일 때(찾기가 어려우니까)
• join에 자주사용되는 컬럼이나 null을 포함하는 컬럼이 많을 때

index 사용하지 말아야 하는 경우

• 테이블의 행 수 적을 때
• where문에 해당 컬럼이 자주 사용되지 않을 때
• 검색결과가 전체 데이터의 10~15% '이상' 일 때
• 테이블에 DML 작업이 많은 경우 즉, 입력/수정/삭제 등이 자주 일어날 때

인덱스생성

CREATE INDEX 인덱스명 ON 테이블명 (컬럼1, 컬럼2, 컬럼3.....);
 
create unique index idx_dept12_dno
on dept12(dno);

 

인덱스 제거

(기본키의 경우 index를 반드시 가져야하므로 제거시 문제발생 )

 DROP INDEX 인덱스명;

 

인덱스 종류

1.고유/비고유 인덱스

고유 인덱스(unique): 기본키(unique +not null) 나 유일키(unique)처럼 유일한 값을 갖는 컬럼에 생성된 인덱스

예) 부서테이블의 부서번호
★★특정 컬럼에 고유 인덱스가 저장되려면 추가한 데이터에 중복된 값이 있어서는 안됨

기본키 같이 반드시 index가 있는 경우에도

비고유 인덱스: 중복된 데이터를 갖는 컬럼에 생성된 인덱스

예)부서테이블의 부서명이나 지역명

create unique index 인덱스명--고유 인덱스
on 테이블명(컬럼명);

2.결합인덱스 : 두개이상의 컬럼으로 구성한 인덱스

create index idx_dept12_2_complex
on dept12_2(dname,loc);

select *
from dept12_2
where dname=' ', loc=' ';
--그런데 위 쿼리가 거의 사용되지 않는다면 오히려 성능 저하 발생

select *
from dept12_2
where dname=' ';
--dname에 index가 없어도 위에서 생성한 인덱스를 사용해도됨, 더 효율적

3.함수기반 인덱스 :수식이나 함수를 적용하여 만든 인덱스

create index idx_emp12_salary12
on emp12(salary*12);--'수식'이므로 컬럼명이 없어서 '가상컬럼' 생성됨