Or12Sequenceindex

/********

# 파일명 : Or12Sequenceindex.sql

# 시퀀스와 인덱스

# 설명 : 테이블의 필드에 순차적인 일련번호를 부여하는 시퀀스와

검색속도를 향상시키는 인덱스를 학습한다.

********/

/*

[시퀀스]

-테이블의 필드에 중복되지 않는 순차적인 일련번호를 부여하는 역할을 한다.

-시퀀스는 테이블 생성후 별도로 만들어야 한다. 즉 시퀀스는 테이블과

독립적으로 저장되고 생성된다.

[참조] 만약 시퀀스를 모르거나 그런 기능이 없다면 일련번호는 다음과 같이

입력해야 할 것이다.

    insert into 테이블명 (일련번호컬럼)

    values ((select max(일련번호컬럼)+1 from(테이블명));

    

    

[시퀀스 생성구문]

create squence 시퀀스명

    [Increment by N] -> 증가치지정

    [Start With N] -> 시작값지정

    [MinValue n | NoMinValue] -> 시퀀스 최소값 지정 : 디폴트1

    [MaxValue n | NoMaxValue] -> 시퀀스 최대값 지정 : 디폴트 1.0000E+28

    [Cycle | NoCycle] -> 최대/최소값에 도달할 경우 처음부터 다시 시작할지 여부 설정

    (cycle로 지정하면 최대값까지 증가후 다시 시작값부터 제 시작됨)

    [Cache | NoCache] -> cache 메모리에 오라클서버가 시퀀스 값을 할당하는가

    여부지정(디폴트로 cache)

    

* 주의사항

1. Start With에 MinValue보다 작은 값을 지정할 수 없다. 즉

Start With값은 MinValue와 같거나 MinValue보다 커야한다.

2. NoCycle로 설정하고 시퀀스를 계속 얻어올때 MaxValue에 지정한 값을 초과하면

아래와 같은 에러발생

"시퀀스명 nextval exceeds maxvalue는 사례로 될수 없습니다"

3. primary key에 Cycle옵션은 절대로 지정하면 안된다.

*/

/*

[NextVal, CurrVal]

NextVal : 다음의 일련번호 값을 반환한다.

CurrVal : 현재의 일련번호 값을 반환한다.

*NextVal과 CurrVal을 사용할 수있는 구문

-SubQuery가 아닌 select절

-insert문의 Values 절

-Update문의 Set 절

* 같은 절 안에서 NextVal을 여러번 사용해도 값은 같다.

* NextVal과 CurrVal을 사용할 수 없는 구문

- View문의 select절

-Distinct를 사용한 select문

-Group by, Having, Order by를 이용한 select문

*/

create sequence seq_serial_num

    increment by 1

    start with 100

    maxvalue 110

    minvalue 99

    cycle

    cache 10;

/*

*/    

-- 생성된 시퀀스 확인하기

select * from user_sequences;

-- 제품정보를 입력할 테이블 생성

create table goods (

    goods_num number(5) ,

    goods_name varchar2(30)

);

--현재 / 다음 시퀀스 확인하기

select seq_serial_num.currval from dual; /*

마지막에 입력된 일련번호 반환됨. 최초 실행시는 실행되지 않음

*/

select seq_serial_num.nextval from dual;/*

다음 입력할 일련번호 반환됨. 실행할 때마다 다음으로 넘어감.

*/

--시퀀스를 이용하여 제품입력하기

insert into goods (goods_num, goods_name)

values (seq_serial_num.nextval, '제품1');

select * from goods;

select seq_serial_num.currval from dual;

insert into goods values (seq_serial_num.nextval, '제품2');

insert into goods values (seq_serial_num.nextval, '제품3');

insert into goods values (seq_serial_num.nextval, '제품4');

insert into goods values (seq_serial_num.nextval, '제품5');

insert into goods values (seq_serial_num.nextval, '제품6');

insert into goods values (seq_serial_num.nextval, '제품7');

insert into goods values (seq_serial_num.nextval, '제품8');

insert into goods values (seq_serial_num.nextval, '제품9');

insert into goods values (seq_serial_num.nextval, '제품10');

select * from goods;

/*

회사를 운영하던 도중 제품명이 긴 제품이 출시되었다고 가정하자.

*/

insert into goods values(seq_serial_num.nextval, '건보특보!촉촉한00가습기');

--입력오류(컬럼길이부족)

alter table goods modify goods_name varchar2(100); --컬럼의 길이를 변경후

insert into goods values

(seq_serial_num.nextval, '전보특보!촉촉한좋은가습기'); --입력됨

/*

데이터가 있는 상태에서 컬럼의 자료형을 변경해야 하는 경우

    : ORA-01439: 데이터 유형을 변경할 열은 비어 있어야 합니다

    즉, 빈 테이블이 아니면 컬럼의 자료형을 변경할 수 없다.

*/

alter table goods modify goods_num varchar2(10);

/*

해결방법

    1. 빈테이블 만들기 -> goods_temp

    2. goods_temp 테이블에서 컬럼 자료형을 변경함.

    3. 데이터를 기존테이블에서 복사하기 : goods -> goods_temp

    4. 기존테이블 삭제

    5. 복사된 테이블의 테이블명 변경하기

*/

