ORACLE 테이블 이해를 위한 오라클 구조, 오라클 논리적, 물리적 구성 요소, 테이블스페이스, 데이터파일

 

ORACLE 테이블 이해를 위한 오라클 구조, 오라클 논리적, 물리적 구성 요소, 테이블스페이스, 데이터파일

10.1. 테이블 이해를 위한 오라클 구조

오라클 데이터베이스의 구조는 크게 논리적 구성 요소와 물리적  구성 요소로 나누어 볼 수 있습니다.

10.1.1 오라클 논리적 구성 요소

논리적 구성 요소는 데이터 블록data blocks, 확장 영역extents, 세그먼트segments, 테이블스페이스tablespace, 데이터베이스database로 나눌 수 있습니다. 

데이터 블록은 데이터를 저장하는 최소 논리 단위로서, 논리 블록logical block, 페이지page등으로 불립니다. 즉 오라클 데이터베이스에 데이터를 쓰고, 읽는 동작의 최소 단위를 말합니다. 데이터 블록 크기는 운영체제 블록 크기의 배수로 해야 불필요한 I/O를 방지할 수 있습니다. 

확장 영역은 특정 유형 정보를 저장하는 목적으로 할당된 논리적으로 연속적인 데이터 블록 세트(집합)입니다. 확장 영역 집합이 세그먼트가 되는데 테이블은 데이터 세그먼트data segments이고, 인덱스는 인덱스 세그먼트index segments입니다. 

사원emp 테이블의 실제 데이터는 자체 데이터 세그먼트에 저장되는 반면, 인덱스는 인덱스 세그먼트에 저장됩니다. 물리적으로 다른 곳에 저장된다는 것 잊지 마세요.

이러한 세그먼트가 여러 개 모여서 테이블스페이스를 이루고 이들이 모여서 데이터베이스를 논리적으로 구성하게 됩니다. SQL 개발자 관점에서 데이터의 입/출력은 테이블 단위로 이루어 지고 이 테이블은 반드시 하나의 테이블스페이스에 속하게 됩니다.

데이터베이스는 테이블스페이스라고 하는 논리적인 스토리지 단위로 나뉘어 지고 테이블 스페이스는 세그먼트 라고하는 논리적 스토리지 단위로 나뉘며, 확장 영역으로 더 나뉩니다. 확장 영역은 연속 된 블록의 모음입니다.

테이블스페이스는 하나 이상의 물리적인 오라클 데이터 파일로 이루어 집니다.

10.1.2 오라클 물리적 구성 요소

오라클 데이터베이스의 물리적 구성 요소로 프로세스, 메모리, 파일이 있습니다. 오라클의 파일은 실제 데이터가 저장되는 데이터 파일data file, 장애가 발생했을 때 복구용으로 활용하는 리두 로그 파일redo log file, 데이터 파일과 리두 로그 파일의 위치를 가지고 있는 컨트롤 파일control file, 데이터베이스 파라미터를 저장하는 파라미터 파일parameter file 등이 있습니다.

이러한 논리적 구성 요소 및 물리적 구성 요소는 데이터베이스 관리자의 영역이므로 자세히 설명 드리지는 않겠습니다. 하지만 개발자 및 기본 SQL 사용자들도 테이블스페이스와 데이터 파일 정도는 알아두시면 좋습니다.

다음은 논리적 구성 요소와 데이터 파일의 관계입니다.

<그림/>

다음그림은 사원 테이블이 데이터 파일 2개에 나뉘어져 있고, 첫 번째 데이터 파일에는 확장 영역이 1개, 두 번째 데이터 파일에는 2개가 있고, 각 확장 영역에는 데이터 블록 12개가 들어 있으며, 데이터 블록 크기를 8이라고 가정했을 때의 그림입니다.

10.1.3 논리적/물리적 구조를 확인하기 위한 실습

실습을 하기전에 자주 출현하는 오라클의 데이터딕셔너리에 대해 간단히 설명 드리겠습니다.

오라클의 데이터딕셔너리data dictionary는 모든 데이터베이스의  정보(사용자, 권한, 스키마 개체, 무결성 제약조건, DB구조, DB 성능정보 등)를 담고 있는 시스템 테이블 및 뷰 입니다. 오라클이 사용하는 영역이므로 SYSTEM 테이블스페이스에 위치해 있으며 사용자들에게 DB의 모든 정보를 제공 합니다. DBA_로 시작하는 뷰는 DBA 권한을 가진 사용자만 볼 수 있는 뷰 인데 오라클의 모든 개체의 정보를 볼 수 있으며,  ALL_로 시작되는 뷰는 특정 사용자가 접근 가능한 객체정보를 , USER_로 시작되는 뷰는 특정 사용자에게 종속되어 있고 그 사용자가 소유한 객체에 대한 정보를 조회 할 수 있습니다.

본서의 실습에서 사용하는 사용자  SCOTT은 이미 DBA 권한을 가지고 있습니다. 0. 환경설정 사용자생성 부분에서 확인하세요.  

