문과생이 공대에서 살아남기

1. 옵티마이저

사용자가 질의한 SQL문에 대해 최적의 실행방법을 결정하는 역할을 수행

실행계획 : 최적의 실행방법, 다른 언어와 달리 요구사항만 기술할 뻔 처리과정에 대한 기술은 없어서 다양한 실행방법 존재

옵티마이저가 선택한 실행방법의 적절성 여부는 질의의 수행 속도에 가장 큰 영향 미침, 최소 일량으로 동일한 일 처리

대부분의 관계형 데이터 베이스는 비용기반 옵티마이저만 제공

가. 규칙기반 옵티마이저

우선순위를 가지고 실행계획을 생성

인덱스의 유무(유일, 비유일, 단일, 복합인덱스)종류,

SQL에서 사용하는 연산자의 종류,

SQL에서 사용하는 객체(힙테이블, 클러스터 테이블)의 종류

인덱스가 존재한다면 전체 테이블 인덱스 방식보다 인덱스 사용 실행계획 생성

조인시에는 인덱스의 유무가 중요한 판단 기준

양쪽 조인 칼럼에 모두 인덱스가 없는 경우는 Sort Merge Join

둘 중 하나라고 조인 칼럼에 인덱스가 존재하는 경우는 NL Join 사용

1. Single row by rowid : 테이블에서 하나의 행을 엑세스 하는 방식, 다른 정보를 참조하지 않아도 바로 원하는 행 엑세스 할 수 있음
2. Single row by cluster join
3. Single row by hash cluster key with unique or primary key
4. Single row by unique or primary key : 유일 인덱스를 통해서 하나의 행 엑세스하는 방식, 인덱스 이후 rowid 추출
5. Cluster join
6. Hash cluster key
7. Indexed cluster key
8. Composite index : 복합인덱스에 동등조건으로 검색, 인덱스 구성 칼럼의 개수가 더 많고 모든 구성 칼럼에 대해 동등 값이 주어질수록 우선순위가 높음
9. Single column index : 단일 칼럼 인덱스에 동등 조건으로 검색
10. Bounded range search on indexed columns : 양쪽범위를 한정하는 형태로 검색하는 방식 Between, like 등
11. Unbounded range search on indexed columns : 한쪽 범위 한정하는 형태로 검색 >, >=, <, <=
12. Sort merge join
13. Max or Min of indexed column
14. Order by on indexed column
15. Full table scan : 전체 테이블을 엑세스함

나. 비용기반 옵티마이저

SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식

비용이란 SQL문을 처리하기 위해 예상되는 소요시간 또는 자원 사용량 의미

비용예측을 위해 규칙기반이 사용하지 않는 테이블, 인덱스, 칼럼 등 다양한 객체 통계정보와 시스템 통계정보등을 이용

정확한 통계정보를 유지하는 것이 최적화에 중요한 요소

• 질의변환기 : 사용자가 작성한 SQL문을 처리하기에 보다 용이한 형태로 변환하는 모듈
• 대안계획생성기 : 동일한 결과를 생성하는 다양한 대안계획을 생성하는 모듈, 연산의 적용순서변경, 연산방법변경, 조인순서변경 등 통해서 생성

 최적화를 위해 대안 계획수 제약가능하나 현실적인 제약으로 인해 포함되지 않을 수도 있음

• 비용예측기 : 대안계획생성시에 의해서 생성된 대안 계획의 비용을 예측하는 모듈

   연산의 중간 집합의 크기, 결과 집합의 크기, 분포의 정확한 예측 필요, 정확한 통계자료 필요하고 연산에 대한 비용 계산식 정확해야함

통계정보, DBMS 버전, DBMS 설정 정보등의 차이로 서로 다른 실행계획이 생성될 수 있음

다양한 한계들로 인해 실행계획의 예측 및 제어가 어렵다는 단점

2. 실행계획

