Alice's Story

일반적으로 object의 size를 검색할 때,

예를 들어 scott 유저의 emp 테이블이라면,

select sum(bytes) from dba_segments

where owner = 'SCOTT' and segment_name = 'EMP';

를 사용하나... 이것은 실제로 정확한 계산은 아닙니다.

조금 더 정확한 계산은...

## TABLE SIZE 계산 공식(ORACLE BLOCK SIZE : 2K 로 가정) ##
------------------------------------------------------
$ sqlplus scott/tiger
SQL> SELECT GREATEST(4, ceil(ROW_COUNT /
((round(((1958 - (initrans * 23)) *
((100 - PCTFREE) /100)) / ADJ_ROW_SIZE)))) * BLOCK_SIZE)
TableSize_Kbytes
FROM dual;

*. 한 개의 BLOCK에 Available 한 Bytes - 1958
*. 각 initrans 는 23 Bytes
*. PCT_FREE : Table 의 pctfree 값(default 10)
*. ADJ_ROW_SIZE : 각 row 의 평균 SIZE 추정치
*. ROW_COUNT : table 의 row 의 갯수
*. BLOCK_SIZE : 1 block의 크기 (단위: K)

예) table 이름이 EMP 일 경우

ROW_COUNT : select count(*) from emp;

ADJ_ROW_SIZE :
analyze table emp compute statistics;
(또는 건수가 매우 많을 때에는 compute 대신 estimate 사용)
select avg_row_len
from user_tables
where table_name='EMP';

를 참고하시고...

더 정확한 계산을 원하신다믄...
##

TABLE(NON-CLUSTERD TABLE)의 초기 사용량을 추정하는 방법을 살펴보자.
여기서는 ORACLE7 SERVER를 기준으로 하여 설명 하기 하며, TABLE의 초기 사용량을
계산하는 단계를 다음과 같이 나눌 수 있다.

STEP 1 - 총 블럭 헤드 크기(BLOCK HEADER SIZE)를 계산
STEP 2 - 데이타 블럭 당 사용 가능한 데이타 영역을 계산
STEP 3 - 평균 ROW의 전체 컬럼의 길이(COMBINED COLUMN LENGTH)를 계산
STEP 4 - 총 평균 ROW 크기를 계산
STEP 5 - 데이타 블럭 내의 평균 ROW 수를 계산
STEP 6 - 테이블에서 요구되는 블럭과 바이트 수를 계산

1. [ STEP 1 ] : 총 블럭 헤드 크기(TOTAL BLOCK HEADER SIZE)를 계산

1) total block header

total block header = block header, part A +
block header, part B

block header, part A = (fixed header + variable transaction header)
block header, part B = (table directory + row directory)

** fixed header : 57 bytes (고정된 블럭 헤드)
variable transaction : 23 * I
header ( I는 해당 TABLE의 INITRANS의 값)

table directory : 4
row directory : 2 * R
( R은 블럭의 ROW 수, STEP 5에서 계산)

2) 예를 들어, INITRANS = 1일때 경우의 total block header
total block header = (57 + (23*1)) + (4 + (2*R))
= 80 + (4 + (2*R)) bytes


2. [ STEP 2 ] : 데이타 블럭 당 사용 가능한 데이타 영역
(DATA SPACE PER DATA BLOCK)을 계산

1) available data space

available data space = (block size - total block header) -
((block size - block header, part A) *
(PCTFREE/100))

** block size : 데이타베이스의 블럭 크기
(SQL*DBA의 SHOW PARAMETER에서
db_block_size를 확인)

2) 예를 들어, db_block_size=2K, PCTFREE=10 일 경우

available data space = (2048 - (80 + (4 + 2R)) -
((2048 - 80) * (10 / 100))
= (1964 - 2R) - (1968 * 0.1)
= (1964 - 2R - 196)
= (1768 - 2R) bytes


3. [ STEP 3 ] : 평균 ROW의 전체 컬럼의 길이
(COMBINED COLUMN LENGTH)를 계산

ROW 길이를 계산하기 위하여 TABLE 정의에서 컬럼 수, 각 컬럼의 데이타
타입, 가변 길이 컬럼의 평균 크기등을 참조한다.

1) D (data space/average row) : table T 가 다음의 스키마로 정의되어
있을 경우의 ROW의 평균 길이

- create table T ( A char(10), B date, C number(10,2))


D (data space/average row) = ( A + B + C )


