오류저장소

오라클SQL과 PL/SQL을 다루는 기술 이라는 책에대한 정리

 

 

 

 

SQL – 구조화된 질의 언어 DBMS 상에서 데이터를 관리하기 위한 프로그램 언어.

SQL – 집합적 언어 (데이터를 특정 집합 단위로 분류해 단위별로 한 번에 처리하는 언어)

 

DDL – 데이터 정의어 (데이터베이스 객체를 관리하는 언어)

[CREATE, DROP, ALTER, TRUNCATE]

 

DML – 데이터 조작어 (삽입, 조회, 삭제, 갱신)

[SELECT, INSERT, UPDATE, DELETE, COMMIT, ROLLBACK]

 

PL/SQL – 절차적 언어. 변수에 값을 할당하고 예외처리도 할 수 있다. 특정 기능을 처리하는 함수나 프로시저를 생성할 수 있다. DB프로그래밍 – PL/SQL을 이용해 함수나 프로시저를 만들어 여러 작업을 처리하는 것.

 

 

2장 데이터베이스 객체의 개요

데이터베이스 객체 – 데이터베이스 내에 존재하는 논리적인 저장 구조. DBMS가 데이터를 관리하기 위해 데이터를 저장하는데 이런 목적을 달성하기 위해 필요한 모든 논리적인 저장 구조.

 

데이터베이스 객체의 종류

데이터베이스 객체	설명
테이블	데이터를 담고 있는 객체
뷰	하나 이상의 테이블을 연결해 마치 테이블인 것처럼 사용하는 객체
인덱스	테이블에 있는 데이터를 빠르게 찾기 위한 객체
시노님	데이터베이스 객체에 대한 별칭을 부여한 객체
시퀀스	일련번호 채번을 할 때 사용되는 객체
함수	특정 연산을 하고 값을 반환하는 객체
프로시저	함수와 비슷하지만 값을 반환하지는 않는 객체
패키지	용도에 맞게 함수나 프로시저를 하나로 묶어 놓은 객체

 

테이블 – DBMS상에서 데이터를 담고 있는 가장 기본적인 객체로 로우와 컬럼으로 구성된 2차원 형태의 객체.

 

CREATE TBLE [스키마.]테이블명(

컬럼1 컬럼1_데이터타입 [NULL, NOT NULL],

컬럼2 컴럼2_데이터타입 [NULL, NOT NULL],

 

...

)[TABLESPACE 테이블스페이스명];

 

 

 

