|
테이블 관리
=============
테이블관리
(1)데이터블록 스페이스가 사용되는 방법 기술
각 테이블 생성 중에 PCTFREE, PCTUSED 파라미터 지정
PCTFREE와 PCTUSED의 지정은 스페이스 사용의 효율성과 테이블의 데이터 세그먼트의 데이터 블록에 있는 현재 데이터를 갱신하기 위해서 예약된 모든 스페이스에 영향을 미친다.
(2)트랜잭션 Entry 매개변수 지정
각 테이블 생성시 INITRANS, MAXTRANS 파라미터를 지정한다.
위 두 파라미터로 인해 테이블 데이터 세그먼트 블록에 트랜잭션 Entry에 대한 영역의 초기 할당량과 최대 할당량을 결정한다.
(3)각 테이블의 위치 지정
올바른 권한과 테이블 스페이스 할당량이 있으면 현재 온라인 상태인 테이블 스페이스에 새 테이블 생성이 가능하다.
새 테이블을 저장할 경우 새로운 스페이스를 저장하려면 CREATE TABLE에 TABLESPACE 옵션을 사용하면 된다. 옵션을 사용하지 않을 경우에는 디폴트 스페이스에 저장된다.
테이블을 올바르게 테이블 스페이스에 저장할 경우 데이터베이스 시스템의 성능향상과 관리에 필요한 시간의 감소 효과를 가지고 온다.
(4) UNRECOVERABLE 테이블 생성시 고려사항
CREATE TABLE AS SELECT 명령문의 하위질의로 테이블을 생성할 때 UNRECOVERABLE을 지정하여 복구할 수 없는 테이블을 생성할 수 있다.
UNRECOVERABLE명령을 사용한 이후에 삽입된 row들은 복구가 가능하다.
UNRECOVERABLE 테이블 생성시 장점
◆ 리두 로그 파일이 절약됨.
◆ 테이블 생성 시간이 감소됨.
◆ 대규모 테이블의 병렬 생성 성능이 향상됨
테이블을 생성하기 전에 테이블의 최대크기를 측정해야 한다.
테이블을 생성한 후 storage parameter를 설정하면? 테이블의 데이터 세그먼트에 더 작은
Extent이 할당되며, 모든 테이블 데이터는 어느 정도 연속적인 디스크 영역에 저장됨.
따라서 테이블과 관련된 디스크 입출력 작업에 필요한 시간을 감소시킴.
테이블 생성 전에 테이블 크기를 측정하든 안 하든 클러스터화 되지 않은 테이블을 생성할 때는 명시적으로 저장영역 매개변수를 설정 가능하다.
클러스터화 된 테이블은 자동으로 클러스터의 storage parameter를 사용한다.
테이블을 생성하거나 변경할 때 명시적으로 설정하지 않으면 테이블의 테이블 스페이스에 설정된 해당 Default storage parameter를 자동으로 사용한다.
테이블의 데이터 세그먼트 확정 영역을 위해 storage parameter를 명시적으로 설정하면 많은 수의 작은 Extent보다는 적은 수의 큰 Extent에 테이블 데이터를 저장 가능하다.
테이블의 저장영역 직접 할당
필요할 때마다 동적으로 테이블의 데이터 세그먼트에 대한 추가 Exent를 할당 가능하다.
테이블의 추가 Extents를 명시적으로 할당 가능하다.
예) ALTER TABLE dept
ALLOCATE EXTENT ( SIZE 20K DATAFILE 'datafile02'INSTANCE 3);
테이블 변경 옵션
◆ 데이터 블록의 영역 사용 매개변수(PCTFREE, PCTUSED)를 수정할 때
SQL> alter table test pctfree 30 pctused 60;
◆ 트랜잭션 입력 항목 설정(INITRANS, MAXTRANS)을 수정할 때
SQL> alter table test initrans 2 maxtrans 4;
◆ Storage Parameter(NEXT, PCTINCREASE)를 수정할 때
SQL> alter table test storage (next 30 pctincrease 0);
◆ 테이블과 관련된 무결성 제약 조건이나 트리거를 활성화하거나 비활성화할 때
SQL> alter table test disable constraint test_no_pk;
SQL> alter table test disable all triggers;
◆ 테이블과 관련된 무결성 제약 조건을 삭제할 때
SQL> alter table test drop constraint test_name_uk;
인덱스관리
============
(1) 테이블 데이터 삽입 후 인덱스 생성
인덱스가 없는 테이블에 데이터를 삽입한 다음 인덱스를 생성하는 것이 유리하다.
데이터가 없는 테이블에 인덱스를 만들면 데이터 삽입 시마다 인덱스를 갱신하기 때문에 성능의 저하를 가져온다.
(2) 테이블 당 인덱스 수 제한
테이블은 인덱스를 무제한 가질 수 있다. 단 인덱스가 많으면 수정시 부하가 많이 걸린다.
데이터를 읽는 속도와 갱신속도에 따라 인덱스를 사용하는 거이 바람직하다.
(3) 트랜잭션 입력 항목에 매개변수 지정
각 인덱스 생성 중에 INITRANS, MAXTRANS 파라미터를 지정한다.
인덱스 세그먼트의 데이터 블록에 트랜잭션 입력 항목에 대한 영역 할당량과 최대 할당량을 결정한다.
(4)인덱스 블록의 영역사용 지정
테이블에 인덱스가 생성 되면 인덱스의 데이터 블록은 최대 PCTFREE까지 테이블의 기존
값으로 채워지며 해당 인덱스 블록에 더 이상 사용할 수 있는 공간이 없으면 인덱스된 값
은 다른 인덱스 블록에 저장된다
(5) 인덱스 크기 측정 및 저장영역 매개변수 설정
인덱스 생성 전에 크기를 측정 함으로서 얻는 장점은 첫 번째 데이터베이스를 유지하는데 필요한 디스크 영역의 양을 결정하는데 테이블크기와 인덱스, 롤백 세그먼트, 리두로그파일의 크기의 측정의 합을 사용가능하면 두 번째로 인덱스 생성시에 적합한 STORAGE Parameter를 설정하고 인덱스를 사용할 응용 프로그램의 입출력 성능을 향상시킬 수 있다.
(6)제약조건과 관련된 인덱스 생성
◆ USING INDEX 옵션을 이용 ENABLE 절을 사용하여 UNIQUE key나 PRIMARY key 제약 조건과 관련된 인덱스에 저장 영역 옵션을 설정할 수 있다.
예) CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, . . . )
ENABLE PRIMARY KEY USING INDEX
TABLESPACE users
PCTFREE 0;
◆ CREATE INDEX 명령어를 사용해서 무결성 제약 조건 외에, 명시적으로 인덱스를 생성할 수 있다.
◆ 기존 인덱스를 원본으로 인덱스를 재생성 할 수 있다.
이러한 방법으로 인덱스를 생성하면 저장 영역 특성을 변경할 수 있고, 새 테이블 스페이스로 이동시킬 수도 있다. 또한 블록의 내부 단편화를 제거할 수 있다.
이러한 방법은 인덱스를 삭제하고 다시 생성하는 것보다 나은 성능을 보여준다
(7)인덱스 영역 모니터링
인덱스에서 키 값이 자주 갱신, 삭제, 삽입 되는 경우 시간이 경과함에 따라 영역을 효율적으로 사용할 수 있지만, 인덱스를 사용할 수 없게 될 수도 있다.
인덱스의 구조를 분석한 다음 INDEX_STATS view를 살펴보아 인덱스의 효율을 모니터할 수 있다 SELECT pct_used FROM sys.index_stats WHERE name = 'indexname'
인덱스의 영역 사용이 평균 이하로 떨어지면 인덱스를 삭제하고 재생성하여 인덱스 영역을 줄이는 것이 좋다
뷰, 시퀀스, 시노님 관리
=======================
뷰, 시퀀스, 시노님 관리
(1) 에러가 있는 뷰의 생성시
View가 에러가 있는 상태로 생성되면 오라클은 생성된 뷰에 에러가 있음을 알리는 메시지를 나타낸다.
예)SQL> CREATE FORCE VIEW dept_v2 as select deptno, dname, loc, mgr FROM dept 일때
Warning: View created with compilation errors.
SQL> DESC dept_v2; 일때는 ORA-04063: view "SCOTT.DEPT_V2" has errors
Error가 있는 view의 상태는 INVALID이다.
(2)뷰 교체하기
뷰의 정의를 바꾸려면 그 뷰를 교체하여야 한다. 뷰는 두 가지 방법으로 교체할 수 있는데
뷰는 drop 한 다음 새로운 정의를 이용하여 다시 만들 수 있다. 뷰를 drop 하면 모든 허가와 특권이 취소된다. 이 허가와 특권은 새로운 뷰를 위하여 다시 만들어져야 한다.
또한 뷰는 or replace 옵션 절과 함께 create view문을 사용하여 재정의 하면 다시 만들 수 있다. 이 방법은 뷰의 현재 정의를 교체하는데 사용되지만 현재의 허가와 특권을 모두 보존한다.
(3)DML 명령문과 조인 뷰
Join view의 UPDATE, INSERT, DELETE 명령문은 key 보존이 되는 base 테이블만 수정할 수 있다 예를 보면
SQL> UPDATE emp_dept
2 SET sal = sal * 1.10
3 WHERE deptno=10;
5 rows updated.
SQL> UPDATE emp_dept
2 SET loc = 'boston'
3 WHERE ename='smith';
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
DEPT 테이블은 EMP_DEPT view에서 key 보존이 되지 않기 때문에 위의 UPDATE 명령문은 수행되지 않는다
Join에서 하나의 유일한 key-preserved 테이블이 있다면 DELETE 명령문으로Join View를 삭제할 수 있다
(4)시퀀스 생성
◆ CREATE SEQUENCE 시스템 권한이나 CREATE ANY SEQUENCE 권한이 필요하다.
. [CACHE 옵션] : 일련의 sequence를 미리 할당하고 메모리에 유지하여 sequence를 빠르게 엑세스.
◆ Cache의 마지막 sequence가 사용되면 오라클은 cache에 다른 시퀀스 집합을 읽어 들인다
◆ Sequence 번호를 cache하면 오라클은 sequence번호를 skip 할 수 있다.
◆ 인스턴스가 비정상적으로 종료되면 cache되었지만 사용되지 않은 sequence도 손실되며
사용되었으나 저장되지 않은 Sequence 번호도 손실하게 된다.
◆ 임포트, 익스포트된 후에 cache된 sequence 번호를 skip할 수 있다.
(5)시퀀스 변경
◆ ALTER SEQUENCE나 ALTER ANY SEQNUECE 권한 필요
◆ Sequence의 시작 번호를 제외하고 sequence를 만드는 방법을 정의하는 parameter를 변경할 수 있다.
◆ Sequence 시작 번호를 변경하려면 sequence를 삭제 후 재생성 해야 한다.
(6)시퀀스 삭제
DROP SEQUENCE 권한이나 DROP ANY SEQUENCE 권한 필요
예를 들어, ORDER_SEQ sequence를 삭제한다면
DROP SEQUENCE order_seq;
Sequence 정의가 데이타 딕셔너리에서 삭제.
Sequence의 synonym은 남지만 참조 할 때 오류가 발생한다.
(7)시노님
◆ Table, view, sequence, or program unit의 별명.
◆ Public synonym : PUBLIC이라는 특별한 사용자 그룹이 소유
(데이타베이스의 모든 사용자가 엑세스 할 수 있다. )
◆ Private synonym : 특정 사용자의 스키마에 포함됨.
(사용자와 사용자가 인정한 사용자만이 사용가능. )
◆ Private synonym을 삭제하려면 PUBLIC 키워드를 뺌.
◆ Public synonym을 삭제하려면 PUBLIC 키워드를 포함시킴
Synonym을 삭제하면 synonym 정의가 데이터 딕셔너리에서 제거됨.
삭제된 synonym을 참조하는 모든 object는 남아 있지만 사용할 수는 없다
클러스트 관리
==============
클러스터 관리
디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 조인이나 자주 사용되는 테이블의 데이터를 디스크의 같은 위치에 저장시키는 방법이다.
(1)적합한 테이블 클러스터
주로 삽입이나 갱신연산 대신 질의를 대상으로 하는 하나이상의 테이블을 저장이나 자주 조인되는 테이블에 적합하다.
(2) 평균 클러스터 키와 이와 관련된 컬럼에 필요한 영역지정
◆ 클러스터 생성시 SIZE를 사용하여 평균 클러스터 키와 이와 관련된 행에 필요한 바이트 수를 지정한다.
◆ 오라클에서 SIZE매개변수를 사용한다.
◆ 기본적으로 오라클은 Cluster data segment의 각 data block에 하나의 Cluster key와 이에 관련된 행만 저장.
◆ Cluster key 값의 모든 행이 하나의 block에 맞지않으면 주어진 key로 모든 값을 빠른 속도로 접근하기위해 block은 체인화 된다.
◆ Cluster index는 block chain의 시작위치를 나타내고 각 block은 Cluster key 값과 관련된 행을 포함. 하나이상의 key가 block에 맞도록 Cluster size가 설정되면 블록은 하나이상의 chain에 속함
(3)클러스터 크기 측정 및 저장영역 매개변수
◆ Index, Rollback segment, Redo log file의 크기 측정치와 Cluster 크기 측정치를 이용하여 현재 데이터베이스 유지에 필요한 디스크 영역 결정 가능 (올바른 하드웨어를 구매 결정 가능)
◆ Cluster 크기 측정치로 Cluster가 사용할 디스크 영역을 잘 관리 할 수 있음. 즉, Cluster가 생성되면 적합한 저장 영역매개변수를 설정하여 Cluster를 사용하는 응용프로그램의 입출력 성능향상 가능
(4)클러스터 인덱스 생성
create index 권한
create any index 권한
Cluster index가 포함될 tablespace에 대한 할당량이나 unlimited tablespace 권한 이 필요하다
(예)SQL>CREATE INDEX emp_dept_index
ON Cluster emp_dept
INITRANS 2
MAXTRANS 5
TABLESPACE users
STORAGE (INITIAL 50k NEXT 50k MINEXTENTS 2 MAXEXTENTS 20 PCTINCREASE 33)
PCTFREE 5;
(5)클러스터된 테이블 생성
Create table, Create any table 권한 필요
(예) SQL>CREATE TABLE dept( deptno NUMBER(3) PRIMARY KEY,
deptname VARCHAR2(20))
Cluster emp_dept (deptno);
SQL>CREATE TABLE emp( empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
deptno NUMBER(3) REFERENCES dept)
Cluster emp_dept (deptno);
(6)클러스터 삭제
Including Tables :Cluster에 관련된 모든 Table 삭제
Cascade Constraints : 관련된 모든 Constraint 삭제
DROP ANY CLUSTER 권한 필요
클러스터된 테이블은 테이블의 Cluster나 다른 Clustered table, Cluster index에 영향을 주지 않고 삭제가 가능하다.
또한 클러스터 인덱스만도 삭제가 가능한데 클러스터 인덱스가 삭제되면 클러스터된 테이블은 사용이 불가능하다.
|
