[오라클] Oracle® Database Concepts 챕터 7 한글 번역

torch·2024년 7월 18일
dboracle
0

Oracle

목록 보기
6/13
post-thumbnail

Reference : Database Concepts - 7. SQL

*GPT 번역 기반으로 내용이 정확하지 않을 수 있습니다.

SQL

이 장에서는 SQL(Structured Query Language, 구조적 질의 언어)에 대한 개요와 Oracle 데이터베이스가 SQL 문을 처리하는 방법을 제공합니다.

이 장에서는 다음과 같은 주제를 다룹니다:

  • SQL 소개
  • SQL 문 개요
  • 옵티마이저 개요
  • SQL 처리 개요

Introduction to SQL

SQL은 Oracle 데이터베이스에서 모든 프로그램과 사용자가 데이터를 접근하는 데 사용하는 집합 기반의 고수준 선언적 컴퓨터 언어입니다.

일부 Oracle 도구와 애플리케이션은 SQL 사용을 마스킹하지만, 모든 데이터베이스 작업은 SQL을 사용하여 수행됩니다. 다른 데이터 접근 방법은 Oracle 데이터베이스에 내장된 보안을 우회하고 데이터 보안 및 무결성을 저해할 수 있습니다.

SQL은 Oracle 데이터베이스와 같은 관계형 데이터베이스에 대한 인터페이스를 제공합니다. SQL은 다음과 같은 작업을 하나의 일관된 언어로 통합합니다:

  • 객체 생성, 교체, 변경 및 삭제
  • 테이블 행 삽입, 업데이트 및 삭제
  • 데이터 쿼리
  • 데이터베이스 및 그 객체에 대한 접근 제어
  • 데이터베이스 일관성과 무결성 보장

SQL은 인터랙티브하게 사용할 수 있으며, 즉 프로그램에 수동으로 문을 입력할 수 있습니다. 또한 SQL 문은 C나 Java와 같은 다른 언어로 작성된 프로그램 내에 포함될 수 있습니다.

참고:

  • "서버 사이드 프로그래밍 소개" 및 "클라이언트 사이드 데이터베이스 프로그래밍"
  • SQL 소개에 대한 Oracle Database SQL Language Reference

SQL Data Access

컴퓨터 언어는 크게 선언적 언어와 절차적 언어의 두 가지로 나눌 수 있습니다. 선언적 언어는 무엇을 해야 하는지를 기술하며, 절차적 언어는 어떻게 해야 하는지를 기술합니다.

SQL은 사용자가 원하는 결과를 지정하고, 결과를 도출하는 방법을 지정하지 않는다는 점에서 선언적입니다. 예를 들어, 다음 문은 성이 K로 시작하는 직원의 기록을 쿼리합니다:

SELECT   last_name, first_name
FROM     hr.employees
WHERE    last_name LIKE 'K%'
ORDER BY last_name, first_name;

데이터베이스는 데이터를 탐색하고 요청된 결과를 검색하는 절차를 생성하는 작업을 수행합니다. SQL의 선언적 특성 덕분에 논리적 수준에서 데이터를 다룰 수 있습니다. 구현 세부 사항은 데이터를 조작할 때만 신경 쓰면 됩니다.

모든 SQL 문은 요청된 데이터에 접근하는 가장 효율적인 수단을 결정하는 데이터베이스의 구성 요소인 옵티마이저를 사용합니다. Oracle 데이터베이스는 옵티마이저가 작업을 더 잘 수행하도록 하는 기술도 지원합니다.

참고:

  • SQL 문 및 기타 SQL 부분(예: 연산자, 함수, 형식 모델)에 대한 자세한 내용은 Oracle Database SQL Language Reference

SQL Standards

Oracle은 산업에서 인정받는 표준을 따르기 위해 노력하며, SQL 표준 위원회에 적극적으로 참여합니다.

산업에서 인정받는 위원회로는 American National Standards Institute(ANSI)와 International Organization for Standardization(ISO)이 있습니다. ANSI와 ISO/IEC 모두 SQL을 관계형 데이터베이스의 표준 언어로 채택했습니다.

SQL 표준은 열 개의 부분으로 구성되어 있습니다. 그 중 하나인 SQL/RPR:2012는 2012년에 새로 추가되었으며, 다른 다섯 부분은 2011년에 개정되었습니다. 나머지 네 부분은 2008년 버전이 유지됩니다.

Oracle SQL은 ANSI/ISO 표준 SQL 언어에 많은 확장을 포함하고 있으며, Oracle Database 도구 및 애플리케이션은 추가 명령문을 제공합니다. SQL*Plus, SQL Developer 및 Oracle Enterprise Manager 도구는 Oracle 데이터베이스에서 ANSI/ISO 표준 SQL 문 및 해당 도구에서 사용할 수 있는 추가 명령문이나 함수를 실행할 수 있게 합니다.

참고:

  • "데이터베이스 관리자용 도구" 및 "데이터베이스 개발자용 도구"
  • Oracle SQL과 표준 SQL의 차이에 대한 설명은 Oracle Database SQL Language Reference
  • SQLPlus 명령과 SQL 문과의 구분을 포함한 SQLPlus User's Guide and Reference

Overview of SQL Statements