** A(컬럼 A의 길이) : 10 bytes -----> CHAR 타입인 경우 fixed length
B( B의 길이) : 7 bytes -----> DATE 타입인 경우
C( C의 길이) : 5 bytes = (P / 2) + 1
-----> NUMBER 타입인 경우에는
PRECISION을 고려해서 계산됨
(단, PRECISION에서 NUMBER의 길
이를 나타내므로 SCALE은 무관함)

4. [ STEP 4 ] : 총 평균 ROW 크기를 계산(TOTAL AVERAGE ROW SIZE)

여기서는 테이블의 ROW에 의해 요구되는 최소 사용 영역을 계산하는 공식을
제공한다.

1) bytes/row

bytes/row = row header + F + V + D


** row header : ROW 당 3 bytes ( NON-CLUSTERED TABLE)
F : 250 bytes 이하를 사용하는 컬럼의 TOTAL LENGTH BYTES
( 각 컬럼 당 1 bytes )
V : 250 bytes 이상을 사용하는 컬럼의 TOTAL LENGTH BYTES
( 각 컬럼 당 3 bytes )
D : [STEP 3]의 D (data space/average row)

2) 예를 들어, [STEP 3]의 테이블 T 의 TOATL AVERAGE ROW SIZE

- 250 byte 이하의 컬럼 수 = 3
250 byte 이상의 컬럼 수 = 0
D (data space/average row) = 22 인 경우
- avg. row size, table T = ( 3 + (1 * 3) + (3 * 0) + 22)
= 28 bytes

5. [ STEP 5 ] : 데이타 블럭내의 평균 ROW 수(AVERAGE ROWS PER BLOCK)를 계산


1) R (avg. # of rows/block)

R (avg. # of rows/block) = available space / average row size


** available space : [STEP 2]의 available data space
average row size : [STEP 4]의 bytes/row

2) 예를 들어, [STEP 2]의 available space가 (1768 - 2R) bytes이고,
[STEP 4]의 테이블 T의 bytes/row가 28bytes일 경우

- R = (1768 - 2R)bytes / 28bytes
28R = 1768 - 2R
30R = 1768
R = 58 rows/block

6. [ STEP 6 ] : 테이블에서 요구되는 블럭과 바이트 수를 계산
(NUMBER OF BLOCKS AND BYTES)

1) # blocks for table과 # bytes for table


# blocks for table = # rows / R


** # rows : TABLE의 ROW 수
R : [STEP 5]의 R rows/block


# bytes for table = # blocks for table * 2048 bytes/block


** 2048 bytes/block : 데이타베이스의 블럭 크기 (db_block_size)


2) 예를 들어, 테이블 T가 10000개의 rows을 가질 경우의 테이블 당 블럭 수

- # blocks for table T = 10000 rows / 58 rows/block
= 173 blocks

- # bytes for table T = 173 blocks * 2048 bytes/block
= 354304 bytes
= 346 Kbytes

#####

를 참고하시기 바랍니다....

[출처] : www.coolx.net

1. 서브쿼리를 이용한 테이블 생성
CREATE TABLE table [column[, column,….]
AS subquery;

ex)CREATE TABLE  addr_second(id, name, addr, phone, e-mail) AS SELECT * FROM address;
CREATE TABLE addr_third AS SELECT id, num FROM address;

2. 테이블 구조만 복사(내용은 복사 안 함)
CREATE TABLE table
AS SELECT * FROM  source_table WHERE condition;

ex) CREATE TABLE addr_forth AS SELECT id, num FROM address WHERE 1=2;

3. 테이블 구조 변경[추가]
ALTER TABLE table
ADD (column datatype [DEFAULT expression], [column datatype]….);

ex)ALTER TABLE address ADD (birth date);
ALTER TABLE address ADD (comments varchar2(200) DEFAULT ‘No Comment’);

4. 테이블 구조 변경[칼럼 삭제]
ALTER TABLE table DROP COLUMN column;

ex) ALTER TABLE address DROP COLUMN comments;

5. 테이블 이름 변경
RENAME old_table TO new_table;

ex)RENAME addr_second TO  client_address;

6. 테이블 삭제
DROP TABLE [schema.]table [cascade constraints];

ex)DROP TABLE addr_third;

7. TRUNCATE 명령문
TRUNCATE TABLE [schema.] table
테이블 구조는 그대로 유지하고 데이터와 할당된 공간을 삭제하는 명령문이다.

