TIL(2023.08.13)

JAKE·2023년 8월 13일
DatabaseTIL

TIL

목록 보기
26/48
post-thumbnail

🏃‍♂️What I learned

1. DDL(Data Definition Language)

DDL(DATA DEFINITION LANGUAGE) : 데이터 정의 언어

  • 객체(OBJECT)를 만들고(CREATE), 수정(ALTER), 삭제(DROP) 등
  • 데이터의 전체 구조를 정의하는 언어
  • DB 관리자나 설계자가 사용
  • ORACLE에서의 객체 : "테이블, 뷰, 시퀀스, 인덱스", 패키지, 트리거,
    프로시져, 함수(FUCTION), 동의어(SYNONYM), 사용자(USER) 등

1. ALTER

  • 테이블 내용 수정
    1) 제약 조건 (추가, 삭제)
    2) 컬럼(추가, 수정, 삭제)
    3) 이름 변경(테이블 명 OR 제약조건 명 OR 컬럼명)
  1. 제약조건(추가/삭제)

[작성법]

// 추가
ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] 제약조건 (컬럼명);	// 기본 제약조건
ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] 제약조건 (컬럼명) 
REFERENCES 참조테이블명(참조컬럼명) 	// 외래키

// 삭제
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

// 수정은 별도 존재하지 않음 --> 삭제 후 추가

//DEPARTMENT 테이블 복사
CREATE TABLE DEPT_COPY AS SELECT * FROM DEPARTMENT;

//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;

// ** 제약조건 명을 설정하지 않은 경우 자동 생성 -> 테이블에서 직접 확인해야함

// *** NOT NULL은 새로운 제약 조건을 추가하는 것이 아니라, 허용/비허용의 성격 
//-> MODIFY : 스위치를 껐다 켰다 

ALTER TABLE DEPT_COPY MODIFY DEPT_TITLE NOT NULL;
// 킬때 
ALTER TABLE DEPT_COPY MODIFY DEPT_TITLE NULL;
// 끌때
  1. 컬럼 (추가, 수정, 삭제)
// 컬럼 추가
// ALTER TABLE 테이블명 ADD(컬럼명 데이터타입[DEFAULT '값']);
ALTER TABLE DEPT_COPY ADD (CNAME VARCHAR2(30));
ALTER TABLE DEPT_COPY ADD (LNAME VARCHAR2(30) DEFAULT '한국');

// 컬럼 수정
// ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입;    <- 데이터 타입 변경
ALTER TABLE DEPT_COPY MODIFY DEPT_ID VARCHAR2(5);

// ALTER TABLE 테이블명 MODIFY 컬럼명 DEFALUT '값';  <- 디폴트값 변경
ALTER TABLE DEPT_COPY MODIFY LNAME DEFAULT 'KOREA'; 
//--> 디폴트 변경 != 기존 데이터 변경

// ALTER TABLE 테이블명 MODIFY 컬럼명 NULL(NOT NULL);     <- NULL 수정

// 컬럼 삭제
// ALTER TABLE 테이블명 DROP (컬럼명);
ALTER TABLE DEPT_COPY DROP(LNAME);

// ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
ALTER TABLE DEPT_COPY DROP COLUMN CNAME;
ALTER TABLE DEPT_COPY DROP COLUMN DEPT_ID;
ALTER TABLE DEPT_COPY DROP COLUMN DEPT_TITLE;
ALTER TABLE DEPT_COPY DROP COLUMN LOCATION_ID;
//* 주의 사항 : 최소 1개의 컬럼이 남아야 하기 때문에, 모두 삭제 불가능
  1. 이름변경
// 1) 컬럼명 변경 
ALTER TABLE DEPT_COPY RENAME COLUMN DEPT_TITLE TO DEPT_NAME;

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

// 3) 테이블명 변경
ALTER TABLE DEPT_COPY RENAME TO DCOPY;
  1. 테이블 삭제
// DROP TABLE 테이블명 [CASCADE CONSTRAINTS];

// 1) 관계형성 없을 때
DROP TABLE DCOPY;

// 2) 관계 형성 테이블 삭제 
CREATE TABLE TABLE1(
	TB1_PK NUMBER PRIMARY KEY,
	TB1_COM NUMBER
);

CREATE TABLE TABLE2(
	TB2_PK NUMBER PRIMARY KEY,
	TB2_COM NUMBER REFERENCES TABLE1
);

