오라클 강의(14강~18강)

14강

트랜젝션

-데이터 베이스 내에서 하나의 그룹으로 처리되어야 하는 명령문들을 모아 놓은 작업 단위

-데이터 일관성과 무결성을 보장하기 위해서는 반드시 트랜젝션관리가 필요함

트랜젝션 제어 명령어

-commit

모든 작업을 정상적으로 처리하겠다고 확정하는 명령어

트랙잭션의 처리과정을 데이터베이스에 반영하기 위해서, 변경된 내용을 모두 영구 저장

commit수행하면, 하나의 트랜잭션 과정을 종료하게 됨

-savepoint

-rollback

작업중 문제가 발생했을때, 트랜젝션의 처리과정에서 발생한 변경사항을 취소하고, 트랜잭션 과정을 종료시킴

트랜잭션으로 인한 하나의 묶음 처리가 시작되기 이전의 상태로 되돌림 즉, 최근 commit상태로 복귀 시킴

sqlplus가 정상 종료되면 자동 commit / 비정상 종료되면 자동 rollback

ddl과 dcl 명령문이 수행된 경우 자동 commit

정전이나 컴퓨터 down시에 자동 rollback 됨

ddl문

-create, alter, drop, rename, truncate

-자동 commit됨

-해당 명령이 에러가 나도 자동commit됨

트랜젝션을  작게 분할하는 savepoint

-현재 트랜잭션을 작게 분할하는 명령어

-저장된 savepoint는 rollback to savepoint문을 사용하여 표시한 곳까지 rollback 할수 있음

-여러개의 sql문 실행을 수반하는 트랜젝션의 경우, 사용자가 트랜잭션 중간 단계에서 savepoint를 지정할 수 있음

-차후 rollback과 함게 사용하여 현재 트랜젝션 내의 특정 savepoint까지 rollback 할 수 있게 됨

savepoint c1;

rollback to c1;

15강 - 16강

무결성 제약 조건

-무결성 : 데이터베이스 내에 있는 데이터의 정확성 유지

-제약 조건 : 바람직하지 않은 데이터가 저장되는 것을 방지하는 것

ex)기본키 : 반드시 입력되게하고, 유일한 값을 갖게 함

제약조건 5가지를 지원함

not null : null을 허용하지 않음

unique : 중복된 값을 허용하지 않고, 항상 유일한 값을 갖도록 함

primary key : null을 허용하지 않고, 중복된 값도 허용하지 않음, not null조건과 unique 조건을 결합한 형태

foreign key : 참조되는 테이블의 칼럼의 값이 존재하면 허용

check : 저장 가능한 데이터의 값의 범위나 조건을 지정하여, 설정한 값만을 허용

컬럼명 컬럼타입 not null 로 제약조건 추가함

컬럼명 컬럼타입 unique 로 제약조건 추가함(null값은 예외로 간주해서 여러번 입력가능하다)

의미있게 제약조건명을 명시하면 위배되었는지 바로 알수 있음(user_contraint딕셔너리를 검색해도 알수 있음)

컬럼명 컬럼타입 constraint 제약조건명 제약조건타입

제약조건명은 테이블명_컬럼명_제약조건타입

기본키제약조건 = unique 조건 + not null 조건

컬럼명 자료형 primary key (제약조건이 걸리게 된다)

참조무결성 : 테이블 사이의 관계에서 발생하는 개념

사원테이블에 부서번호를 입력할때, 부서 테이블에 존재하는 부서번호만 입력하도록 지정(참조 무결성)

-> 사원 테이블의 부서번호 컬럼에 외래키 제약조건을 명시해야 함

참조 무결성은 두 테이블 사이의 주종관계에 의해서 결정됨

부모테이블 :  주체가 되는 테이블 

자식 테이블 : 종속이 되는 테이블

사원 회사 내에 존재하는 부서에 소속되어 있어야 합니다

부모테이블 : 부서

자식테이블 : 사원

주종관계가 애매한 경우

어느 테이블의 데이터가 먼저 정의되어야 하는가?

먼저 정의되어야 하는 테이블 -> 부모테이블

나중에 정의되어야 하는 테이블 -> 자식 테이블

회사를 설립하고, 어떤 부서를 구성하여 운영할지 정한 후에 그 부서에서 일할 사원을 뽑아야 소속이란 관계가 성립

부모 테이블(부서)  자식테이블(사원)

부모테이블(기본키) -> 자식테이블의(외래키)

부모키가 되기 위한 컬럼은 반드시 부모 테이블의 기본키나 유일키로 설정되어 있어야 함

참조 무결성 제약조건

사원 테이블의 부서 번호는 부서 테이블에 저장된 부서번호중의 하나와 일치하거나 null값만 입력이 가능해야 한다는 조

건

제약조건으로 foreign key를 지정

오라클에서 제공하는 EMP테이블과 dept테이블의 제약조건