Oracle 데이터베이스에서 정보에 수행되는 모든 작업은 SQL 문을 사용하여 실행됩니다. SQL 문은 식별자, 매개변수, 변수, 이름, 데이터 형식 및 SQL 예약어로 구성된 컴퓨터 프로그램 또는 명령입니다.

참고: SQL 예약어는 SQL에서 특별한 의미를 가지며 다른 목적으로 사용해서는 안 됩니다. 예를 들어, SELECT와 UPDATE는 예약어이며 테이블 이름으로 사용해서는 안 됩니다.

SQL 문은 완전한 SQL 문장이어야 합니다. 예를 들어:

SELECT last_name, department_id FROM employees;

Oracle 데이터베이스는 완전한 SQL 문만 실행합니다. 다음과 같은 조각은 추가 텍스트가 필요하다는 오류를 발생시킵니다:

SELECT last_name;

Oracle SQL 문은 다음과 같은 범주로 나눌 수 있습니다:

  • 데이터 정의 언어(DDL) 문
  • 데이터 조작 언어(DML) 문
  • 트랜잭션 제어 문
  • 세션 제어 문
  • 시스템 제어 문
  • 임베디드 SQL 문

Data Definition Language (DDL) Statements

데이터 정의 언어(DDL) 문은 스키마 객체를 정의, 구조적으로 변경 및 삭제합니다.

DDL을 사용하면 객체에 접근하는 애플리케이션을 변경하지 않고 객체의 속성을 변경할 수 있습니다. 예를 들어, 인사 애플리케이션이 접근하는 테이블에 열을 추가해도 애플리케이션을 다시 작성할 필요가 없습니다. DDL을 사용하면 데이터베이스 사용자가 작업을 수행하는 동안에도 객체의 구조를 변경할 수 있습니다.

더 구체적으로, DDL 문을 사용하면 다음을 수행할 수 있습니다:

  • 스키마 객체 및 기타 데이터베이스 구조(데이터베이스 자체 및 데이터베이스 사용자 포함)를 생성, 변경 및 삭제합니다. 대부분의 DDL 문은 CREATE, ALTER 또는 DROP 키워드로 시작합니다.
  • 객체 구조를 제거하지 않고 스키마 객체의 모든 데이터를 삭제합니다(TRUNCATE).

참고: DELETE와 달리, TRUNCATE는 undo 데이터를 생성하지 않으므로 DELETE보다 빠릅니다. 또한, TRUNCATE는 delete 트리거를 호출하지 않습니다.

  • 권한 및 역할을 부여하고 취소합니다(GRANT, REVOKE).
  • 감사 옵션을 켜고 끕니다(AUDIT, NOAUDIT).
  • 데이터 사전에 주석을 추가합니다(COMMENT).

예제 7-1 DDL 문

다음 예제는 plants 테이블을 생성하는 데 DDL 문을 사용하고, 두 행을 테이블에 삽입하는 데 DML을 사용합니다. 그런 다음 테이블 구조를 변경하고, 이 테이블에 대한 읽기 권한을 사용자에게 부여하고 취소한 다음 테이블을 삭제합니다.

CREATE TABLE plants
    ( plant_id    NUMBER PRIMARY KEY, 
      common_name VARCHAR2(15) );

INSERT INTO plants VALUES (1, 'African Violet'); # DML 문

INSERT INTO plants VALUES (2, 'Amaryllis'); # DML 문

ALTER TABLE plants ADD 
    ( latin_name VARCHAR2(40) );

GRANT READ ON plants TO scott;

REVOKE READ ON plants FROM scott;

DROP TABLE plants;

DDL 문을 실행하기 전에 암시적 COMMIT이 발생하고, DDL 문을 실행한 후에는 COMMIT 또는 ROLLBACK이 발생합니다. 위 예제에서는 두 개의 INSERT 문이 ALTER TABLE 문으로 이어지므로 데이터베이스는 두 개의 INSERT 문을 커밋합니다. ALTER TABLE 문이 성공하면 데이터베이스는 이 문을 커밋하고, 그렇지 않으면 이 문을 롤백합니다. 어쨌든 두 개의 INSERT 문은 이미 커밋되었습니다.

참고:

  • 권한 및 역할에 대해 배우려면 "데이터베이스 보안 개요"
  • 스키마 객체를 생성하는 방법에 대해 배우려면 Oracle Database 2 Day Developer's Guide 및 Oracle Database Administrator’s Guide
  • 차단 및 비차단 DDL의 차이점에 대해 배우려면 Oracle Database Development Guide
  • DDL 문 목록은 Oracle Database SQL Language Reference

Data Manipulation Language (DML) Statements

데이터 조작 언어(DML) 문은 기존 스키마 객체의 데이터를 쿼리하거나 조작합니다.

DDL 문이 데이터베이스의 구조를 변경하는 반면, DML 문은 내용물을 쿼리하거나 변경합니다. 예를 들어, ALTER TABLE은 테이블 구조를 변경하고, INSERT는 테이블에 하나 이상의 행을 추가합니다.

DML 문은 가장 자주 사용되는 SQL 문이며 다음을 수행할 수 있습니다:

  • 하나 이상의 테이블 또는 뷰에서 데이터를 검색합니다(SELECT).
  • 열 값 목록을 지정하거나 기존 데이터를 선택하고 조작하는 하위 쿼리를 사용하여 테이블이나 뷰에 새 데이터 행

