TIL - 23. DDL, VIEW, SEQUENCE, INDEX [230811]

송원철·2023년 8월 12일
0

너무 아름다운 다운 다운 다운 뷰

DDL

ALTER

테이블에 정의된 내용을 수정할 때 사용하는 데이터 정의어로
컬럼의 추가/삭제, 제약조건의 추가/삭제, 컬럼의 자료형 변경, DEFAULT 값 변경,
테이블 명/컬럼 명/제약 조건 명 변경 등을 할 수 있음

컬럼 추가

컬럼 수정

컬럼 삭제


제약조건 추가

제약조건 삭제

컬럼 이름 변경

제약조건 이름 변경

테이블 이름 변경

DROP

데이터베이스 객체를 삭제하는 구문

DDL DB

-- DDL(Data Definition Language)
-- 객체를 만들고(CREATE), 바꾸고(ALTER), 삭제(DROP)하는 데이터 정의 언어


/*
 * ALTER(바꾸다, 수정하다, 변조하다)
 * 
 * - 테이블에서 수정할 수 있는 것
 * 
 * 1) 제약 조건(추가/삭제) 
 * 2) 컬럼(추가/수정/삭제)
 * 3) 이름 변경(테이블명, 제약조건명, 컬럼명)
 * 
 * */


----------------------------------------------

-- 1. 제약조건(추가/삭제)

-- [작성법]
-- 1) 추가 : ALTER TABLE 테이블명
-- 					ADD [CONSTRAINT 제약조건명] 제약조건(컬럼명)
-- 					[REFERENCES 테이블명[(컬럼명)]] <-- FK 인 경우 추가


-- 2) 삭제 : ALTER TABLE 테이블명
-- 					DROP CONSTRAINT 제약조건명;

-- * 수정은 별도 존재하지 않음! --> 삭제 후 추가를 이용해서 수정 *


-- DEPARTMENT 테이블 복사 (컬럼명, 데이터타입, NOT NULL 만 복사)
CREATE TABLE DEPT_COPY
AS SELECT * FROM DEPARTMENT;


SELECT * FROM DEPT_COPY;


-- DEPT_COPY의 DEPT_TITLE 컬럼에 UNIQUE 추가
ALTER TABLE DEPT_COPY 
ADD CONSTRAINT DEPT_COPY_TITLE_U UNIQUE(DEPT_TITLE);


-- DEPT_COPY의 DEPT_TITLE 컬럼에 UNIQUE 삭제
ALTER TABLE DEPT_COPY
DROP CONSTRAINT DEPT_COPY_TITLE_U;


-- *** DEPT_COPY의 DEPT_TITLE 컬럼에 NOT NULL 제약조건 추가/삭제 ***
ALTER TABLE DEPT_COPY
ADD CONSTRAINT DEPT_COPY_TITLE_NN NOT NULL(DEPT_TITLE);
--> NOT NULL 제약조건은 새로운 제약조건을 추가하는 것이 아닌
-- 컬럼 자체에 NULL 허용/비허용을 제어하는 성질 변경의 형태로 인식됨.


-- MODIFY (수정하다) 구문을 사용해서 NULL 제어
ALTER TABLE DEPT_COPY
MODIFY DEPT_TITLE NOT NULL; -- DEPT_TITLE 컬럼을 NOT NULL 로 수정

ALTER TABLE DEPT_COPY
MODIFY DEPT_TITLE NULL; -- DEPT_TITLE 컬럼을 NULL 로 수정


--------------------------------------------------------------------------


-- 2. 컬럼(추가/수정/삭제)

-- 컬럼 추가
-- ALTER TABLE 테이블명 ADD(컬럼명 데이터타입 [DEFAULT '값']);

-- 컬럼 수정
-- ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입; -> 데이터 타입 변경

-- ALTER TABLE 테이블명 MODIFY 컬럼명 DEFAULT '값'; -> DEFAULT 값 변경

