[DB] SEQUENCE, INDEX, SYNONYM

<SEQUENCE>

=자동으로 unique한 번호 생성

공유 가능한 객체, 여러 테이블에서 사용 가능

주로 기본키에 저장될 값 만드는 데 사용

애플리케이션 코드 대체

메모리 Cache되면 시퀀스 값 사용하는 효율성 향상

 

시퀀스 생성

예제

create sequence depts_seq
	start with 91
        increment by 1
        maxvalue 100
        nocycle
        nocache;

시퀀스 확인

select seqence_name, min_value, max_value,
	increment_by, last_number
from user_sequences;

select object_name
from user_objects
where object_type='SEQUENCE';

 

NEXTVAL과 CURRVAL

nextval:다음 사용 가능한 시퀀스 값 return

currval:현재 시퀀스 값 return

currval참조 전에 nextval이 먼저 이용되어야 한다.

 

시퀀스 사용

ex) depts테이블에 새로운 부서 정보 삽입

--depts테이블에 새로운 부서 정보 삽입
insert into depts(deptno, dname, loc)
values (depts_seq.nextval, 'marketing', 'san diego');

--depts_seq시퀀스에 대한 현재 값 본다
select depts_seq.currval from sual;

 

시퀀스 수정

=MAXVALUE한계에 도달한다면 더 이상 시퀀스 값 할당 받을 수 없다. + 에러 발생

시퀀스 계속 사용하기 위해 alter sequence로 수정할 수 있다.

 

ex) depts_seq최대값을 99999로 변경

alter sequence depts_seq
	MAXVALUE 99999;

 

시퀀스 삭제

drop sequence로 시퀀스 제거 가능

drop sequence 시퀀스이름;

 

IDENTITY COLUMN

시퀀스 대체를 위해 테이블 정의, 수정시

열레벨에 identity구문을 포함할 수 있다

generated [always | by default ]on null]]
as identity [(identity_options)]

CTAS명령으로 생성한 테이블은 Identity속성 상속안받음. not null속성만 상속받는다!

 

예제

create table depts
(
	deptno number(2) generated always as identity
    				(start with 10 increment by 10).
        dname varchar2(14),
        loc varchar2(13) unique,
        constraint depte_deptno_pk primary key(deptno)
);

generated always as identity=항상 값을 알아서 만드는 것. 값을 주면 안된다

(start with 10 increment by 10) 10부터 시작하여 10씩 시작한다는 말

그러므로

deptno열 값은 지정하지 않아도 된다.

insert into depts(dname, loc)
values('MARKETING', 'SAN DIEGO');

 

 

<INDEX>

=data검색을 더 빠르게 가능하게 하는 데이터 구조

자동 인덱스=primary key, unique제한 규칙으로 자동 생성되는 인덱스

수동 인덱스=create index명령어로 만드는 인덱스

 

인덱스 생성

자동 생성 = primary key, unique키 제약조건 정의시 자동 생성

수동 생성 = 행에 대한 액세스 시간 향상시키기 위해 열에서 유일하지 않은 인덱스를 생성 가능

create [unique|bitmap] index 인덱스명
on 테이블이름 (열이름, 열이름, 열이름...);

 

 

1. 인덱스 생성 전 실행 계획

select * from emps where first_name='David';

데이터 조회 후 F10으로 실행계획 확인하면

table access(full) 인것을 볼 수 있다

 

2. 인덱스 생성 후 실행 계획

create_index emps_first_name_idx
on emps(first_name);

데이터 조회 후 F10으로 실행계획 확인하면

table access(by index rowid)  인것을 볼 수 있다

 

인덱스 삭제

=필요없으면 지워도 괜찮다.

삭제해도 데이터에는 아무런 영향 미치지 않는다.

index소유자 이거나 drop any index권한을 가져야만 지울 수 있다.

 

조회 성능을 높이지만 새로운 데이터가 많이 쏟아질 경우

인덱스를 재구성해야 하고 이것이 시간이 많이 걸리므로 차라리 지우는 것이 더 좋다

drop index 이름;

 

인덱스를 언제 만들어야할까?

인덱스 생성 권장

- 열이 where절 또는 조인 조건에 자주 사용될 경우

- 열이 광범위한 값을 포함할 경우

- 열이 많은 수의 Null값을 포함한 경우

- 둘 또는 이상의 열이 where 절 또는 조인 조건에서 자주 함께 사용될 경우

- 테이블은 대형이고 대부분 질의가 행의 2~4% 보다 적게 읽을 것으로 예상할 경우

 

인덱스 생성하지 않는 것 권장하는 경우

- 테이블 작다

- where join절에 거의 안쓰일 경우

- 질의가 한번에 많이 읽을 때

- 자주 테이블이 갱신될 때.

 

인덱스 확인

인덱스 정보 확인

 

select * from USER_INDEXES;

인덱스 이름, 유형, 테이블,이름, 유일성 여부 등을 알 수 있다.

 

select * from user_ind_columns;

테이블명, 컬럼이름, 컬럼 위치 길이, 인덱스와 관련된 컬럼 등..

 

인덱스 종류

1. Bitmap 인덱스

=인덱스에 저장된 컬럼 값들을 binary화 하여 저장. 

적은 개수, 독특한 값일 경우 적합

테이블 크기 변경이 잘 없을 때 적합

 

2. Unique 인덱스

=Primary Key, Unique제약 조건시 자동 생성. 중복X

 

3. 함수기반 인덱스

= 인덱스 사용한 컬럼에 중복 데이터 값 가질 수 있다

 

4. 복합 인덱스

=2개 이상~16개의 컬럼수 로 이루어진 인덱스

<SYNONYM>

=동의어를 생성하여 객체 액세스를 단순화 한다.

긴 이름을 가진 객체들에 유용하게 사용

 

public:다른 사람도 쓸 수 있게 하는

create [public] SYNONYM 동의어 이름
for 이전이름;

 

예제

단축 이름 생성

create synonym emp60
for emp_dept60;

동의어 삭제

drop synonym emp60;