을 추가합니다(INSERT).

  • 테이블이나 뷰의 기존 행의 열 값을 변경합니다(UPDATE).
  • 조건부로 테이블이나 뷰에 행을 업데이트하거나 삽입합니다(MERGE).
  • 테이블이나 뷰에서 행을 제거합니다(DELETE).
  • SQL 문에 대한 실행 계획을 봅니다(EXPLAIN PLAN).
  • 테이블이나 뷰를 잠금으로써 다른 사용자의 접근을 일시적으로 제한합니다(LOCK TABLE).

다음 예제는 DML을 사용하여 직원 테이블을 쿼리합니다. 예제는 DML을 사용하여 직원 테이블에 행을 삽입하고, 이 행을 업데이트한 다음 삭제합니다:

SELECT * FROM employees;

INSERT INTO employees (employee_id, last_name, email, job_id, hire_date, salary)
  VALUES (1234, 'Mascis', 'JMASCIS', 'IT_PROG', '14-FEB-2008', 9000);

UPDATE employees SET salary=9100 WHERE employee_id=1234;

DELETE FROM employees WHERE employee_id=1234;

논리적 작업 단위를 형성하는 DML 문 모음을 트랜잭션이라고 합니다. 예를 들어, 돈을 이체하는 트랜잭션은 저축 계좌 잔액을 줄이고, 당좌 계좌 잔액을 늘리며, 계좌 이체를 기록하는 세 가지 개별 작업을 포함할 수 있습니다. DDL 문과 달리 DML 문은 현재 트랜잭션을 암시적으로 커밋하지 않습니다.

참고:

  • DML과 DDL 처리의 차이점에 대해 배우려면 "Differences Between DML and DDL Processing"
  • 트랜잭션 소개에 대해 배우려면 "Introduction to Transactions"
  • 데이터를 쿼리하고 조작하는 방법에 대해 배우려면 Oracle Database 2 Day Developer's Guide
  • DML 문 목록은 Oracle Database SQL Language Reference

SELECT Statements

쿼리는 테이블이나 뷰에서 데이터를 검색하는 작업입니다.

SELECT는 데이터를 쿼리하는 데 사용할 수 있는 유일한 SQL 문입니다. SELECT 문을 실행하여 검색된 데이터 집합을 결과 집합이라고 합니다.

다음 표는 두 개의 필수 키워드와 SELECT 문에 자주 사용되는 두 키워드를 보여줍니다. 표는 SELECT 문 키워드의 기능도 설명합니다.

키워드필수 여부설명기능
SELECT예결과에 표시할 열을 지정합니다. 투영은 테이블의 열 집합을 생성합니다.투영
FROM예데이터를 검색할 테이블이나 뷰를 지정합니다.조인
WHERE아니오행을 필터링할 조건을 지정하여 테이블의 행 하위 집합을 생성합니다. 조건은 하나 이상의 식과 논리 연산자를 조합하여 TRUE, FALSE, UNKNOWN 값을 반환합니다.선택
ORDER BY아니오행을 표시할 순서를 지정합니다.

참고:

  • SELECT 구문과 의미론에 대한 자세한 내용은 Oracle Database SQL Language Reference

Joins

조인은 두 개 이상의 테이블, 뷰, 물질화된 뷰에서 행을 결합하는 쿼리입니다.

다음 예제는 직원 테이블과 부서 테이블을 조인하고(FROM 절), 지정된 조건을 충족하는 행만 선택하며(WHERE 절), 두 열의 데이터를 검색합니다(SELECT). SQL 문 다음에는 샘플 출력이 나옵니다:

SELECT email, department_name
FROM   employees 
JOIN   departments
ON     employees.department_id = departments.department_id
WHERE  employee_id IN (100,103)
ORDER BY email;

출력:

EMAILDEPARTMENT_NAME
AHUNOLDIT
SKINGExecutive

조인에는 여러 가지 유형이 있습니다:

  • 내부 조인: 조인 조건을 충족하는 행만 반환합니다.
  • 외부 조인: 조인 조건을 충족하는 행과 다른 테이블의 행이 조건을 충족하지 않는 경우에도 행을 반환합니다.
  • 카티션 제품: 두 테이블이 조인 조건이 없으면 카티션 조인을 수행합니다.

참고:

  • 조인에 대해 배우려면 Oracle Database SQL Tuning Guide
  • 조인에 대한 자세한 설명과 예제는 Oracle Database SQL Language Reference

Subqueries

서브쿼리는 다른 SQL 문 내에 중첩된 SELECT 문입니다. 서브쿼리는 여러 쿼리를 실행하여 단일 문제를 해결해야 할 때 유용합니다.

다음 예제는 서브쿼리를 포함합니다:

SELECT first_name, last_name 
FROM   employees
WHERE  department_id 
IN     ( SELECT department_id 
         FROM departments 
         WHERE location_id = 1800 );

내부 SELECT 문은 위치 ID 1800인 부서의 ID를 검색합니다. 이러한 부서 ID는 외부 쿼리 블록에 의해 사용되어 해당 부서에 속한 직원의 이름을 검색합니다.

참고:

  • Virtual Private Database (VPD)

Transaction Control Statements

