[문제] 아래에서 설명하는 테이블을 생성하시오.
테이블 이름은 YEWON이다.
칼럼 ID는 고정길이가 10이며 기본키이다.
칼럼 NAME의 최대 길이는 20이며 NULL을 허용하지 않는다.
칼럼 AGE는 숫자 3자리이며 기본값은 1이다.
Create Table YEWON(
ID CHAR(10) PRIMARY KEY,
NAME VARCHAR2(20) NOT NULL,
AGE NUMBER(3) DEFAULT 1
);[문제] 아래에서 설명하는 가족 테이블을 생성하시오.
가족 테이블에는 칼럼 이름, 나이, 부양사번이 있다.
여기서, 가족 테이블의 부양사번은 사원 테이블의 사번을 참조하는 외래키이다.
Create Table 가족(
이름 VARCHAR2(20),
나이 NUMBER(3)
부양사번 VARCHAR2(20),
Constraint family_pk Primary Key(이름, 부양사번),
Constraint family_fk Foreign Key(부양사번) References 사원 (사번)
);[문제] 직원 테이블 매니저 칼럼에 인덱스 idx_mgr를 생성하는 SQL문을 작성하시오
CREATE INDEX inx_mgr on 직원(매니저);[정리] DELETE FROM VS TRUNCATE
- Truncate
- TRUNCATE TABLE 테이블은 데이터를 빠르게 삭제해서 로그를 기록하지 않는다.
- TRUNATE TABLE은 특정만을 삭제할 수 없다.
- 외래키 무결성을 확인하지 않고 테이블에 대해서 LOCK을 취득 후 일괄적으로 빠르게 삭제한다.
- 테이블 용량도 초기화된다.
- 자동으로 COMMIT 된다.
- WHERE절을 지정할 수 없다.
- ROLLBACK이 될 수 없다.
- Delete From
- DELETE FROM문은 테이블의 데이터는 삭제할 수는 있으나, 테이블 구조는 삭제할 수 없다.
- 특정 행만을 삭제할 수 있다.
[정리] 분산 데이터베이스
| 장점 | 단점 |
|---|---|
| - 빠른 응답속도와 통신비용 절감 - 데이터의 가용성과 신뢰성 증가 - 효용성과 융통성 - 각 지역 사용자의 요구 수용 증대 - 시스템 규모의 적절한 조절 - 신뢰성과 가용성 - 지역 자ㅣ성, 점증적 시스템 용량 확장 | - 데이터 무결성에 대한 위협 - 오류의 잠재성 증대 - 처리 비용의 증대 - 통제의 어려움 - 소프트웨어 개발 비용 - 불규칙한 응답 소고 - 설계, 관리의 복잡성과 비용 |
[정리] 인덱스
- 주로 B-Tree 인덱스로 되어 있다.
- B-Tree 인덱스는 Equal 조건뿐 아니라 Between, >과 같은 연산자로 검색하는 범위 검색에도 사용될 수 있다.
- 인덱스는 테이블 기반으로 선택적으로 생성할 수 있는 구조이다.
- 기본 목적은 검색 성능 최적화이다.
- DML 작업은 테이블과 인덱스를 함께 변경하므로 느려지는 단점이 존재한다.
- 테이블에 만들 수 있는 인덱스의 수는 제한이 없으나, 너무 많이 만들면 오히려 성능부하가 발생한다.
- 외래키가 설계되어 있지만 인덱스가 없는 상태에서는 입력/삭제/수정의 부하가 덜생긴다.
- 자주 사용하는 컬럼이나, 조인에 사용하는 컬럼, 참조 제약조건이 있는 컬럼은 인덱스를 쓰는 것이 빠르다.
[정리]Row Changing과 Row Migration
| 구분 | Row Changing | Row Migration |
|---|---|---|
| 정의 | 하나의 Row를 하나의 블록에서 저장할 수 없어서 여러 블록에 걸쳐서 저장하는 현상 | Update로 인하여 늘어나는 공간을 저장할 공간이 없어서 다른 블록으로 Row를 옮기는 현상 |
| 특성 | Initial Row Piece(행 조각)과 Row Pointer로 블록 내에 저장됨 | 기존 블록에는 Migration되는 데이터의 row header와 블록 주소값을 갖게 되고, 새로운 블록에는 Migration 되는 데이터가 저장됨. |
| 문제점 | Row의 정보를 검색하기 위해 하나 이상의 데이터블록을 Scan해야하기 대문에 성능이 감소됨. | Migration된 Row를 읽기 전에 기존 블록에서 헤더를 통해 Migration된 Row를 읽기 때문에 성능이 감소됨. |
| 해결책 | 블록의 크기를 크게 만듦 | - PCTFREE를 크게 설정 - 객체를 Export하고 삭제한 후, Import - 객체를 Migration하고 Truncate |
[정리] Natural Join
- 자연 조인은 등가 조인하는 방법 중 하나이다.
- 동일한 타입과 이름을 가진 컬럼을 이용한다는 조건으로 조인을 간단히 표현하는 방법이다.
- 반드시 두 테이블 간에 동일한 이름과 타입을 가진 컬럼이 필요하다.
- 조인에 이용되는 컬럼을 명시하지 않아도 자동으로 조인된다.
- 동일한 이름의 컬럼이더라도, 데이터 타입이 다르면 에러가 발생한다.
- 조인하는 테이블 간의 동일 컬럼이 SELECT절에 기술되어도 테이블 이름을 생략해야 한다.
- 예시
SELECT DEPTNO, EMPNO, ENAME, DNAME FROM EMP NATURAL JOIN DEPT[정리] USNING을 활용한 JOIN
- 자연 조인에서 사용하는 테이블 간에 동일한 이름과 형식의 컬럼이 둘 이상인 경우 자연 조인을 사용할 수 없다.
- NATURAL 절과 USING절은 함께 사용될 수 없다.
- 조인 컬럼은 괄호로 묶어서 기술해야 한다.
SELECT DEPTNO, EMPNO, ENAME, DNAME FROM EMP JOIN DEPT USING(DEPTNO);[정리] BETWEEN
SELECT * FROM EMPLOYEE WHERE 200 BETWEEN COL1 AND COL2- COL1과 COL2 사이에 200이 있어야 한다는 말.
- 즉, COL1<=200 && 200<=COL2
[정리] 트랜잭션의 특징
| 특징 | 설명 |
|---|---|
| 원자성(Atomicity) | 트랜잭션은 더 이상 분해가 부간으한 업무의 최소 단위이므로, 전부 처리되거나 그렇지 않으면 하나도 처리되지 않아야 한다. |
| 일관성(Consistency) | 일관된 상태의 데이터베이스에서 하나의 트랜잭션을 성공적으로 완료하고 나면 그 데이터베이스는 여전히 일관된 상태여야 한다. |
| 고립성(Isolation) | 실행 중인 트랜잭션의 중간 결과를 다른 트랜잭션이 접근할 수 없다. |
| 영속성(Durability, 지속성) | 트랜잭션이 일단 실행을 성공적으로 완료하면 그 결과는 데이터베이스에 영속적으로 저장한다. |
[정리] COMMIT되지 않은 데이터
- 나 자신이 볼 수 있다.
- 다른 사용자가 볼 수 없다.
- 다른 사용자가 COMMIT되지 않은 변경된 데이터를 고칠 수 없다.
- ROLLBACK명령어로 바로 직전에 COMMIT한 지점까지 데이터를 복구할 수 있다.
[정리] Escape
- LIKE 연산으로 '_'나 '%'가 들어간 문제를 검색하기 위해서는 Escape 명령어를 사용한다.
- 사용 방법은 '_'나 '%' 앞에 ESCAPE로 특수 문자를 지정하면 검색할 수 있다.
SELECT * FROM EMPLOYEE
WHERE NAME LIKE '%@_%' ESCAPE '@';[정리] NULL 관련 함수
- NVL
- NVL 함수는 NULL 값을 다른 값으로 바꿀 때 사용. 모든 데이터 타입에 적용 가능.
SELECT EMPNO, NVL(MGR, 0) MGR FROM EMP WHERE DEPTNO=10;- MGR 값이 NULL이면 0으로 바꿔줌.
- NVL2
- DECODE 함수와 비슷
- NVL2(A, B, C) A가 NULL이 아니면 B를 반환하고 NULL이면 C를 반환함
SELECT EMPNO, NVL2(MGR, 1, 0) MGR FROM EMP WHERE DEPTNO=10;- MGR 값이 NULL이 아니면 1, NULL이면 0을 출력함.
- NULLIF
- NULLIF(A, B)
- A와 B의 값이 같으면 NULL, 그렇지 않으면 A를 반환
- CASE WHEN A = B THEN NULL ELSE A END
- COALESCE
- NVL 함수와 비슷하다
- COALESCE(A, B, C, ...) A가 NULL이면 B, 그렇지 않으면 (C, ...)의 값을 반환.
[정리] Subquery
SELECT (A) FROM (B) WHERE X=(C);- A: 스칼라 서브쿼리, B: 인라인뷰, C: 서브쿼리
[정리] PL/SQL 특징
- PL/SQL은 Block 구조로 되어 있어 각 기능별로 모듈화가 가능하다.
- 변수, 상수 등을 선언하여 SQL 문장 간 값 교환을 한다.
- IF/LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
- DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
- PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.
- PL/SQL은 응용 프로그램의 성능을 향상시킨다.
- PL/SQL은 여러 SQL 문장을 Block으로 묶고, 한 번에 Block 전부를 서버로 보내 통신량을 줄일 수 있다.