데이터타입 – 컬럼이 저장되는 데이터 유형. (기본 데이터 타입//사용자 정의 타입)

 

데이터 타입	설명
TIMESTAMP	연도, 월, 일, 시, 분, 초 밀리초 입력가능
CLOB	문자형 대용량 객체. 고정길이와 가변길이 문자 집합 지원, 최대크기는 (4GB-1)*(데이터베이스 블록사이즈)
NCLOB	유니코드를 포함한 문자형 대용량 객체, 최대크기는 (4GB-1)*(데이터베이스 블록 사이즈)
BLOB	이진형 대용량 객체. 최대크기는 (4GB-1)*(데이터베이스 블록사이즈)
BFILE	대용량 이진 파일에 대한 로케이터(위치, 이름) 저장. 최대크기 4GB

 

제약조건

UNIQUE – 해당컬럼에 들어가는 값이 유일해야 한다는 의미. 중복 값을 허용하지 않는다.

칼럼명 데이터타입 UNIQUE

혹은

CONSTRAINTS 제약조건명 UNIQUE(칼럼명, ...)

 

외래키

CONSTRAINT 외래키명 FOREIGN KEY(칼럼명 ...)

REFERENCES 참조테이블(참조 테이블 컬럼명, ...)

반드시 참조하는 테이블이 먼저 생성되어야 한다. 참조키가 참조 테이블의 기본키로 만들어져 있어야 한다. 외래키에 사용가능한 최대 컬럼 수는 32개. 여러 컬럼을 외래키로 만들려면, 차조하는 컬럼과 외래키 컬럼의 순서와 개수는 같아야 한다.

 

CHECK – 컬럼에 입력되는 데이터르,f 체크해 특정 조건에 맞는 데이터만 입력 받고 그렇지 않으면 오류를 뱉어낸다.

CONSTRAINT 체크명 CHECK(체크조건)

 

칼럼속성 – DEFAULT

컬럼의 디폴트값을 명시하는데 사용.

 

테이블 삭제

DROP TABLE [스키마.]테이블명 [CASCADE CONSTRAINTS]

제약조건도 자동 삭제.

 

테이블 변경

ALTER TABLE [스키마.]테이블명 RENAME COLUMN 변경전컬럼명 TO 변경후컬럼명;

 

컬럼 타입 변경

ALTER TABLE [스키마.]테이블명 MODIFY 컬럼명 데이터타입;

컬럼추가

ALTER Table [스키마.]테이블명 ADD 컬럼명 데이터타입;

컬럼 삭제

ALTER TABLE [스키마,]테이블명 DROP COLUMN 컬럼명;

 

제약조건 추가

ALTER TABLE [스키마.]테이블명 ADD CONSTRAINTS 제약조건명 PRIMARY KEY(칼럼명,..);

 

제약조건 삭제

ALTER TABLE [스키마.]테이블명 DROP CONSTRINTS 제약조건명;

 

테이블 복사

CRETE TABLE [스키마.]테이블명 AS

SELECT 컬람1. 컬럼2, . . . . . . .

FROM 복사할 테이블명;

 

VIEW – 하나 이상의 테이블이나 다른 뷰의 데이터를 볼 수 있게 하는 데이터 베이스 객체.

생성 – CREATE OR REPLACE VIEW [스키마.]뷰명 AS

SELECT 문장;

 

인덱스 – 데이터를 빨리 찾기 위한 용도의 데이터베이스 객체.

인덱스 구성 컬럼 개수에 따른 분류 – 단일 인덱스와 결합 인덱스

유일성 여부에 따른 분류 – UNIQUE 인덱스, NON-UNIQE 인덱스

인덱스 내부 구조에 따른 분류 – B-tree 인덱스, 비트맵 인덱스, 함수 기반 인덱스

 

CREATE[UNIQUE] INDEX [스키마명.]인덱스명

ON [스키마명.]테이블명(컬럼1, 컬럼2, . . . );

 

테이블에 있는 데이터를 빨리 찾기 위한 조회 성능을 높이려는 목적에서 만들어 졌고 인덱스 자체에 키와 매핑 주소 값을 별도로 저장. 테이블에 데이터를 입력하거나 삭제, 수정할 때 인덱스에 저장된 정보도 이에 따라 생성, 수정이 이루어진다. 인덱스를 너무 많이 만들면 SELECT 외에 INSERT, DELETE, UPDATE 시 성능에 부하.

 

인덱스 생성 고려사항

테이블 전체 로우 수의 15% 이하의 데이터를 조회할 때 인덱스를 생성.

테이블 건수가 적다면 굳이 인덱스를 만들 필요가 ㅇ벗다.

데이터의 유일성 정도가 좋거나 범위가 넒은 값을 가진 컬럼을 인덱스로 만드는 것이 좋다.

NULL이 많이 포함된 컬럼은 인덱스 컬럼으로 만들기 적당치 않다.

결합 인덱스를 만들 때는 컬럼의 순서가 중요하다

테이블에 만들 수 있는 인덱스 수의 제한은 없으나 너무 많이 만들면 오히려 성능 부하가 발생한다.

DROP INDEX [스키마명.]인덱스명;

시노님 – 데이터베이스 객체는 각자 고유한 이름. 이 객체들에 대한 동의어를 만드는 것.

PUBLIC과 PRIVATE 시노님이 있다.

시노님 생성

CREATE OR REPLACE [PUBLIC] SYNONYM [스키마명.]시노님명

FOR[스키마명,]객체명;

 

PUBLIC 시노님은 DBA권한이 있는 사용자만 생성 및 삭제가 가능하다.

FOR절 이하의 객체에는 테이블, 뷰, 프로시저, 함수, 패키지, 시퀀스 등이 올 수 있다.

PUBLIC 시노님은 소유자명을 붙이지 않아도 참조가 가능한데 그 이유는 해당 시노님의 소유자가 시노님을 만든 ora_user가 아닌 PUBLIC이 되기 때문이다.

 

생성된 시노님 정보는 PRIVATE은 USER_SYNONYMS, PUBLIC까지 보려면 ALL_SYNONYMS를 참조하면 된다.

 

DROP [PUBLIC] SYNONYM [스키마명.]시노님명;

 

 

시퀀스

CREATE SEQUENCE [스키마명.]시퀀스명

INCREMENT BY 증감숫자

START WITH 시작숫자

NOMINVALUE | MINVALUE 최솟값

NOMAXVLUE | MAXVLUE 최댓값

NOCYCLE | CYCLE

NOCACHE | CACHE;

 

INCREMENT BY 증감숫자 : 증감숫자는 0이 아닌 정수. 양수면 증가 음수면 감소 디폴트 1

START WITH 시작숫자 : 시작숫자의 디폴트 값은 증가일 때 MINVALUE, 감소일 때 MAXVALUE.

NOMINVALUE : 디폴트 값으로 증가일 때 1, 감소의 경우-(1027-1)

MINVALUE 최솟값 : 최솟값은 시작숫자와 작거나 같아야 하고 MANVALUE보다 작아야.

NOMAXVALUE : 디폴트값으로 증가일 때 1028-1, 감소의 경우-1

MAXVALUE 최댓값 : 최댓값은 시작숫자와 같거나 커야하고 MINVALUE보다 커야한다.

NOCYCLE : 디폴트 값으로 최대나 최솟값에 도달하면 생성 중지.

CYCLE : 증가는 최댓값에 도달하면 다시 최솟값부터 시작. 감소는 반대.

NOCACHE : 디폴트로 메모리에 시퀀스 값을 미리 할당해 놓지 않으며 디폴트 값은 20.

CACHE : 메모리에 시퀀스 값을 미리 할당해 놓음.

 

시뭔스명.NEXTVAL

시퀀스명.CURRVAL

DROP SEQUENCE[스키마명.]시퀀스명;

파티션 테이블

테이블을 생성할 때 파티션으로 테이블을 만들 수 있다. 파티션이라 하은 테이블에 있는 특정 컬럼값을 기준으로 데이터를 분할해 저장해 놓는 것. 이때 논리적인 테이블은 1개지만, 물리적으로는 분할한 만큼 파티션이 만들어져 입력되는 컬럼 값에 ᄄᆞ라 분할된 파티션별로 데이터가 저장된다. 파티션 테이블을 만드는 목적은 대용량 테이블의 경우 데이터 조회 시 효율성과 성능을 높이기 위한 것.

 

CREATE TABLE SALES(

PROD_ID Number(6,0) NOT NULL,

CUST_ID Number(6,0) NOT NULL,

CHANNEL_ID Number(6,0) NOT NULL,

EMPLOYEE_ID Number(6,0) NOT NULL,

SALES_DATE Date DEFAULT SYSDATE NOT NULL,

SALES_MONTH Varchar2(6 ),

QUANTITY_SOLD Number(10,2),

AMOUNT_SOLD Number(10,2),

CREATE_DATE Date DEFAULT SYSDATE,

UPDATE_DATE Date DEFAULT SYSDATE

)

PARTITION BY RANGE(SALES_MONTH)

(

...

PARTITION SALES_Q1_1998 VALUES LESS THAN (‘199804’) TABLESPCE MYTS,

PARTITION SALES_Q2_1998 VALUES LESS THAN (‘199807’) TABLESPCE MYTS,

PARTITION SALES_Q3_1988 VALUES LESS THAN (‘199810’) TABLESPCE MYTS,

PARTITION SALES_Q4_1988 VALUES LESS THAN (‘199901’) TABLESPCE MYTS,

...

PARTITION SALES_Q4_2003 VALUES LESS THAN (‘200401’) TABLESPCE MYTS

);

 

판매월을 기준으로 RANGE 파티션을 사용했는데 1998년 1분기는 SALES_Q1_1998, 2분기는 SALES_Q2_1998이란 이름의 파티션을 만들었다. 이렇게 테이블을 생성한 뒤 INSERT를 하면 들어오는 데이터 값에 따라 자동으로 파티션별로 데이터가 적재된다. 이때는 한 파티션에 세 달치 데이터가 들어가게 될 것. 파티션 종류에는 RANGE 파티션외에도 LIST파티션, 해시파티션, 그리고 여러 파티션을 조합한 복합 파티션이 있다.

 

파티션 테이블이든 일반 테이블이든 개발자 입장에서 DML문을 작성할 때 차이점은 전혀 없으나, 대용량 테이블은 파티션으로 분할해 놓으면 성능 향상에 큰 도움이 된다.

 

 

 2장끝.