트랜잭션 제어 문은 DML 문에 의해 수행된 변경 사항을 관리하고 DML 문을 트랜잭션으로 그룹화합니다.

이 문은 다음을 수행할 수 있게 합니다:

  • 트랜잭션의 변경 사항을 영구적으로 만듭니다(COMMIT).
  • 트랜잭션 시작 이후(ROLLBACK) 또는 저장점 이후(ROLLBACK TO SAVEPOINT) 변경 사항을 취소합니다. 저장점은 트랜잭션 컨텍스트 내의 사용자 정의 중간 마커입니다.

참고: ROLLBACK 문은 트랜잭션을 종료하지만, ROLLBACK TO SAVEPOINT는 트랜잭션을 종료하지 않습니다.

  • 저장점을 설정합니다(SAVEPOINT).
  • 트랜잭션의 속성을 설정합니다(SET TRANSACTION).
  • 연기 가능한 무결성 제약 조건이 각 DML 문 후에 검사되는지 또는 트랜잭션이 커밋될 때 검사되는지 지정합니다(SET CONSTRAINT).

다음 예제는 'Update salaries'라는 이름의 트랜잭션을 시작합니다. 예제는 저장점을 생성하고, 직원의 급여를 업데이트한 후 저장점으로 트랜잭션을 롤백합니다. 그런 다음 급여를 다른 값으로 업데이트하고 커밋합니다:

SET TRANSACTION NAME 'Update salaries';

SAVEPOINT before_salary_update;

UPDATE employees SET salary=9100 WHERE employee_id=1234 # DML

ROLLBACK TO SAVEPOINT before_salary_update;

UPDATE employees SET salary=9200 WHERE employee_id=1234 # DML

COMMIT COMMENT 'Updated salaries';

참고:

  • 트랜잭션 소개에 대해 배우려면 "Introduction to Transactions"
  • 제약 조건의 유효성을 검사하는 시기에 대해 배우려면 "When the Database Checks Constraints for Validity"
  • 트랜잭션 제어 문에 대해 배우려면 Oracle Database SQL Language Reference

Session Control Statements

세션 제어 문은 사용자 세션의 속성을 동적으로 관리합니다.

세션은 데이터베이스 인스턴스 메모리에서 현재 사용자가 데이터베이스에 로그인한 상태를 나타내는 논리적 엔티티입니다. 세션은 사용자가 데이터베이스에 인증되어 로그인한 시간부터 로그아웃하거나 데이터베이스 애플리케이션을 종료할 때까지 지속됩니다.

세션 제어 문은 다음을 수행할 수 있게 합니다:

  • 현재 세션을 변경하여 기본 날짜 형식 설정과 같은 특수 기능을 수행합니다(ALTER SESSION).
  • 현재 세션에 대해 권한 그룹인 역할을 활성화하고 비활성화합니다(SET ROLE).

다음 문은 세션의 기본 날짜 형식을 'YYYY MM DD-HH24:MI:SS'로 동적으로 변경합니다:

ALTER SESSION 
   SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';

세션 제어 문은 현재 트랜잭션을 암시적으로 커밋하지 않습니다.

참고:

  • 연결 및 세션에 대해 배우려면 "Connections and Sessions"
  • ALTER SESSION 구문 및 의미론에 대해 배우려면 Oracle Database SQL Language Reference

System Control Statement

시스템 제어 문은 데이터베이스 인스턴스의 속성을 변경합니다.

유일한 시스템 제어 문은 ALTER SYSTEM입니다. 이 문은 공유 서버의 최소 수를 변경하거나, 세션을 종료하거나, 시스템 수준의 작업을 수행할 수 있게 합니다.

시스템 제어 문 예제는 다음과 같습니다:

ALTER SYSTEM SWITCH LOGFILE; 

ALTER SYSTEM KILL SESSION '39, 23';

ALTER SYSTEM 문은 현재 트랜잭션을 암시적으로 커밋하지 않습니다.

참고:

  • ALTER SYSTEM 구문 및 의미론에 대해 배우려면 Oracle Database SQL Language Reference

Embedded SQL Statements

임베디드 SQL 문은 절차적 언어 프로그램 내에 DDL, DML 및 트랜잭션 제어 문을 포함합니다.

임베디드 문은 Oracle 프

리컴파일러와 함께 사용됩니다. 임베디드 SQL은 절차적 언어 애플리케이션에 SQL을 포함하는 한 가지 방법입니다. 또 다른 방법은 ODBC(Open Database Connectivity) 또는 JDBC(Java Database Connectivity)와 같은 절차적 API를 사용하는 것입니다.

임베디드 SQL 문은 다음을 수행할 수 있게 합니다:

  • 커서를 정의, 할당 및 해제합니다(DECLARE CURSOR, OPEN, CLOSE).
  • 데이터베이스를 지정하고 연결합니다(DECLARE DATABASE, CONNECT).
  • 변수 이름을 할당합니다(DECLARE STATEMENT).
  • 디스크립터를 초기화합니다(DESCRIBE).
  • 오류 및 경고 조건을 처리하는 방법을 지정합니다(WHENEVER).
  • SQL 문을 구문 분석하고 실행합니다(PREPARE, EXECUTE, EXECUTE IMMEDIATE).
  • 데이터베이스에서 데이터를 검색합니다(FETCH).

