### 테이블 관리
DBA_TABLES
DBA_OBJECTS
테이블 종류
1) 일반테이블
2) 분할테이블 ( PARTITION )
3) 인덱스 구성 테이블
4) 클러스터화된 테이블
oracle 내장 데이터 유형
char , nchar, varchar2, nvarchar2
CLOB = 대형 고정 너비 문자 데이터 저장 ( ex : 게시판 )
NCLOB = 대형 고정 너비 국가별 문자 집합 데이터 저장
BLOB = 구조화되지 않은 데이터를 저장 ( 사진, 영상 등... )
BFILE = 구조화되지 않은 데이터를 운영 체제 파일에 저장
create table data_test(
ch char(5),
nch nchar(5),
vchar varchar2(5),
nvchar nvarchar2(5))
;
insert into data_test values('abc','abc','abc','abc');
SQL> select vsize(ch), vsize(nch), vsize(vchar), vsize(NVCHAR) from data_test;
VSIZE(CH) VSIZE(NCH) VSIZE(VCHAR) VSIZE(NVCHAR)
---------- ---------- ------------ -------------
5 10 3 6
SQL> select * from nls_database_parameters where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
PARAMETER VALUE
------------------------- ------------------------------
NLS_CHARACTERSET KO16MSWIN949
NLS_NCHAR_CHARACTERSET AL16UTF16
#. ROWID
- 테이블 내 행의 위치를 지정하는 논리적인 주소 값
- 데이타베이스 전체에서 중복되지 않는 유일한 값
- SELECT 명령문으로 조회 가능, 하지만 INSERT, UPDATE와 같은 임의적 변경 불가능
AAASN1 AAH AAAASl AAA
1 2 3 4
1) 6자리 : 데이터 오브젝트 번호
2) 3자리 : 상대적 파일 번호
3) 6자리 : 블록 번호
4) 3자리 : 블록내의 행 번호
=> 서브쿼리 작업
-> 행을 찾아가는 가장 빠른 작업 ( 인덱스 관련 ) ( 인덱스도 rowid 를 가지고 있으므로 )
ex
update system.hash_pt
set name='P'
where rowid in (select rowid from system.hash_pt where name like 'sc%');
#. ROWNUM
- 쿼리에 의해 추출된 각 행의 부여 일련번호, 쿼리 추출 값을 1부터 시작하는 순차 값으로 할당
=> 데이터 row 가 많을 경우 rownum 로 짤라서 봄 = mysql 의 limit 과 같음
SQL> select rownum, NO, rowid, YEAR, MONTH, DAY, NAME, PRICE from system.HASH_PT;
ROWNUM NO ROWID YEAR MO DA NAME PRICE
---------- ---------- ------------------ ---- -- -- -------- ----------
1 6 AAASN1AAHAAAASlAAA 2015 10 23 pbj 5000
2 2 AAASN3AALAAAACGAAA 2011 4 22 jone 4300
3 5 AAASN3AALAAAACGAAB 2014 8 18 tiger 3200
4 1 AAASN4AAMAAAACGAAA 2010 3 12 scott 2500
5 3 AAASN4AAMAAAACGAAB 2012 5 30 miller 2100
6 4 AAASN4AAMAAAACGAAC 2013 6 6 lion 1500
6 rows selected.
SQL> select rownum,
2 NO,
3 rowid,
4 YEAR,
5 MONTH,
6 DAY,
7 NAME,
8 PRICE
9 from system.HASH_PT
10 order by NO ;
ROWNUM NO ROWID YEAR MO DA NAME PRICE
---------- ---------- ------------------ ---- -- -- -------- ----------
4 1 AAASN4AAMAAAACGAAA 2010 3 12 scott 2500
2 2 AAASN3AALAAAACGAAA 2011 4 22 jone 4300
5 3 AAASN4AAMAAAACGAAB 2012 5 30 miller 2100
6 4 AAASN4AAMAAAACGAAC 2013 6 6 lion 1500
3 5 AAASN3AALAAAACGAAB 2014 8 18 tiger 3200
1 6 AAASN1AAHAAAASlAAA 2015 10 23 pbj 5000
6 rows selected.
##################### ROWNUM 값 주목
ALTER TABLE hr.employees
DROP COLUMN comments
CASCADE CONSTRAINTS CHECKPOINT 1000; << 여기서 말하는 checkpoint 는
1000 건씩 지우는 것을 말함
즉 1000건 drop 후 commit , 1000 건 drop 후 커밋
# Temp Table의 특징
목적
: 큰 정렬 작업이나, 필요할 경우 임의적으로 생성 하여 사용 할 수 있다.
- ON COMMIT DELETE ROWS는 트랜잭션 내에서만 행을 볼 수 있도록 지정 (기본값)
- ON COMMIT PRESERVE ROWS는 전체 세션에서 행을 볼 수 있도록 지정
--[PRESERVE ROWS] 트랜잭션의 종료 (commit)후에도 남아있음 세션종료시 데이터가 삭제됨
--[DELETE ROWS] 트랜잭션의 종료와 함께 삭제됨
--[SUB QUERY CLAUSE] SELECT된 테이블의 구조와 같이 테이블 생성
## on commit preserve rows - 옵션
새롭게 연결된 세션에서 검색 x
commit 해도 데이터 삭제되지 않음 -> 세션 종료시 삭제됨
create global temporary table aa_temp(
a varchar(15) not null,
b number not null
) on commit preserve rows
insert into aa_temp values('a','1');
insert into aa_temp values('b','2');
insert into aa_temp values('c','3');
insert into aa_temp values('d','4');
insert into aa_temp values('e','5');
insert into aa_temp values('f','6');
SQL> select * from aa_temp;
A B
--------------- ----------
a 1
b 2
c 3
d 4
e 5
f 6
6 rows selected.
SQL>
SQL> commit;
Commit complete.
SQL> select * from aa_temp;
A B
--------------- ----------
a 1
b 2
c 3
d 4
e 5
f 6
SQL> exit
[oracle@DBTEST /]$
[oracle@DBTEST /]$
[oracle@DBTEST /]$ ss
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 14 17:06:50 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL>
SQL> select * from aa_temp;
no rows selected
## ON COMMIT DELETE ROWS 테스트
create global temporary table bb_temp(
a varchar(15) not null,
b number not null
) on commit delete rows
insert into bb_temp values('a','1');
insert into bb_temp values('b','2');
insert into bb_temp values('c','3');
insert into bb_temp values('d','4');
insert into bb_temp values('e','5');
insert into bb_temp values('f','6');
SQL>
SQL> select * from bb_temp;
A B
--------------- ----------
a 1
b 2
c 3
d 4
e 5
f 6
6 rows selected.
SQL> commit;
Commit complete.
SQL> select * from bb_temp;
no rows selected
#### table move 시키기
SQL> select username, DEFAULT_TABLESPACE from dba_users
where username IN ( 'USER_1','EMI');
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYSTEM SYSTEM
USER_1 DATA01
SQL> SELECT * FROM SYSTEM.HASH_PT;
NO YEAR MO DA NAME PRICE
---------- ---- -- -- ------------------------------ ----------
6 2015 10 23 pbj 5000
2 2011 4 22 jone 4300
5 2014 8 18 tiger 3200
1 2010 3 12 P 2500
3 2012 5 30 miller 2100
4 2013 6 6 lion 1500
6 rows selected.
SQL> alter table system.hash_pt
move tablespace DATA01;
alter table system.hash_pt
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object
HASH_PT 테이블이 파티션이라 이동이 안됨
=> ALTER TABLE HASH_PT MOVE PARTITION SYS_P27 TABLESPACE DATA01
SQL> SELECT * FROM HASH_PT PARTITION (SYS_P27);
NO YEAR MO DA NAME PRICE
---------- ---- -- -- ------------------------------ ----------
2 2011 4 22 jone 4300
5 2014 8 18 tiger 3200
SQL>
SQL>
** 일반 테이블 MOVE
SQL> SELECT * FROM EMI.CUSTOMERS1;
ID
----------
1
1
SQL> select username, DEFAULT_TABLESPACE from dba_users
2 where username IN ( 'PBJ','EMI');
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
EMI DATA01
PBJ PBJ_DAT
alter table emi.customers1
move tablespace PBJ_DAT;
#unused
- 실제로 테이블의 한 칼럼을 사용하지 못하게 함
- 활성화가 불가능
=> 사용 예
- 데이터가 많은 테이블의 한 칼럼을 drop 시 너무 오래걸림
=> 칼럼을 비활성화 하고 널널한 시간에 실제로 drop
==>
alter table user_1.UNDO_TEST
set unused column name cascade constraints;
SQL> select * from user_1.UNDO_TEST
2 ;
ID
----------
1
2
3
4
5
SQL> desc user_1.UNDO_TEST;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(10)
SQL> select * from DBA_UNUSED_COL_TABS;
OWNER TABLE_NAME COUNT
------------------------------ ------------------------------ ----------
USER_1 UNDO_TEST 1
## 인덱스 테이블, unused 칼럼이 있는 인덱스 테이블 비교
=> 인덱스가 해제가 되나 ?
1) 인덱스 테이블
CREATE TABLE CHECK_DROP ( A1 NUMBER, A2 VARCHAR2(100), A3 VARCHAR2(100)) ;
CREATE INDEX XAK1_CHECK_DROP ON CHECK_DROP ( A1, A2, A3) ;
INSERT INTO CHECK_DROP VALUES ( 1, 'A','B');
INSERT INTO CHECK_DROP VALUES ( 2, 'C','D');
INSERT INTO CHECK_DROP VALUES ( 3, 'E','F');
COMMIT ;
SELECT * FROM CHECK_DROP ;
// A1, A2 가 PK 로 선언되어짐
SELECT * FROM DBA_CONS_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAM POSITION
---------- --------------- --------------- ---------- ----------
SYSTEM XPK_CHECK_DROP CHECK_DROP A2 ##########
SYSTEM XPK_CHECK_DROP CHECK_DROP A1 ##########
// 인덱스 관련 테이블
SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME FROM DBA_INDEXES WHERE TABLE_NAME ='CHECK_DROP';
OWNER INDEX_NAME TABLE_NAME
---------- --------------- ---------------
SYSTEM XAK1_CHECK_DROP CHECK_DROP
// PK 관련 테이블 확인
SQL> SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME ='CHECK_DROP';
no rows selected
SQL> DESC CHECK_DROP ;
Name Null? Type
----------------------------------------- -------- ----------------------------
A1 NUMBER
A2 VARCHAR2(100)
A3 VARCHAR2(100)
SQL>
SQL> 인덱스 테이블 삭제
SQL> ALTER TABLE CHECK_DROP DROP COLUMN A3 ;
Table altered.
SQL>
SQL>
SQL> DESC CHECK_DROP
Name Null? Type
----------------------------------------- -------- ----------------------------
A1 NUMBER
A2 VARCHAR2(100)
// 생성되었던 인덱스 테이블이 삭제됨을 확인
SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME FROM DBA_INDEXES WHERE TABLE_NAME ='CHECK_DROP';
no rows selected
SQL>
SQL>
SQL> SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME ='CHECK_DROP';
no rows selected
SQL>
SQL> SELECT * FROM DBA_UNUSED_COL_TABS ;
no rows selected
3) unused 칼럼이 있는 인덱스 table
DROP TABLE CHECK_DROP ;
CREATE TABLE CHECK_DROP ( A1 NUMBER, A2 VARCHAR2(100), A3 VARCHAR2(100)) ;
CREATE INDEX XAK1_CHECK_DROP ON CHECK_DROP ( A1, A2, A3) ;
INSERT INTO CHECK_DROP VALUES ( 1, 'A','B');
INSERT INTO CHECK_DROP VALUES ( 2, 'C','D');
INSERT INTO CHECK_DROP VALUES ( 3, 'E','F');
COMMIT ;
SELECT * FROM CHECK_DROP ;
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,NULLABLE
FROM DBA_TAB_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';
// 인덱스 관련 확인
SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME FROM DBA_INDEXES WHERE TABLE_NAME ='CHECK_DROP';
OWNER INDEX_NAME TABLE_NAME
---------- --------------- ---------------
SYSTEM XAK1_CHECK_DROP CHECK_DROP
// PK 관련 테이블 확인
SQL> SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME ='CHECK_DROP';
no rows selected
SQL> DESC CHECK_DROP
Name Null? Type
----------------------------------------- -------- ----------------------------
A1 NUMBER
A2 VARCHAR2(100)
A3 VARCHAR2(100)
// A3 칼럼 UNUSED
SQL> ALTER TABLE CHECK_DROP SET UNUSED COLUMN A3 ;
Table altered.
SQL> DESC CHECK_DROP ;
Name Null? Type
----------------------------------------- -------- ----------------------------
A1 NUMBER
A2 VARCHAR2(100)
SQL>
// 인덱스 관련 확인 -> A3 칼럼 unused 후
SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME FROM DBA_INDEXES WHERE TABLE_NAME ='CHECK_DROP';
no rows selected
SQL> SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME ='CHECK_DROP';
no rows selected
// 테이블을 조회해보니 값이 있음
SQL> SELECT * FROM DBA_UNUSED_COL_TABS ;
OWNER TABLE_NAME COUNT
---------- --------------- ----------
SYSTEM CHECK_DROP 1