오라클 데이터베이스 관리자 기초- (13)인덱스 관리

* 인덱스 분류

논리적

- 단일 열 또는 연결된 인덱스

- 고유 또는 비고유 인덱스

- 함수 기반 인덱스

- 도메인 인덱스

 

물리적

- 분할된 또는 분할되지 않은 인덱스

- B트리(일반 또는 역방향 키)

- 비트맵

 

B-Tree Index

- 영어로 분기는 branch, 최하위 행은 leaf 노드.

- 데이터는 leaf 노드에만 저장.

- 트리를 생성할때 리프노드부터 rowid를 입력하며 작성. 

- 블록내에서는 Fragmentation 현상을 방지하기 위한 compress가 발생하지만 index에서는 되지 않는다.

 

B-tree의 생성 과정

- gno의 14를 지우고 14.5의 데이터가 들어오면 인덱스의 13-15-14.5로 리프 노드에 저장

- 여기서 13.5라는 row id가 들어오면 블록 하나를 새로 가져와서 13과 13.5가 들어있는 블록과 14.5와 15가 들어있는 블록으로 재구성.

- 리프 노드에 블록이 하나 들어오면 branch의 내용도 수정해야하고 만약 branch의 용량도 초과해서 추가적으로 블록을 가져오다가 보면 루트를 쪼개야 하는 상황도 나올 수 있다.

-이러한 특징으로 인해 B-tree는 상방향으로 커진다.

 

비트맵 인덱스

- 비트맵 인덱스는 cardinality(기수)가 낮은 컬럼에만 생성.

- 전체적으로는 트리 구조지만 리프노드에 rowid 대신 각 키 값에 대한 비트맵을 저장.

- 비트맵에 행의 위치 정보가 저장되어 있다.

- or연산이 가능하다. 

- transaction이 딱 1개만 발생한다.

 

* B트리 인덱스와 비트맵 인덱스 비교

인덱스 공간 할당 및 할당 해제

- 인덱스의 공간 할당을 강제로 설정.

- 할당할때는 용량을 적을 수 있고 해제할때는 unused 옵션을 사용해서 회수. 그러나 공간 할당을 강제로 준 경우에만 회수가 가능.

 

인덱스 및 유효성 검사

- 인덱스 행의 몇개나 지워질지 알아볼때 사용.

 

사용되지 않은 인덱스 식별

- 설정해준 단위기간만큼 index를 사용했는지 모니터링

- 사용한 횟수가 나오는 것은 아니고 사용 여부를 출력

- 중지하려면 NOMONITORING을 사용

 

[실습]table 및 index의 물리적인 통계 정보 조회

<사용 명령어> 
SELECT table_name, num_rows, chain_cnt, blocks,
empty_blocks as empty, avg_space, avg_row_len
FROM dba_tables
WHERE OWNER = <user명>;
- Table의 물리적인 통계정보를 조회.
- NUM_ROWS : Row의 개수
- CHAIN_CNT : Chaining 된 row의 수.(migration 포함)
- BLOCKS : 사용 중인 block의 수.(High water mark 안쪽 block의 개수)
- EMPTY_BLOCKS : 할당된 block중에 빈 block 수.(High water mark 바깥쪽 block의 개수)
- AVG_SPACE : Block당 평균적인 사용정도
- AVG_ROW_LEN : 저장된 row의 평균 길이

 

SELECT table_name, index_name, status, num_rows, leaf_blocks, blevel
FROM dba_indexes
WHERE OWNER = <user명>;
- Index의 물리적인 통계정보를 조회.
- STATUS : index의 상태 [VALID / UNUSABLE]
- NUM_ROWS : 저장된 row의 수
- LEAF_BLOCKS : Leaf block의 수
- BLEVEL : Tree의 깊이. block이 하나면 0이라고 나온다.

 
ANALYZE TABLE <table명> COMPUTE STATISTICS;
- Table에 대한 통계정보를 갱신.
- Table에 포함된 index의 통계정보도 갱신.

 

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(<user명>);

