인덱스, 시퀀스

[ 인덱스 index ]

- 책갈피와 같은 역할

- 인덱스가 부여된 필드를 먼저 찾아준다.

- 자주 사용되는 컬럼에 부여=자주 검색이 되는 필드(where 조건식)

- 데이터 저장공간이 필요(<->저장공간이 필요없는 것 : view)

- Primary key, Unique constraint 정의 시 자동으로 인덱스 생성(unique index)

*Primary key : not null+unique 키가 부여

- 중복되지 않고 빨리 찾아주길 원하는 데이터가 부여

[ 인덱스 종류 ]

1) Unique index

- 자동으로 primary key가 생성

create unique index 인덱스명(테이블명_필드명_idx)

 on 적용 테이블명(필드명1)

2) non-unique index

- 중복이 되도 괜찮은 필드에 부여(job, 성별, 직급 등)

create index 인덱스명(테이블명_필드명_idx)

 on 적용 테이블명(필드명1)

 on 적용 테이블명(필드명1,필드명2) -> 복합키

*인덱스가 부여되면 안되는 경우 

- 데이터가 자주 변경이 되는 데이터

-> 수정이 자주되는 필드에 인덱스 부여할 경우 내부적으로 정렬을 하여 찾는 속도가 느려짐

[ 인덱스 조회 방법 ]

1) user_indexs : 인덱스 이름, 고유의 정보

2) user_ind_columns : 인덱스가 어떤 필드에 적용되었는지, 인덱스 이름, 테이블, 필드명

3) 즉, 1) + 2)를 조인을 하라는 의미

Q3. 인덱스를 생성하고 생성된 인덱스를 조회하시오

1) b_emp2테이블 ename에 index 생성

create index emp_ename_idx

 on emp(ename);

인덱스가 생성되었습니다.

2) 인덱스 조회

- user_ind_columns(c) : index_name, column_name, column_position

- user_indexes(i) : uniqueness

select c.index_name,c.column_name,c.column_position,

 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

구   5:  and c.table_name='&table_name'

신   5:  and c.table_name='EMP'

INDEX_NAME

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

COLUMN_NAME

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

COLUMN_POSITION UNIQUENES

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

EMP_ENAME_IDX

ENAME

              1 NONUNIQUE // 중복을 허용해주는 필드

PK_EMP

EMPNO

              1 UNIQUE // PRIMARY KEY를 부여하면 자동으로 생성

INDEX_NAME

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

COLUMN_NAME

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

COLUMN_POSITION UNIQUENES

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

 

3) 뷰 만들기

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1  create or replace view v_index

  2  as

  3  select c.index_name,c.column_name,c.column_position,

  4   i.uniqueness

  5   from user_indexes i, user_ind_columns c

  6   where c.index_name=i.index_name

  7*  and c.table_name=upper('&table_name')

SQL> /

table_name의 값을 입력하십시오: emp

구   7:  and c.table_name=upper('&table_name')

신   7:  and c.table_name=upper('emp')

뷰가 생성되었습니다.

SQL> select * from v_index;

INDEX_NAME

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

COLUMN_NAME

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

COLUMN_POSITION UNIQUENES

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

EMP_ENAME_IDX

ENAME

              1 NONUNIQUE

PK_EMP

EMPNO

              1 UNIQUE

INDEX_NAME

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

COLUMN_NAME

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

COLUMN_POSITION UNIQUENES

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

4) INDEX 삭제

drop index emp_ename_idx;

인덱스가 삭제되었습니다.

SQL> select * from v_index;

INDEX_NAME

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

COLUMN_NAME

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

COLUMN_POSITION UNIQUENES

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

PK_EMP

EMPNO

              1 UNIQUE

---

시컨스(sequence) 객채, 시노님(동의어)-> 사용권한에서 더 다룸

Q5. [ 시컨스(sequence) ]

- 특정 필드에 연결시켜서 데이터를 입력할 때, 자동적으로 지정한 번호를 넣어주는 역할을 하는 오라클 객체

*rownum과 유사 : 레코드 앞에 번호 부여하나 번호를 지정할 수 없음

- 특정필드 : primary key와 연결된 필드(중복 되지 않는 유일한 값)

- 수동 대신에 자동으로 입력, 편리성을 추구

형식)

CREATE SEQUENCE sequence_name(테이블명_필드명)

[START WITH n] // n-> 시작번호

[INCREMENT BY n] // n -> 증가치

[{MAXVALUE n | NOMAXVALUE}] // n 최대값 | nomaxvlue 무제한

[{MINVALUE n | NOMINVALUE}] // n 최소값 | 최소값을 설정하지 않는 경우

[{CYCLE | NOCYCLE}] // 순환유무 cycle 최대값을 도달한 이후 다시 시작번호부터 시작

[{CACHE n | NOCACHE}] // 메모리 저장 유무

EX1) defualt 시퀀스

SQL> create sequence b_emp4_empno;

시퀀스가 생성되었습니다.