-- ALTER TABLE 테이블명 MODIFY 컬럼명 NULL / NOT NULL; -> NULL 여부 변경



-- 컬럼 삭제
-- ALTER TABLE 테이블명 DROP(삭제할컬럼명);
-- ALTER TABLE 테이블명 DROP COLUMN 삭제할컬럼명;

--> * 컬럼 삭제 시 주의사항 *
-- 테이블이란? 행과 열로 이루어진 DB의 가장 기본 객체
-- 테이블은 최소 1개 이상의 컬럼이 존재해야 되기 때문에
-- 모든 컬럼을 다 삭제할 수는 없다!


SELECT * FROM DEPT_COPY;

-- CNAME 컬럼 추가
ALTER TABLE DEPT_COPY ADD(CNAME VARCHAR2(30));

-- LNAME 컬럼 추가(기본값 '한국')
ALTER TABLE DEPT_COPY ADD(LNAME VARCHAR2(30) DEFAULT '한국');
--> 컬럼이 생성되면서 DEFAULT 값이 자동 삽입됨.


-- D10 개발1팀 추가
INSERT INTO DEPT_COPY
VALUES('D10', '개발1팀', 'L1', DEFAULT, DEFAULT);
-- ORA-12899: "KH"."DEPT_COPY"."DEPT_ID" 열에 대한 값이 너무 큼(실제: 3, 최대값: 2)


-- DEPT_ID 컬럼 수정
ALTER TABLE DEPT_COPY MODIFY DEPT_ID VARCHAR2(5);
--> 다시 위 INSERT 실행 -> 성공!


-- LNAME 의 기본값을 'KOREA' 로 수정
ALTER TABLE DEPT_COPY MODIFY LNAME DEFAULT 'KOREA';
--> 기본값을 변경했다고 해서 기존 데이터가 변하지는 않는다.

-- LNAME '한국' -> 'KOREA'로 변경
UPDATE DEPT_COPY SET LNAME = DEFAULT WHERE LNAME = '한국';

COMMIT;


-- 모든 컬럼 삭제
ALTER TABLE DEPT_COPY DROP(LNAME);
ALTER TABLE DEPT_COPY DROP COLUMN CNAME;
ALTER TABLE DEPT_COPY DROP(LOCATION_ID);
ALTER TABLE DEPT_COPY DROP(DEPT_TITLE);
ALTER TABLE DEPT_COPY DROP(DEPT_ID);
-- ORA-12983: 테이블에 모든 열들을 삭제할 수 없습니다


-- 테이블 삭제
DROP TABLE DEPT_COPY;


-- DEPARTMENT 테이블 복사해서 DEPT_COPY 생성
CREATE TABLE DEPT_COPY
AS SELECT * FROM DEPARTMENT;
-- 컬럼명, 데이터타입, NOT NULL 여부만 복사


SELECT * FROM DEPT_COPY;


-- DEPT_COPY 테이블에 PK 추가 (컬럼 : DEPT_ID, 제약조건명 : D_COPY_PK)
ALTER TABLE DEPT_COPY ADD CONSTRAINT D_COPY_PK PRIMARY KEY(DEPT_ID);


-- 3. 이름변경(컬럼, 제약조건, 테이블)

-- 1) 컬럼명 변경 (DEPT_TITLE -> DEPT_NAME)
ALTER TABLE DEPT_COPY RENAME COLUMN DEPT_TITLE TO DEPT_NAME;


-- 2) 제약조건명 변경(D_COPY_PK -> DEPT_COPY_PK)
ALTER TABLE DEPT_COPY RENAME CONSTRAINT D_COPY_PK TO DEPT_COPY_PK;


-- 3) 테이블명 변경 (DEPT_COPY -> DCOPY)
ALTER TABLE DEPT_COPY RENAME TO DCOPY;


SELECT * FROM DEPT_DOPY;
SELECT * FROM DCOPY;


--------------------------------------------------------------------------