- 지정한 스키마의 모든 segment에 대한 통계정보를 갱신.

 

EXEC DBMS_STATS.GATHER_TABLE_STATS(<user명>,<table명>);

- Table에 대한 통계정보를 갱신.

- 실행계획이 있는 table만 갱신

ANALYZE INDEX VALIDATE STRUCTURE;

- Index_stats에 지정된 index의 통계정보를 갱신.

SELECT NAME, BLOCKS, LF_ROWS, DEL_LF_ROWS FROM INDEX_STATS;
- ANALYZE INDEX명령으로 통계정보가 갱신된 index에 대한 상세한 통계정보를 조회.

- BLOCKS : 사용중인 block의 수

- LF_ROWS : Leaf block에 저장된 row의 수

- DEL_LF_ROWS : 지워진 row의 수. 즉, 공간을 사용하고 있지만 내가 사용하지는 못 함.

 

sqlplus / as sysdba

SELECT owner, table_name, tablespace_name
FROM dba_tables
WHERE OWNER = 'ST';

// st 스키마에 저장된 table과 저장된 tablespace를 관리자로 접속해서 조회. 모든 테이블이 usr 테이블 스페이스에 저장되어 있음을 확인

 

SELECT table_name, num_rows, chain_cnt, blocks, empty_blocks as empty, avg_space, avg_row_len
FROM dba_tables
WHERE owner = 'ST' AND table_name IN ('STUDENT','PROFESSOR','COURSE','SCORE');

// 통계정보가 갱신되지 않았기 때문에 아무 것도 나오지 않는다.

 

SELECT table_name, index_name, status, num_rows, leaf_blocks, blevel
FROM dba_indexes
WHERE owner = 'ST' AND table_name IN ('STUDENT','PROFESSOR','COURSE','SCORE');

// Index의 물리적인 통계정보를 조회. ST_SNAME을 제외하고는 통계정보가 갱신되지 않았기 때문에 아무 것도 출력 안 됨.

EXEC DBMS_STATS.GATHER_TABLE_STATS('ST', 'STUDENT');

// st.student에 대한 통계정보를 갱신. st는 사용자, student는 테이블명.

SELECT table_name, num_rows, chain_cnt, blocks, empty_blocks as empty, avg_space, avg_row_len 
FROM dba_tables 
WHERE owner = 'ST' AND table_name IN ('STUDENT','PROFESSOR','COURSE','SCORE');

// student는 갱신됐기때문에 출력 된다.

 

SELECT table_name, index_name, status, num_rows, leaf_blocks, blevel 
FROM dba_indexes 
WHERE owner = 'ST' AND table_name IN ('STUDENT','PROFESSOR','COURSE','SCORE');

//table에 대한 통계정보 갱신하면 관련 index의 통계정보도 같이 갱신. student는 조회 된다. 리프 블록이 하나니까 blevel이 0이다. 

ANALYZE TABLE st.student COMPUTE STATISTICS;

ANALYZE TABLE st.professor COMPUTE STATISTICS;

ANALYZE TABLE st.course COMPUTE STATISTICS;

ANALYZE TABLE st.score COMPUTE STATISTICS;

// analyze 명령으로 dbms_stats 패키지로 갱신되지 않는 통계정보를 갱신.

 

SELECT table_name, num_rows, chain_cnt, blocks, empty_blocks as empty, avg_space, avg_row_len
FROM dba_tables
WHERE owner = 'ST' AND table_name IN ('STUDENT','PROFESSOR','COURSE','SCORE');

// chain_cnt, empty_blocks등 모든 물리적인 통계정보가 갱신

SELECT table_name, index_name, status, num_rows, leaf_blocks, blevel
FROM dba_indexes
WHERE owner = 'ST' AND table_name IN ('STUDENT','PROFESSOR','COURSE','SCORE');

// 인덱스의 모든 통계 정보도 조회 가능하다.

SELECT name, blocks, lf_rows, del_lf_rows FROM index_stats;
// index_stats는 index에 대한 별도의 ANALYZE INDEX 명령을 수행한 index에 대한 정보만 저장.

 