참고:

  • 서버 사이드 프로그래밍 소개 및 클라이언트 사이드 API

Overview of the Optimizer

Oracle 데이터베이스가 SQL 문을 처리하는 방법을 이해하려면 옵티마이저(쿼리 옵티마이저 또는 비용 기반 옵티마이저라고도 함)를 이해하는 것이 필요합니다. 모든 SQL 문은 옵티마이저를 사용하여 지정된 데이터에 접근하는 가장 효율적인 수단을 결정합니다.

Use of the Optimizer

옵티마이저는 실행 계획을 생성하여 실행 가능한 방법을 설명합니다.

옵티마이저는 여러 정보 출처를 고려하여 가장 효율적인 실행 계획을 결정합니다. 예를 들어, 옵티마이저는 쿼리 조건, 사용 가능한 접근 경로, 시스템에 대한 통계 및 힌트를 고려합니다.

DML 문을 실행하려면 Oracle 데이터베이스가 여러 단계를 수행해야 할 수 있습니다. 각 단계는 데이터베이스에서 물리적으로 데이터를 검색하거나 문을 발행한 사용자를 위해 데이터를 준비합니다. 데이터베이스가 문을 실행하는 데 사용하는 단계는 문 실행 속도에 큰 영향을 미칩니다. DML 문을 처리하는 방법에는 여러 가지가 있을 수 있습니다. 예를 들어, 테이블 또는 인덱스에 접근하는 순서가 달라질 수 있습니다.

SQL 문에 대한 최적의 실행 계획을 결정할 때, 옵티마이저는 다음 작업을 수행합니다:

  • 표현식 및 조건 평가
  • 무결성 제약 조건 검사
  • 문 변환
  • 옵티마이저 목표 선택
  • 접근 경로 선택
  • 조인 순서 선택

옵티마이저는 쿼리를 처리하는 대부분의 가능한 방법을 생성하고, 생성된 실행 계획의 각 단계에 비용을 할당합니다. 가장 낮은 비용의 계획이 실행할 쿼리 계획으로 선택됩니다.

참고: SQL 문에 대한 실행 계획을 실행하지 않고도 얻을 수 있습니다. 데이터베이스가 실제로 쿼리를 실행하기 위해 사용하는 실행 계획만이 정확하게 쿼리 계획이라고 불립니다.
옵티마이저 선택에 영향을 주기 위해 옵티마이저 목표를 설정하고 대표적인 통계를 수집할 수 있습니다. 예를 들어, 옵티마이저 목표를 다음 중 하나로 설정할 수 있습니다:

  • 총 처리량
    • ALL_ROWS 힌트는 옵티마이저에게 결과의 마지막 행을 클라이언트 애플리케이션에 가능한 한 빨리 전달하도록 지시합니다.
  • 초기 응답 시간
    • FIRST_ROWS 힌트는 옵티마이저에게 첫 번째 행을 클라이언트에 가능한 한 빨리 전달하도록 지시합니다.

일반적인 최종 사용자 인터랙티브 애플리케이션은 초기 응답 시간 최적화의 이점을 누릴 수 있으며, 배치 모드의 비인터랙티브 애플리케이션은 총 처리량 최적화의 이점을 누릴 수 있습니다.

참고:

  • DBMS_STATS 사용에 대한 정보는 Oracle Database PL/SQL Packages and Types Reference 참조
  • 옵티마이저 및 힌트 사용에 대한 자세한 내용은 Oracle Database SQL Tuning Guide 참조

Optimizer Components

옵티마이저는 변환기, 추정기 및 계획 생성기라는 세 가지 주요 구성 요소로 구성됩니다.

다음 다이어그램은 구성 요소를 나타냅니다:

  • 옵티마이저는 파싱된 쿼리를 입력으로 받아, 사용 가능한 접근 경로와 힌트를 기반으로 SQL 문에 대한 잠재적 계획 집합을 생성합니다.
  • 옵티마이저는 데이터 사전의 통계를 기반으로 각 계획의 비용을 추정합니다. 비용은 특정 계획으로 문을 실행하는 데 필요한 예상 리소스 사용량에 비례하는 추정 값입니다.
  • 옵티마이저는 계획의 비용을 비교하고 가장 낮은 비용의 계획을 쿼리 계획으로 선택하여 행 소스 생성기에 전달합니다.

참고:

  • "SQL 파싱"
  • "SQL 행 소스 생성"

Query Transformer

쿼리 변환기는 쿼리의 형태를 변경하여 옵티마이저가 더 나은 실행 계획을 생성할 수 있는지 판단합니다. 쿼리 변환기의 입력은 파싱된 쿼리이며, 옵티마이저는 이를 쿼리 블록의 집합으로 표현합니다.

참고:

  • "쿼리 재작성"

Estimator

추정기는 주어진 실행 계획의 전체 비용을 결정합니다.

추정기는 이를 달성하기 위해 세 가지 유형의 측정을 생성합니다:

  • 선택도: 행 집합에서 행의 비율을 나타냅니다. 선택도는 last_name='Smith'와 같은 쿼리 술어 또는 술어의 조합과 관련이 있습니다.
  • 기수: 행 집합의 행 수를 나타냅니다.
  • 비용: 사용된 작업 또는 리소스를 나타냅니다. 쿼리 옵티마이저는 디스크 I/O, CPU 사용량 및 메모리 사용량을 작업 단위로 사용합니다.