-- 4. 테이블 삭제

-- DROP TABLE 테이블명 [CASCADE CONSTRAINTS];

-- 1) 관계가 형성되지 않은 테이블 삭제
DROP TABLE DCOPY;


-- 2) 관계가 형성된 테이블 삭제
CREATE TABLE TB1(
	TB1_PK NUMBER PRIMARY KEY,
	TB1_COL NUMBER
);
CREATE TABLE TB2(
	TB2_PK NUMBER PRIMARY KEY,
	TB2_COL NUMBER REFERENCES TB1
);

-- TB1에 샘플 데이터 삽입
INSERT INTO TB1 VALUES(1,100);
INSERT INTO TB1 VALUES(2,200);
INSERT INTO TB1 VALUES(3,300);

COMMIT;

-- TB2에 샘플 데이터 삽입
INSERT INTO TB2 VALUES(11,1);
INSERT INTO TB2 VALUES(22,2);
INSERT INTO TB2 VALUES(33,3);

-- TB1 삭제
DROP TABLE TB1;
-- ORA-02449: 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있습니다
--> 해결 방법
-- 1) 자식, 부모 테이블 순서로 삭제
-- 2) ALTER 를 이용해서 FK 제약조건을 삭제 후 TB1 삭제
-- 3) DROP TABLE 삭제옵션 CASCADE CONSTRAINTS 사용
		--> 삭제하려는 테이블과 연결된 FK 제약조건을 모두 삭제
		
DROP TABLE TB1 CASCADE CONSTRAINTS;
--> 삭제 성공


--------------------------------------------------------------------------

/* DDL 주의사항 */

-- 1) DDL 은 COMMIT/ROLLBACK 이 되지 않는다.
--> ALTER, DROP 을 신중하게 진행해야함.

-- 2) DDL과 DML 구문을 섞어서 수행하면 안된다!
--> DDL 수행중에 DML 수행을 하면 DML 이 COMMIT 을 수행하지 않아도, 자동 COMMIT;

SELECT * FROM TB2;

COMMIT;

INSERT INTO TB2 VALUES(14, 4);
INSERT INTO TB2 VALUES(15, 5);

SELECT * FROM TB2;


-- 컬럼명 변경 DDL
ALTER TABLE TB2 RENAME COLUMN TB2_COL TO TB2_COLUMN;


ROLLBACK;
SELECT * FROM TB2;

VIEW




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






VIEW 구조

VIEW 옵션

  1. OR REPLACE 옵션
    생성한 뷰가 존재하면 뷰를 갱신함
  2. FORCE/NOFORCE 옵션
    FORCE옵션은 기본 테이블이 존재하지 않더라도 뷰 생성
    NOFORCE 옵션이 기본 값으로 지정되어 있음
  3. WITH CHECK OPTION 옵션
    옵션을 설정한 컬럼의 값을 수정 불가능하게 함(삭제는 가능)
  4. WITH READ ONLY 옵션
    뷰에 대해 조회만 가능하고 삽입, 수정, 삭제 등은 불가능하게 함

INLINE VIEW

일반적으로 FROM절에 사용된 서브쿼리의 결과 화면에 별칭을 붙인 것을 말함
FROM절에 서브쿼리를 직접 사용해도 되고 따로 뷰를 생성 후 FROM절에 생성한 뷰를 사용해도 됨

VIEW DB

