[SQL] 시퀀스(Sequence), 인덱스(index)

시퀀스 Sequence

• 순차적으로 증가하는 값 - PK에 많이 사용 (구분하는 기본 키 값을 생성하기 위해 사용)
• 배열에 반복적으로 값을 넣는 것과 같은 용도
• 시퀀스 번호는 테이블과 관계없이 생성되고 저장되기 때문에 동일한 시퀀스는 여러 테이블에서 사용 가능
• 시퀀스가 테이블에서 사용되고 있다면 drop 하면 안됨

1. 생성

-- 기본값으로 생성
create sequence depts_seq(테이블이름) nocache;

• increment - 증가값 양수면 증가, 음수면 감소 (default 1)

 

2. 확인

-- 시퀀스 확인
select * from user_sequences;

3. 사용

Nextval

• 다음 사용 가능한 시퀀스 값
• 한 번 전진하면 alter로 조절하지 않는 이상 후진 x

Currval

• 현재 시퀀스 값 리턴
• nextval → currval 순서로 진행되기 때문에 앞에 한 번은 nextval 이 실행되어야 함

시퀀스 사용!

-- 테이블
create table edpt3 (
    dept_no number(2) primary key,
    dept_name varchar2(20),
    loca varchar2(20),
    dept_date date
);
-- 시퀀스 생성 (기억해야함)
create sequence dept3_seq
        increment by 1
        start with 1
        maxvalue 10
        minvalue 1
        nocycle
        nocache;
        
-- 시퀀스 삭제
drop SEQUENCE dept_seq;
-- 시퀀스 생성 (기본값으로 생성)
create sequence dept3_seq nocache; 
        
-- 시퀀스 사용 currvar, nextvar * 무조건 암기
select dept3_seq.currval from dual; -- currvar는 nextvar 한 번 이후에 사용가능
select dept3_seq.nextval from dual;

-- 오류남
insert into dept3 (dept_no, dept_name, loca, dept_date)
values (dept3_seq.nextval, 'test', 'test', sysdate);

-- 시퀀스수정
alter sequence dept3_seq nocache;
alter sequence dept3_seq maxvalue 1000;
alter sequence dept3_seq increment by 10;

-- 시퀀스가 테이블에서 사용되고 있다면 drop하면 안됩니다.
-- 시퀀스 값을 초기화 하려면?
-- 1. 현재 시퀀스 확인
select dept3_seq.currval from dual;
-- 2. 증가값은 - 현재시퀀스
alter sequence dept3_seq minvalue 0;
alter sequence dept3_seq increment by -125;
-- 3. nextval로 실행
select dept3_seq.nextval from dual;
-- 4. 증가값을 1로 변경
alter sequence dept3_seq increment by 1;
-- 5. 실행
select dept3_seq.nextval from dual;

시퀀스 사용 응용

-- 시퀀스 사용의 응용
create table dept4 ( -- dept4 테이블 생성
    dept_no varchar2(30) primary key,
    dept_name varchar2(30)
);

drop SEQUENCE dept4_seq; -- 삭제
create sequence dept4_seq nocache; -- 생성
-- 2022-00001, 2022-00002 ....
-- LPAD('값', '맥스길이', '채움값')를 이용해서 pk에 적용하는 값을 (년월-0000시퀀스) 형태로 insert
-- 1. 확인하고 그 값을 (이 부분을 못함)
select concat ( to_char(sysdate, 'YYYYMM' ), lpad (dept4_seq.nextval, 5, '0') ) from dual;

-- 2. insert로 넣음
insert into dept4 (dept_no)
values (
        to_char(sysdate, 'YYYY') || lpad (dept4_seq.nextval, 5, 0)
);

select * from dept4;

4. 수정

 

---

인덱스 index

• index = 색인, 순서
• 원하는 행(레코드)를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조 = 조회가 빨라짐
• primary key, unique(key) 제약 조건에 자동 생성
  → where절에 PK를 통한 컬럼 조회를 하면 빨리 진행되는 이유
• 일반 인덱스(bitmap)로 유일하지 않은 인덱스 생성 - 수동생성

인덱스를 사용하는 경우

인덱스 유무 비교, 삭제

일반 인덱스 full스캔 - 인덱스X

first_name 인덱스 생성 후 인덱스 조회 - 인덱스 O

인덱스는 단지 데이터를 조회하기 때문에 삭제는 테이블에 영향을 미치지 않음

-- 인덱스 삭제
drop index emps_first_name_idx(테이블 인덱스 이름);

create table emps2 as (select * from employees);

-- 인덱스가 없을 때 full 스캔
select * from emps2 where first_name = 'Nancy';

-- 인덱스 생성
create index emps2_firstname_idx on emps2(first_name);
-- 인덱스 있을 때
select * from emps2 where first_name = 'Nancy';

-- 인덱스 삭제 (테이블에 영향을 미치지 않음)
drop index emps2_firstname_idx;


-- 결합인덱스 (컬럼 2개 이상)
create index emps2_names_idx 
on  emps2 (first_name, last_name);

select * from emps2 where first_name = 'Nancy'; -- 힌트
select * from emps2 where first_name = 'Nancy' and last_name = 'JJ'; -- 힌트

종류 (unique 인덱스, 결합 인덱스 등)

오라클 인덱스 힌트 - 검색하면 나옴 ↓

-- 힌트
select /* + index(emps2 emps_name_idx) */ -- 인덱스를 태운다
            employee_id, salary
from emps2;

-------------------------------------------------------------------------
-- 힌트구문 (외우기 x)
select * 
from emps2
order by first_name desc; -- order by: 뒤섞임, 1. 아래의 from 절에 넣음


select rownum as rn, -- rownum: 조회가 되는 순서
            a.*
from ( select * 
            from emps2
            order by first_name desc) a; -- 2. 아래의 from 절에 넣음

-- order by와 rownum 분리시키는 이유!

select *
from ( select rownum as rn,
                        a.*
            from ( select * 
                        from emps2
                        order by first_name desc) a
        )
where rn > 10 and rn <= 20; -- 2. whrere에 조건 넣어줌
----------------------------------------------------------------------------
-- 힌트 desc (거꾸로)
select /* + index_desc(emps2 emps_name_idx) */
          rownum as rn,
          employee_id, 
          salary
from emps2; -- 1. index 힌트를 사용하여 order by 사용하지 않고, rownum과 함께 사용가능

select  *
from ( select /* + index_desc(emps2 emps_name_idx) */
                      rownum as rn,
                      employee_id, 
                      salary
            from emps2
); -- 2. 위에 구문을 from 안에 넣음

---

오늘 하루

기억에 남는 부분

- 

- 

 

어려운 부분

- 

- 

 

문제 해결 부분

- 

-