WEB개발/DB

[DB] 테이블 논리단위, DDL TABLE (CREATE, ALTER, DROP)

wooyeon06 2022. 1. 13. 14:32

 

1. 논리전 단위 (Logical Unit)

 

  • 논리전 단위는 데이터베이스 내에서 사용자가 데이터를 다루는 기본적인 논리적인 단위입니다. 일반적으로 테이블이나 인덱스 등이 논리전 단위로 간주됩니다. 이러한 논리적 단위는 물리적인 저장 매체와는 별개로, 데이터를 논리적으로 구성하고 관리하는 단위입니다.
  • 예를 들어, 하나의 테이블은 논리적 데이터 단위입니다. 테이블 안에는 행(row)과 열(column)로 구성된 데이터가 논리적으로 저장됩니다.

 

 

2. 데이터 블록 (Data Block)

 

  • 데이터 블록은 데이터베이스에서 가장 작은 물리적 저장 단위입니다. DBMS에서 데이터를 실제로 디스크에 저장할 때 데이터를 블록 단위로 저장합니다. 데이터 블록은 디스크에서 읽거나 쓸 때의 최소 단위입니다.
  • 예를 들어, Oracle에서는 데이터 블록 크기가 2KB, 4KB, 8KB(Oracle default), 16KB 등으로 설정될 수 있습니다. 한 블록에는 여러 레코드가 포함될 수 있으며, 각 블록은 데이터 파일에 물리적으로 저장됩니다.
  • 데이터 블록은 데이터가 물리적으로 어떻게 디스크에 저장되는지를 결정하는 중요한 요소입니다.

 

데이터 블록 크기를 높게 설정할 경우의 장단점

더보기

데이터 블록 크기를 높게 설정하는 경우(예: 16KB, 32KB 등)에는 다음과 같은 장단점이 있습니다.

 

장점:

  1. 대용량 데이터 처리 효율성:
    • 대량의 데이터를 삽입하거나 조회할 때 블록 크기가 크면 한 번의 디스크 읽기/쓰기 작업으로 더 많은 데이터를 처리할 수 있습니다. 따라서 I/O 작업 횟수를 줄일 수 있습니다.
  2. 데이터베이스 성능 향상:
    • 많은 양의 데이터를 한 번에 읽고 쓰기 때문에, 트랜잭션 성능에 유리할 수 있습니다.
  3. 디스크 공간 효율성:
    • 데이터 블록 크기가 크면, 빈번하게 발생하는 "슬래이스 공간 낭비"를 줄일 수 있습니다. 

단점:

  1. 공간 낭비 (Small Rows):
    • 데이터 블록 크기가 크면, 작은 레코드(한 블록에 충분히 들어가지 않는 작은 데이터 행)들이 공간을 낭비하게 됩니다. 예를 들어, 하나의 레코드가 1KB인데, 32KB 데이터 블록을 사용하면 그 블록의 나머지 공간이 비게 되어 공간 효율성이 떨어질 수 있습니다.
  2. 메모리 사용량 증가:
    • 큰 데이터 블록을 사용할 경우, 버퍼 캐시에 더 많은 메모리를 요구할 수 있습니다. 이는 시스템의 메모리 부담을 증가시키고, 메모리 관리 측면에서 비효율적일 수 있습니다.
  3. 트랜잭션 성능 저하:
    • 작은 변경(예: 작은 업데이트, 작은 삽입)이 있을 때, 큰 데이터 블록은 그 변경이 발생할 때마다 더 많은 데이터를 처리해야 하므로 성능에 영향을 미칠 수 있습니다.

 

데이터 블록 크기를 낮게 설정할 경우의 장단점

더보기

데이터 블록 크기를 낮게 설정하는 경우(예: 2KB, 4KB 등)에는 다음과 같은 장단점이 있습니다.

 

장점:

  1. 작은 데이터에 최적화:
    • 작은 데이터 블록 크기는 작은 레코드들이 효율적으로 저장되도록 하여, 공간 낭비를 줄입니다. 작은 행들이 많을 경우, 작은 데이터 블록을 사용하면 디스크 공간을 효율적으로 활용할 수 있습니다.
  2. 메모리 부담 감소:
    • 작은 데이터 블록은 버퍼 캐시에서 더 적은 메모리를 사용하므로, 메모리 요구량이 적어집니다. 메모리 자원이 제한적인 환경에서는 유리할 수 있습니다.
  3. 짧은 레코드 처리에 유리:
    • 작은 레코드의 읽기/쓰기가 더 빠르고, I/O 효율성이 높을 수 있습니다. 특히 트랜잭션이 작고 자주 발생하는 경우에 성능에 유리할 수 있습니다.