/* VIEW
	
	- SELECT문의 실행 결과(RESULT SET)를 저장하는 객체
	- 논리적 가상 테이블
		-> 테이블 모양을 하고는 있지만
			실제로 값을 저장하고 있지는 않음.

	** VIEW 사용 목적 **
	1) 복잡한 SELECT문을 쉽게 재사용하기 위해서 사용.
	2) 테이블의 진짜 모습을 감출 수 있어 보안상 유리함.
	
	***** VIEW 사용 시 주의사항 *****
	1) 가상의 테이블(실제 테이블 X) -> ALTER 구문 사용 불가
	2) VIEW를 이용한 DML(INSERT/UPDATE/DELETE)가 
	   가능한 경우도 있지만 
	   많은 제약이 따르기 때문에 SELECT 용도로 사용 하는 것을 권장.


    [VIEW 생성 방법]
    CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름 [(alias[,alias]...)]
    AS subquery
    [WITH CHECK OPTION]
    [WITH READ ONLY];
    
    -- 1) OR REPLACE 옵션 : 기존에 동일한 뷰 이름이 존재하는 경우 덮어쓰고, 
    --                      존재하지 않으면 새로 생성.
    -- 2) FORCE / NOFORCE 옵션
    --      FORCE : 서브쿼리에 사용된 테이블이 존재하지 않아도 뷰 생성
    --      NOFORCE : 서브쿼리에 사용된 테이블이 존재해야만 뷰 생성(기본값)
    -- 3) WITH CHECK OPTION 옵션 : 옵션을 설정한 컬럼의 값을 수정 불가능하게 함.
    -- 4) WITH READ ONLY 옵션 : 뷰에 대해 조회만 가능(DML 수행 불가)    
*/

-- 사번, 이름, 부서명, 직급명 조회 결과를 저장하는 VIEW 생성
CREATE OR REPLACE VIEW V_EMP
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
	FROM EMPLOYEE
	JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
	JOIN JOB USING(JOB_CODE);

-- ORA-01031: 권한이 불충분합니다
--> 1) SYS 관리자 계정 접속
--> 2) ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;
--> 3) GRANT CREATE VIEW TO kh;
--> VIEW 생성 구문 다시 실행

ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;
GRANT CREATE VIEW TO kh;

-- 생성된 view 통해서 조회
SELECT * FROM V_EMP;


-----------------------------------------------------------------------

-- OR REPLACE 확인 + 별칭
CREATE OR REPLACE VIEW V_EMP
AS SELECT EMP_ID 사번, EMP_NAME 이름, DEPT_TITLE 부서명, JOB_NAME 직급명
	FROM EMPLOYEE
	JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
	JOIN JOB USING(JOB_CODE);

-- ORA-00955: 기존의 객체가 이름을 사용하고 있습니다.
-- OR REPLACE 추가 후 실행 -> 성공
--> 기존 V_EMP를 새로운 VIEW 덮어쓰기

SEQUENCE

순차적으로 정수 값을 자동으로 생성하는 객체로 자동 번호 발생기 역할을 함


NEXTVAL/CURRCAL 사용 가능 여부


SEQUENCE DB

/* SEQUENCE(순서, 연속, 수열)
	- 순차적 번호 자동 발생기 역할의 객체
	
	-> SEQUENCE 객체를 생성해서 호출하게되면
	   지정된 범위 내에서 일정한 간격으로 증가하는 숫자가
	   순차적으로 출력됨.
	   
	 EX) 1부터 10까지 1씩 증가하고 반복하는 시퀀스 객체
	 1 2 3 4 5 6 7 8 9 10 1 2 3 4 5 6 7 8 9 10
	 
	 ** SEQUENCE는 주로 PK역할의 컬럼에 삽입되는 값을 만드는 용도로 사용 **    
	--> 인위적 주식별자
	
	  [작성법]
	  
  CREATE SEQUENCE 시퀀스이름
  [STRAT WITH 숫자] -- 처음 발생시킬 시작값 지정, 생략하면 자동 1이 기본
  [INCREMENT BY 숫자] -- 다음 값에 대한 증가치, 생략하면 자동 1이 기본
  [MAXVALUE 숫자 | NOMAXVALUE] -- 발생시킬 최대값 지정 (10의 27승 -1)
  [MINVALUE 숫자 | NOMINVALUE] -- 최소값 지정 (-10의 26승)
  [CYCLE | NOCYCLE] -- 값 순환 여부 지정
  [CACHE 바이트크기 | NOCACHE] -- 캐쉬메모리 기본값은 20바이트, 최소값은 2바이트

-- 시퀀스의 캐시 메모리는 할당된 크기만큼 미리 다음 값들을 생성해 저장해둠
-- --> 시퀀스 호출 시 미리 저장되어진 값들을 가져와 반환하므로 
--     매번 시퀀스를 생성해서 반환하는 것보다 DB속도가 향상됨.


    ** 시퀀스 사용 방법 **
    
    1) 시퀀스명.NEXTVAL : 다음 시퀀스 번호를 얻어옴. (INCREMENT BY만큼 증가된 값)
                          단, 시퀀스 생성 후 첫 호출인 경우 START WITH의 값을 얻어옴.
    
    2) 시퀀스명.CURRVAL : 현재 시퀀스 번호 얻어옴.
                          단, 시퀀스 생성 후 NEXTVAL 호출 없이 CURRVAL를 호출하면 오류 발생.
	
*/


