Chapter 1. SQL과 데이터
1. SQL의 역사와 준수사항
현재 존재하는 SQL은 ANS에서 표준으로 정한 SQL과 데이터베이스 제품 별로 제공하는 SQL이 합쳐진 형태의 SQL로 변화되었다.
(1) SQL의 역사
l 1970년 대 말 IBM의 한 연구소에서 데이터베이스의 사용자 편의를 위해 개발
l 최초로 SQL을 이용하는 <SYSTEM R>을 개발 하였지만 상업용으로는
매우 느렸고 1980년대 초 IBM에서 <SQL/DS>와 <DB2>를 발표 하면서 SQL을 지원
l 1986년 ANSI(American National Standard Institute)에서 SQL을 관계형 데이터 베이스 표준 질의어로 채택
(2) SQL 사용시 준수 사항
l 읽기 편리성 제공: 들여쓰기, SQL, 작성 형식 등을 통일하여 읽기 편리성 제공, 다른 사람이 작성한 SQL을 해석하기 위해 많은 시간이 소요되면 결과가 좋아도 잘 작성된 것이 아니다.
l 성능 보장: 실행계획을 고려한 SQL을 작성하여 최소한의 성능 보장, 작성하는 것에 따라 성능이 천차만별이므로 작성시 고려해야 한다.
l 이름 지정 규칙 준수: 테이블 명이나 열 명 등을 규칙에 맞춰 생성하여 작성
l 일관적인 작성: 대소문자 등 모든 SQL에 대해 일관성 유지, 모든 SQL에 적용되어 동일한 형식으로 SQL이 작성 되야 한다는 것이다. 추후에 문제가 발생하거나 관리에 어려움이 생길 수 있다.
SQL > SELECT 사원번호, 이름
FROM 사원 a;
SQL > SELECT 사원번호, 이름 FROM 사원 Z;
SQL > SELECT 부서, 이름
FROM 사원 AS PNPSECURE;3) 부서 열에 어떤 데이터가 있는지 알 수가 없다. 그래서 부서 열을 항상 조회해야 할 것이다. 그래야만 부서 열에 부서번호가 저장되어 있는지 부서이름이 저장되어 있는지 알 수 있다.
2. 데이터 관리
(1) 데이터를 저장하기 위한 구성 요소
l 데이터: 데이터는 업무를 위해 반드시 저장하고 있어야 하는 정보,
l 시스템: 일반적으로 사용하는 컴퓨터를 의미, 서버
l 데이터베이스: 실제 데이터를 저장하기 위한 산업용 소프트웨어, 적은 량이라면 디스크에 저장, 대용량일 경우 소프트웨어를 사용하여 성능과 데이터 관리
l 디스크: 일반 하드디스크, 성능과 안전성이 강화된 서버용 디스크
(2) 데이터베이스와 테이블
테이블?
l 데이터베이스의 기본적인 데이터 저장 단위.
l 사용자가 접근 가능한 모든 데이터를 보유하며 레코드와 컬럼으로 구성.
l 시스템내에서 독립적으로 사용되길 원하는 엔티티를 표현할수 있다.
예를 들면, 회사에서의 고용자나 제품에 대한 주문은 테이블로 표현 가능.
l 두 엔티티간의 관계를 표현할 수 있다. 즉 테이블은 고용자와 그들의 작업
(3) 테이블 행
저장되는 데이터 한건한건이 행에 저장되며 행은 열로 구성되어 있다. 예를들어 카드를 결제했을때 거래내역의 테이블에 행은 카드번호, 거래일자, 가맹점 및 사용액 등으로 내용이 구성되어 저장될 것이다.
(4) 테이블 열
l 행을 구성하는 요소. 위의 예에서 카드번호, 거래일자, 가맹점 및 사용 액 등 해당 행을 구성하는 각각의 요소를 열이 된다.
l 열 단위 추출: 데이터를 추출하기 위해서는 행 단위로 추출하게 된다. 하지만 일부 열만 필요한데 모든 열을 추출한다면 성능 저하 및 운영의 문제를 발생시킬 수 있다. 이는 해당 SQL을 수행하는 과정에서 데이터베이스는 추출하고자 하는 열의 모든 정보를 데이터베이스의 데이터 딕셔너리에서 추출하기 때문이다. 따라서 불필요한 정보를 데이터 딕셔너리로부터 추출하면 효율이 떨어지는 원인이 된다.
* 데이터 딕셔너리 : 사용자가 테이블을 생성하거나 변경하는 등의 작업을 할 때, 데이터베이스 서버에 의해 자동으로 갱신되는 테이블.
* 카탈로그(딕셔너리) : 메타정보 데이터..테이블의 틀의 구조를 저장한 테이블
3. 테이블 생성
(1) 일반적인 방법을 이용한 테이블 생성
CREATE TABLE [schema] table_name
(column datatype
[.. column datatype ..]
)
[TABLESPACE tablespace]
[PCTFREE integer]
[PCTUSED integer]
[INITRANS integer]
[MAXTRANS integer]
[STORAGE storage-clause]
[LOGGING | NOLOGGING]
Ø schema: 테이블의 소유자
Ø table_name: 테이블 이름
Ø column: 컬럼의 이름
Ø datatype: 컬럼의 데이터 유형
Ø TABLESPACE: 테이블이 데이터를 저장 할 테이블스페이스
Ø PCTFREE: 블럭내에 이미 존재하고 있는 Row에 Update가 가능하도록 예약시켜 놓는 블럭의 퍼센트 값을 지정 한다.
즉, 수정 시 늘어나는 데이터를 수용하기 위한 공간
디폴트는 0이나 빈번히 수정되면서 null이었다가 데이터가 채워질 때 이 값을 약 20 혹은 30 까지 크게 설정한다.
ex) PCTFREE(20%) : 블록은 80% 찰 때까지 행을 삽입할 수 있고 20%는 기존 행을 갱신할 경우를 위해 빈 영역으로 남겨둔다.
* PCTFREE가 적을 경우
- 기존 테이블 행 갱신에 의한 확장을 위해 적은 공간을 확보.
- 많은 로우가 한 블럭에 입력 가능.
- 수정이 적은 세그먼트에 적합.
* PCTFREE가 클 경우
- 블럭당 적은 row가 입력됩니다. 즉 같은 row를 입력하기 위해서 많은 블럭이 소요.
- 행 조각을 자주 체인화할 필요가 없으므로 수정 수행 속도가 증가.
- 자주 수정되는 세그먼트에 적합.
Ø PCTUSED: 테이블 데이터가 저장 될 블록의 행 데이터 부분의 크기를 퍼센트지로 지정 한다. PCTFREE에 의해 지정된 크기만큼 Block이 차면 PCTUSED 값보다 작아져야 새로운 행 삽입이 가능 하다.
즉, 재사용되기 위해 필요한 블럭의 사용량을 설정한다.
ex) PCTUSED(40%) : 데이터블록의 사용된 영역이 39%보다 작아져야 새로운 행을 삽입할 수 있다.
* PCTUSED값이 적을 경우
- 블록이 재사용되는 일이 적어질 수 있으므로 처리 비용이 감소 합니다.
- 데이터베이스에서 사용되지 않은 공간이 증가 합니다.
* PCTUSED값이 클 경우
- 블록이 재사용되는 일이 많아질 수 있으므로 처리 비용이 증가 합니다.
- 공간 사용도를 향상 시킵니다.
Ø INITRANS: 하나의 블럭에 얼마만큼 동시에 트랜잭션을 처리할 수 있도록 할 것인지 설정. 즉, 각 세그먼트의 블록에는 블록헤더가 존재하는데 블록헤더의 한 부분으로 트랜잭션 테이블이 존재한다. 이 트랜잭션 테이블에 만들어진 엔트리들은 어느 트랜잭션이 블록의 어떤 로우에 락킹했는지를 보여준다.
기본값은 2이며 최소값도 2이다.
Ø MAXTRANS: 하나의 데이터 블록에 지정될 수 있는 트랜잭션 최대 수를 지정 한다.
기본값은 255이며 최소값도 255이다. 즉 고정이다.
Ø STORAGE: 익스텐트 스토리지에 대한 값을 지정 한다.
Ø LOGGING: 테이블에 대해 이후의 모든 작업이 리두 로그 파일 내에 기록 되도록 지정 한다
Ø NOLOGGING: 리두 로그 파일에 테이블의 생성과 특정 유형의 데이터 로드를 기록하지 않도록 지정 한다.
SQL > CREATE TABLE 카드
(카드번호 NUMBER(10),
…
주소 VARCHAR2(100))
TABLESPACE TEST
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 100
STORAGE( INITIAL 10M NEXT 10M PCTINCREASE 0
MINEXTENTS 1 MAXEXTENTS UNLIMITED);- INITIAL : Segment가 생성될 때 할당될 때 첫 번째 확장영역의 크기.
- NEXT : 다음 Extents의 크기, 두 번째 확장영역은 NEXT의 원래 크기와 동일하며 다음부터의
NEXT는 (1+PCTINCREASE/100)과 Next의 이전크기를 곱한 크기로 설정 됩니다
- MINEXTENTS : 생성할 Extents의 최소 값
- MAXEXTENTS : 생성할 Extents의 최대 값
(2) 기존 테이블을 이용한 테이블 생성
SQL > CREATE TABLE 임시급여;
AS SELECT * FROM 급여;위의 예제는 급여 테이블의 열과 행을 모두 복사해서 임시급여 테이블을 생성하게 된다.
따라서 두가지 요소가 복사되는것과 같다.
1) 해당 테이블의 열과 열의 속성
2) 해당 테이블의 행
주의사항) 테이블 생성시 설정하는 열의 기본값은 복사하지 못한다, CTAS를 통해 테이블을 생성 후에는 별도로 열의 기본값을 설정해주어야한다.
모든 데이터를 복사해 만드는것이 아니라 원하는 값만 복사애 임시급여 테이블을 만들때는
SQL > CREATE TABLE 임시급여
AS SELECT *
FROM 급여
WHERE 부서번호 = ‘10’;부서가 10인 사원들만 임시급여테이블에 데이터가 저장되게 된다.
SQL > CREATE TABLE 임시급여
AS SELECT 사원번호, 급여 FROM 급여;사원번호 열과 급여 열만 복사해서 임시급여 테이블을 생성하게 된다. 이처럼 원하는 열만 복사해서 사용하고 싶다면 AS SELECT 절에 원하는 열의 이름을 설정해주면 된다.
이런 방식을 보통 CTAS방식이라고 하며 사용하는 이유는 두가지가 있다.
1) 손쉽게 사용할 수 있는 용이성
2) 성능, CTAS는 직접로딩과 NOLOGGING이 가능하다.
기존 데이터 삽입 작업은 반드시 장애의 경우 복구를 위해 삽입 작업에 대한 로그를 기록하게 되는데 CTAS의 경우는 로그를 기록하지 않는 NOLOGGING으로 작업이 수행가능합니다.
구조만 복사할 때,
CREATE TABLE EMP_TEMP AS SELECT * FROM SCOTT.EMP WHERE 1=2;
(3) 테이블 생성 확인
DESCRIBE : 컬럼 정보 조회 명령어
- Column : 테이블에 정의된 열의 이름
- NULL? : 해당 열에 대해 NULL값이 허용가능한지 표시
- TYPE : 각 열에 대한 타입을 표시
- Comment : 각 열에대한 주석을 표시
데이터 딕셔너리 뷰
데이터베이스 내의 모든 정보(* 데이터 아님) 를 조회할 수 있는 뷰.
테이블을 생성한 후에는 특정 데이터 딕셔너리 뷰에 해당 테이블 정보가 저장 돼야 한다.
딕셔너리 뷰 종류
USER_TAB_COLUMNS: 테이블 열 정보를 저장하고 있다.
ALL_TAB_COLUMNS: 해당 유저의 모든 테이블 정보를 저장하고 있다.
DBA_TAB_COLUMNS: 해당 데이터베이스의 모든 테이블 정보를 조회할 수 있다.
SQL> SELECT owner, object_name, object_type
FROM all_objects
WHERE object_name LIKE ‘ALL_%’;
간단하게 각 컬럼들이 어떤 속성을 가지는지, 테이블이 어떤 컬럼으로 구성되어있는지 확인하려면 DESCRIBE사용
SQL > DESC all_object;딕셔너리 목록 조회 할때는 DICT 테이블을 조회한다.
SELECT * FROM DICT;
=> select * from dict where table_name like ‘USER%’;
(4) 테이블 생성시 주의 사항
l 권한 필요: 테이블을 생성하기 위해서는 CRAETE TABLE 권한이 필요
l 테이블 생성 공간 필요: 테이블은 디스크의 저장공간을 차지하게 된다. 저장공간은 테이블 생성시 TABLESPACE옵션으로 지정할 수 있다.
l 테이블 이름 중복 주의: 생성하고자 하는 테이블의 이름은 같은 유저가 동일이름의 테이블을 가지고 있으면 에러가 생긴다.
l 사용 가능 단어 제한: A~Z, a~z, 0~9, _, $, #만 테이블 이름을 만들 때 사용 가능하다.
l 테이블 이름 및 길이: 테이블 이름의 길이는 1~30자까지사용가능하며 이미 정의된 키워드는 사용할 수 없다. (SELECT, FROM,..)
l 테이블 이름을 지정하고 각 칼럼들은 괄호 "()" 로 묶어 지정한다.
l 컬럼뒤에 데이터 타입은 꼭 지정되어야 한다.
l 각 칼럼들은 콤마","로 구분되고, 항상 끝은 세미콜론";" 으로 끝난다.
l 한 테이블 안에서 칼럼이름은 같을 수 없으며 다른 테이블에서의 칼럼이름과는 같을 수 있다.
(5) 테이블 열 타입
l VARCHAR2: 문자형 데이터만 저장된다. 따라서 문자형 데이터를 저장하려면 varchar2로 열을 정의 하여 해당 테이블을 생성 해야 한다(가변길이)
l NVARCHAR2: 유니코드 형식의 데이터를 가지는 가변 길이 문자타입, NVARCHAR2는 0~10바이트를 가진다. 최대 4000바이트 까지 저장 가능
l CHAR: 문자형 데이터를 저장하기 위해 사용하는 열 타입(고정길이, 안 써도 확보)
l NCHAR: 유니코드 형식의 데이터를 포함하는 고정 길이 문자 타입
l NUMBER: 숫자 형식의 데이터를 저장하는 열 타입이며 사원번호 또는 주민번호 등과 같은 숫자 열로 사용할 수 있다. 소수점은 NUMBER(10, 2) 와 같이 콤마로 분리한다.
l DATE: 일자에 대한 내용을 저장 하는 열로 거래 일자 및 승인 일자 열 들 일자의 속성을 가지는 열에 사용될 수 있다. 또한 해당 열에는 시 분초를 저장하지 않아도 자동으로 저장된다. 기본적으로 DD-MON-YY 형식으로 저장 되며 여기서 MON은 월을 세 글자로 표시한 형태 이다.
l BLOB: 오라클 9i이하에서는 4GB까지 저장할 수 있고, 10g이상에서는 (4GB)*10(Database Blocksize)바이트 까지 가능
l CLOB: BLOB과 동일하며 평문 정보를 저장하기에 적합
(CLOB 은 Character, BLOB 는 Binary 저장용, 게시판 등을 제작할 때 본문은 길이 제한을 보통 하지 않을 때 이용하는 데이터타입이 CLOB. BLOB 는 파일을 그대로 DB 에 저장할 때 사용.)
l RAW: 가변 길이 이진 데이터 타입
l LONG: 문자 데이터를 최대 2GB까지 저장할 수 있다. 제약이 많아 CLOB으로 변경 추천
l LONG RAW: 이진 정보를 2GB까지 저장, BLOB타입으로 변경 추천
l ROWID: 데이터베이스에서 로우에 대한 10바이트 주소를 관리하는데 효과적이다.
l TIMESTAMP: 고정길이 7 또는 11바이트 데이터 타입, 초 단위 이하의 데이터를 가진다는 점에서 DATE 타입과는 다르며, 초단 위를 소수점 9자리 까지 표현
l TIMESTAMP WITH TIME ZONE: 고정길이 13바이트 TIMESTAMP타입, 표준 시간대 정보를 저장
l TIMESTAMP WITH LOCAL TIME ZONE: TIMESTAMP와 같이 고정길이 7 또는 11바이트 DATE/TIME 데이터 타입으로, 표준시간대의 영향을 받는다.
l INTERVAL YEAR TO MONTH: 고정길이 5바이트 데이터 타입으로, 연과 월에 대한 기간 저장
l INTERVAL DAY TO SECOND: 고정길이 11바이트 데이터 타입으로, 일, 시, 분, 초의 기간을 저장한다, 선택적으로 소수점 9자리 초 단위까지도 저장
l BFILE: 오라클 디렉토리 객체와 데이터베이스 컬럼에 파일명을 저장할 수 있고 파일을 읽을 수 있다. 파일이 데이터베이스 자체에 저장되는 것처럼, 읽기 전용 방식으로 데이터베이스 서버에서 OS파일에 접근하는데 효과적이다.
l BINARY FLOAT: 32비트 단정도 부동소수점 숫자.
l BINARY DOUBLE: 64비트 배정도 부동소수점 숫자.
(6) NVARCHAR2 NCHAR는 무슨용도로 사용되는가?
멀티플 케릭터 셋을 관리하거나 저장할 필요성이 있는 시스템에 사용된다.
● 텍스트는 기본 케릭터 셋이 아닌 내셔널 케릭터 셋에 의해 저장되고 관리된다.
● 길이는 char옵션 길이 표현만 지원한다.
------------------------------------------------------------------------------------------
(7) 일자 타입과 문자타입의 관계
DATE타입과 VARCHAR2타입 또는 CHAR타입을 예로 2017년 8월2일의 데이터를 추출해야 한다면?
DATE타입은 LIKE연산자나 BETWEEN연산자, SUBSTR등의 함수를 사용하여 값을 추출,
VARCHAR2, CHAR의 경우는 LIKE연산자, = 연산자를 이용하여 손쉽게 추출가능
즉, 인덱스를 이용하기에는 DATE타입보다 VARCHAR2, CHAR가 수월해진다.
DATE관련 함수를 이용하기 위해 TO_DATE, TO_CHAR를 사용하면 불필요한 CPU사용증가를 발생시키며 SQL를 길어지게 한다.
(8) 숫자 타입과 문자타입의 관계
LIKE등의 문자열 연산자를 많이 사용할 때는 VARCHAR2, CHAR가 유리한 반면에
인덱스를 이용하기 위해서는 NUMBER타입을 사용할 수 있지만 인덱스 액세스에 대해 VARCHAR2나 CHAR타입이 유리하다.
NUMBER타입을 이용하는 것은 산술연산에 유리하다. 저장공간을 적게 사용한다.
(9) 문자타입들간의 관계
CHAR타입의 경우는 저장하는 데이터가 주소일 경우 집주소가 길수도 짧을 수도 있기 때문에 주소에 대해서는 CHAR로 하면 미리 저장공간을 할당 받기 때문에 낭비되는 저장공간이 발생한다
따라서 열이 사용되는 목적에 따라 열 타입을 신중하게 선택해야 문제가 발생하지 않는다.
(10) 테이블 열 속성
SQL > CREATE TALBE 거래
(카드번호 VARCHAR2(10),
거래일자 VARCHAR2(8) DEFAULT SYSDATE,
가맹점 VARCHAR2(20));이렇게 테이블을 생성할 경우 열에 DEFAULT옵션으로 설정되어 해당 열에 값이 저장되지 않을 경우 DEFAULT로 저장된 값이 저장된다.
SQL > SELECT SYSDATE
FROM DUAL;
SYSDATE
--------------
02-AUG-08NLS_DATA_FORMAT파라매터가 DD-MON-YY로 설정되어야 하며 DUAL은 더미 테이블로 SYSDATE 등과 같이 임시적으로 한번 값을 추출할 경우 SQL형식을 맞추기 위해서 사용된다.
만약 설정된 열의 속성을 바꿔야 하는 경우가 발생한다면?
시스템 구축에 문제가 있다는 것을 의미, VARCHAR2(10)에서 CHAR(10)타입으로 변경한다면?
해당 열의 값이 1일{때 VARCHAR2는 1byte만 저장하고 있었을 텐데 CHAR는 저장된 데이터가 10byte인지 1byte인지 구별하지 않고 저장공간을 10byte를 확보하기 때문에 성능에 안 좋은 영향을 끼치게 된다.
DEFAULT값을 0으로 변경한다면?
만약 해당 열에 NULL이 많다면 그 값들은 모두 0으로 변경하는 작업 또한 엄청난 부하는 발생시키게 된다. 성능뿐만 아니라 데이터 정합성에 문제가 발생할 수 있다.
(11) NULL속성
어떠한 값도 아닌 것을 NULL이라고하며 다음과 같이 정의된다.
l 0도 아니며 스페이스도 아닌 정의되지 않은 값.
l NULL값과 다른 값과의 연산 결과는 NULL
개발자들은 NULL이 저장되는 열에 대해 해당 데이터가 개발자는 알 수 없기 때문에 NULL로 저장 되야 한다고 한다. 그 값은 조회할 경우 NVL을 사용하는데 NVL은 NULL값을 특정 값으로 치환하여 결과를 추출하는 함수를 의미한다.
예를 들어 NVL(column1,0)은 column1열의 값을 추출하면서 값이 null인경우 0의 값으로 추출하라는 의미가 된다. 보통 이와 같이 null을 처리하게 된다.
추출하는 값을 왜 null로 변경하여 저장하나? 개발자는 알 수 없기 때문에 0으로 저장할 수 없다고 이야기하는데 알 수 없는 값인데 추출할 경우 왜 매번 0으로 추출해야 하는가? 도 의문.
NULL대신 특정 값을 저장하여 데이터 정합성을 맞출 수 있다. null대신 특정 값을 넣으면 NVL함수를 사용하지 않아도 되며 해당 열의 인덱스를 사용해야 할 때 효과적으로 인덱스를 이용할 수 있다.
하지만 null값을 다른 값으로 치환하는 것은 문제를 발생시킬 수도 있기 때문에 주의 깊게 사용해야 한다.
'IT > DB' 카테고리의 다른 글
| DB - SQL Query 작성 1 (0) | 2023.04.18 |
|---|---|
| DB - SQL Query 작성 2 (0) | 2023.04.18 |
| DB - SQL Query 작성 3 & 연습문제 (0) | 2023.04.18 |
| [Oracle 관리실무] Chapter 3. 트랜잭션 (0) | 2023.04.17 |
| [Oracle 관리실무] Chapter 2. SQL의 사용 (0) | 2023.04.17 |
