SQL index 시퀀스

sql index
관리지향적

프라이머리 키나 유니크 키를 만들면 인덱스를 만들어준다.

스캔 두가지 : 풀테이블스캔 , 인덱스스캔

테이블의 데이터는 정렬해서 들어가지 않지만
인덱스에서는 정렬해서 저장된다.

풀테이블스캔 : 위에서부터 데이터를 찾는데 데이터를 찾았다고해도 중복되는 데이터가 있을 수도 있어서
            끝까지 다 스캔한다.

인덱스스캔 : 인덱스에 정렬된 데이터의 가운데부터 물어봐서 찾으려는 데이터보다 큰지 작은지 물어보고
          만약 크다면 아래는 스캔하지 않고 위쪽의 가운데부터 다시 물어보는 식으로 범위를 좁혀나가서
          데이터를 찾는다.

고유 인덱스 (Unique index)        : 인덱스 값이 중복되지 않는다.
-자동 생성 : PK, UK 가 설정된 컬럼에 자동 생성된다.

비 고유 인덱스 (Non unique index)  : 인덱스 값이 중복될수도 있다.
-create index 명령으로 생성한다.

 인덱스 사례
 - 전체 데이터의 10%~15% 정도의 데이터를 검색하는 경우
 - WHERE 절이나 조인에 사용되는 컬럼
 - 데이터의 행이 매우 많은 경우
 - FK 컬럼!!


비 고유 인덱스는 삭제할 수있지만 고유 인덱스는 제약조건을 제거하면 삭제된다.


프라이머리 키를 두개의 컬럼을 묶어서 만들면 인덱스또한 두개의 컬럼을 묶은 상태로 인덱스가 만들어지며
COLUMN_POSITION 에 1, 2 로 위치가 앞뒤로 표기 된다. 어떤 컬럼이 먼저 정렬되있는지가 굉장히 중요하다.

인덱스 조회 명령어

SELECT i.table_name, i.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 i.table_name IN ('STUDENT', 'PROFESSOR', 'COURSE', 'SCORE')
 ORDER BY i.table_name;

select c.table_name, c.constraint_name, c.constraint_type, s.column_name
from user_constraints c, user_cons_columns s
where c.constraint_name = s.constraint_name
and c.table_name IN ('STUDENT', 'PROFESSOR', 'COURSE', 'SCORE')
ORDER BY c.table_name, c.constraint_name;

논 유니크 인덱스 생성
create index course_pno_fk
on course (pno);
create index score_cno_fk
on score (cno);

select i.table_name, i.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 i.index_name LIKE '%FK'
order by c.table_name;

create index student_sname_indx
on student (sname);
create index student_major_sname_indx
on student (major, sname);
create index student_coavr_indx
on student (avr*4.5/4.0);

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 = 'STUDENT'
order by c.index_name, c.column_position;


인덱스 삭제

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 = 'STUDENT';

논유니크 인덱스 삭제
drop index student_major_sname_indx;

유니크 인덱스 삭제
alter table student drop constraint student_sno_pk CASCADE;


인덱스 
컬럼의 물리적인위치 rowid도 저장

뷰(VIEW) 물리적인 공간을 차지 하지 않는다.(alter는 없음)
         테이블을 임의로 볼 수 있는 창문
검색하는 용도로 사용한다.
수정할땐 지우고 새로 만들면 된다.

SQL> CREATE [OR REPLACE][FORCE | NOFORCE] VIEW 뷰 (컬럼 ... )
 2 AS (SELECT 문장 : sub query)
 3 [WITH CHECK OPTION [CONSTRAINT constrant_name]]
 4 [WITH READ ONLY [CONSTRAINT constrant_name]];


SQL> SELECT view_name, text FROM user_views;
SQL> SET LONG 1000 (1000바이트까지 출력)

뷰는 상위 계정에서 사용하는데 관리자 계정으로 접속해서 해당 계정에게 만들 수 있는 권한을 할당할 수 있다.
C:\...\>sqlplus / as sysdba
SQL> GRANT create view TO st;
권한이 부여되었습니다.

테이블 사용하는 것과 똑같이 사용하면 된다.

단순 뷰는 insert 할 수 있다.하지만 insert 하지 않는게 원칙이다. (뷰에 보이지않는 데이터도 insert 될 수도 있다.)
방지방법 WITH CHECK OPTION CONSTRAINT view_뷰이름_ck;

뷰를 만드는 이유는 복잡한 sql문을 사용하기 어려운 개발자들을 위해 데이터베이스 튜너가 만들어준다.

시퀀스
시퀀스 넘버 ( ex)게시물 번호 ) : 값을 구별은 되지만 값의 의미는 없음 (cycle로 중복될수도 있지만 사용하지 않는다.),
                            어떤 번호를 받을지는 모른다.

시퀀스 생성
SQL> CREATE SEQUENCE 시퀀스 
 2 [ START WITH 시작_값             -> 몇번부터 시작할지 (한번만들면 수정 불가)
 3 INCREMENT BY 증가_값             -> 얼마씩 증가할지 (기본값1)
 4 MAXVALUE [상한_값 | NOMAXVALUE]  -> 몇번까지 줄건지 (기본으로는 상한선이 없음)
 5 MINVALUE [하한_값 | NOMINVALUE]  
 6 CYCLE | NOCYCLE                 -> nocycle이 기본, cycle은 거의 안씀
 7 CACHE [cache_개수| NOCACHE]];    

SQL> CREATE SEQUENCE 시퀀스;    -> 기본값으로 시퀀스가 만들어진다.

SQL> ALTER SEQUENCE 시퀀스 
 2 INCREMENT BY 증가_값 
 3 MAXVALUE [상한_값 | NOMAXVALUE]
 4 MINVALUE [하한_값 | NOMINVALUE]
 5 CYCLE | NOCYCLE 
 6 CACHE cache_개수| NOCACHE;



시퀀스 삭제
SQL> DROP SEQUENCE 시퀀스;



시퀀스를 추가 할 수 있다.
SQL> ... 시퀀스.NEXTVAL ... 
SQL> ... 시퀀스.CURRVAL ...