단점:

  1. I/O 작업 증가:
    • 작은 데이터 블록을 사용할 경우, 많은 데이터가 여러 개의 블록에 분산되어 저장됩니다. 이로 인해 디스크 I/O 횟수가 늘어나게 되어, I/O 병목 현상이 발생할 수 있습니다. 예를 들어, 2KB 데이터 블록을 사용하면 같은 데이터량을 처리하는 데 여러 블록을 읽어야 하므로 I/O 효율성이 떨어질 수 있습니다.
  2. 디스크 공간 효율성 저하:
    • 작은 블록 크기는 슬래이스 공간 낭비가 커질 수 있습니다. 특히 데이터가 크고, 각 레코드가 블록에 맞게 잘 배치되지 않는 경우, 비효율적인 공간 사용이 발생할 수 있습니다.
  3. 성능 저하:
    • 더 많은 블록을 관리해야 하므로, 오버헤드가 증가할 수 있습니다. 대규모 데이터 처리나 복잡한 쿼리에서는 성능이 저하될 수 있습니다.

 

 

3. 익스텐트 (Extent)

 

  • 익스텐트는 연속된 데이터 블록의 집합으로, DBMS가 데이터를 저장할 때 하나의 논리적 단위로 묶어 관리합니다. 하나의 익스텐트는 특정 테이블이나 인덱스와 같은 객체의 데이터를 저장하기 위한 연속된 블록들이 모인 것입니다.
  • 예를 들어, 테이블이 데이터를 추가할 때, 처음에는 데이터 블록이 하나씩 할당되지만, 데이터가 많아지면 여러 데이터 블록이 연속적으로 할당되어 하나의 익스텐트를 형성합니다.
  • 익스텐트는 하나의 연속된 블록들로 구성되어 있기 때문에, 데이터를 효율적으로 읽고 쓸 수 있습니다.

 

 

4. 세그먼트 (Segment)

 

  • 세그먼트는 데이터베이스에서 특정한 데이터 구조(예: 테이블, 인덱스 등)에 대해 할당된 여러 익스텐트의 집합입니다. 세그먼트는 데이터를 저장하는 객체의 실제 데이터가 저장되는 물리적인 영역입니다.
  • 예를 들어, 하나의 테이블은 여러 익스텐트를 차지하고, 그 익스텐트들이 모여 하나의 테이블 세그먼트를 형성합니다. 마찬가지로, 하나의 인덱스도 여러 익스텐트를 차지하며, 그 익스텐트들이 모여 인덱스 세그먼트를 형성합니다.
  • 세그먼트는 DBMS가 데이터를 저장하고 관리하는 물리적인 단위로, 한 세그먼트 내의 데이터는 모두 특정 객체(예: 테이블, 인덱스 등)에 속합니다.

 

 

5. 테이블스페이스 (Tablespace)

 

  • 테이블스페이스는 데이터베이스의 물리적 저장 영역을 나타내는 더 큰 논리적 단위입니다. 테이블스페이스는 데이터베이스의 세그먼트들을 포함하며, 여러 데이터 파일로 구성될 수 있습니다. 각 테이블스페이스는 특정 데이터베이스 객체들을 저장하는 데 사용되는 저장 공간을 정의합니다.
  • 예를 들어, 하나의 테이블스페이스는 테이블과 그에 해당하는 인덱스의 세그먼트를 포함할 수 있습니다. 각 테이블스페이스는 여러 개의 데이터 파일로 분산 저장되며, 데이터 파일은 실제로 데이터를 디스크에 저장하는 물리적 파일입니다.
  • 논리적 구조인 테이블스페이스는 데이터베이스의 물리적 구조를 추상화하여 관리하기 쉽게 도와줍니다. 이를 통해 데이터베이스의 용량을 관리하고, 테이블이나 인덱스가 어느 저장 공간에 위치할지 제어할 수 있습니다.

TABLESPACE