통계가 있는 경우, 추정기는 이를 사용하여 측정을 계산합니다. 통계는 측정의 정확도를 향상시킵니다.

Plan Generator

계획 생성기는 제출된 쿼리에 대해 다양한 계획을 시도합니다. 옵티마이저는 비용이 가장 낮은 계획을 선택합니다.

각 중첩된 서브쿼리 및 병합되지 않은 뷰에 대해 옵티마이저는 서브플랜을 생성합니다. 옵티마이저는 각 서브플랜을 별도의 쿼리 블록으로 나타냅니다. 계획 생성기는 다양한 접근 경로, 조인 방법 및 조인 순서를 시도하여 쿼리 블록에 대한 다양한 계획을 탐색합니다.

적응형 쿼리 최적화 기능은 문 실행 중 수집된 통계를 기반으로 계획을 변경합니다. 모든 적응 메커니즘은 기본 계획과 다른 최종 계획을 실행할 수 있습니다. 적응 최적화는 문 실행 중 서브플랜을 선택하는 동적 계획 또는 현재 실행 이후 계획을 변경하는 재최적화를 사용합니다.

참고:

  • "애플리케이션 및 SQL 튜닝"
  • 옵티마이저 구성 요소 및 적응 최적화에 대해 배우려면 Oracle Database SQL Tuning Guide 참조

Access Paths

접근 경로는 쿼리가 행을 검색하는 기술입니다.

예를 들어, 인덱스를 사용하는 쿼리는 사용하지 않는 쿼리와 다른 접근 경로를 가집니다. 일반적으로 인덱스 접근 경로는 테이블 행의 작은 부분을 검색하는 문에 가장 적합합니다. 테이블의 큰 부분에 접근하는 경우 전체 스캔이 더 효율적입니다.

데이터베이스는 테이블에서 데이터를 검색하기 위해 여러 가지 다른 접근 경로를 사용할 수 있습니다. 다음은 대표적인 목록입니다:

  • 전체 테이블 스캔: 이 유형의 스캔은 테이블의 모든 행을 읽고 선택 기준을 충족하지 않는 행을 필터링합니다. 데이터베이스는 세그먼트의 모든 데이터 블록을 순차적으로 스캔합니다.
  • rowid 스캔: 행의 rowid는 행을 포함하는 데이터 파일과 데이터 블록 및 해당 블록의 행 위치를 지정합니다. 데이터베이스는 먼저 선택된 행의 rowid를 얻고, 그런 다음 이 rowid를 기반으로 각 선택된 행을 찾습니다.
  • 인덱스 스캔: 이 스캔은 SQL 문에서 접근하는 인덱싱된 열 값을 인덱스에서 검색합니다. 문이 인덱스 열만 접근하는 경우, Oracle 데이터베이스는 인덱스에서 직접 인덱스 열 값을 읽습니다.
  • 클러스터 스캔: 클러스터 인덱스가 있는 인덱싱된 테이블 클러스터에서 데이터를 검색합니다. 데이터베이스는 클러스터 인덱스를 스캔하여 선택된 행의 rowid를 얻습니다. Oracle 데이터베이스는 이 rowid를 기반으로 행을 찾습니다.
  • 해시 스캔: 해시 클러스터에서 행을 검색합니다. 데이터베이스는 먼저 문에서 지정한 클러스터 키 값에 해시 함수를 적용하여 해시 값을 얻습니다. Oracle 데이터베이스는 이 해시 값을 가진 행을 포함하는 데이터 블록을 스캔합니다.

옵티마이저는 문에 대한 사용 가능한 접근 경로와 각 접근 경로 또는 경로 조합의 사용 비용을 기반으로 접근 경로를 선택합니다.

참고:

  • 접근 경로에 대해 배우려면 Oracle Database 2 Day + Performance Tuning Guide 및 Oracle Database SQL Tuning Guide 참조

Optimizer Statistics

옵티마이저 통계는 데이터베이스와 데이터베이스 내 객체에 대한 세부 정보를 설명하는 데이터 모음입니다. 통계는 옵티마이저가 접근 경로를 평가할 때 사용할 수 있는 데이터 저장 및 분포에 대한 통계적으로 정확한 그림을 제공합니다.

옵티마이저 통계에는 다음이 포함됩니다:

  • 테이블 통계: 행 수, 블록 수, 평균 행 길이 등을 포함합니다.
  • 열 통계

: 열의 고유 값 수 및 null 값 수와 데이터 분포를 포함합니다.

  • 인덱스 통계: 리프 블록 수 및 인덱스 레벨을 포함합니다.
  • 시스템 통계: CPU 및 I/O 성능 및 활용도를 포함합니다.

Oracle 데이터베이스는 자동 유지 관리 작업으로 모든 데이터베이스 객체에 대한 옵티마이저 통계를 자동으로 수집하고 유지 관리합니다. 또한 DBMS_STATS 패키지를 사용하여 통계를 수동으로 수집할 수 있습니다. 이 PL/SQL 패키지는 통계를 수정, 보기, 내보내기, 가져오기 및 삭제할 수 있습니다.