select * from goods;

--테이블의 스키마만 복사하기

create table goods_temp

as

select * from goods where 1=0;

--복사된 테이블 확인하기

select * from goods_temp;

desc goods_temp;

--컬럼 자료형 수정

alter table goods_temp modify goods_num varchar2(10);

--기존 테이블에서 레코드 복사하기

insert into goods_temp select * from goods;

select * from goods_temp;

--테이블명 변경

rename goods_temp to goods_str;

/* 컬럼을 지정해서 데이터 복사하기 */

create table goods_pants (

    goods_no number(3),

    goods_name varchar(10),

    goods_price number(10)

);

/*

원본테이블 : goods

티켓테이블 : goods_pants

*/

insert into goods_pants (goods_no, goods_name)

select goods_num, goods_name from goods;

--테이블 컬럼 크기 수정

alter table goods_pants modify goods_name varchar2(100);

insert into goods_pants (goods_no, goods_name)

select goods_num, goods_name from goods;

select * from goods_pants;

/* 원본과 사본 테이블의 컬럼명의 갯수가 서로 다를 때 복사하기 끝 */

/*

[시퀀스 수정]

alter sequence 시퀀스명

    [Increment By 증가값]

    [MaxValue N | MinValue N]

    [Cycle | NoCycle]

    [Cache | NoCache] ;

    

* start with N 은 수정할 수 없음

[시퀀스 삭제]

drop sequence 시퀀스명;

*/

--시퀀스 수정하기

select * from user_sequences;

--전체보기

select * from user_sequences where sequence_name=upper('seq_serial_num');

--하나만 보기

alter sequences seq_serial_num

    increment by 2

    nomaxvalue

    minvalue 1

    nocycle

    nocache; /* start with 100 : 이미 생성된 시퀀스의 시작값은 수정불가 */

select * from user_sequences;

--시퀀스 삭제하기

drop sequence seq_serial_num;

/*

[ 인덱스 ]

- 행의 검색속도를 향상 시킬 수 있는 개체

- 인덱스는 명시적(create index)  또는 자동적으로 (primary key, unique)

생성할 수 있다.

- 컬럼에 대한 인덱스가 없으면 한 테이블 전체를 검색하게 된다. 즉 인덱스는

쿼리의 성능을 향상 시키는 것이 목적이다.

- insert / update / delete가 많은 컬럼에 대해서는 index를 되도록이면

설정하지 않는 것이 좋다.

- index가 많은 것이 항상 좋은 것은 아니다. 이유는 index를 가진 테이블에 대한

DML작업은 인덱스도 갱신되어야 함을 의미하기 때문이다.

- 어떤 컬럼에 index를 설정하는 것이 좋은가?

1. where 조건이나 조인 조건에서 자주 사용되는 컬럼

2. 광범위한 값을 포함하는 컬럼

3. 많은 null값을 포함하는 컬럼

*테이블의 자료의 양이 적거나 자주 갱신되는 테이블은

오히려 index를 걸지 않는 것이 좋다.

[index 구문 형식]

- 인덱스 생성

create index 인덱스명 on 테이블명 (컬럼1, 컬럼2.....);

- 인덱스 삭제

droup index 인덱스명;

- 인덱스 수정 : 인덱스는 수정이 불가능하다. 삭제후 재생성 해야한다.

- 컬럼에 대한 인덱스 보기

select * from user_ind_columns;

*/

--인덱스 생성하기 : goods테이블의 goods_name에 인덱스 걸기

create index index_goods_name on goods (goods_name);

--생성된 인덱스 확인하기

select * from user_ind_columns where table_name=upper('goods');

--인덱스 삭제하기

drop index index_goods_name;

/*

1. 사원 테이블의 사원번호가 자동으로 생성되도록 시퀀스를 생성하시오.

시작은 1, 증가치 1, 최대치 100000 

시퀀스명 : emp_seq

*/

create sequence emp_seq

Increment by 1

Start With 1

MaxValue 100000

;

-- 풀이

create sequence emp_seq2

    start with 1

    increment by 1

    MaxValue 100000;

    

select * from user_sequences;

    

/*

2. #제약조건 1번에서 만들었던 “pr_emp_sample” 테이블에 데이터를 삽입하시오.

“emp_seq” 시퀀스로부터 기본키값을 얻어 입력해야 하며, 데이터는 5개 입력하시오.

*/

-- 풀이

insert into pr_emp_sample

    (employee_id, first_name, last_name, hire_date, job_id, email, department_id)

values

    (emp_seq.nextval, '라이', '순신', 

    sysdate, '장군', 'sunshin@kosmo.co.kr', 20);

    

insert into pr_emp_sample

    (employee_id, first_name, last_name, hire_date, job_id, email, department_id)

values

    (emp_seq.nextval, '세종', '대왕', 

    sysdate, '임금', 'sunshin@kosmo.co.kr', 20);

    

select * from pr_emp_sample;

/*

3.  “pr_emp_sample” 테이블의 “first_name” 컬럼에 인덱스를 지정하시오 .

인덱스명은 “index_emp_name”으로 하시오.

*/

create index index_emp_name on pr_emp_sample (first_name);

select * from user_ind_columns

    where index_name = upper('index_emp_name');

-- 풀이