[Oracle] 인덱스, 시퀀스
인덱스
인덱스
-
책갈피, 목차와 같은 역할을 한다.
-
데이터에 빠르게 접근하는 것을 도와 데이터베이스의 성능 향상에 도움을 주는 객체이다.
인덱스 특징
-
인덱스가 부여된 필드 먼저 찾아준다.(자주 검색이 되는 컬럼에 부여) - where 조건식
-
데이터 저장공간이 필요
-
primary key -> (not null) 와 (unique 키(=unique index))
인덱스 종류
-
unique index 가 자동으로 부여된다(자동으로 pk가 생성)
-
**non-unique index -> 중복이 되어도 되는 필드에 적용, 중복이 되면서 자주 검색이 되는 필드에 부여
(업무별, 성별, 이름, 직책 검색)
인덱스 장점
-
검색속도가 빨라진다.
-
시스템에 걸리는 부하를 줄여서 시스템 전체 성능을 향상시킨다.
인덱스 단점
-
인덱스를 위한 추가적인 공간이 필요하다.
-
인덱스를 생성하는데 시간이 오래 걸린다.
-
데이터의 변경 작업(insert, update, delete)이 자주 일어나는 경우에는 오히려 성능이 저하된다.
-> 수정이 자주되는 필드에는 부여하지 않는 것이 원칙이다.
예) ㄱ~ㅎ 내부정렬이 완료된 상태에서 -> ㄱ~ㅎ중 새로이 변경 -> 다시 정렬 -> 검색속도가 느려진다.
DDL
형식) create [unique] index 인덱스명(테이블명 _ 필드명 _ idx)
on 적용테이블명(필드명1)
~on 적용테이블명(필드명1, 필드명2) => 복합키
SQL> create index emp_ename_idx on emp(ename);
인덱스가 생성되었습니다.
<문1>
index => user _ indexes 별칭(i) => 인덱스 이름, 고유의 정보 -> uniquness
user _ ind _ columns => 인덱스 이름, 테이블, 필드명
SQL> select c.index_name, c.column_name, i.uniqueness
from user_indexes i, user_ind_columns c
where c.index_name=i.index_name and c.table_name='&table_name';
SQL> select c.index_name, c.column_name, i.uniqueness from user_indexes i, user_ind_columns c where c.index_name=i.index_name and c.table_name='&table_name';
table_name의 값을 입력하십시오: EMP
구 1: select c.index_name, c.column_name, i.uniqueness from user_indexes i, user_ind_columns c where c.index_name=i.index_name and c.table_name='&table_name'
신 1: select c.index_name, c.column_name, i.uniqueness from user_indexes i, user_ind_columns c where c.index_name=i.index_name and c.table_name='EMP'
INDEX_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
UNIQUENES
---------
EMP_ENAME_IDX // 생성된 인덱스 이름
ENAME // 인덱스가 부여된 필드
NONUNIQUE // 중복을 허용해주는 인덱스
PK_EMP // 인덱스명
EMPNO // pk이 자동으로 부여된 상태
UNIQUE // unique 인덱스가 부여된 상태
INDEX_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
UNIQUENES
---------
SQL> create or replace view v_index
2 as
3 select c.index_name,c.column_name,
4 i.uniqueness from user_indexes i,
5 user_ind_columns c
6 where c.index_name=i.index_name and
7 c.table_name=upper('&table_name')
8 ;
table_name의 값을 입력하십시오: dept
구 7: c.table_name=upper('&table_name')
신 7: c.table_name=upper('dept')
뷰가 생성되었습니다.
SQL> select * from v_index;
INDEX_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
UNIQUENES
---------
PK_DEPT
DEPTNO
UNIQUE
<문2>
인덱스 삭제 => drop index 삭제시킬 인덱스명 => DDL
drop index emp_ename_idx;
SQL> drop index emp_ename_idx;
인덱스가 삭제되었습니다.
시퀀스
-
시퀀스는 유일(unique)한 값을 생성해주는 오라클 객체
-
시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 칼럼을 자동적으로 생성할 수 있다.
-
특정필드에 연결시켜서 데이터를 입력할때 자동적으로 지정한 번호를 넣어주는 역할을 하는 오라클 객체.
시퀀스 속성
-
시퀀스명.속성명(curval or nextval)
-
curval: 현재값을 반환(현재 설정값을 알고싶을 때 사용)
-
nextval: 현재 지정된 시쿼스 값의 다음번째 값을 반환 (증가된 값을 반환)
-
형식)
CREATE SEQUENCE *sequence_name*
[START WITH n] ① // 시작번호
[INCREMENT BY n] ② // 증가치
[{MAXVALUE n | NOMAXVALUE}] ③ // 최대값
[{MINVALUE n | NOMINVALUE}] ④ // 최소값
[{CYCLE | NOCYCLE}] ⑤ // 순환유무
[{CACHE n | NOCACHE}] ⑥ // 시퀀스번호 메모리에 저장 유무
SQL> b_emp4 -> empno
-시퀀스 생성 예
SQL> create sequence b_emp4_empno;
-> 특별한 옵션을 주지 않으면 default가 적용된다.(순서번호 1번부터 시작하여 1씩 증가 1,2,3,4,5,,,,)
SQL> create sequence b_emp4_empno;
시퀀스가 생성되었습니다.
-시퀀스 속성 예
SQL> select b_dept2_deptno.currval from dual;
SQL> select b_dept2_deptno.currval from dual;
select b_dept2_deptno.currval from dual
*
1행에 오류:
ORA-08002: 시퀀스 B_DEPT2_DEPTNO.CURRVAL은 이 세션에서는 정의 되어 있지
않습니다
에러가 발생한다? 오류의 원인은?
-> 현재 b_dept2_deptno.currval에 설정된 값이 없다. (초기값 설정이 되어있지 않다.)
-> 처음 생성하고 값을 입력해주고(nextval) -> 확인을 해야한다 (currval)
SQL> select b_dept2_deptno.nextval from dual;
NEXTVAL
----------
1
SQL> select b_dept2_deptno.currval from dual;
CURRVAL
----------
1
SQL> select b_dept2_deptno.currval from dual;
SQL> select b_dept2_deptno.currval from dual;
CURRVAL
----------
1
SQL> select b_dept2_deptno.nextval from dual;
NEXTVAL
----------
2
SQL> select b_dept2_deptno.currval from dual;
CURRVAL
----------
2
-시퀀스 생성
SQL> create sequence b_dept2_deptno;
SQL> create sequence b_dept2_deptno;
시퀀스가 생성되었습니다.
-생성된 테이블에 적용
SQL> select * from b_dept2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SUPPORT KWANGJU
-> insert 60,70,,, (시작값 60, 증가치 10) deptno2로 새로이 생성해보자 (최대값100000, 반복x, 저장x)
SQL> create sequence b_dept2_deptno2 start with 60 increment by 10 maxvalue 100000 nocycle nocache;
SQL> create sequence b_dept2_deptno2 start with 60 increment by 10 maxvalue 100000 nocycle nocache;
시퀀스가 생성되었습니다.
-값 입력(nextval사용)
SQL> insert into b_dept2 values(b_dept2_deptno2.nextval,'TESTING','SEOUL');
수동일 경우: insert into b_dept2 values(60, 'TESTING','SEOUL'); -> 60차례인 것을 알고있다면
SQL> insert into b_dept2 values(b_dept2_deptno2.nextval,'TESTING','SEOUL');
1 개의 행이 만들어졌습니다.
SQL> insert into b_dept2 values(b_dept2_deptno2.nextval,'TESTING2','SEOUL2');
1 개의 행이 만들어졌습니다.
SQL> insert into b_dept2 values(b_dept2_deptno2.nextval,'TESTING3','SEOUL3');
1 개의 행이 만들어졌습니다.
SQL> select * from b_dept2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SUPPORT KWANGJU
60 TESTING SEOUL
70 TESTING2 SEOUL2
80 TESTING3 SEOUL360, 70, 80에 차례대로 입력이 된 것을 확인할 수 있다.
skip 현상이 발생문제로 최근에는 사용되지 않는 문법이다. max(num)과 같은 문법을 사용한다.
<문3> 시퀀스 -> user_sequences에 저장 -> 뷰를 통해서 조회?
SQL> desc user_sequences
SQL> desc user_sequences
이름 널? 유형
----------------------------------------- -------- ----------------------------
SEQUENCE_NAME // 시컨스명 NOT NULL VARCHAR2(30)
MIN_VALUE // 최소값 NUMBER
MAX_VALUE // 최대값 NUMBER
INCREMENT_BY // 증가치 NOT NULL NUMBER
CYCLE_FLAG VARCHAR2(1)
ORDER_FLAG VARCHAR2(1)
CACHE_SIZE NOT NULL NUMBER
LAST_NUMBER // 현재 저장된 마지막값 NOT NULL NUMBER
SQL> create view v_sequence_seq as select sequence_name, min_value, max_value, increment_by, last_number from user_sequences;
SQL> create view v_sequence_seq as select sequence_name, min_value, max_value, increment_by, last_number from user_sequences;
뷰가 생성되었습니다.SQL> select * from v_sequence_seq;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ---------- ------------ -----------
B_DEPT2_DEPTNO 1 1.0000E+28 1 21
B_DEPT2_DEPTNO2 1 100000 10 90
B_EMP4_EMPNO 1 1.0000E+28 1 1
<문4> 시퀀스 수정
형식)
ALTER SEQUENCE 수정할 시퀀스명
[START WITH n] ①
[INCREMENT BY n] ②
[{MAXVALUE n | NOMAXVALUE}] ③
[{MINVALUE n | NOMINVALUE}] ④
[{CYCLE | NOCYCLE}] ⑤
[{CACHE n | NOCACHE}] ⑥
-수정시 주의할 점
SQL> alter sequence b_dept2_deptno2 increment by 2 maxvalue 23 minvalue 1 nocycle nocache;
SQL> alter sequence b_dept2_deptno2 increment by 2 maxvalue 23 minvalue 1 nocycle nocache;
alter sequence b_dept2_deptno2 increment by 2 maxvalue 23 minvalue 1 nocycle nocache
*
1행에 오류:
ORA-04009: MAXVALUE 에 현재치보다 작은 값을 지정할 수 없습니다currval 현재 설정 값이 80인데 최대값이 23으로 입력했으니 오류가 발생한 것이다.
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SUPPORT KWANGJU
60 TESTING SEOUL
70 TESTING2 SEOUL2
80 TESTING3 SEOUL3
-시퀀스(nextval, currval)를 사용할 수 없는 경우
-
VIEW의 SELECT 절
-
DISTINCT 키워드가 있는 SELECT 문
-
GROUP BY, HAVING, ORDER BY 절이 있는 SELECT 문
-> 그룹함수와 같이 사용불가
-
SELECT, DELETE, UPDATE의 서브 쿼리
-> 서브쿼리 내부에서는 사용불가
-
CREATE TABLE, ALTER TABLE 명령의 DEFAULT 값
-> 시퀀스는 정해진 값(default)을 입력하는 것이 아니다, 검색에서도 사용 불가하다.
-시퀀스(nextval, currval)를 사용할 수 있는 경우
-
서브 쿼리가 아닌 SELECT 문
-
INSERT 문의 SELECT 절
-
INSERT 문의 VALUE절
-
UPDATE문의 SET 절
<문5> 시퀀스 삭제
형식) drop sequence 삭제시킬 시퀀스명 => user_sequences에 저장
SQL> drop sequence b_dept2_deptno2;
SQL> drop sequence b_dept2_deptno2;
시퀀스가 삭제되었습니다.