참고: 옵티마이저 통계는 쿼리 최적화를 목적으로 생성되며, 데이터 사전에 저장됩니다. 이러한 통계를 동적 성능 뷰를 통해 볼 수 있는 성능 통계와 혼동하지 마십시오.
Optimizer Statistics Advisor는 현재 통계를 수집하는 방법, 기존 통계 수집 작업의 효과 및 수집된 통계의 품질을 분석하는 내장 진단 소프트웨어입니다. Optimizer Statistics Advisor는 현재 기능 세트를 기반으로 Oracle의 모범 사례를 구현하는 규칙을 유지합니다. 이를 통해 어드바이저는 통계 수집에 대한 최신 권장 사항을 항상 제공합니다.

참고:

  • 통계 수집 및 관리 방법에 대해 배우려면 Oracle Database 2 Day + Performance Tuning Guide 및 Oracle Database SQL Tuning Guide 참조
  • DBMS_STATS에 대해 배우려면 Oracle Database PL/SQL Packages and Types Reference 참조

Optimizer Hints

힌트는 SQL 문에서 옵티마이저에 대한 지침 역할을 하는 주석입니다.

때때로 특정 애플리케이션의 데이터에 대해 더 많은 정보를 가진 애플리케이션 디자이너가 SQL 문을 실행하는 더 효과적인 방법을 선택할 수 있습니다. 애플리케이션 디자이너는 SQL 문에 힌트를 사용하여 문을 실행하는 방법을 지정할 수 있습니다. 다음 예제는 힌트 사용을 보여줍니다.

예제 7-2 FIRST_ROWS 힌트를 사용한 SELECT의 실행 계획

대화형 애플리케이션이 50개의 행을 반환하는 쿼리를 실행한다고 가정합니다. 이 애플리케이션은 처음 25개의 행만 검색하여 최종 사용자에게 보여줍니다. 사용자가 기다리지 않도록 처음 25개의 레코드를 가능한 한 빨리 가져오는 계획을 옵티마이저가 생성하도록 하려면 다음과 같이 힌트를 사용할 수 있습니다:

SELECT /*+ FIRST_ROWS(25) */ employee_id, department_id
FROM   hr.employees
WHERE  department_id > 50;

실행 계획은 옵티마이저가 부서 ID가 50 이상인 직원의 처음 25개 행을 찾기 위해 employees.department_id 열에 인덱스를 선택했음을 보여줍니다. 옵티마이저는 인덱스에서 검색된 rowid를 사용하여 employees 테이블에서 레코드를 검색하고 클라이언트로 반환합니다. 첫 번째 레코드의 검색은 일반적으로 거의 즉각적입니다.

예제 7-3 힌트 없는 SELECT의 실행 계획

동일한 문을 힌트 없이 실행한다고 가정합니다:

SELECT employee_id, department_id
FROM   hr.employees
WHERE  department_id > 50;

실행 계획은 두 인덱스를 조인하여 요청된 레코드를 가능한 한 빨리 반환함을 보여줍니다. 예제 7-2와 달리 인덱스에서 테이블로 반복해서 이동하는 대신, 옵티마이저는 부서 ID가 50 이상인 모든 행을 찾기 위해 EMP_DEPARTMENT_IX의 범위 스캔을 선택하고 이 행들을 해시 테이블에 배치합니다. 그런 다음 EMP_EMP_ID_PK 인덱스를 읽기로 결정합니다. 이 인덱스의 각 행에 대해 해시 테이블을 조사하여 부서 ID를 찾습니다.

이 경우, 데이터베이스는 EMP_DEPARTMENT_IX의 인덱스 범위 스캔이 완료될 때까지 첫 번째 행을 클라이언트에 반환할 수 없습니다. 따라서 이 생성된 계획은 첫 번째 레코드를 반환하는 데 더 오래 걸립니다. 예제 7-2의 계획과 달리, 테이블을 인덱스 rowid로 접근하는 것이 아니라 다중 블록 I/O를 사용하여 큰 읽기를 수행합니다. 이 읽기는 전체 결과 집합의 마지막 행을 더 빨리 반환할 수 있게 합니다.

참고:

  • 옵티마이저 힌트 사용 방법에 대해 배우려면 Oracle Database SQL Tuning Guide 참조

Overview of SQL Processing

이 섹션에서는 Oracle Database가 SQL 문을 처리하는 방법을 설명합니다. 특히 데이터 정의 언어(DDL) 문을 사용하여 객체를 생성하고, 데이터 조작 언어(DML) 문을 사용하여 데이터를 수정하며, 쿼리를 통해 데이터를 검색하는 방식을 설명합니다.

Stages of SQL Processing

SQL 처리의 일반적인 단계는 파싱, 최적화, 행 소스 생성 및 실행입니다. 문에 따라 데이터베이스는 일부 단계를 생략할 수 있습니다.

다음 그림은 일반적인 단계를 나타냅니다:

  • 파싱
  • 최적화
  • 행 소스 생성
  • 실행

SQL Parsing

SQL 처리의 첫 번째 단계는 SQL 파싱입니다. 이 단계는 SQL 문의 부분을 다른 루틴에서 처리할 수 있는 데이터 구조로 분리하는 것을 포함합니다.

