[ORACLE] ALL_NESTED_TABLES 뷰 완전 정복과 실무 적용 전략

Oracle Database는 객체지향 기능을 지원하며, 그 중 하나가 네스티드 테이블(Nested Table)입니다. 네스티드 테이블은 테이블 컬럼 안에 또 다른 테이블 형태의 데이터를 저장할 수 있게 해주는 강력한 기능입니다. 이 구조는 복잡한 데이터를 구조화하고, 유연하게 관리할 수 있는 수단을 제공합니다. 오라클에서는 이러한 네스티드 테이블 정보를 추적하고 관리하기 위한 시스템 뷰로 ALL_NESTED_TABLES를 제공합니다.

1. ALL_NESTED_TABLES란?

ALL_NESTED_TABLES 뷰는 현재 사용자가 접근 가능한 모든 네스티드 테이블의 정의 정보를 보여주는 데이터 딕셔너리 뷰입니다. 이는 스키마 내에서 객체 컬렉션의 구조를 파악하거나, 데이터 모델링 시 네스티드 구조를 분석할 때 유용하게 사용됩니다.

2. 주요 컬럼 설명

컬럼명	설명
TABLE_NAME	네스티드 테이블을 포함한 상위 테이블의 이름
TABLE_TYPE_OWNER	컬렉션 타입을 정의한 스키마
TABLE_TYPE_NAME	컬렉션 타입 이름
STORAGE_TABLE_NAME	실제 데이터를 저장하는 내부 테이블 이름
COLUMN_NAME	네스티드 테이블이 정의된 컬럼 이름

3. 네스티드 테이블 개념 이해

네스티드 테이블은 PL/SQL이나 오라클 객체 모델링에서 자주 사용됩니다. 예를 들어 고객 정보를 저장하는 테이블 안에, 고객의 구매 내역을 테이블 형태로 저장하고 싶다면, 네스티드 테이블이 이상적인 솔루션입니다.

4. 실전 예제

다음은 네스티드 테이블 타입과 이를 사용하는 테이블을 정의하고, ALL_NESTED_TABLES 뷰에서 정보를 조회하는 예시입니다.

-- 객체 타입 및 네스티드 테이블 정의
CREATE TYPE purchase_obj AS OBJECT (
  item_id NUMBER,
  quantity NUMBER
);

CREATE TYPE purchase_table AS TABLE OF purchase_obj;

-- 네스티드 테이블을 사용하는 메인 테이블 생성
CREATE TABLE customers (
  customer_id NUMBER,
  name VARCHAR2(100),
  purchases purchase_table
) NESTED TABLE purchases STORE AS purchases_nt;

-- 네스티드 테이블 정보 확인
SELECT table_name, column_name, storage_table_name
FROM all_nested_tables
WHERE table_name = 'CUSTOMERS';
    

5. 유사 뷰 비교

네스티드 테이블과 관련된 오라클의 다른 뷰들과 ALL_NESTED_TABLES를 비교해 보겠습니다.

뷰 이름	용도	차이점
ALL_NESTED_TABLES	네스티드 테이블의 정의 및 저장 구조 조회	STORAGE 테이블까지 명시
ALL_TABLES	전체 테이블 목록 및 속성 정보 제공	네스티드 테이블 여부는 직접 알 수 없음
ALL_TAB_COLUMNS	컬럼 상세 정보 제공	객체 타입 컬럼 포함 여부는 표시됨

6. 실무 활용 전략

• 모델링 분석: 테이블 내 객체 구조 파악 및 문서화
• 데이터 마이그레이션: STORAGE_TABLE_NAME을 기반으로 네스티드 데이터 추출 가능
• 성능 튜닝: 내부 저장 구조를 고려한 인덱스 전략 수립

7. 자주 발생하는 문제 및 해결 방법

• 문제: 네스티드 테이블 컬럼에 데이터 삽입 오류 발생
• 원인: NESTED TABLE이 저장될 STORAGE TABLE이 명시되지 않음
• 해결: 테이블 생성 시 반드시 STORE AS 절 포함 필요

8. 참고사항

• 네스티드 테이블은 다른 테이블과는 달리 정규화된 형태가 아니므로 과도한 중첩은 피해야 합니다.
• ALL_NESTED_TABLES는 사용자 접근 권한이 있는 객체만을 보여주므로, 전체 정보가 필요한 경우 DBA_NESTED_TABLES 사용을 권장합니다.

9. 참고자료

• Oracle Database 19c Documentation: https://docs.oracle.com/en/database/
• Oracle PL/SQL Programming by Steven Feuerstein
• 실제 업무 프로젝트 기반 실무 경험