-> defualt가 적용, 자동으로 1부터 시작하여 증가치도 1

-> 시작번호나 증가치를 지정하고 싶은 경우 옵션 명령문 활용(start with~)

EX2) 시퀀스 속성

create sequence b_dept2_deptno;

시퀀스가 생성되었습니다.

-> deptno 대신에 정해준 번호를 자동으로 입력하려는 경우, 시퀀스 속성을 활용

[ 시퀀스 속성] 

1) currval : 현재값을 반환(현재 설정된 값), 즉 현재 설정된 값을 확인

2) nextval : 현재 지정된 시퀀스 값의 다음값을 반환, 즉 다음 증가할 값을 얻는다

3) 시퀀스명.속성명(currval of nexval)

select b_dept2_deptno.currval from dual;

1행에 오류:

ORA-08002: 시퀀스 B_DEPT2_DEPTNO.CURRVAL은 이 세션에서는 정의 되어 있지

않습니다

-> 시퀀스를 생성하고 바로 현재값(currval)을 확인할 수 없음

-> 현재 시퀀스에 설정된 값이 저장이 되어있지 않기 때문에 오류 발생

-> 즉, 시퀀스 생성시 반드시 nextval을 설정

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1* select b_dept2_deptno.nextval from dual

SQL> /

   NEXTVAL

----------

         1

-> 지정된 값의 다음 값을 얻는다

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1* select b_dept2_deptno.currval from dual

SQL> /

   CURRVAL

----------

         1

-> 현재 지정된 값을 확인

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1* select b_dept2_deptno.nextval from dual

SQL> /

   NEXTVAL

----------

         2

-> 현재 값인 1의 다음 값인 2을 얻는다

EX3) 테이블에 어떻게 적용시켜서 사용할 것인가?

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

-> insert : 60, 60부터 입력이 되어야함

-> 시퀀스의 시작값은 60, 증가치는 10

2) insert

select into b_dept2 values(60,'TESTING','SEOUL')

-> 입력시, DEPTNO가 중복될수 있음

-> 시퀀스를 입력하여 자동으로 입력

select into b_dept2 values(b_dept2_deptno.nextval,'TESTING','SEOUL')

-> 시퀀스를 대입

EX4) 새로운 시퀀스 생성하기

1) 시퀀스 생성

SQL> create sequence b_dept2_deptno2

  2   start with 60 // 시작값

  3   increment by 10 // 증가치 값

  4   maxvalue 10000 // 최대값

  5   nocycle  // 순환유무

  6   nocache; // 저장유무

시퀀스가 생성되었습니다.

2) 시퀀스 설정

SQL> insert into b_dept2 values(b_dept2_deptno2.nextval,'TESTING','SEOUL');

1 개의 행이 만들어졌습니다.

SQL> insert into b_dept2 values(b_dept2_deptno2.nextval,'TESTING2','BUSAN');

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       BUSAN

7 개의 행이 선택되었습니다.

시퀀스의 번호를 skip되는 경우

1) 시퀀스를 어려개의 테이블에 연결할 때

2) crash, down 되었을 경우

  -> 게시물 번호 -> 최대값+1

Q6. 시퀀스 : user_sequences에 저장된 것을 뷰를 이용하여 조회

1) 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

             (=가장 최근에 저장된 값)

             

2) 뷰 생성

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      10000           10          80

B_EMP4_EMPNO                            1 1.0000E+28            1           1

Q7. 시퀀스 수정하기

형식)

ALTER SEQUENCE sequence_name(테이블명_필드명)

[START WITH n] // n-> 시작번호

[INCREMENT BY n] // n -> 증가치

[{MAXVALUE n | NOMAXVALUE}] // n 최대값 | nomaxvlue 무제한

[{MINVALUE n | NOMINVALUE}] // n 최소값 | 최소값을 설정하지 않는 경우

[{CYCLE | NOCYCLE}] // 순환유무 cycle 최대값을 도달한 이후 다시 시작번호부터 시작

[{CACHE n | NOCACHE}] // 메모리 저장 유무

1) 시퀀스 수정

alter sequence b_dept2_deptno2

 increment by 2 

 maxvalue 23

 minvalue 1

 nocycle

 nocache;

1행에 오류:

ORA-04009: MAXVALUE 에 현재치보다 작은 값을 지정할 수 없습니다

-> maxvalue 값을 설정할 때 현재값보다 작은 값을 설정할 수 없다.

[시퀀스(currval, nextval) ]을 사용할수 없는 경우]

1) veiw의 select 절 : view절 구문 내 사용 불가

2) distint 키워드가 있는 slecet문

3) order by 절 : 그룹함수 사용 시

4) select, delete, update의 서브쿼리

5) create table, alter table의 명령과 defualt 값

2) 시퀀스 삭제

drop sequence 삭제시킬 시퀀스명

SQL> drop sequence b_dept2_deptno2;

시퀀스가 삭제되었습니다.

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_EMP4_EMPNO                            1 1.0000E+28            1           1