SQL에서 요구한 사항을 처리하기 위한 절차와 방법, 어떤 순서로 어떻게 실행할 지 결정하는 작업

구성요소

1. 조인순서 : FROM절에 있는 테이블 수의 N! 만큼 조인순서 가능
2. 조인기법 : NL JOIN, HASH JOIN, SORT MERGE JOIN
3. 엑세스기법 : 하나의 테이블을 엑세스할때 사용할 수 있는 방법, 인덱스 스캔, 전체테이블스캔 가능
4. 최적화 정보 : 각 단계마다 예상되는 비용 사항 표시
• COST : 상대적인 비용
• CARD : CARDNALITY 주어진 조건을 만족한 결과 집합, 조인조건을 만족한 결과집합의 건수
• BYTES : 메모리양, 예상치
5. 연산 : 여러가지 조작을 통해서 원하는 결과를 얻어내는 일련의 작업

  조인기법, 엑세스기법, 필터, 정렬, 집계, 뷰 등 다양한 종류 존재

3. SQL 처리 흐름도

조인순서(어떤 테이블을 먼저 읽었는지), 엑세스기법(인덱스, 전체 테이블 스캔 중 무엇을 수행했는지) 등으로 표현가능

엑세스건수, 조인시도건수, 테이블엑세스건수, 성공선수 등을 통해 작업건수 또는 처리결과 건수들을 표시할 수 있음

NL JOIN 흐름도

1. 절차형 SQL 개요

절차 지향적인 프로그램이 가능하도록 DBMS 벤더별로 PL(Procedural Language)/SQL(Oracle), SQL/PL(DB2), T-SQL(SQL Server) 등의 절차형 SQL을 제공

절차형 SQL을 이용하면 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성

2. PL/SQL 개요

가.  특징

블럭구조로 되어있고, DML문장, QUERY문장, 절차형 언어(IF, LOOP)등이 사용하여 절차적 프로그래밍 기능을 하게 된 트랜잭션 언어

저장모듈 : PL/SQL 문장을 데이터 베이스 서버에 저장하여 사용자와 어플리케이션 사이에 공유할 수 있도록 만든 SQL 컴포넌트 프로그램

 독립적으로 또는 타 프로그램으로부터 실행 될 수 있는 완전한 실행 프로그램

- PL/SQL은 Block 구조로 되어있어 각 기능별로 모듈화가 가능하다.

- 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다.

- IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.

- DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.

- PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.

- PL/SQL은 응용 프로그램의 성능을 향상시킨다.

- PL/SQL은 여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

프로그램 문장은 PL/SQL 엔진이 처리하고

SQL 문장은 ORACLE 서버의 SQL STATEMENT EXECUTOR 실행하도록 작업 분리하여 처리

나. 구조

• DECLARE : BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부
• BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부
• EXCEPTION : BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것이지를 정의하는 예외 처리부

다. 기본문법

CREATE [OR REPLACE] Procedure [Procedure_name]

( argument1 [mode] data_type1, argument2 [mode] date_type2, ... ... )

IS [AS] ... ...

BEGIN ... ...

EXCEPTION ... ...

END;

/

• OR REPLCAE : 같은 이름의 프로시저가 있을 경우 덮어쓰기 함
• ARGUMENT : 프로시저 안으로 어떤 값이 들어오거나 혹은 처리한 결과값을 리턴시킬 매개 변수 저장
• MODE : 매개변수 3가지 유형 IN, OUT, INOUT
• / : 마지막으로 데이터 베이스에게 프로시저 컴파일하라는 명령어

프로시저 삭제

DROP Procedure [Procedure_name];

3. T-SQL 개요

가. 특징

SQL SERVER을 제어하기 위한 언어, ANSI표준에 약간의 기능을 더 추가해 보완적으로 만든 것

- 변수 선언 기능 @@이라는 전역변수(시스템 함수)와 @이라는 지역변수가 있다.

- 지역변수는 사용자가 자신의 연결 시간 동안만 사용하기 위해 만들어지는 변수이며 전역변수는 이미 SQL서버에 내장된 값이다.