실습

USER_DATA_FILES 또는 DBA_DATA_FILES 딕셔너리뷰는 실제 데이터가 들어있는 오라클의 물리적 구성요소인 데이터파일data file에 대해 정보를 제공하는 뷰입니다. 파일이름, 테이블스페이스 이름, 데이터파일의 크기, 가용한지 등의 상태 정보를 제공합니다.

현재 데이터베이스에서 사용중인  테이블스페이스와 이를 구성하는, 데이터 파일을 DBA_DATA_FILES 딕셔너리뷰를 통해 조회 합시다.

SELECT TABLESPACE_NAME              , BYTES/1024/1024 MB              , FILE_NAME FROM    DBA_DATA_FILES;

<실행결과>

	TABLESPACE_NAME	MB	FILE_NAME
1	USERS	5	C:\APP\ORADATA\ORCL\USERS01.DBF
2	UNDOTBS1	55	C:\APP\ORADATA\ORCL\UNDOTBS01.DBF
3	SYSTEM	910	C:\APP\ORADATA\ORCL\SYSTEM01.DBF
4	SYSAUX	730	C:\APP\ORADATA\ORCL\SYSAUX01.DBF

현재 데이터베이스에는 4개의 테이블스페이스가 있고 각 테이블스페이스를 구성하는 데이터파일의 경로 및 파일명을 보여 줍니다.

실습

본 교재의 실습을 위한 사용자는 SCOTT입니다. 이 사용자는 0. 환경설정에서 사용자를 생성할 때 기본 테이블스페이스dafault tablespace를 별도로 지정하지 않았는데,  오라클 21C에서는 USERS 테이블스페이스로 설정합니다. 그래서 SCOTT 사용자에서 생성한 테이블은 USERS라는 테이블스페이스에 생성이 됩니다. 아래 실습을 통해 접속한 사용자의 기본 테이블스페이스와 관련된 정보를 확인해 보겠습니다.

현재 접속한 사용자의 사용자명, 기본 테이블스페이스, 생성일자, 최종로그인일시를 확인하는 쿼리를 작성합시다.

SELECT USERNAME              , DEFAULT_TABLESPACE              , CREATED              , LAST_LOGIN FROM    DBA_USERS WHERE USERNAME =  ❶USER ;

<실행결과>

	USERNAME	DEFAULT_TABLESPACE	CREATED	LAST_LOGIN
1	SCOTT	USERS	20/11/03	20/12/06 14:25:44.000000000 ASIA/SEOUL

 ❶ USER 함수는 현재 세션의 사용자의 이름을 VARCHAR2 현식으로 돌려줍니다. 쿼리 결과를 보았을 때 SCOTT 사용자로 접속된 상태라는 것을 알 수 있습니다. USER 함수는 CHECK 제약조건에서는 사용할 수 없습니다.

실습

USER_TABLES 딕셔너리뷰를 통해  현재 사용자가 소유(작성)한 테이블의 목록을 확인 할 수 있습니다.

현재 사용자의 테이블 목록을 조회하면서 테이블스페이스명도 같이 조회합시다.

SELECT TABLESPACE_NAME              , TABLE_NAME FROM     USER_TABLES;

<실행결과>

	TABLESPACE_NAME	TABLE_NAME
1	USERS	DEPT
2	USERS	EMP
3	USERS	BONUS
4	USERS	SALGRADE
5	USERS	CUSTOMER
6	USERS	SALES

현재 SCOTT 계정에는 6개의 테이블이 만들어 졌음을 알 수 있습니다.

실습

테이블의 크기가 얼마일까? 궁금할 때가 가끔 있습니다. 아래 실습에서 확인하세요.

사원emp 테이블의 데이터 블록data block 수와 몇 데이터 크기kb를 쿼리하세요.

SELECT BLOCKS          , BYTES/1024 AS KB FROM   USER_SEGMENTS WHERE  SEGMENT_NAME =❶ 'EMP';

<실행결과>

	BLOCKS	KB
1	8	64

❶ 테이블명은 오라클 딕셔너리에 대문자로 저장되어 있으므로 WHERE 절에서 문자 리터럴로 비교시 대문자로 작성해야 합니다.​

 

#ORACLE, #테이블, #테이블스페이스,#데이터파일,#오라클구조

 #ORACLE, #테이블, #테이블스페이스, #데이터파일, #오라클구조

오라클 테이블 소개

 오라클 테이블 소개

오라클에서 데이터 저장소 기본 단위인 테이블은 스키마 오브젝트schema cbjects의 일종으로 관계형 데이터베이스를 구성하는 기본 데이터 구조입니다. 행과 열 구조를 가지며 사용자들은 논리 단위인 테이블을 이용하여 데이터를 입력, 수정, 삭제, 추출 등을 할 수 있습니다.

