자바 프로그래밍_Day_39_DML, VIEW, SEQUENCE, INDEX

 

2021. 10. 13

 

 

     > DML명령어로 VIEW 조작이 불가능한 경우

       -> 1. 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우

          -> 뷰 정의에 포함되지 않은 컬럼을 INSERT/UPDATE하는 경우 에러가 발생하지만, DELETE로는 조작이 가능하다.

 

       -> 2. 뷰에 포함되지 않은 컬럼 중에 베이스가 되는 컬럼이 NOT NULL 제약조건이 지정된 경우

          -> 뷰에 포함되지 않은 NOT NULL 제약조건이 있는 컬럼이 존재하면 INSERT / UPDATE시 에러가 발생하지만, DELETE는 가능.

 

       -> 3. 산술 표현식으로 정의된 경우

 

       -> 4. 그룹 함수나 GROUP BY 절을 포함한 경우

          -> 그룹 함수 또는 GROUP BY를 사용한 경우 INSERT / UPDATE / DELETE시 에러가 발생한다.

 

       -> 5. DISTINCT를 포함한 경우

 

       -> 6. JOIN을 이용해 여러 테이블을 연결한 경우

          -> 뷰 정의 시 JOIN을 사용한 경우 INSERT / UPDATE 시 에러가 발생하지만, DELETE는 가능하다.

       -> 테스트용 뷰 생성

 

 

       -> too many values 오류가 뜬다.

 

 

       -> 뷰에는 JOB_CODE만 정의되어 있기 때문에 JOB_NAME을 넣을 경우 오류가 발생한다.

 

 

       -> 기본적으로 NULL로 정의된다.

 

 

       -> JOB_CODE가 NULL이기 때문에 (PK : NOT NULL 제약 조건) 오류 발생

 

 

       -> UPDATE는 가능

 

 

       -> 외래키(FK) 제약조건, J7을 참조하고 있어서 오류가 발생 (자식테이블 참조)

 

 

       -> 결과

 

 

       -> 산술 표현식으로 정의 된 경우(SALARY * 12 AS "연봉") 실습용 뷰 생성

 

 

       -> V_EMP_SAL 실제 테이블에 존재하는 컬럼이 아니고 가상 테이블이기 때문에 INSERT할 시 오류가 발생한다.

 

 

       -> 뷰에 있는 연봉 테이블은 가상 컬럼이기 때문에 직접적으로 수정할 수 없다.

 

       -> 산술연산과 무관한 컬럼은 변경이 가능하다.

 

       -> 연봉 삭제는 가능하다. (EMP_ID : 200 삭제)

       -> 별칭(연봉)으로 했을 시 삭제 불가능 : 진행 순서 때문에 WHERE절에서 연봉이라고 하면 어떤 연봉인지 모르기 때문에 오류가 발생

 

       -> 가상 컬럼에 INSERT하기 때문에 발생하는 오류

 

 

       -> 그룹 함수나 GROUP BY 절을 포함한 경우 DML(INSERT, UPDATE, DELETE)를 허용하지 않는다.

 

       -> DISTINCT를 포함한 경우에도 INSERT, UPDATE, DELETE가 불가능하다.

 

 

       -> JOIN을 사용한 경우 DELETE만 사용이 가능하다.

 

 

 

 

     > VIEW 구조

       -> 뷰 정의 시 사용한 쿼리 문장이 TEXT 컬럼에 저장되어 있으며, 뷰가 실행될 때는 TEXT에 기록된 SELECT에 문장이 다시 실행되면서 결과를 보여주는 구조이다.

 

     > VIEW 옵션

       -> 1. OR REPLACE 옵션

          -> 생성한 뷰가 존재하면 뷰를 갱신함

       -> 2. FORCE / NOFORCE 옵션

          -> FORCE옵션은 기본 테이블이 존재하지 않더라도 뷰 생성

          -> NOFORCE 옵션이 기본값으로 지정되어 있다.

       -> 3. WITH READ ONLY 옵션

          -> 뷰에 대해 조회만 가능하고 삽입, 수정 삭제등은 불가능하게 한다.

 

       -> OR REPLACE 옵션

 

 

       -> FORCE / NOFORCE 옵션

 

 

 

       -> WITH CHECK OPTION

 

       -> WITH READ OLNY 옵션 적용 되면 DML작업을 할 수 없다.

 

 

     > 인라인 뷰(INLINE-VIEW)

       -> 일반적으로 FROM절에 사용된 서브쿼리의 결과 화면에 별칭을 붙인 것을 말한다.

       -> FROM절에 서브쿼리를 직접 사용해도 되고, 따로 뷰를 생성 후 FROM절에 생성한 뷰를 사용해도 된다.

 

 

 

     > 시퀀스(SEQUENCE)

       -> 순차적으로 정수 값을 자동으로 생성하는 객체로 자동 번호 발생기 역할을 한다.

       -> 시퀀스 수정 시 CREATE에 사용한 옵션을 변경 가능하다.

       -> 단 START WITH 값 변경은 불가하기 때문에 변경하려면 삭제 후 다시 생성해야 한다.

 

       -> 표현식

          -> CREATE SEQUENCE 시퀀스명

          -> [START WITH 숫자]                            -> 처음 발생시킬 시작 값, 기본값 1

          -> [INCREMENT BY 숫자]                       -> 다음 값에 대한 증가치, 기본값 1

          -> [MAXVALUE 숫자 | NOMAXVALUE]  -> 발생시킬 최대값, 10의 27-1까지 가능

          -> [MINVALUE 숫자 | NOMINVALUE]     -> 발생시킬 최소값, -10의 26승

          -> [CYCLE | NOCYCLE]                          -> 시퀀스가 최대값까지 증가 완료 시, CYCLE은 START WITH 설정 값으로 돌아감.

                                                                                   NOCYCLE은 에러 발생

          -> [CACHE | NOCACHE]                         -> CACHE는 메모리 상에서 시퀀스 값 관리, 기본값 20

 

 

     > SEQUENCE | NEXTVAL/CURRVAL 사용 가능 여부

 