- 데이터 유형(Data Type)을 제공한다. 즉 int, float, varchar 등의 자료형을 의미한다.

- 연산자(Operator) 산술연산자( +, -, *, /)와 비교연산자(=, <, >, <>) 논리연산자(and, or, not) 사용이 가능하다.

- 흐름 제어 기능 IF-ELSE와 WHILE, CASE-THEN 사용이 가능하다.

- 주석 기능한줄 주석 : -- 뒤의 내용은 주석범위 주석 : /* 내용 */ 형태를 사용하며, 여러 줄도 가능함

나. 구조

- DECLARE : BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부

- BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부이

      T-SQL에서는 BEGIN, END 문을 반드시 사용해야하는 것은 아니지만 블록 단위로 처리하고자 할 때는 반드시 작성

- ERROR 처리 : BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것이지를 정의하는 예외 처리부

다. 기본 문법

CREATE Procedure [schema_name.]Procedure_name

@parameter1 data_type1 [mode],

@parameter2 date_type2 [mode],

... ...

WITH 

AS ... ...

BEGIN ... ...

ERROR 처리 ... ...

END;

• 변경이 필요한 경우는 ALTER 구분으로 변경
• @PARAMETER 값이 들어오거나 프로시저에서 처리한 값을 리턴 시킬 매개 변수 지정할 때 사용
• MODE 부분의 매개변수 4가지 유형

① VARYING : 결과 집합이 출력 매개 변수로 사용되도록 지정, CURSOR 매개변수에만 적용

② DEFAULT : 지정된 매개변수가 프로시저를 호출할 당시 지정되지 않을 경우 지정된 기본값으로 처리

  즉, 기본 값이 지정되어 있으면 해당 매개 변수를 지정하지 않아도 프로시저가 지정된 기본 값으로 정상적으로 수행

③ OUT, OUTPUT : 프로시저에서 처리된 결과 값을 EXECUTE 문 호출 시 반환

④ READONLY : 자주 사용되지는 않음 프로시저 본문 내에서 매개 변수를 업데이트하거나 수정할 수 없음

   매개 변수 유형이 사용자 정의 테이블 형식인 경우 READONLY를 지정

• WITH 부분 3가지 옵션

① RECOMPILE : 데이터베이스 엔진에서 현재 프로시저의 계획을 캐시하지 않고 프로시저가 런타임에 컴파일 

    엔진에서 저장 프로시저 안에 있는 개별 쿼리에 대한 계획을 삭제하려 할 때 RECOMPILE 쿼리 힌트를 사용

② ENCRYPTION : CREATE PROCEDURE 문의 원본 텍스트가 알아보기 어려운 형식으로 변환

 변조된 출력은 SQL Server의 카탈로그 뷰 어디에서도 직접 표시되지 않음

 원본을 볼 수 있는 방법이 없기 때문에 반드시 원본은 백업 필요

③ EXECUTE AS : 해당 저장 프로시저를 실행할 보안 컨텍스트를 지정

프로시저 삭제 명령

DROP Procedure [schema_name.]Procedure_name;

4. PROCEDURE의 생성과 활용

주의해야할 문법적 요소

1. 다양한 변수의 유형
2. SQL의 SELECT 문장이 다름, 결과값이 반드시 있어야 하며 하나 있어야함 (T-SQL은 결과값이 없어도 에러 미발생)
3. 대입 연사자 ":="사용 ( 일반적으로 "="로 사용)
4. 예외처리부에서 WHEN ~ THEN 사용하여 종류별 처리 가능

5. USER DEFINED FUNCTION의 생성과 활용

절차형 SQL을 로직과 함께 데이터베이스 내에 저장해 놓는 명령문의 집합

PROCEDURE와 다른점은 RETURN을 사용해서 하나의 값을 반드시 되돌려 주어야 함