애플리케이션이 SQL 문을 발행하면 애플리케이션은 문을 실행 준비를 위해 데이터베이스에 파싱 호출을 합니다. 파싱 호출은 커서를 열거나 생성합니다. 커서는 파싱된 SQL 문과 기타 처리 정보를 포함하는 세션 전용 비공개 SQL 영역에 대한 핸들입니다. 커서와 비공개 SQL 영역은 PGA에 있습니다.

파싱 호출 동안 데이터베이스는 다음 검사를 수행합니다:

  • 구문 검사
  • 의미 검사
  • 공유 풀 검사

위의 검사들은 문 실행 전에 발견될 수 있는 오류를 식별합니다. 일부 오류는 파싱으로 잡을 수 없습니다. 예를 들어, 데이터베이스는 데드락이나 데이터 변환 오류를 문 실행 중에만 만날 수 있습니다.

참고:

  • "잠금 및 데드락"

SQL Optimization

쿼리 최적화는 SQL 문을 실행하는 가장 효율적인 수단을 선택하는 과정입니다.

데이터베이스는 실제로 접근하는 데이터에 대해 수집된 통계를 기반으로 쿼리를 최적화합니다. 옵티마이저는 행 수, 데이터 집합의 크기 및 기타 요소를 사용하여 가능한 실행 계획을 생성하고 각 계획에 숫자 비용을 할당합니다. 데이터베이스는 가장 낮은 비용의 계획을 사용합니다.

데이터베이스는 각 고유한 DML 문에 대해 최소한 한 번은 하드 파싱을 수행해야 하며, 이 파싱 동안 최적화를 수행합니다. DDL은 하위 쿼리와 같은 최적화가 필요한 DML 구성 요소를 포함하지 않는 한 절대 최적화되지 않습니다.

참고:

  • "옵티마이저 개요"
  • 쿼리 옵티마이저에 대한 자세한 내용은 Oracle Database SQL Tuning Guide 참조

SQL Row Source Generation

행 소스 생성기는 옵티마이저로부터 최적의 실행 계획을 받아 데이터베이스의 나머지 부분에서 사용할 수 있는 반복 가능한 계획(쿼리 계획)을 생성하는 소프트웨어입니다.

쿼리 계획은 여러 단계의 조합 형태로 이루어집니다. 각 단계는 행 집합을 반환합니다. 이 집합의 행은 다음 단계에서 사용되거나 마지막 단계에서는 SQL 문을 발행한 애플리케이션에 반환됩니다.

행 소스는 실행 계획의 단계에서 반환된 행 집합과 행을 반복적으로 처리할 수 있는 제어 구조입니다. 행 소스는 테이블, 뷰 또는 조인 또는 그룹화 작업의 결과일 수 있습니다.

SQL Execution

실행 단계에서 SQL 엔진은 행 소스 생성기가 생성한 트리의 각 행 소스를 실행합니다. 이는 DML 처리에서 유일한 필수 단계입니다.

실행 중에 데이터가 메모리에 없으면 데이터베이스는 디스크에서 데이터를 메모리로 읽습니다. 데이터베이스는 또한 데이터 무결성을 보장하기 위해 필요한 잠금과 래치를 취하고 SQL 실행 중에 수행된 변경 사항을 기록합니다. SQL 문 처리의 마지막 단계는 커서를 닫는 것입니다.

데이터베이스가 인메모리 칼럼 저장소(IM 칼럼 저장소)를 사용하도록 구성된 경우, 데이터베이스는 가능한 경우 쿼리를 IM 칼럼 저장소로 투명하게 라우팅하고 그렇지 않은 경우 디스크와 데이터베이스 버퍼 캐시로 라우팅합니다. 단일 쿼리는 IM 칼럼 저장소, 디스크 및 버퍼 캐시를 모두 사용할 수 있습니다. 예를 들어, 쿼리가 두 테이블을 조인할 때, 그 중 하나만 IM 칼럼 저장소에 캐시되어 있을 수 있습니다.

참고:

  • "인메모리 영역"
  • 실행 계획 및 EXPLAIN PLAN 문에 대한 자세한 내용은 Oracle Database SQL Tuning Guide 참조

Differences Between DML and DDL Processing

Oracle 데이터베이스는 DML과는 다르게 DDL을 처리합니다.

예를 들어, 테이블을 생성할 때 데이터베이스는 CREATE TABLE 문을 최적화하지 않습니다. 대신 Oracle 데이터베이스는 DDL 문을 파싱하고 명령을 실행합니다.

DDL과 달리 대부분의 DML 문에는 쿼리 구성 요소가 있습니다. 쿼리에서 커서를 실행하면 쿼리에 의해 생성된 행이 결과 집합에 배치됩니다.

데이터베이스는 결과 집합의 행을 한 번에 하나씩 또는 그룹으로 가져올 수 있습니다. 페치 과정에서 데이터베이스는 행을 선택하고 쿼리 요청에 따라 행을 정렬합니다. 각 연속적인 페치는 마지막 행이 페치될 때까지 결과 집합의 다른 행을 검색합니다.

참고:

  • DDL 처리, 트랜잭션 제어 및 기타 유형의 문에 대해 배우려면 Oracle Database Development Guide 참조
profile
torch
비전공 개발 공부 이야기
이전 포스트

[오라클] Oracle® Database Concepts 챕터 6 한글 번역

다음 포스트

[오라클] Oracle® Database Concepts 챕터 8 한글 번역

0개의 댓글