CREATE SEQUENCE SEQ_TEST;

-- * CURRVAL 주의 사항 *
--> CURRVAL는 마지막 NEXTVAL 호출값을 다시 보여주는 기능!
--> NEXTVAL를 먼저 호출해야 CURRVAL 호출이 가능! 

SELECT SEQ_TEST.CURRVAL FROM DUAL;
--ORA-08002: 시퀀스 SEQ_TEST.CURRVAL은 이 세션에서는 정의 되어 있지 않습니다


SELECT SEQ_TEST.NEXTVAL FROM DUAL; -- 1

SELECT SEQ_TEST.CURRVAL FROM DUAL; -- 1

SELECT SEQ_TEST.NEXTVAL FROM DUAL; -- 2
SELECT SEQ_TEST.NEXTVAL FROM DUAL; -- 3
SELECT SEQ_TEST.CURRVAL FROM DUAL; -- 3


------------------------------------------------------------------------

-- 실제 사용 예시

CREATE TABLE EMP_TEMP
AS SELECT EMP_ID, EMP_NAME FROM EMPLOYEE;

SELECT * FROM EMP_TEMP;


-- 223 번부터 10씩 증가하는 시퀀스 생성

CREATE SEQUENCE SEQ_TEMP
START WITH 223		-- 223 번 시작
INCREMENT BY 10 	-- 10씩 증가
NOCYCLE		-- 반복 X (기본값)
NOCACHE; 	-- 캐시 X (기본 20)


-- EMP_TEMP 테이블에 사원 정보 삽입
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL, '홍길동'); -- 223
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL, '고길동'); -- 233
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL, '김길동'); -- 243

SELECT * FROM EMP_TEMP
ORDER BY EMP_ID DESC;



---------------------------------------------------

-- SEQUENCE 수정
/*
  ALTER SEQUENCE 시퀀스이름
  [INCREMENT BY 숫자] -- 다음 값에 대한 증가치, 생략하면 자동 1이 기본
  [MAXVALUE 숫자 | NOMAXVALUE] -- 발생시킬 최대값 지정 (10의 27승 -1)
  [MINVALUE 숫자 | NOMINVALUE] -- 최소값 지정 (-10의 26승)
  [CYCLE | NOCYCLE] -- 값 순환 여부 지정
  [CACHE 바이트크기 | NOCACHE] -- 캐쉬메모리 기본값은 20바이트, 최소값은 2바이트
*/

-- SEQ_TEMP를 1씩 증가하는 형태로 변경
ALTER SEQUENCE SEQ_TEMP
INCREMENT BY 1;

INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL, '이길동'); -- 244
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL, '박길동'); -- 245
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL, '최길동'); -- 246

SELECT * FROM EMP_TEMP
ORDER BY EMP_ID DESC;


-- 테이블(EMP_TEMP), 뷰(V_EMP), 시퀀스(SEQ_TEMP) 삭제

DROP TABLE EMP_TEMP;
DROP VIEW V_EMP;
DROP SEQUENCE SEQ_TEMP;

