ORACLE 테이블 컬럼
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 테이블[편집]
1.1 테이블 생성[편집]
CREATE TABLE TB_TEST
(
COL1 numeric(10) not null,
COL2 varchar2(50) not null,
COL3 varchar2(50),
CONSTRAINT PK_TEST PRIMARY KEY (COL1)
);1.2 테이블 변경[편집]
1.3 테이블명 변경[편집]
ALTER TABLE OWNER.TB_A RENAME TO TB_B;1.3.1 오라클_테이블_최종_입력수정일시 조회[편집]
-- 테이블 최종 입력/수정/삭제 일시
- ALL_TAB_MODIFICATIONS는 마지막 통계가 테이블에 수집 된 이후 수정 된 현재 사용자가 액세스 할 수있는 테이블정보 임.
- MONITORING 속성이있는 테이블만 가능
- 오랜 기간에 걸친 통계 수집을 위해 고안됨.
- 성능상의 이유로 Oracle Database는 실제 수정이 발생할 때 즉시 채우지 않음.
: DIMS_STATSPL / SQL 패키지에서 FLUSH_DATABASE_MONITORING_INFO 프로시저를 실행 최신 정보로 갱신 할수 있음.
SELECT *
FROM ALL_TAB_MODIFICATIONS
WHERE TABLE_OWNER = 'BABYMEAL'
AND TABLE_NAME NOT LIKE 'BIN%'
(원문)
ALL_TAB_MODIFICATIONS 438/1627 ALL_TAB_MODIFICATIONS describes tables accessible to the current user that have been modified since the last time statistics were gathered on the tables.
Related Views
DBA_TAB_MODIFICATIONS provides such information for all tables in the database.
USER_TAB_MODIFICATIONS provides such information for tables owned by the current user. This view does not display the TABLE_OWNER column.
Note:
These views are populated only for tables with theMONITORINGattribute. They are intended for statistics collection over a long period of time. For performance reasons, the Oracle Database does not populate these views immediately when the actual modifications occur. Run theFLUSH_DATABASE_MONITORING_INFOprocedure in theDIMS_STATSPL/SQL package to populate these views with the latest information. TheANALYZE_ANYsystem privilege is required to run this procedure.
Column Datatype NULL Description
TABLE_OWNER VARCHAR2(30) Owner of the modified table.
TABLE_NAME VARCHAR2(30) Name of the modified table
PARTITION_NAME VARCHAR2(30) Name of the modified partition
SUBPARTITION_NAME VARCHAR2(30) Name of the modified subpartition
INSERTS NUMBER Approximate number of inserts since the last time statistics were gathered
UPDATES NUMBER Approximate number of updates since the last time statistics were gathered
DELETES NUMBER Approximate number of deletes since the last time statistics were gathered
TIMESTAMP DATE Indicates the last time the table was modified
DROP_SEGMENTS NUMBER Number of partition and subpartition segments dropped since the last analyze
1.4 테이블 읽기/쓰기 모드 변경[편집]
- 11g 이전 버전의 경우, 테이블에 대한 DML 수행을 막는 방법으로 가상의 스키마 유저를 생성하고 테이블에대한 SELECT 권한만 부여하는 방식으로 사용
- 11g 부터 테이블의 속성을 'READ ONLY'로 변경함으로써 가상의 유저만이 아니라 OWNER까지 포함하여 DML을 사용할수 없게끔 제한할수 있는 기능 추가됨.
- 단, READ ONLY 테이블의 제한 대상은 DML에 국한되면 DDL은 허용됨.
- CREATE 명령
CREATE TABLE TB_TEST ... ... READ ONLY;- ALTER 명령
ALTER TABLE TB_TEST READ ONLY;
ALTER TABLE TB_TEST READ WRITE;1.5 테이블(제약사항 포함) 삭제[편집]
DROP TABLE OWNER.TB_A CASCADE CONSTRAINT;1.6 테이블/컬럼 정보 조회[편집]
1.6.1 테이블_컬럼정보 HTML로 추출하기[편집]
1.6.2 사용자 테이블/컬럼 전체 조회용 (html)[편집]
- 현재 접속중인 USER 테이블/컬럼정보
DECLARE
TYPE TYPE_VARCHAR2 IS TABLE OF VARCHAR2(4000);
V_TABLE_NAME TYPE_VARCHAR2;
V_TABLE_COMT TYPE_VARCHAR2;
V_CNT NUMBER;
V_MAX_PX NUMBER;
V_SQL VARCHAR2(4000);
V_VERSION VARCHAR2(100) ;
BEGIN
V_VERSION := 'ORACLE DBA ';
-- 1. 전체 테이블 정보 조회
SELECT A.TABLE_NAME
, REGEXP_SUBSTR(B.COMMENTS,'[^|]+',1,1) AS CMT
BULK COLLECT INTO V_TABLE_NAME
, V_TABLE_COMT
FROM USER_TABLES A
, USER_TAB_COMMENTS B
WHERE B.TABLE_NAME = A.TABLE_NAME
ORDER BY A.TABLE_NAME;
DBMS_OUTPUT.PUT_LINE('<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">');
DBMS_OUTPUT.PUT_LINE('<html>');
DBMS_OUTPUT.PUT_LINE('<head>');
DBMS_OUTPUT.PUT_LINE(' <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />');
DBMS_OUTPUT.PUT_LINE(' <title>'||V_VERSION||'</title>');
DBMS_OUTPUT.PUT_LINE(' <style type="text/css">');
DBMS_OUTPUT.PUT_LINE(' .entity{');
DBMS_OUTPUT.PUT_LINE(' border: solid 1px #7E2828;');
DBMS_OUTPUT.PUT_LINE(' margin-bottom: 20px;');
DBMS_OUTPUT.PUT_LINE(' font-size:10pt;');
DBMS_OUTPUT.PUT_LINE(' line-height:16pt;');
DBMS_OUTPUT.PUT_LINE(' }');
DBMS_OUTPUT.PUT_LINE(' .header{');
DBMS_OUTPUT.PUT_LINE(' background-color: #7E2828;');
DBMS_OUTPUT.PUT_LINE(' color: white;');
DBMS_OUTPUT.PUT_LINE(' }');
DBMS_OUTPUT.PUT_LINE(' .column-header{');
DBMS_OUTPUT.PUT_LINE(' background-color: #E8E8E8;');
DBMS_OUTPUT.PUT_LINE(' }');
DBMS_OUTPUT.PUT_LINE(' td{');
DBMS_OUTPUT.PUT_LINE(' padding-left: 5px;');
DBMS_OUTPUT.PUT_LINE(' padding-right: 5px;');
DBMS_OUTPUT.PUT_LINE(' }');
DBMS_OUTPUT.PUT_LINE(' </style>');
DBMS_OUTPUT.PUT_LINE('</head>');
DBMS_OUTPUT.PUT_LINE('<body>');
DBMS_OUTPUT.PUT_LINE('<h2>'||V_VERSION||' </h2>');
-- 2.테이블 별 LOOP
FOR I IN V_TABLE_NAME.FIRST..V_TABLE_NAME.LAST LOOP
SELECT MAX(A.COLUMN_ID) AS CNT
, (MAX(LENGTH(COLUMN_NAME)) * 14) AS MAX_PX
INTO V_CNT
, V_MAX_PX
FROM USER_TAB_COLS A
WHERE A.TABLE_NAME = V_TABLE_NAME(I)
;
DBMS_OUTPUT.PUT_LINE('<font size ="2"><table class="entity" cellpadding="0" cellspacing="0" width="100%" style="word-break:break-all;">');
DBMS_OUTPUT.PUT_LINE('<tr>');
DBMS_OUTPUT.PUT_LINE(' <td colspan="5" class="header">'||V_TABLE_NAME(I)||'('||V_TABLE_COMT(I)||')'||'</td>');
DBMS_OUTPUT.PUT_LINE('</tr>');
DBMS_OUTPUT.PUT_LINE(' <tr class="column-header">');
DBMS_OUTPUT.PUT_LINE(' <td>논리명</td>');
DBMS_OUTPUT.PUT_LINE(' <td>물리명</td>');
DBMS_OUTPUT.PUT_LINE(' <td>데이터 타입</td>');
DBMS_OUTPUT.PUT_LINE(' <td ALIGN="CENTER">Not Null</td>');
DBMS_OUTPUT.PUT_LINE(' <td>설명</td>');
DBMS_OUTPUT.PUT_LINE(' </tr>');
-- 3. 컬러 정보
FOR J IN 1..V_CNT LOOP
SELECT ' <tr>'||CHR(10)||' <td WIDTH="'||V_MAX_PX*1.5||'">'||A.LG||'</td>'||CHR(10)||' <td WIDTH="'||V_MAX_PX||'">'||A.COLUMN_NAME||'</td>'||CHR(10)||' <td WIDTH="120">'||A.DATA_TYPE||'</td>'||CHR(10)||' <td WIDTH="100" ALIGN="CENTER">'||A.NOTNULL||'</td>'||CHR(10)||' <td>'||A.COMMENTS||'</td>'||CHR(10)||' </tr>'
INTO V_SQL
FROM ( SELECT REGEXP_SUBSTR(B.COMMENTS, '[^|]+', 1, 1)||CASE WHEN C.COLUMN_NAME IS NOT NULL THEN '(PK)' END AS LG
, A.COLUMN_NAME
, A.DATA_TYPE || (CASE WHEN A.DATA_TYPE IN ('CHAR', 'VARCHAR2', 'NVARCHAR') THEN '(' || A.DATA_LENGTH || ')'
WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE = 0 AND A.DATA_PRECISION IS NOT NULL THEN '(' || A.DATA_PRECISION || ')'
WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE <> 0 THEN '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')'
END ) AS DATA_TYPE
, DECODE(A.NULLABLE, 'Y', 'N', 'N', 'Y') AS NOTNULL
, B.COMMENTS
FROM USER_TAB_COLS A
, USER_COL_COMMENTS B
, ( SELECT C.INDEX_NAME, C.TABLE_NAME, C.COLUMN_NAME
FROM USER_IND_COLUMNS C
WHERE EXISTS ( SELECT 1
FROM USER_CONSTRAINTS S
WHERE S.CONSTRAINT_TYPE = 'P'
AND S.TABLE_NAME = C.TABLE_NAME
AND S.INDEX_NAME = C.INDEX_NAME
)
) C
WHERE A.COLUMN_NAME != 'HIW_SOLUTION_TPCODE'
AND A.TABLE_NAME = V_TABLE_NAME(I)
AND B.TABLE_NAME = A.TABLE_NAME
AND B.COLUMN_NAME = A.COLUMN_NAME
AND C.TABLE_NAME(+) = A.TABLE_NAME
AND C.COLUMN_NAME(+) = A.COLUMN_NAME
AND A.COLUMN_ID = J
) A ;
DBMS_OUTPUT.PUT_LINE(V_SQL);
V_SQL := NULL;
END LOOP;
DBMS_OUTPUT.PUT_LINE('</table>');
V_CNT := 0;
END LOOP;
DBMS_OUTPUT.PUT_LINE('</body>');
DBMS_OUTPUT.PUT_LINE('</html>');
END;
/1.6.3 DBA용 조회 SQL[편집]
SELECT OWNER
, TABLE_NAME
, COLUMN_NAME
, PK
, COLUMN_NAME
, DATA_TYPE || '( ' || NVL(DATA_TYPE_2, DATA_LENGTH) || ' )' DATA_TYPE
, NULLABLE
, COMMENTS
FROM (SELECT A.OWNER
, A.TABLE_NAME
, A.COLUMN_ID
, B.POSITION PK
, A.COLUMN_NAME
, A.DATA_TYPE
, A.DATA_PRECISION || DECODE(A.DATA_SCALE, NULL, NULL, ',' || A.DATA_SCALE) DATA_TYPE_2
, A.DATA_LENGTH
, A.DATA_PRECISION
, A.DATA_SCALE
, A.NULLABLE
, A.COMMENTS
, ROW_NUMBER() OVER (PARTITION BY A.OWNER, A.TABLE_NAME, A.COLUMN_ID ORDER BY A.COLUMN_ID, B.POSITION) RN
FROM (SELECT COL.OWNER
, COL.TABLE_NAME
, COL.COLUMN_ID
, COL.COLUMN_NAME
, COL.DATA_TYPE
, COL.DATA_LENGTH
, COL.DATA_PRECISION
, COL.DATA_SCALE
, COL.NULLABLE
, COM.COMMENTS
FROM DBA_TAB_COLUMNS COL
, DBA_COL_COMMENTS COM
WHERE COL.COLUMN_NAME = COM.COLUMN_NAME
AND COL.OWNER = COM.OWNER
AND COL.TABLE_NAME = COM.TABLE_NAME
AND COM.OWNER =:IN_OWNER
AND COM.TABLE_NAME LIKE:IN_TABLE_NAME || '%') A
, DBA_CONS_COLUMNS B
WHERE B.TABLE_NAME(+) = A.TABLE_NAME
AND B.COLUMN_NAME(+) = A.COLUMN_NAME) X
WHERE X.RN = 1 ORDER BY X.TABLE_NAME, X.COLUMN_ID;1.6.4 일반 사용자 용[편집]
SELECT TABLE_NAME , COLUMN_NAME , PK , COLUMN_NAME , DATA_TYPE || '( ' || NVL(DATA_TYPE_2, DATA_LENGTH) || ' )' DATA_TYPE , NULLABLE , COMMENTS
FROM (SELECT A.TABLE_NAME , A.COLUMN_ID , B.POSITION PK , A.COLUMN_NAME , A.DATA_TYPE
, A.DATA_PRECISION || DECODE(A.DATA_SCALE, NULL, NULL, ',' || A.DATA_SCALE) DATA_TYPE_2
, A.DATA_LENGTH , A.DATA_PRECISION , A.DATA_SCALE , A.NULLABLE , A.COMMENTS
, ROW_NUMBER() OVER (PARTITION BY A.TABLE_NAME, A.COLUMN_ID
ORDER BY A.COLUMN_ID, B.POSITION) RN
FROM (SELECT COL.TABLE_NAME , COL.COLUMN_ID , COL.COLUMN_NAME , COL.DATA_TYPE , COL.DATA_LENGTH
, COL.DATA_PRECISION , COL.DATA_SCALE , COL.NULLABLE , COM.COMMENTS
FROM USER_TAB_COLUMNS COL
, USER_COL_COMMENTS COM
WHERE COL.COLUMN_NAME = COM.COLUMN_NAME
AND COL.TABLE_NAME = COM.TABLE_NAME
AND COM.TABLE_NAME LIKE:IN_TABLE_NAME || '%') A
, USER_CONS_COLUMNS B
WHERE B.TABLE_NAME(+) = A.TABLE_NAME AND B.COLUMN_NAME(+) = A.COLUMN_NAME
) X
WHERE X.RN = 1 ORDER BY X.TABLE_NAME, X.COLUMN_ID;1.7 테이블 생성일자 보기[편집]
SELECT SUBSTRB(OBJECT_NAME, 1, 15) AS OBJECT_NAME
, CREATED
, LAST_DDL_TIME
, TIMESTAMP
, STATUS
FROM USER_OBJECTS
WHERE OBJECT_NAME = UPPER('&테이블명')
AND OBJECT_TYPE = 'TABLE';1.8 CONSTRAINT 제약 사항 보기[편집]
SELECT DECODE(A.CONSTRAINT_TYPE, 'P', 'Primary Key', 'R', 'Foreign Key', 'C', 'Table Check', 'V', 'View Check', 'U', 'Unique', '?') AS "유형"
, SUBSTRB(A.CONSTRAINT_NAME, 1, 25) AS CONSTRAINT_NAME
, B.POSITION
, SUBSTRB(B.COLUMN_NAME, 1, 25) AS COLUMN_NAME
FROM DBA_CONSTRAINTS A
, DBA_CONS_COLUMNS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.OWNER = 'E_LUCIS'
AND A.TABLE_NAME = UPPER('&테이블명')
ORDER BY 1, 2, 3;
1.9 테이블 사이즈 정보[편집]
1.9.1 테이블 용량 산정[편집]
데이터 용량 산정 방법: (컬럼 평균사이즈*로우스*(1 + 0.2(헤더값감안))) + 인덱스 사이즈
- 컬럼평균사이즈 = ROW 행에 대한 사이즈 평균 (LENGTHB 를 통해 구해도 됨)
- 인덱스별로 + 18바이트 (rowid) + a1.9.2 테이블의 크기 및 블록 보기[편집]
SELECT SUBSTR(SEGMENT_NAME, 1, 20), BYTES, BLOCKS
FROM USER_SEGMENTS
WHERE SEGMENT_NAME = UPPER('&테이블명');1.9.3 테이블 사이즈 조사[편집]
SELECT A.OWNER
, A.SEGMENT_NAME
, A.SEGMENT_TYPE
, ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB"
FROM DBA_SEGMENTS A
, DBA_TABLES B
WHERE A.SEGMENT_NAME = B.TABLE_NAME
AND A.SEGMENT_TYPE IN ('TABLE','TABLE PARTITION')
AND A.OWNER = B.OWNER
-- AND A.OWNER = '유저아이디'
AND EXISTS (SELECT 1
FROM MIG_TABLES C
WHERE C.TABLE_NAME = B.TABLE_NAME
AND C.OWNER = B.OWNER
AND C.SYSTEM_CODE = 'LC1'
AND C.USE_YN = 'Y'
)
GROUP BY A.OWNER
, A.SEGMENT_NAME
, A.SEGMENT_TYPE
;1.9.4 테이블별(인덱스+LOB+테이블) 사이즈[편집]
SELECT owner, table_name, TRUNC (SUM (bytes) / 1024 / 1024 / 1024) GB
FROM (SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION')
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN ('INDEX', 'INDEX PARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
---WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM (bytes) / 1024 / 1024 > 10
/* Ignore really small tables */
ORDER BY SUM (bytes) DESC1.9.5 테이블 사이즈 1기가 이상[편집]
SELECT segment_name, segment_type, SUM(bytes)/1024/1024/1024 GBytes
FROM dba_segments
WHERE OWNER IN ('OYSTDBA','OYHQDBA') -- schema owners
AND segment_type IN ('TABLE')
GROUP BY segment_name, segment_type
HAVING SUM(bytes)/1024/1024/1024 > 1;
2 컬럼[편집]
2.1 상호 DB간에 컬럼 이름 비교[편집]
/*
양쪽 DB에서 사용하는 테이블 중에서 컬럼 이름 다른 항목을 찾는다.
*/
SELECT A.TABLE_NAME
, A.COLUMN_NAME
, A.COLUMN_ID
, A.DATA_TYPE || '(' || A.DATA_LENGTH || ')' DATA_TYPE
FROM USER_TAB_COLUMNS@LINK_ESTDB A WHERE A.TABLE_NAME =:IN_TABLE_NAME AND NOT EXISTS
(SELECT 'X'
FROM USER_TAB_COLUMNS B
WHERE B.TABLE_NAME = A.TABLE_NAME
AND B.COLUMN_NAME = A.COLUMN_NAME);2.2 테이블 간의 비교[편집]
SELECT A.TABLE_NAME, A.COLUMN_NAME, A.COLUMN_ID
FROM USER_TAB_COLUMNS@LINK_ESTDB A
WHERE A.TABLE_NAME =:IN_TABLE_NAME
MINUS
SELECT A.TABLE_NAME, A.COLUMN_NAME, A.COLUMN_ID
FROM USER_TAB_COLUMNS A
WHERE A.TABLE_NAME =:IN_TABLE_NAME2.3 컬럼 추가[편집]
2.4 컬럼 변경[편집]
ALTER TABLE TB_EMP
RENAME 컬럼 TO 컬럼2;2.5 컬럼 삭제[편집]
ALTER TABLE TB_EMP
DROP COLUMN 컬럼명;2.6 PK 컬럼 추가/삭제[편집]
2.6.1 컬럼 PK추가[편집]
ALTER TABLE TB_EMP
ADD CONSTRAINT PK명 PRIMARY KEY (컬럼1,컬럼2,...);2.6.2 PK 삭제[편집]
ALTER TABLE TB_EMP
DROP CONSTRAINT PK명;2.6.3 PK 사용 DISABLE[편집]
-- 방법 1
ALTER TABLE TB_EMP
DISABLE PRIMMARY KEY;
-- 방법 2
ALTER TABLE TB_EMP
DISABLE CONSTRAINT PK명;2.6.4 PK 사용 ENABLE[편집]
-- 방법 1
ALTER TABLE TB_EMP
ENABLE PRIMMARY KEY;
-- 방법 2
ALTER TABLE TB_EMP
ENABLE CONSTRAINT PK명;2.6.5 PK RENAME[편집]
ALTER TABLE SCOTT.TB_EMP
RENAME CONSTRAINT PK_EMP TO PK_EMP_NEW ;3 파티셔닝 테이블[편집]
3.1 파티션 테이블 쿼리 추출 자동생성 스크립트[편집]
-- MIG_ADM.MIG_TABLES 은 사용자가 만든 임시테이블임
SELECT 'SELECT dbms_metadata.get_ddl(''TABLE'','''||A.TABLE_NAME||''','''||A.OWNER||''') DDL_QUERY from dual;'
FROM MIG_ADM.MIG_TABLES A
WHERE A.SYSTEM_CODE = 'LC1'
AND A.PARTITION_YN = 'Y';3.2 파티션 테이블 생성[편집]
CREATE TABLE TB_SALE (
SALE_DATE VARCHAR2(8 BYTE) NOT NULL ,
SALE_TIME VARCHAR2(6 BYTE) NOT NULL ,
CUST_NO VARCHAR2(10 BYTE) NOT NULL ,
SALE_AMT NUMBER ,
INPUT_DATE DATE
)
TABLESPACE TS_DATA
PARTITION BY RANGE (SALE_DATE)
(
PARTITION PR_TB_SALE_201801 VALUES LESS THAN ('20180201') ,
PARTITION PR_TB_SALE_201802 VALUES LESS THAN ('20180301') ,
PARTITION PR_TB_SALE_201803 VALUES LESS THAN ('20180401') ,
PARTITION PR_TB_SALE_201804 VALUES LESS THAN ('20180501') ,
PARTITION PR_TB_SALE_201805 VALUES LESS THAN ('20180601') ,
PARTITION PR_TB_SALE_201806 VALUES LESS THAN ('20180701') ,
PARTITION PR_TB_SALE_201807 VALUES LESS THAN ('20180801') ,
PARTITION PR_TB_SALE_201808 VALUES LESS THAN ('20180901') ,
PARTITION PR_TB_SALE_201809 VALUES LESS THAN ('20181001') ,
PARTITION PR_TB_SALE_201810 VALUES LESS THAN ('20181101') ,
PARTITION PR_TB_SALE_201811 VALUES LESS THAN ('20181201') ,
PARTITION PR_TB_SALE_201812 VALUES LESS THAN ('20190101') ,
PARTITION PR_TB_SALE_201901 VALUES LESS THAN ('20190201') ,
PARTITION PR_TB_SALE_201902 VALUES LESS THAN ('20190301') ,
PARTITION PR_TB_SALE_201903 VALUES LESS THAN ('20190401') ,
PARTITION PR_TB_SALE_201904 VALUES LESS THAN ('20190501') ,
PARTITION PR_TB_SALE_201905 VALUES LESS THAN ('20190601') ,
PARTITION PR_TB_SALE_201906 VALUES LESS THAN ('20190701') ,
PARTITION PR_TB_SALE_201907 VALUES LESS THAN ('20190801') ,
PARTITION PR_TB_SALE_201908 VALUES LESS THAN ('20190901') ,
PARTITION PR_TB_SALE_201909 VALUES LESS THAN ('20191001') ,
PARTITION PR_TB_SALE_201910 VALUES LESS THAN ('20191101') ,
PARTITION PR_TB_SALE_201911 VALUES LESS THAN ('20191201') ,
PARTITION PR_TB_SALE_201912 VALUES LESS THAN ('20200101')
-- PARTITION PR_TB_SALE_MAX VALUES LESS THAN (MAXVALUE)
)
;
-- PK 생성
CREATE UNIQUE INDEX PK_TB_SALE ON TB_SALE (SALE_DATE, SALE_TIME, CUST_NO)
TABLESPACE TS_INDEX LOCAL ;
ALTER TABLE TB_SALE
ADD CONSTRAINT PK_TB_SALE PRIMARY KEY (SALE_DATE, SALE_TIME, CUST_NO);
-- 코멘트
COMMENT ON COLUMN TB_SALE.SALE_DATE IS '매출일자';
COMMENT ON COLUMN TB_SALE.SALE_TIME IS '매출시간';
COMMENT ON COLUMN TB_SALE.CUST_NO IS '고객번호';
COMMENT ON COLUMN TB_SALE.SALE_AMT IS '매출 금액';
COMMENT ON TABLE TB_SALE IS '고객 매출내역';
파티션 구성 시 마지막에 MAXVALUE 를 주석으로 막은 이유는
분할해 둔 파티션의 마지막이 임박해오는 경우 SPLIT 보다 파티션 추가가 간편하기 때문
물론 예기치 않은 데이터가 들어오는 경우 MAXVALUE 를 지정해두는것이 안정적이긴 합니다.
3.3 파티션 키 컬럼 조회[편집]
SELECT *
FROM DBA_PART_KEY_COLUMNS
WHERE OWNER= 'SCOTT'
;3.4 파티션 추가[편집]
ALTER TABLE TB_SALE
ADD PARTITION PR_TB_SALE_202001 VALUES LESS THAN ('20200201' )
TABLESPACE TS_INDEX ;3.5 파티션 삭제[편집]
ALTER TABLE TB_SALE
DROP PARTITION PR_TB_SALE_202001 ;3.6 파티션 분할 (MAXVALUE 파티션 분할) =[편집]
ALTER TABLE TB_SALE
SPLIT PARTITION PR_TB_SALE_MAX AT ( '20200201' )
INTO ( PARTITION PR_TB_SALE_202001
, PARTITION PR_TB_SALE_MAX
) ;MAXVALUE 로 잡혀있던 PR_TB_SALE_MAX 파티션을 2020년 02월 01일 이전 데이터가 입력될 파티션으로 분리
3.6.1 파티션 TRUNCATE[편집]
ALTER TABLE TB_SALE
TRUNCATE PARTITION PR_TB_SALE_201801 ;3.6.2 파티션 테이블 인덱스 생성[편집]
CREATE UNIQUE INDEX PK_TB_SALE ON TB_SALE (SALE_DATE, SALE_TIME, CUST_NO)
TABLESPACE TS_INDEX LOCAL ;3.6.3 파티션 테이블을 생성할 때 INDEX 설정 시 주의 사항[편집]
3.6.4 RANGE 파티션을 가진 테이블을 월별로 파티션을 분리한다고 했을 때[편집]
- 테이블 전체에 INDEX 를 구성하는 것 보다 각각의 파티션 별로 INDEX 를 가지고 있어야 대용량의 테이블을 조회할 때 더 효과적
3.6.5 INDEX 지정 시 LOCAL INDEX 가 아닌 default 나 GLOBAL INDEX 로 설정할 경우[편집]
- 파티션기준이 아닌 전체 테이블을 기준으로 INDEX 가 만들어지니 주의
- 파티션 테이블이 GLOBAL INDEX 로 생성될 경우 테이블의 중간중간 데이터가 삭제되거나 변경된다면
전체 테이블 기준으로 최적화된 INDEX 가 뒤죽박죽 될 수 있으나 LOCAL 로 생성되는 경우에는 다른 파티션의 INDEX 에는 영향이 가지 않으니 INDEX 를 사용하는 SQL 인 경우 더 빠른 실행계획을 세울 수 있게 됩니다.
- 파티션 변경(추가/삭제) 시 GLOBAL INDEX가 IU(Index Unusable) 로 변경되면서 인덱스를 사용할수 없게됨.
3.6.6 파티션 테이블의 파티션 범위 보기[편집]
SELECT TABLE_NAME
, SUBSTRB(PARTITION_NAME, 1, 30) AS PARTITION_NAME -- 파티셔닝 명
, SUBSTRB(TABLESPACE_NAME, 1, 30) AS TABLESPACE_NAME -- 테이블스페이스명
, HIGH_VALUE
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = UPPER('&테이블명');