1. USER_ 데이터 딕셔너리 뷰
  :자신이 생성한 테이블, 인덱스, 뷰, 동의어 등과 같은 객체나 해당 사용자에게 부여된 권한 정보를 조회할 수 있다.
  - USER_TABLES : 테이블이 저장된 테이블 스페이스 이름, 데이터가 저장된 물리적 공간 그리고 블록 파라미터 정보 등이 저장된다.
  - USER_OBJECTS : 사용자가 생성한 테이블 정보와 함께 인덱스, 시퀀스, 동의어, 뷰 등과 같은 객체에 대한 이름, 종류, 생성 날짜 등의 정보를 저장한다.
  - USER_CATALOG : 사용자 소유로 생성된 모든 객체 이름과 객체 종류에 대한 정보를 저장한다.

  ex)SELECT table_name FROM user_tables;

2. ALL_ 데이터 딕셔너리 뷰
  : 자신이 생성한 테이블, 인덱스, 뷰, 동의어 등과 같은 객체나 자신에게 권한이 있는 객체나 정보를 조회할 수 있다.
  ex) SELECT table_name FROM ALL_tables;

3. DBA_ 데이터 딕셔너리 뷰
  : DBA 나 SELECT ANY TABLE 권한을 가진 사용자만 접근할 수 있다
  ex) SELECT owner,table_name FROM dba_tables;

4. v$_ 데이터 딕셔너리 뷰
  : DB의 관리나 상태의 정보
  ex) SELECT * FROM v$instance;

1. 데이터 무결성 제약 조건의 종류
  - NOT NULL : 해당 칼럼값은 NULL을 포함할 수 없음
  - 고유키(Unique key) : 테이블에서 해당 칼럼 값은 반드시 유일해야 함
  - 기본키 : 해당 칼럼 값은 반드시 존재해야 하며, 유일해야 함. Unique Key, NOT NULL 제약 조건이 결합된 형태
  - 참조(foreign key) : 해당칼럼 값은 참조되는 테이블의 칼럼 값 중의 하나와 일치하거나 NULL을 가짐
                           foreign key(자식)와 reference key(부모)는 index key가 있어야 속도가 빨라짐
  - CHECK : 해당 칼럼에 저장 가능한 데이터 값의 범위나 조건 지정

2. 테이블 생성 시 무결성 제약조건 생성
CREATE TABLE [schema.] table
    (column datatype [DEFAULT expression]
    [column_constraint],
    [table_constraint] [,…])

ex)CREATE TABLE subject
     (subno      NUMBER(5)
         CONSTRAINT subject_no_pk PRIMARY KEY
         DERERRABLE INITIALLY DEFERRED
         USING INDEX TABLESPACE indx,
      subname    VARCHAR2(20)
         CONSTRAINT subject_name_nn NOT NULL
      term       VARCHAR2(1)
         CONSTRAINT subject_term_ck CHECK(term in('1','2')),  or (term > 5) => 가능
      type       VARCHAR2(1));

3. 기존 테이블에 무결성 제약 조건 추가 방법
ALTER TABLE table
   ADD [CONSTRAINT constraint_name] constraint_type (column);

ex)ALTER TABLE department ADD CONSTRAINT dept_no_pk PRIMARY KEY(deptno);
ALTER TABLE department MODIFY (dname CONSTRAINT dept_dname_nn NOT NULL);
* NOT NULL 제약조건 추가는 NOT NULL 허용상태를 NOT NULL 불가로 변경하는 것이므로 MODIFY 명령문을 사용해야 한다.

4. 무결성 제약 조건 삭제
ALTER TABLE table
   DROP CONSTRAINT constraint_name [CASCADE];

ex)ALTER TABLE subject DROP CONSTRAINT subject_term_ck;

5. 제약조건의 비활성화
ALTER TABLE table
   DISABLE CONSTRAINT constraint_name [cascade];

ex)ALTER TABLE sugang DISABLE CONSTRAINT sugang_pk;

6. 제약조건의 활성화
ALTER TABLE table
   ENABLE [NOVALIDATE] CONSTRAINT constraint_name [CASCADE];

  * NOVALIDATE : 기존 데이터는 적용시키지 않고 새로 입력되거나 수정되는 데이터에만 적용시킨다는 의미.

7. 무결성 제약조건 데이터 딕셔너리
  : 사용자에 의해 정의된 무결성 제약조건을 조회하기 위한 데이터 딕셔너리 뷰는 USER_CONSTRAINTS,  USER_CONS_COLUMNS 가 있다.
  * USER_CONSTRAINTS : 제약 조건이 정의된 테이블 이름, 제약조건 이름, 제약 조건 종류 및 활성화 상태 정보 저장
  * USER_CONS_COLUMNS : 제약조건 정의된 칼럼과 제약조건 이름 저장