INDEX

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

INDEX 표현식

INDEX 구조

INDEX 종류

  1. 고유 인덱스(UNIQUE INDEX)
    중복 값이 포함될 수 없음
    PRIMARY KEY 제약조건을 생성하면 자동으로 생성됨

  1. 비고유 인덱스(NONUNIQUE INDEX)
    빈번하게 사용되는 일반 컬럼을 대상으로 생성
    주로 성능 향상을 위한 목적으로 생성

  2. 단일 인덱스(SINGLE INDEX)
    한 개의 컬럼으로 구성한 인덱스

  3. 결합 인덱스(COMPOSITE INDEX)
    두 개 이상의 컬럼으로 구성한 인덱스

  4. 함수 기반 인덱스(FUNCTION-BASED INDEX)
    SELECT절이나 WHERE절에 산술 계산식이나 함수식이 사용된 경우
    계산식은 인덱스의 적용을 받지 않음

INDEX 재생성

INDEX 활용한 정렬

INDEX DB

/*  INDEX(색인)
    
- SQL 명령문 중 SELECT의 처리 속도를 향상 시키기 위해 
  컬럼에 대해서 생성하는 객체

- 인덱스 내부 구조는 B* 트리 형식으로 되어있음.


* 인덱스 장점
 - 이진 트리 형식으로 구성되어 있어 자동 정렬 및 검색 속도가 빠름.
 - 조회 시 전체 테이블 내용을 조회하는 것이 아닌
   인덱스가 지정된 컬럼만을 이용해서 조회하기 때문에
   시스템 부하가 낮아져 전체적인 성능이 향상된다.

* 인덱스 단점
 - 데이터 변경(INSERT, UPDATE, DELETE) 작업 빈번한 경우 오히려 성능이 저하되는 문제가 발생.
 - 인덱스도 하나의 객체이다보니 이를 저장하기 위한 별도 공간.
 - 인덱스 생성 시간이 필요 



  --인덱스 생성 방법--

    [작성법]
    CREATE [UNIQUE] INDEX 인덱스명
    ON 테이블명 (컬럼명, 컬럼명, ... | 함수명, 함수계산식);

    
    -- 인덱스가 자동으로 생성되는 경우
    --> PK 또는 UNIQUE 제약조건이 설정되는 경우
*/


-- ** 인덱스를 이용한 검색 방법 **
--> WHERE 절에 인덱스가 지정된 컬럼을 언급하면 된다.

SELECT * FROM EMPLOYEE
WHERE EMP_ID = 215; -- 인덱스 사용 O

SELECT * FROM EMPLOYEE
WHERE EMP_NAME = '대북혼'; -- 인덱스 사용 X


-- 인덱스 확인용 테이블 생성

CREATE TABLE TB_IDX_TEST (
	TEST_NO NUMBER PRIMARY KEY, -- 자동으로 인덱스가 생성됨
	TEST_ID VARCHAR(20) NOT NULL
);


-- TB_IDX_TEST 테이블에 샘플데이터 100만개 삽입 (PL/SQL 사용)
BEGIN
	FOR I IN 1..1000000 
	LOOP
		INSERT INTO TB_IDX_TEST VALUES(I, 'TEST'||I);
	END LOOP;
	COMMIT;
END;
/


-- 샘플데이터 100만개 확인
SELECT COUNT(*) FROM TB_IDX_TEST;

-- 'TEST500000' 찾기

SELECT * FROM TB_IDX_TEST
WHERE TEST_ID = 'TEST500000'; -- 31ms	-- INDEX 사용 X

SELECT * FROM TB_IDX_TEST
WHERE TEST_NO = 500000; 	   -- 2ms	-- INDEX 사용 O
profile
송원철
초보자
이전 포스트

TIL - 22. DCL, DDL [230810]

다음 포스트

TIL - 24. JDBC [230814]

0개의 댓글

관련 채용 정보