SQL 문장에서 특정 작업을 수행하고 반드시 수행결과 값을 리턴해야함

6. TRIGGER의 생성과 활용

특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, 자동으로 동작하도록 작성된 프로그램

사용자가 직접호출하는 것이 아니라 자동적으로 수행하게 됨

테이블과 뷰, 데이터베이스 작업을 대상으로 정의할 수 있음

전체 트랜잭션작업에 대해 발생되는 TRIGGER, 각 행에 대해서 발생되는 TRIGGER가 있음

데이터베이스 보안의 적용, 유효하지 않은 트랜잭션 예방, 업무규칙 자동적용제공 등에 사용가능하다.

7. PROCEDURE과 TRIGGER의 차이

1. DCL의 개요

유저를 생성하고 권한을 제어할 수 있는 DATA CONTROL LANGUAGE

2. 유저와 권한

ORACLE에서 제공하는 유저

• SCOTT   : 테스트용 샘플 유저
• SYS       : DBA ROLE을 부여받은 유저
• SYSTEM : 모든 권한을 부여받은 DBA유저, ORACLE 설치시 패스워드 설정

ORACLE은 유저를 통해 데이터베이스에 접속, 아이디와 비밀번호 방식으로 인스턴스에 접속하고 스키마에 오브젝트 생성 권한 부여

SQL SERVER은 인스턴스를 접근하기 위해 로그인 생성, 인스턴스 내 존재하는 다수의 데이터베이스를 연결하기 위한 작업을 위해 유저 생성 후 맵핑

특정유저는 특정데이터베이스 내의 특정 스키마에 대한 권한을 부여 받을 수 있음

1. WINDOW에 인증하는 방식, 암호를 요청하지 않고 유효성 검사 수행하지 않음, 기본 인증 모드, 신뢰된 인증
2. 혼합모드, WINDOW인증 또는 SQL로 인증, 강력한 암호를 사용해아함,

가. 유저 생성과 시스템 권한 부여

유저를 생성하고 데이터 베이스에 접속하지만 오브젝트 생성할 수 없음, 적절한 권한이 필요

ROLE을 통해 여러가지 유형의 유저 관리를 가능하게 함

Oracle) SYSTEM이 권한을 주어야 한다.

    GRANT CREATE USER TO SCOTT; 권한이 부여되었다.

    CONN SCOTT/TIGER 연결되었다.

    CREATE USER PJS IDENTIFIED BY KOREA7; 사용자가 생성되었다.

SQL Server) SA로 로그인 한 후 유저 생성

 CREATE LOGIN PJS WITH PASSWORD='KOREA7', DEFAULT_DATABASE=AdventureWorks 로그인 진행

 USE ADVENTUREWORKS;

 GO

 CREATE USER PJS FOR LOGIN PJS WITH DEFAULT_SCHEMA = dbo; 유저 생성

나. OBJECT에 대한 권한 부여

다. ROLE을 이용한 이용 권한 부여

많은 데이터 베이스에서 유저들과 권한들 사이에 중개하는 역할

관리자는 ROLE을 생성하고 각종 권한들을 부여한 후 롤을 다른 롤이나 유저에게 부여

롤에 포함되어 있는 권한들이 필요한 유저에게는 해당 롤만 부여함을써 빠르고 정확하게 필요한 권한 부여 가능

롤은 CONNECTD(로그인 권한)와 RESOURCE(오브젝트 생성 권한)

Oracle

CONN SYSTEM/MANAGER 연결되었다.

DROP USER JISUNG CASCADE; 사용자가 삭제되었다. ☞ JISUNG 유저가 만든 MENU 테이블도 같이 삭제되었다.

CREATE USER JISUNG IDENTIFIED BY KOREA7; 사용자가 생성되었다.

GRANT CONNECT, RESOURCE TO JISUNG; 권한이 부여되었다.

CONN JISUNG/KOREA7 연결되었다.

CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10)); 테이블이 생성되었다.

REVOKE 권한을 뺏었다.