VIEW 객체
- 테이블과 유사한 객체
- SELECT 문의 실행결과 집합(가상의 테이블) - 이름이 없음, 이름을 지어주면 저장이 됨.
- 필요한 정보가 여러 테이블에 분산되어 있고 해정 정보를 추출하는 쿼리가 자주 사용되는 경우
- 테이블의 컬럼 중 일부 컬럼만 추출이 필요한 경우
- 특정자료의 접근은 제한하고 결과만 제공하고자 하는 경우(보안)
(사용형식)
CREATE [OR REPLACE][FORCE|NOFORCE] VIEW 뷰이름[(컬럼list)]
-->컬럼list에 뷰의 컬럼명 지정할 수 있음
AS
SELECT 문
[WITH CHECK OPTION][WITH READ ONLY];
. REPLACE : 이미 같은 이름의 뷰가 존재하면 대치
. FORCE : 기준 테이블이 없어도 뷰생성, 기본은 NOFORCE
. 컬럼list : 뷰에서 사용할 컬럼명(생략하면 SELECT문의 컬럼별칭이 뷰의 컬럼명의 되고,
컬럼 별칭이 없는 경우 SELECT문의 컬럼명이 뷰의 컬럼명이 됨)
. WITH CHECK OPTION : SELECT 문의 WHERE 절 조건을 위배하는 뷰에 대한 DML 명령은 수행될 수 없음(오류)
. WITH READ ONLY : 읽기전용 뷰 생성
** 뷰에대한 DML 명령이 제한되더라도 원본테이블에 대한 DML명령은 제한없이 사용되고 그 결과는 뷰에 즉시 반영됨
컬럼LIST는 생략가능 -
1순위. 뷰의 컬럼에 붙여질 컬럼명, 뷰에 붙여진 컬럼명
2순위. SELECT 절에 사용된 컬럼별칭
3순위. AS를 사용하지 않으면 SELECT에 사용된 컬럼명 자체가 뷰의 이름이 됨
원본 테이블이 변하면 VIEW 객체는 자동으로 변함
OR REPLACE : 사용자는 사용 불가
CREATE OR REPLACE = 생성 OR 대치시킴
내가 만드는 뷰가 이미 저장된 뷰를 OVERRIDE함
뷰에서 업데이트하면 원본도 업데이트 됨,
WITH READ ONLY 사용하면 뷰에대해서 UPDATE, DELETE, INSERT를 사용 불가
아예 변경 불가
WITH CHECK OPTION - 원본 테이블의 SELECT 절에 의해서 WHERE절의 조건을 위배하는 데이터로
뷰를 고칠 수 없음 -> 원본테이블 자체는 DML명령 다 가능
WHERE절의 조건을 만족하는 쪽은 변경 가능
=> 생성된 뷰에만 적용 / 동시에 두 가지 모두 사용할 수 없음.
사용예)회원테이블에서 마일리지가 10000을 넘는 회원의 회원번호,회원명,마일리지,직업을 조회하여 그 결과를 뷰로 저장하시오.
CREATE OR REPLACE VIEW V_MEM_MILE(MID,MNAME,MILEAGE,MJOB) -- 뷰객체 생성, 테이블과 같이 사용 가능
AS
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 마일리지,
MEM_JOB AS 직업
FROM MEMBER
WHERE MEM_MILEAGE>=10000;
CREATE OR REPLACE VIEW V_MEM_MILE --같은 이름의 뷰객체가 생성되어도 덮어쓰니까 실행가능
AS
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 마일리지,
MEM_JOB AS 직업
FROM MEMBER
WHERE MEM_MILEAGE>=10000;
SELECT * FROM V_MEM_MILE;
사용예)생성된 뷰(V_MEM_MILE)에서 'b001'회원의 마일리지를 5000으로 변경하시오.
UPDATE V_MEM_MILE
SET MEM_MILEAGE=5000 --변경되면 뷰에서 사라짐. / 뷰를 고치면 원본이 고쳐짐
WHERE MEM_ID='b001';
사용예)회원테이블에서 'b001'회원의 마일리지를 15000으로 변경하시오.
UPDATE MEMBER
SET MEM_MILEAGE=15000
WHERE MEM_ID='b001';
사용예)회원테이블에서 마일리지가 20000이상인 회원의 회원번호,회원명,마일리지,연락처(핸드폰번호)로 읽기전용 뷰를 생성하시오
CREATE OR REPLACE VIEW V_MEM_MILE02
AS
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 마일리지,
MEM_HP AS 핸드폰번호
FROM MEMBER
WHERE MEM_MILEAGE>=20000
WITH READ ONLY;
사용예)뷰 V_MEM_MILE02에서 'r001'회원을 삭제하시오 -- 읽기전용 뷰라서 삭제가 안됨(오류)
DELETE V_MEM_MILE02
WHERE 회원번호='r001';
사용예)MEMBER 테이블에서 'r001'회원을 삭제하시오
--CART에서 부모테이블로 사용중이기 때문에 삭제 안 됨
UPDATE MEMBER
SET MEM_MILEAGE=30000
WHERE MEM_ID ='r001';
SELECT * FROM V_MEM_MILE02;
사용예)사원테이블에서 급여가 3000이하인 사원의 사원번호,사원명,부서명,직책명을 WITH CHECK OPTION 뷰로 생성하시오
CREATE OR REPLACE VIEW V_EMP_SAL
AS
SELECT A.EMPLOYEE_ID,
A.EMP_NAME,
B.DEPARTMENT_NAME,
A.SALARY,
C.JOB_TITLE
FROM HR.EMP A, HR.DEPT B, HR.JOBS C
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
AND A.JOB_ID=C.JOB_ID
AND A.SALARY<3000
WITH CHECK OPTION
사용예)뷰 V_EMP_SAL에서 사원번호 130번 사원의 급여를 3100으로 변경하시오
UPDATE V_EMP_SAL
SET SALARY=3100
WHERE EMPLOYEE_ID=130; --숫자는 오른쪽 정렬, 문자는 왼쪽 정렬(오른쪽 정렬되어 숫자사용가능)
--3000보다 높은 값을 주면 V_EMP_SAL 뷰의 WHERE 조건에 위배됨
/ 조건에 위배되었을 때 뷰를 변경 못하게 함 - WITH CHECK OPTION
UPDATE EMP
SET SALARY=3100
WHERE EMPLOYEE_ID=130;
SELECT * FROM V_EMP_SAL; -- 130번의 급여가 3100이 되어 130번 사원의 정보가 뷰에서 사라짐
ROLLBACK;
COMMIT; SEQUENCE 객체
- 자동으로 증가(감소)되는 숫자를 생성시키는 객체
- 일부 다른 DBMS에서는 숫자컬럼의 속성 중 AUTO INCREMENT 속성으로 구현됨
- 오라클에서는 테이블과 독립된 객체로 제공
(사용형식)
CREATE SEQUENCE 시퀀스명
[START WITH n] --시작 값, 기본은 MIN_VALUE
[INCREMENT BY n] --증감값 (자동으로 10씩 증가시키고싶을 때 n에 10을 대입)
[MAXVALUE n|NOMAXVALUE] --최대값, 기본은 NOMAXVALUE이고 10^27까지 사용
[MINVALUE n|NOMINVALUE] --최소값, 기본은 NOMINVALUE이고 그 값은 1, -10^27까지 사용
[CYCLE|NOCYCLE] --최대(최소)값까지 도달 후 다시 SEQUENCE 생성여부 기본은 NOCYCLE
[CACHE n|NOCACHE] --캐쉬에 미리 만들어 놓고 사용할지 여부 기본은 CACHE 20
[ORDER|NOORDER] --시퀀스 객체 생성을 보증할지 여부 기본은 NOORDER
**시퀀스 객체의 Pseudo Column(의사컬럼) --시스템에서 제공해주는 가상의 컬럼
----------------------------------------------------
Pseudo Column 내용
----------------------------------------------------
시퀀스명.CURRVAL 시퀀스객체의 현재 값
시퀀스명.NEXTVAL 시퀀스객체의 다음 값
---------------------------------------------------- ***해당 세션에서 시퀀스 객체는 적어도 한 번 이상의 .NEXTVAL이 수행된 후 CURRVAL에 값이 배정됨
--맨 처음의 명령은 반드시 NEXTVAL(다음 값 생성, 반환)이어야 함, 그래야 값이 배정됨.
사용예)LPROD테이블에 신규 분류코드를 추가 삽입하기 위한 시퀀스를 생성하고 다음 분류코드를 추가 삽입하시오.
시퀀스는 LPROD_ID 데이터로 사용
[자료]
LPROD_GU : 'P501'
LPROD_NM : '농산물'
LPROD_GU : 'P502'
LPROD_NM : '임산물'
(시퀀스 생성)
CREATE SEQUENCE SEQ_LPROD_ID
START WITH 10;
SELECT SEQ_LPROD_ID.NEXTVAL FROM DUAL;
--시퀀스를 처음 만들면 가리키는 주소가 시퀀스명을 가리킴, NEXTVAL을 해줘야 STATR값인 10이 됨.
SELECT SEQ_LPROD_ID.CURRVAL FROM DUAL;
--시퀀스를 만들고 나서 처음 명령은 NEXTVAL여야 함.(오류)
INSERT INTO LPROD
VALUES(SEQ_LPROD_ID.CURRVAL,'P501','농산물');
INSERT INTO LPROD
VALUES(SEQ_LPROD_ID.NEXTVAL,'P502','임산물');
DELETE LPROD WHERE LPROD_GU='P502'
SELECT * FROM LPROD SYNONYM 객체(동의어)
- 오라클 객체들에 부여된 또 다른 이름
- SQL문에서 사용되는 테이블 별칭과 차이점은 동의어는 로그인된 세션 전체에서 언제나 사용 가능,
테이블 별칭 등은 해당 SQL문에서만 유효 - 다른 소유자의 객체에 접근하거나 긴 객체명을 사용하는 경우 사용하기 간편한 별도의 이름을 부여하기 위해 사용됨
(사용형식)
CREATE [OR REPLACE] SYNONYM 동의어 FOR 객체명;
. '객체명' : 원본 객체명
. '동의어' : 원본 객체명에 부여할 별칭
사용예)HR 계정의 JOB_HISTORY 테이블을 JOB_HIS로 별칭을 부여하여 생성하시오
CREATE OR REPLACE SYNONYM JOB_HIS FOR HR.JOB_HISTORY;
SELECT * FROM JOB_HIS; --HR계정으로 로그인하지 않아도 별칭만으로 조회 가능 INDEX 객체
--요약본에서 주소를 찾고 그 주소에서 객체를 찾음
-
검색의 효율성을 증대시키기 위한 객체
-
데이터베이스 서버의 성능을 결정하는 요소 중 하나
-
데이터 검색, 삽입, 변경시 해당 자료의 선택(WHERE 조건)의 효율성을 위해 사용
-
정렬, 그룹화 등에도 사용
-
별도의 저장공간 및 처리 프로세스 필요
-
데이터의 삽입,삭제가 빈번한 경우 인덱스 파일 재구성에 많은 자원 소요됨
-
인덱스 객체의 종류
1)UNIQUE/NON-UNIQUE INDEX
- 중복 값의 허용 여부에 따른 분류
- UNIQUE인덱스에서도 NULL 값은허용되나 1번만 허용(단 기본키 인덱스는 제외)
- NON-UNIQUE INDEX에서는 중복 값 허용
2)SINGLE/COMPOSITE INDEX
--SINGLE:하나의 컬럼 사용해서 만들어짐 / COMPOSITE:여러 개의 컬럼 사용해서 만들어짐- 인덱스 구성 컬럼의 수에 의한 분류
- 복합 인덱스인 경우 인덱스 구성 컬럼의 수와 컬럼의 순서가 매우 중요
3)Normal Index --Default 인덱스
- 기본 인덱스
- 트리(이진 Tree)구조 사용 - 모든 노드에 평균검색 시간 할당
- 컬럼 값과 rowid를 기반으로 계산된 주소 할당
--rowid도 의사컬럼, 행이 저장된 물리적 주소 위치
4)bitmap Index --bit:이진수(0,1)
- 컬럼 값과 rowid 값에 대한 이진수 조합으로 변환된 값을 주소로 사용
- 추가,삽입,삭제가 빈번한 경우 비효율적
5)Function-Based Normal Index
- 함수가 적용된 컬럼을 대상으로 인덱스 구성
- WHERE 절 사용시 인덱스 구성에 사용된 함수의 구성과 같은 조건문사용이 가장 효율적
(사용형식)
CREATE [UNIQUE|BITMAP] INDEX 인덱스명
ON 테이블명(컬럼명[,컬럼명,...])([ASC|DESC]) --컬럼명에 들어가는 컬럼을 가지고 인덱스를 만듦
(컬럼이 들어가는 순서 중요!)
-- 기본은 NON-UNIQUE INDEX 임
사용예)
CREATE INDEX IDX_PROD ON PROD(PROD_NAME); --인덱스 이름은 IDX로 시작됨
SELECT *
FROM PROD
WHERE PROD_NAME='삼성 캠코더'; **인덱스 삭제
DROP INDEX 인덱스명;
DROP INDEX IDX_PROD;
사용예)장바구니테이블에서 월/일/순번으로 데이터 검색이 자주 발생됨
이 검색의 효율성을 높이기 위한 인덱스 구성
CREATE INDEX IDX_CART
ON CART(SUBSTR(CART_NO,5)); --5번째자리부터 전부다(연도를 제외)
SELECT *
FROM CART
WHERE SUBSTR(CART_NO,5)='050300002';
DROP INDEX IDX_CART; 2)인덱스의 재구성
- 데이터베이스 테이블이 다른 저장공간으로 이동 된 경우
- 원본테이블에 자료의 입출력이 빈번히 발생된 직후
=> 인덱스의 재구성이 필요
(사용형식)
ALTER INDEX 인덱스명 REBUILD;
--인덱스는 수정 불가(컬럼 수를 바꾸거나 컬럼명을 바꾸거나 하는 수정)