ANALYZE INDEX st.st_sname VALIDATE STRUCTURE;

// 인덱스의 통계정보 갱신 

 

SELECT name, blocks, lf_rows, del_lf_rows FROM index_stats;

// 마지막으로 ANALYZE INDEX를 수행한 index에 대한 정보만 저장된다.

 

[실습]table move와 index rebuild

<사용 명령어>

ALTER TABLE <table명> MOVE [TABLESPACE <tablespace명>];
- Table을 지정한 tablespace로 이동한다.
- Tablespace를 지정하지 않으면 현재 저장된 tablespace에 재구성한다.

 

ALTER INDEX <index명> REBUILD [TABLESPACE <tablespace명>];
- Index를 지정한 tablespace로 이동한다.
- Tablespace를 지정하지 않으면 현재 저장된 tablespace에 재구성한다.

 

SELECT D.TN, ROUND(TOTAL/1000000) "TOTAL(MB)",
ROUND(FREE/1000000) "FREE(MB)",
TO_CHAR(ROUND(FREE/TOTAL*100,1),'99.9')||'%' RATE
FROM (SELECT TABLESPACE_NAME TN, SUM(BYTES) TOTAL FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME TN, SUM(BYTES) FREE FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TN=F.TN;
- 각 tablespace에 남아있는 공간을 MB단위와 백분율로 검색.

SELECT table_name, num_rows, chain_cnt
FROM dba_tables 
WHERE owner = 'ST' and table_name = 'STUDENT';

// table의 chaining row수를 확인한다. 만일 chaining row의 수가 크다면 move를 고려한다.

 

SELECT owner, table_name, tablespace_name
FROM dba_tables
WHERE owner = 'ST' AND table_name IN ('STUDENT','PROFESSOR','COURSE','SCORE');
// 각 table이 저장된 tablespace를 확인. 전부 usr인 것을 확인

SELECT tablespace_name, bytes, file_name FROM dba_data_files;

SELECT D.TN, ROUND(TOTAL/1000000) "TOTAL(MB)", 
ROUND(FREE/1000000) "FREE(MB)", 
TO_CHAR(ROUND(FREE/TOTAL*100,1),'99.9')||'%' RATE 
FROM (SELECT TABLESPACE_NAME TN, SUM(BYTES) TOTAL FROM DBA_DATA_FILES 
GROUP BY TABLESPACE_NAME) D, 
(SELECT TABLESPACE_NAME TN, SUM(BYTES) FREE FROM DBA_FREE_SPACE 
GROUP BY TABLESPACE_NAME) F 
WHERE D.TN=F.TN; 

// 각 테이블 스페이스에 사용 가능한 공간 조회

ALTER USER st
QUOTA UNLIMITED ON INDX;

// INDX 테이블 space에 대해 quota를 설정

 

ALTER TABLE st.student MOVE
TABLESPACE INDX;

// student table을 indx tablespace로 이동.

 

SELECT owner, table_name, tablespace_name
FROM dba_tables
WHERE owner = 'ST' AND table_name IN ('STUDENT','PROFESSOR','COURSE','SCORE');

// 옮겨진 테이블 확인

ANALYZE TABLE st.student COMPUTE STATISTICS;

// table 과 table에 포함된 index의 통계정보도 갱신하기 위해 analyze 명령을 사용했으나 실패

 

SELECT table_name, index_name, status
FROM dba_indexes
WHERE owner = 'ST' and TABLE_NAME = 'STUDENT';

// student table의 index 상태를 확인.

모두 unusable 상태로 인덱스가 깨져있음을 확인했고 이로 인해 analyze 명령이 실패 했음을 확인

 

ALTER INDEX st.student_sno_pk REBUILD;

ALTER INDEX st.st_sname REBUILD;

// index를 현재 저장된 tablespace에 재구성

 

SELECT table_name, index_name, status
FROM dba_indexes
WHERE owner = 'ST' and TABLE_NAME = 'STUDENT';

// 인덱스의 상태가 valid로 사용 가능함을 확인

ANALYZE TABLE st.student COMPUTE STATISTICS;

// 통계 정보 갱신

 

SELECT table_name, num_rows, chain_cnt 
FROM dba_tables 
WHERE OWNER = 'ST' and TABLE_NAME = 'STUDENT';

// table을 move하면 chaning row는 없어지지만 migration된 row는 해소되지 못한다.

 

[실습]Segment에 대한 공간 할당과 해제

<사용 명령어>

SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS
FROM DBA_EXTENTS;
- Table에 할당된 extent에 대한 정보를 조회.
- EXTENT_ID : Table에 할당된 Extent의 식별자
- BYTES : Extent의 크기
- BLOCKS : Extnet를 구성한 block의 개수

 

ALTER TABLE <table명> ALLOCATE EXTENT [( <크기> [DATAFILE '데이터 파일명'])]
- Table에 extent를 추가.
- DATAFILE : extent를 추가할 data file명

 

ALTER TABLE <table명> DEALLOCATE UNUSED;
- H/M(High water mark) 이후 extent를 모두 할당 해제.
- storage절의 minextents에 해당하는 extent는 해제하지 못한다.

 

ALTER INDEX <index명> ALLOCATE EXTENT [( <크기> [DATAFILE '데이터 파일명'])]
- Index에 extnet를 추가.

ALTER INDEX <index명> DEALLOCATE UNUSED;
- H/M(High water mark) 이후 extent를 모두 할당 해제.
- storage절의 minextents에 해당하는 extent는 해제하지 못한다.

ANALYZE TABLE st.score COMPUTE STATISTICS;

// 통계 정보 갱신

 

SELECT table_name, num_rows, chain_cnt, blocks, empty_blocks as empty, avg_space, avg_row_len
FROM dba_tables
WHERE owner = 'ST' and table_name = 'SCORE';

// blocks는 하이워터마크 안, empty_blocks as empty는 하이워터마크 밖

SELECT owner, segment_name, tablespace_name, extent_id, bytes, blocks
FROM dba_extents
WHERE owner = 'ST' and segment_name='SCORE';

// table에 8개 block으로 구성된 extent가 두 개 할당됨.

 

SELECT table_name, index_name, status, num_rows, leaf_blocks, blevel
FROM dba_indexes 
WHERE OWNER = 'ST' and TABLE_NAME = 'SCORE';

// score 테이블의 인덱스 정보 조회

ANALYZE INDEX st.score_sno_cno_pk VALIDATE STRUCTURE;

 

SELECT name, blocks, lf_rows, del_lf_rows FROM index_stats;

 

ALTER TABLE st.score ALLOCATE EXTENT (SIZE 512K);

// score table에 extent를 강제로 추가.

 

ALTER INDEX st.score_sno_cno_pk ALLOCATE EXTENT (SIZE 64K);

//  index에 extent를 강제로 추가.

 

ANALYZE TABLE st.score COMPUTE STATISTICS;

ANALYZE INDEX st.score_sno_cno_pk VALIDATE STRUCTURE;

// 통계 정보 갱신

 

SELECT table_name, num_rows, chain_cnt, blocks, empty_blocks as empty, avg_space, avg_row_len
FROM dba_tables
WHERE OWNER = 'ST' and TABLE_NAME = 'SCORE';

//  할당된 공간은 H/M 이후에 추가되서 3개에서 67개로 64개가 추가 된 걸 확인 할 수 있다. 

SELECT owner, segment_name, tablespace_name, extent_id, bytes, blocks
FROM dba_extents
WHERE owner = 'ST' and segment_name='SCORE';

// 할당된 extent의 구성을 확인. extent_id 2번부터 9번까지 전부 하이워터 마크 밖에 있는 새로운 extent가 조회됨.

 

SELECT name, blocks, lf_rows, del_lf_rows FROM index_stats;

// 16개에서 24개로 증가. 

 

SELECT table_name, index_name, status, num_rows, leaf_blocks, blevel
FROM dba_indexes
WHERE OWNER = 'ST' and TABLE_NAME = 'SCORE';

// score 테이블의 인덱스 정보 조회

ALTER TABLE st.score ALLOCATE EXTENT (SIZE 1M);

// score table에 extent를 강제로 추가.

 

SELECT owner, segment_name, tablespace_name, extent_id, bytes, blocks
FROM dba_extents
WHERE OWNER = 'ST' and SEGMENT_NAME='SCORE';

// extent_id 10번부터 새롭게 Table에 할당된 extent가 생성된 것을 확인 

ALTER TABLE st.score DEALLOCATE UNUSED;

ALTER INDEX st.score_sno_cno_pk DEALLOCATE UNUSED;

// 사용하지 않는 공간 할당 해제

SELECT table_name, num_rows, chain_cnt, blocks, empty_blocks as empty, avg_space, avg_row_len 
FROM dba_tables 
WHERE OWNER = 'ST' and TABLE_NAME = 'SCORE';

// 통계 정보 갱신을 하지 않으면 할당 해제 했음에도 존재한다고 출력

ANALYZE TABLE st.score COMPUTE STATISTICS;

ANALYZE INDEX st.score_sno_cno_pk VALIDATE STRUCTURE;

 

SELECT table_name, num_rows, chain_cnt, blocks, empty_blocks as empty, avg_space, avg_row_len
FROM dba_tables
WHERE OWNER = 'ST' and TABLE_NAME = 'SCORE';

SELECT table_name, index_name, status, num_rows, leaf_blocks, blevel
FROM dba_indexes
WHERE OWNER = 'ST' and TABLE_NAME = 'SCORE';

// 인덱스 정보 조회

 

SELECT name, blocks, lf_rows, del_lf_rows FROM index_stats;

// blocks가 24개에서 16개로 변경된 것을 확인

 

[실습]List partition table 생성과 관리

<사용 명령어>

SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME
FROM DBA_TAB_PARTITIONS;
- Table의 partition을 조회.

- HIGH_VALUE : 파티션을 나누는 기준. list Partition에서는 일치하는 값, range partition에서는 상한 값.

 

SELECT OWNER, NAME, COLUMN_NAME
FROM DBA_PART_KEY_COLUMNS;
- Partition의 기준의 되는 column을 조회.

- DBA_PART_KEY_COLUMNS : 파티션을 나눌 때 기준이 되는 컬럼.

 

CREATE TABLE <table명> (
..........
)
PARTITION BY LIST (<column명>)
(
PARTITION <partition명> VALUES (<column값>) [ TABLESPACE <tablespace명> ],
PARTITION <partition명> VALUES (<column값>) [ TABLESPACE <tablespace명> ],
......
);
- Partition으로 구현된 table을 생성.

- LIST 절에 column명은 여러 개 지정할 수 없다.(다중 컬럼 파티션은 되지 않음) 

- 파티션

 

ALTER TABLE <table명>
ADD PARTITION <partition명> VALUES (<column값>) [ TABLESPACE <tablespace명> ];
- table에 partition을 추가.

 

ALTER TABLE <table명>
DROP PARTITION <partition명>;
- partition을 삭제.

- partition에 들어있던 행도 자동으로 삭제

- alter는 rollback의 개념이 없기 때문에 바로 삭제됨

SELECT tablespace_name, bytes, file_name FROM dba_data_files;

// 테이블 스페이스 확인

CREATE TABLESPACE t1 
DATAFILE '/app/ora12c/oradata/disk2/t101.dbf' SIZE 20M;

 

CREATE TABLESPACE t2
DATAFILE '/app/ora12c/oradata/disk2/t201.dbf' SIZE 20M;

 

CREATE TABLESPACE t3
DATAFILE '/app/ora12c/oradata/disk2/t301.dbf' SIZE 20M;

// partition을 저장할 3개의 tablespace를 생성. 

 

SELECT tablespace_name, status, contents, extent_management, segment_space_management
FROM dba_tablespaces
WHERE tablespace_name LIKE 'T_' 
ORDER BY 1;

// t로 시작하는 tablespace를 검색해서 새로 생성한 tablespace를 검색. 

 

SELECT tablespace_name,file_name FROM dba_data_files
WHERE tablespace_name LIKE 'T_'
ORDER BY 1;

ALTER USER st
QUOTA UNLIMITED ON t1
QUOTA UNLIMITED ON t2
QUOTA UNLIMITED ON t3;

// quota 설정

 

DESC st.student;

CREATE TABLE st.st(
sno VARCHAR2(8), 
sname VARCHAR2(10), 
sex VARCHAR2(4), 
syear NUMBER(1), 
major VARCHAR2(10), 
avr NUMBER(4,2), 
constraint st_sno_pk primary key(sno) 
using index tablespace indx 
) 
partition by list (major) 
( 
partition ma_ch VALUES ('화학') tablespace t1, 
partition ma_ph VALUES ('물리') tablespace t2, 
partition ma_all VALUES ('생물','식영','유공') tablespace t3
); 

 

// Major column 값에 따라 입력 위치가 다른 partition table을 생성. 

// major column값을 기준으로 partition을 구분.

// major column값이 지정된 화학, 물리, 생물, 식영, 유공인 row만 입력 가능. 마치 제약조건처럼 발동 

INSERT INTO st.st
SELECT * FROM st.student;

// 테이블 구조가 같으면 그대로 입력 됨.

 

SELECT * FROM st.st;

// 데이터 출력  

SELECT owner, table_name, partitioned FROM dba_tables
WHERE OWNER = 'ST';

// st partition table 확인. PAR가 yes면 파티션된 테이블 

 

SELECT owner, name, column_name
FROM dba_part_key_columns
WHERE owner = 'ST' and name = 'ST';

// partition 구분의 기준이 되는 column 조회. column_name으로 파티션이 구분되어 있음 

SELECT table_owner, table_name, partition_name, high_value, tablespace_name
FROM dba_tab_partitions
WHERE table_owner = 'ST' and table_name = 'ST';

// st table의 partition 구조 조회.

 

SELECT owner, segment_name, partition_name, tablespace_name, extent_id, bytes, blocks
FROM dba_extents
WHERE OWNER = 'ST' and segment_name = 'ST';

// 각 partition의 extent 할당 내역을 조회.

ALTER TABLE st.st
ADD PARTITION ma_so VALUES ('사회') TABLESPACE t1;

// major가 사회인 파티션 추가. 이 이후부터 사회 입력 가능.

 

ALTER TABLE st.st
DROP PARTITION ma_ch;

// Partition이 삭제되면 저장된 row도 모두 삭제.

SELECT table_owner, table_name, partition_name, high_value, tablespace_name
FROM dba_tab_partitions
WHERE table_owner = 'ST';

// 사회는 추가되고 화학은 삭제 됨. 화학과 데이터는 전부 사라짐.

SELECT owner, segment_name, partition_name, tablespace_name, extent_id, bytes, blocks
FROM dba_extents
WHERE OWNER = 'ST' and segment_name = 'ST';

// ma_so는 파티션은 만들어 졌지만 segment가 들어가 있지 않기 때문에 공간이 할당되지 않음.

 

ALTER INDEX st.st_sno_pk REBUILD;

 

INSERT INTO st.st (sno, major) VALUES ('11111111','사회');

COMMIT;

 

SELECT owner, segment_name, partition_name, tablespace_name, extent_id, bytes, blocks
FROM dba_extents 
WHERE OWNER = 'ST' and segment_name = 'ST';

// 첫 행이 입력되서 공간이 할당되서 출력 되는 것을 확인.

 

[실습]Range partition table 생성과 관리

<사용 명령어>

CREATE TABLE <table명> (
..........
)
PARTITION BY RANGE (<column명>)
(
PARTITION <partition명> VALUES LESS THAN (<상한값>) [ TABLESPACE  ],
PARTITION <partition명> VALUES LESS THAN (<상한값>) [ TABLESPACE  ],
......
);
- range partition으로 구현된 table을 생성.
- VALUES LESS THEN : 상한 값을 정의.(미만 처럼 범위에 포함되지 않음.)

 

ALTER TABLE <table명>
ADD PARTITION <partition명> VALUES LESS THAN (<상한값>) [ TABLESPACE <tablespace명> ];
- table에 range partition을 추가.

CREATE TABLE st.sale ( 
no NUMBER, 
goods VARCHAR2(20), 
price NUMBER, 
s_date DATE 
) 
PARTITION BY RANGE(price)
( 
PARTITION p100 VALUES LESS THAN (100),
PARTITION p200 VALUES LESS THAN (200),
PARTITION p300 VALUES LESS THAN (300), 
PARTITION pr_max VALUES LESS THAN ( MAXVALUE )
);

// price column을 기준으로 partition.

// p100 은100 미만인 값, p200은 100 이상 200 미만, p300은 200이상 300미만, pr_max는 300이상인 모든 row 저장. 

SELECT owner, name, column_name
FROM dba_part_key_columns
WHERE owner = 'ST' and name = 'SALE';

// st.sale의 partition의 기준의 되는 column을 조회. 

 

SELECT table_owner, table_name, partition_name, high_value, tablespace_name
FROM dba_tab_partitions
WHERE table_OWNER = 'ST' and table_name = 'SALE';

// MAXVALUE가 정의됐기 때문에 모든 범위의 값이 정의되서 빈공간이 없음.

// range 분할에서 값의 범위는 중복 불가

 

SELECT owner, segment_name, partition_name, tablespace_name, extent_id, bytes, blocks
FROM dba_extents
WHERE owner = 'ST' and segment_name = 'SALE';

// 행이 입력되지 않았기 때문에 공간 할당이 되지 않음;

ALTER TABLE st.sale
ADD PARTITION p400 VALUES LESS THAN (400);
// MAXVALUE값으로 인해 지정된 범위값이 중복되서 생성 불가.

 

ALTER TABLE st.sale
DROP PARTITION pr_max;

// p400을 추가하고자 범위가 중복되는 pr_max 파티션을 삭제.

 

ALTER TABLE st.sale 
ADD PARTITION p400 VALUES LESS THAN (400);
// pr_max가 삭제됐기 때문에 문제없이 생성 됨.

 

ALTER TABLE st.sale
ADD PARTITION pr_max VALUES LESS THAN (MAXVALUE);

// p400이 생성됐기 때문에 pr_max는 400이상인 모든 row 저장

// 변경된 MAXVALUE 확인.

DROP TABLE st.sale;

// 테이블 삭제

CREATE TABLE st.sale (
no NUMBER,
goods VARCHAR2(20),
price NUMBER,
s_date DATE
)
PARTITION BY RANGE(s_date)
(
PARTITION s1999 VALUES LESS THAN ( TO_DATE('2000-01-01','YYYY-MM-DD' ) ),
PARTITION s2000_01_03 VALUES LESS THAN ( TO_DATE('2000-04-01','YYYY-MM-DD' ) ),
PARTITION s2000_04_06 VALUES LESS THAN ( TO_DATE('2000-07-01','YYYY-MM-DD' ) ),
PARTITION s2000_07_09 VALUES LESS THAN ( TO_DATE('2000-10-01','YYYY-MM-DD' ) ),
PARTITION s2000_10 VALUES less than ( MAXVALUE )
);

// 분할 기준에 날짜를 이용할 수 있다. 실제로는 대부분 날짜를 이용해서 분할

// s1999는 2000년 1월 1일 이전의 값

// s2000_01_03은 2000년 4월 1일 이전의 값

SELECT owner, name, column_name 
FROM dba_part_key_columns 
WHERE owner = 'ST' and name = 'SALE';

// st.sale의 partition의 기준의 되는 column을 조회. 

SELECT table_owner, partition_name, high_value, tablespace_name
FROM dba_tab_partitions
WHERE table_OWNER = 'ST' and table_name = 'SALE';