: 오라클은 데이터를 관리하는 데이터베이스입니다. 데이터를 어딘가에 저장해놓고 사용하는 시스템이라고 볼 수 있습니다. 데이터 저장 단위 중 가장 상위에 있는 단위를 테이블 스페이스라고 합니다.

논리적 단위는 데이터블록 > 익스텐트 > 세그먼트 > 테이블스페이스 이렇게 나뉩니다.

 

 

 

CREATE TABLE

CREATE TABLE  NEW_EMP (ENO NUMBER(10), ENAME VARCHAR2(10)
)
TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE
(
   INITIAL 65536
   NEXT 1048576
   MINEXTENTS 1
   MAXEXTENTS UNLIMITED
   BUFFER_POOL DEFAULT
   PCTINCREASE 50
)
LOGGING
DISABLE ROW MOVEMENT ;

 

 

PCTFREE 10 : 블록내 여유공간 10% 블럭내에 이미 존재하고 있는 Row에 Update가 가능하도록 예약시켜 놓는 블럭의 퍼센트 값을 지정합니다.

( DEFAULT : 10% )

 

INITRANS 1 : 세그먼트의 속성 중 동시 트랜잭션 수를 제어하는 옵션 INITRANS의 기본값은 2이며 최소값도 2입니다.( DEFAULT : 2 )

 

MAXTRANS 255

최대 트랜잭션 수를 지정합니다. 여기서는 테이블당 최대 255개의 트랜잭션을 설정합니다.

( DEFAULT : 255 )

 

STORAGE : 데이터베이스 객체를 저장하는 방법을 지정할 수 있음

{ 

    INITIAL 65536 : Segment가 생성될 때 할당될 때 첫 번째 확장영역의 크기

    NEXT 1048576 : 다음 Extents의 크기, 두 번째 확장영역은 NEXT의 원래 크기와 동일하며 다음부터의 NEXT는                                        (1+PCTINCREASE/100)과 Next의 이전크기를 곱한 크기로 설정됨

    MINEXTENTS 1 : 생성할 Extents의 최소 값

    MAXEXTENTS UNLIMITED : 생성할 Extents의 최대 값

    PCTINCREASE : Extents의 증가율, (Default :  50)

}

 

BUFFER_POOL DEFAULT : 기본 버퍼 풀에서 사용하도록 지정 하는것. 버퍼 영역을 두고 사용 빈도가 높은 데이터를 메모리 내에서 운용하며 재활용합니다.

 

LOGGING : 로깅옵션 키는것(default 값임 NOLOGGING이 로깅 안하겠다는 옵션(이 경우 복구가 잘되지않음))

 

DISABLE ROW MOVEMENT : 이 옵션은 행 이동을 비활성화합니다. 즉, 테이블의 행을 업데이트하여 데이터베이스에서 행의 물리적 위치가 변경되지 않도록 합니다.

 

 

 

테이블스페이스(Tablespace) 조회

 

SELECT tablespace_name, status, contents, extent_management, allocation_type
FROM dba_tablespaces;

 

 

세그먼트(Segment) 조회

 

SELECT tablespace_name, segment_name
FROM USER_SEGMENTS

 

 

익스텐트(Extent) 조회

 

SELECT segment_name, segment_type, tablespace_name, extent_id, file_id, block_id, bytes
FROM dba_extents



제약조건 조회

 

SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = '테이블_이름';

 

 

ALTER TABLE

 

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE employees
MODIFY (emp_salary NUMBER(10, 2));

ALTER TABLE table_name
DROP COLUMN column_name;

 

 

ALTER TABLE 테이블_이름
ADD CONSTRAINT 제약_이름 제약_유형(열);

ALTER TABLE 테이블_이름
MODIFY CONSTRAINT 제약_이름 새로운_제약_설정;

ALTER TABLE employees
DROP CONSTRAINT 제약_이름;

 

 

  • 열 추가: ADD 키워드 사용
  • 열 수정: MODIFY 키워드 사용
  • 열 이름 변경: RENAME COLUMN 키워드 사용
  • 제약 조건 추가 또는 삭제: ADD CONSTRAINT, DROP CONSTRAINT 키워드 사용
  • 인덱스 추가 또는 삭제: ADD INDEX, DROP INDEX 키워드 사용 등

 

DROP TABLE

 

DROP TABLE table_name;

 

 

 

 

 

 

 

 

https://positivemh.tistory.com/329

https://coding-factory.tistory.com/411