select table_name, constraint_type, constraint_name, R_constraint_name(외래키인경우 어떤 primary key를 참조했는지

에 대한 정보를 가짐) from user_constraints where table_name in(‘dept’, ‘emp’);

왜래키 설정하는 법

deptno number(2) constraint emp06_deptno_pk references dept(deptno);

참조무결성이 걸려있을땐느 자식테이블 삭제 -> 부모테이블 삭제 해야함

check 제약조건

입력되는 값을 체크하여, 설정된 값 이외의 값이 들어오면, 오류 메시지와 함께 명령이 수행되지 못하게 하는 것

-조건으로 데이터 값의 범위나 특정 패턴의 숫자나 문자값을 설정할 수 있음

ex) sal number(7,2) contstraint emp07_sal_ck check(sal between 500 and 5000)

ex) gender varchar(1) constraint emp07_gender_ck check(gender in(‘M’, ‘F’))

default제약조건

아무런 값을 입력하지 않았을때, 디폴트로 설정한 값이 입력되도록 하는 것

제약조건 변경

alter table을 이용해서 제약조건을 추가,삭제, 변경 가능

ex)alter table emp01 add constraint emp01_empno_pk primary key(empno)

alter table emp01 add constraint emp01_deptno_fk foreign key(deptno) references dept(deptno)

alter table emp01 modify ename constraint emp01_ename_nn not null

alter table emp01 drop constraint emp01_ename_nn

제약조건의 비활성화

alter table emp01 disable constraint emp01_ename_nn

alter table emp01 enable constraint emp01_ename_nn

cascade 옵션

-부모 테이블과 자식 테이블 간의 참조 설정이 되어 있을대

부모 테이블의 제약조건을 비활성화화면, 이를 참조하고 있는 자식 테이블의 제약 조건까지 함께 비활성화시켜 주는 옵션

-제약 조건의 삭제에도 활용됨(테이블의 제약 조건을 삭제하면, 이를 참조하고 있는 자식테이블의 제약조건도 같이 삭제

됨)

cascade옵션을 지정하여 기본키 제약조건을 비활성화하면, 이를 참조하는 외래키 제약조건도 연속적으로 비활성화되는지

를 확인

ex)alter table delpt01 disable primary key cascade;

17강

뷰

테이블 - 데이터가 있음

뷰 - 데이터가 없음(sql만 저장)

사용자가 해당 view에 접근하면, 그때 view에 들어있던 sql이 수행되어 결과를 가져오는 것

기본테이블 : 뷰를 생성하기 위해서는 실질적으로 데이터를 저장하고 있는 물리적인 테이블이 필요한 데 그것이 기본 테

이블이다

뷰를 생성할때 사용하는 옵션

create or replace view : 같은 이름의 view가 있을 경우 삭제후 다시 생성

force : 기본 테이블의 존재 여부에 상관없이 view를 생성

with check option : 주어진 제약조건에 맞는 데이터만 입력 및 수정을 허용

with read only : select만 가능한 읽기 전용 뷰를 생성

뷰는 번거로운 select문을 매번 입력하는 대신 보다 쉽게 원하는 결과를 얻고자 하는 바람에서 출발한 개념

ex) create view emp_view30

as select empno, enmae, deptno

from emp_copy

where deptno=30;

뷰는 보안에 유리

-사용자에게 특정객체만 조회할 수 있도록 권한 부여

-동일한 테이블에 접근하는 사용자들마다 서로 다른 뷰에 접근하도록 여러 개의 뷰를 정의

-특정 사용자만 해당 뷰에 접근 가능

뷰는 물리적으로 데이터를 저장하지 않고 create view 명령어로 뷰를 정의할때

as절 다음에 기술한 쿼리 문장 자체를 저장함

뷰의 쿼리를 확인하려면

ex)select view_name, text from user_views;

뷰를 삭제 = user_views 데이터 딕셔너리에 저장되어 있는 뷰의 정의를 삭제하는 것

뷰를 삭제해도 뷰를 정의한 기본 테이블의 구조나 데이터에는 전혀 영향을 주지 않음

단순뷰 : 하나의 테이블로 생성

그룹함수의 사용이 불가능

distinct 사용이 불가능

dml(insert/update/delete) 사용이 가능

***단순뷰에서 dml 명령어 사용이 불가능한 경우

뷰 정의에 포함되지 않은 컬럼 중에 기본 테이블의 컬럼이 not null 제약 조건이 지정되어 있는 경우 insert문 사용이 불

가능

->뷰에 대한 insert문은 기본 테이블에 null값을 입력하는 형태가 되기 때문임

sal*12와 같이 산술표현식으로 정의된 가상 컬럼이 뷰에 정의되면,insert나 update가 불가능

distinct를 포함한 경우에도 dml 명령 사용이 불가능

그룹 함수나 group by 절을 포함한 경우 dml 명령 사용이 불가능