오라클에서 사용자는 DB에 접속하기 위한 계정이고 스키마는 해당 계정에서 만든 테이블, 인덱스, 뷰와 같은 객체 입니다. 즉 스키마는 해당 계정에 속하는 데이터베이스 개체의 집합입니다.  오라클에서 CREATE USER로 사용자를 생성하면 이는 빈 스키마를 생성하는 것과 동일 합니다. 사용자와 스키마 간의 직접적인 일대일 관계로 인해 두 용어는 종종 같은 의미로 사용됩니다.

스키마 오브젝트는 해당 사용자 계정에서 만든 객체입니다. SQL로 생성되고 조작되는 TABLES, INDEXES, VIEWS, SEQUENCES, SYNONYM, CLUSTERS, DATABASE LINK, PROCEDURES, FUNCTIONS, PACKAGE 등이 있습니다.

테이블에서 테이블 각 열에 규칙을 지정할 수 있습니다. 이러한 규칙을 무결성 제약조건이라고 합니다. 예를 들어 열에 지정하는 NOT NULL은 무결성 제약조건입니다. 이 제약조건은 해당 열의 모든 행은 반드시 값을 가지도록 제약합니다.
종류
테이블 종류로는 행과 열의구조를 가진 일반적인 관계형 테이블, 오브젝트 타입, XMLType 테이블도 있습니다. 이 중에서는 오브젝트 타입, XMLType 테이블은 실무에서 거의 사용하지 않아 이 책에서는 다루지 않습니다.

테이블은 영구히 존재하는 테이블과 세션 또는 트랜잭션 기간 동안에만 존재하는 임시 테이블temporary table로 구분할 수 있습니다. 임시 테이블은 여러 SQL 작업을 실행하여 각 결과를 결합해 최종 결과를 얻을 때 중간 경과를 메모리에 임시로 보관하는 용도에 유용합니다.

사용법 핵심 요약
테이블을 생성한 후 SQL문을 사용하여 데이터 행을 삽입합니다. 그런 다음 SQL을 사용하여 테이블 데이터를 쿼리(SELECT), 삽입(INSERT), 삭제(DELETE) 또는 업데이트(UPDATE) 할 수 있습니다.

테이블을 더 유용하게 사용하려면 COMMENT 명령을 사용하여 각 테이블과 해당 열에 주석을 반드시 표기해야 합니다.

또한 테이블의 열에 무결성 제약조건(개체 무결성, 참조무결성)을 정의하면 데이터베이스의 비즈니스 규칙을 자동으로 적용할 수 있으니 Primary Key, Foreign Key, Not Null, Check, Unique 제약조건을 테이블 생성 시 적절하게 사용하기 바랍니다.

#오라클 #테이블 #테이블이란 #ORACLE #TABLE

오라클 SQL조인, 안티 조인(Anti Join)

 

오라클 SQL조인, 안티 조인(Anti Join)

조인의 대상이 되는 테이블과 일치하지 않는 데이터를 추출하는 조인방식 입니다.
 

서브 쿼리의 테이블에는 없는 메인 쿼리의 테이블의 데이터만 추출하는 조인 방법으로 NOT IN, NOT EXISTS 사용시 발생하는 조인연산이며 오라클에서 중첩루프조인 안티, 머지조인 안티, 해시 조인 안티로 실행 됩니다.
 

NOT IN을 이용한 안티 조인 예문 입니다. EMP 테이블에서 사번, 성명, 부서코드를 추출하는데 부서코드를 가지고 있지않은 사원들만 추출하는 쿼리 문 입니다. “KING” 사원은 최고관리자로 부서코드(deptno) 값을 가지고 있지않지만  NULL값이므로  결과 대상에서 제외 되었습니다.(NULL과 연산하는 결과는 NULL이 됨)

-- NOT IN SELECT empno, ename, deptno FROM emp WHERE deptno NOT IN (   SELECT deptno   FROM dept )

 

<실행결과>

선택된 행 없음

<실행계획(F10)>

오라클19C에서 별다른 힌트구문없이 그냥 실행 했을 때 해시조인 안티로 실행계획을 수립함을 확인할 수 있습니다.

NOT EXISTS을 이용한 안티 조인 예문 입니다. DEPT 테이블에서 부서코드, 부서명을 출력 하는데  EMP 테이블에 부서원들을 가지고 있지 않은 부서코드, 부서명을 출력합니다. 40번 부서는 EMP 테이블에 해당하는 부서원들이 없습니다. HASH_AJ 힌트구문은 안티 해시조인을 하라는 힌트구문으로 사용하지 않으면 오라클19C에서는 머지조인 안티로 쿼리문을 수행 합니다.

-- EXISTS SELECT deptno, dname FROM    dept WHERE NOT EXISTS    (SELECT /*+ HASH_AJ */ 1     FROM   emp     AND      emp.deptno = dept.deptno)

 

<실행결과>

	DEPTNO	ENAME
1	40	OPERATION

<실행계획(F10)>

#SQL조인, #조인이란, #안티조인, #AntiJoin, #SQL교육, #SQL강의, #오라클교육, #오라클, #오라클조인