사용 가능	사용 불가
서브 쿼리가 아닌 SELECT문	VIEW의 SELECT절
INSERT문의 SELECT절	DISTINCT 키워드가 있는 SELECT문
INSERT문의 VALUE절	GROUP BY, HAVING, ORDER BY 절이 있는 SELECT문
UPDATE문의 SET절	SELECT, DELETE, UPDATE의 서브쿼리
	CREATE TABLE, ALTER TABLE 명령의 DEFAULT값

 

 

       -> 시퀀스(SEQUENCE) 실습 코드

 

 

       -> NEXTVAL를 한 번이라도 수행하지 않고 CURRVAL을 실행할 시 오류 발생.

       -> CURRVAL은 마지막으로 수행된 NEXTVAL값을 저장해서 보여주는 값이다.

 

 

 

       -> 증가치 5를 설정해놔서 305 출력이 된다.

 

 

       -> 마지막으로 호출된 NEXTVAL의 값을 보여준다.

 

 

 

-- 매번 새로운 사번이 발생되는 시퀀스 생성
CREATE SEQUENCE SEQ_EID
START WITH 910;
 
-- 매번 새로운 사번이 발생되는 시퀀스 사용
INSERT INTO EMPLOYEE
VALUES(SEQ_EID.NEXTVAL,'홍길동', '666666-6666666', 'HONG@KH.OR.KR', '01011112222', 'D2', 'J2',
        50000000, 0.1, NULL, SYSDATE, NULL, DEFAULT);
        
INSERT INTO EMPLOYEE
VALUES(SEQ_EID.NEXTVAL,'도깨비', '666666-6666666', 'HONG@KH.OR.KR', '01011112222', 'D2', 'J2',
        50000000, 0.1, NULL, SYSDATE, NULL, DEFAULT);
        
ROLLBACK;

       -> 910번부터 새로운 사번이 발생되는 시퀀스를 활용해 데이터 생성

 

 

 

 

     > 객체 : INDEX

       -> SQL 명령문의 처리 속도를 향상시키기 위해서 컬럼에 대해 생성하는 오라클 객체로 내부 구조는 B*TREE 형식으로 구성되어 있음.

 

 

     > INDEX 장점 | 단점

       -> 장점 : 검색 속도가 빨라지고 시스템에 걸리는 부하를 줄여 시스템 전체 성능 향상

          -> 데이터 정렬시 ORDER BY로 정렬하는 것 보다 인덱스(EX. WEHRE절)를 활용하는 것이 더 좋은 성능을 보인다.

       -> 단점 : 인덱스를 위한 추가 저장 공간이 필요하고, 인덱스를 생성하는데 시간이 걸리며, 데이터의 변경 작업(INSERT/UPDATE/DELETE)이 자주 일어날 경우 오히려 성능 저하

 

 

     > INDEX 표현식

       -> CREATE [UNIQUE] INDEX 인덱스 명

       -> ON 테이블 명(컬럼 명, 컬럼 명 | 함수 명, 함수 계산식);

 

     > INDEX 구조

       -> KEY / ROWID로 구성되어 여러 테이블들의 행들마다 가지는 고유의 ID

       -> ROWID : 색인 역할

 

     > INDEX 종류

       -> 고유 인덱스 (UNIQUE INDEX)

          -> 중복 값이 포함될 수 없음

          -> PRIMARY KEY 제약조건을 생성하면 자동으로 생성됨

          -> UNIQUE INDEX는 중복 값이 있는 컬럼에 생성 시 에러 발생

 

       -> 비고유 인덱스 (NONUNIQUE INDEX)

          -> 빈번하게 사용되는 일반 컬럼을 대상으로 생성

          -> 주로 성능 향상을 위한 목적으로 생성

          -> NONUNIQUE INDEX는 중복 값이 있는 컬럼에도 생성이 가능하다

 

       -> 단일 인덱스 (SINGLE INDEX)

          -> 한 개의 컬럼으로 구성한 인덱스

 

       -> 결합 인덱스 (COMPOSITE INDEX)

          -> 두 개 이상의 컬럼으로 구성한 인덱스

          -> COMPOSITE INDEX는 두 개 이상의 컬럼을 하나의 인덱스로 생성이 가능하다.

          -> COLUMN_POSITION의 순서에 의해 성능이 차이날 수 있다.

 

       -> 함수 기반 인덱스 (FUNCTION-BASED INDEX) - oracle8i부터 지원

          -> SELECT절이나 WHERE절에 산술 계산식이나 함수식이 사용된 경우 계산식은 인덱스의 적용을 받지 않음

 

 

     > INDEX 재생성

       -> DML 작업(특히 DELETE 명령)을 수행한 경우, 해당 인덱스 엔트리가 논리적으로만 제거되고, 실제 엔트리는 그냥 남아 있게 되므로 제거된 인덱스가 필요없는 공간을 차지하고 있지 않도록 인덱스를 재생성 해야한다.

       -> 표현식 : ALTER INDEX 인덱스명 REBUILD;

 

 

       -> OPTION : FULL -> 테이블 전체 스캔(FULL SCANNING)

       -> COST는 해당 쿼리가 동작되면서 소요되는 비용으로 보면 되는데, 숫자가 높을 수록 오라클이 많은 일을 하고 있다.(무거움)