복합뷰:여러개의 테이블로 생성

그룹 함수의 사용이 가능

distinct 사용이 가능

dml 사용이 불가능

with check option : 조건 컬럼값을 변경하지 못하게 하는 옵션(조건에 사용한 컬럼의 값을 수정못하게 함)

ex)update view_read30 set comm=2000; -> 불가능함

with read only : 기본 테이블의 어떤 컬럼에 대해서도 뷰를 통한 내용 수정을 불가능하게 만드는 옵션(테이블의 모든컬

럼값을 수정 ㅁ소하게)

뷰를 활용하여 top-n을 구하기

-예) 사원 중에서 입사일이 빠른 사람 5명(top-5)만을 추출한느 것

-rownum 사용

테이블 구조를 생성하면 제공되는 컬럼

-rowid(테이블의 특정 레코드를 랜덤하게 접근하기 위한 논리적인 주소값)

-rownum(각 행에 대한 일련번호)

create or replace view view_hire

as select empno, ename, hiredate from emp

order by hiredate;

select rownum, empno, ename. hiredate from view_hire

where rownum <=5;

18강

인덱스 : SQL 명령문의 처리속도를 향상시키기 위해서 컬럼에 대해서 생성하는 오라클 객체

인덱스의 장점

-검색속도가 빨라짐

-시스템에 걸리는 부하를 줄여서 시스템 전체 성능을 향상시킴

인덱스는 내부구조는 b-트리 형식으로 구성

인덱스의 단점

-인덱스를 생성하는 데 시간이 걸림

-인덱스를 위한 추가적인 공간이 필요함

-데이터의 변경작업(insert/update/delete)이 자주 일어날 경우에는 오히려 성능이 저하됨

인덱스는 기본키나 유일키와 같은 제약조건을 지정하면 자동으로 생성

인덱스 객체에 대한 정보 확인

user_indexes, user_ind_columns 데이터 딕셔너리 뷰에서 확인

-> 사용자가 정의한 인덱스 모두 조회 가능

ex) select indx_name, table_name, column_name from user_ind_columns where table_name in(‘emp’, ‘dept’); //테이블명은 대문자로 입력해야 함

인덱스의 종류

고유 인덱스(unique index) - 유일한 값을 갖는 컬럼에 대해서만 인덱스를 설정할 수 있음

비고유 인덱스(nonunique index) - 중복된 데이터를 갖는 컬럼에 대해서 생성하는 인덱스

단일 인덱스(single index)  - 한개의 컬럼으로 구성한 인덱스

결합 인덱스(composite index) - 두개 이상의 컬럼으로 구성한 인덱스

함수 기반 인덱스(function based index) - 수식이나 함수를 적용하여 만든 인덱스

인덱스 생성

create index

인덱스 삭제

drop index

인덱스를 사용해야 하는 경우

테이블에 행의 수가 많을때

where 문에 해당 컬럼이 많이 사용될때

검색 결과가 전체 데이터의 2%~4%정도 일때

join에 자주 사용되는 컬럼이나 null을 포함하는 컬럼이 많은 경우

인덱스를 사용하지 말아야 하는 경우

테이블에 행의 수가 적을때

where문에 해당 컬럼이 자주 사용되지 않을때

검색결과가 전체 데이터의 10%~15% 이상일때

테이블에 dml작업이 많은 경우 즉, 입력 수정 삭제 등이 자주 일어 날때

인덱스를 가끔 한번씩 재생성을 해주어야만 빠른 효율을 누릴 수 있음

ex)alter index idx_emp01_deptno rebuild;

인비저블 인덱스 - 11g new feature

인덱스를 삭제하기전에 사용안함 상태로 만들어서 테스트해 볼수 있는 기능을 제공하는 인덱스

권한의 역할과 종류

시스템 권한 - 사용자의 생성과 제거, DB접근 및 각종 객체를 생성할수 있는 권한

ex) 사용자 생성/삭제, 테이블 삭제, 함수기반 인덱스 생성, 테이블 백업, 접속권한, 테이블생성권한, 뷰생성권한, 시퀀스생성권한, 함수생성권한

객체 권한 - 객체를 조작할 수 있는 권한 

ex)테이블, 뷰, 인덱스, 시퀀스, 동의어등

시스템 권한 부여 grant

사용자에게 시스템 권한을 with admin option과 함께 부여하면, 그 사용자는 데이터베이스 관리자가 아닌데도 불구하고 부여받은 시스템 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여 받게 됨

현재 사용자가 다른 사용자에게 부여한 권한 정보를 알려줌

user_tab_privs_made

자신에게 부여된 사용자 권한을 알려줌

user_tab_privs_recd

권한을 회수하기 위해 명령어 revoke

사용자에게 객체 권한을 with grant option과 함께 부여하면, 그 사용자는 객체를 접근할 권한을 부여받으면서 그 권한을 다른 사용자에게 부여할 수 있는 권한