INSERT INTO TABLE1 VALUES(1, 100);
INSERT INTO TABLE1 VALUES(2, 200);
INSERT INTO TABLE1 VALUES(3, 300);

INSERT INTO TABLE2 VALUES(11, 1);
INSERT INTO TABLE2 VALUES(22, 2);
INSERT INTO TABLE2 VALUES(33, 3);

DROP TABLE TABLE1;   // 불가

// 해결 방법
// 1) 자식, 부모 순으로 삭제
// 2) 제약조건 삭제 후 테이블 삭제(ALTER)
// 3) DROP TABLE 삭제옵션 CASCADE CONSTRAINTS 사용 -> 삭제하려는 FK 제약조건 모두 삭제

DROP TABLE TABLE1 CASCADE CONSTRAINTS;

DDL 주의 사항

1) DDL은 COMMIT, ROLLBACK이 되지 않음
--> ALTER와 DROP을 신중히
2) DDL과 DML 구문을 번갈아 쓰면 안됨
--> DDL이 자동 커밋되어버리기 떄문에 DML이 강제 COMMIT 되어버림

2. 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 수행 불가) 

// 사번, 이름, 부서명, 직급명 조회 결과를 저장하는 뷰를 생성

CREATE OR REPLACE VIEW V_EMP AS 
	SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
	FROM EMPLOYEE 
	JOIN DEPARTMENT ON(DEPT_ID = DEPT_CODE)
	JOIN JOB USING(JOB_CODE);
// 권한 필요 -> 'CREATE VIEW'

GRANT CREATE VIEW TO kh;

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

3. 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 시퀀스이름
[START WITH 숫자] -- 처음 발생시킬 시작값 지정, 생략하면 자동 1이 기본
[INCREMENT BY 숫자] -- 다음 값에 대한 증가치, 생략하면 자동 1이 기본
[MAXVALUE 숫자 | NOMAXVALUE] -- 발생시킬 최대값 지정 (10의 27승 -1)
[MINVALUE 숫자 | NOMINVALUE] -- 최소값 지정 (-10의 26승) ** START WITH != MINVALUE
[CYCLE | NOCYCLE] -- 값 순환 여부 지정
[CACHE 바이트크기 | NOCACHE] -- 캐쉬메모리 기본값은 20바이트, 최소값은 2바이트

시퀀스 사용 방법
1) 시퀀스명.NEXTVAL : 다음 시퀀스 번호를 얻어옴. (INCREMENT BY만큼 증가된 값)
    단, 시퀀스 생성 후 첫 호출인 경우 START WITH의 값을 얻어옴.

2) 시퀀스명.CURRVAL : 현재 시퀀스 번호 얻어옴.
    단, 시퀀스 생성 후 NEXTVAL 호출 없이 CURRVAL를 호출하면 오류 발생.

-- 실제 사용 예시

CREATE TABLE EMP_TEMP
AS SELECT EMP_ID, EMP_NAME FROM EMPLOYEE;

SELECT * FROM EMP_TEMP
ORDER BY EMP_ID DESC;

// 223번부터 10씩 증가하는 시퀀스 생성
CREATE SEQUENCE SEQ_TEMP
START WITH 223 
INCREMENT BY 10
NOCYCLE  -- 반복 X(기본값)
NOCACHE; -- 캐시 X (기본 20)

// EMP_TEMP에 사원정보 삽입
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL, '홍길동');
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL, '고길동');
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.CURRVAL, '김길동');


// 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, '홍개똥');
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL, '고개똥');
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL, '김개똥');


// 테이블(EMP_TEMP), 뷰 (V_EMP), 시퀀스(SEQ_TEMP) 삭제
DROP TABLE EMP_TEMP;
DROP VIEW V_EMP;
DROP SEQUENCE SEQ_TEMP;

4. INDEX

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

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

    SELECT를 주로 할때 사용하는 게 좋음

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

// 인덱스 확인용 테이블 생성
CREATE TABLE TB_IDX_TEST(
 TEST_NO NUMBER PRIMARY KEY,
 TEST_ID VARCHAR2(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;

SELECT COUNT(*) FROM TB_IDX_TEST;

SELECT * FROM TB_IDX_TEST 
WHERE TEST_NO = 500000;  // 인덱스 사용(더 빠름) 

SELECT * FROM TB_IDX_TEST 
WHERE TEST_ID = 'TEST500000';  // 인덱스 미사용
profile
JAKE
이전 포스트

TIL(2023.08.10)

다음 포스트

TIL(2023.08.14)

0개의 댓글