오라클 Admin (Index)

1. ROWID Format 가. 종류 : Oracle 7 foramt(8byte), Oracle 8 format(10byte ) / 현재는 Oracle 8 format을 주로 쓴다 나. 8 Format의 형태
2. 테이블 생성 시 참고사항 가. 많은 수의 테이블을 생성 할 경우 하나의 테이블스페이스에 저장하지 말고, 여러개의 테이블스페이스에 분산 저장한다 -> 성능향상 나. LMT(LOCAL MANAGEMENT Tablespace) 방식을 이용한다. 3. Temporary Tables 가. 현재 트랜잭션 또는 세션 동안에만 데이터를 보관하고, 작업이 끝난 후에는 데이터가 자동으로 삭제되는 테이블 나. 인덱스, 뷰, 트리거를 temporary table에 생성할 수 있다.

CREATE GLOBAL TEMPORARY TABLE hr.employees_temp
AS SELECT * FROM hr.employees;

4. 수동 EXTENT 할당방법 Datafile안에 특정 테이블의 extent를 예약하는 것으로, 예약된 extent안에는 지정된 table의 데이터만 저장된다. 연관된 데이터가 모여 저장되므로 성능향상이 있다.

ALTER TABLE hr.employees
ALLOCATE EXTENT (SIZE 500K);

5. Nonpartitioned TABLE Reorganization (Delete로 인해 낭비되는 공간을 회수하기 위한 방법) 가. export/import 이용 나. 다른 Tablespace로 Table 이동 -> INDEX 재생성 또는 REBUILD

ALTER TABLE hr.employees move TABLESPACE data1;

6. 컬럼삭제 대량의 데이터가 들어있는 테이블에서 컬럼 삭제 시 많은 시간이 소요되므로 업무이외의 시간에 작업하도록 한다.

ALTER TABLE hr.employees
DROP COLUMN comments
CASCADE CONSTRAINTS CHECKPOINT 1000;

– CASCADE CONSTRAINTS : Reference key 삭제 시 필요 – CHECKPOINT 1000 : 대량의 데이터가 있는 테이블의 컬럼 삭제 시 지정한 수치 이후에 바로 Checkpoint를 발생시켜 datafile에 반영하라는 의미, 여기서는 1000건마다 CHECKPOINT 발생

참고 : ALTER, ANALYZE 명령어의 우선순위 ALTER, ANALYZE 명령어는 SELECT에 비해 우선순위가 높다. 그래서 업무시간 도중 ALTER 명령을 실행하게 되면, 오라클은 SELECT 명령보다 ALTER 명령을 먼저 처리하려고 SELECT 작업을 늦게 처리한다. 또한 ALTER, ANALYZE 작업들은 서버에 많은 부하를 주기 때문에 업무시간 이외에 수행하도록 해야한다.

7. B-Tree INDEX 가. 관계형 데이터베이스에서 가장 일반적으로 사용되는 인덱스 나. 나무구조처럼 가지에 해당하는 Branch, 잎사귀에 해당되는 Leaf, 뿌리에 해당하는 Root 로 구성 다. root 블록을 기준으로 Branch 블록들의 깊이가 모두 동일하며, 모든 Leaf 블록이 같은 높이에 위치하도록 유지한다.

CREATE INDEX hr.employees_last_name_idx ON hr.employees(last_name)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
NOLOGGING
TABLESPACE indx;

참고 : B-Tree에서의 PCTUSED 파라미터 B-Tree 인덱스에서 데이터 삭제 시 실질적인 데이터 삭제는 일어나지 않기 때문에, 데이터블록의 사용량이 감소하는 일은 발생하지 않는다. 따라서 인덱스 생성 시에는 PCTUSED 파라미터는 사용하지 않는다

참고 : UNIQUE INDEX 중복값을 허용하지 않는 인덱스로 Primary Key와 Unique Key 생성 시 자동으로 생성된다. create unique index hr.employees_last_name_idx on hr.employees(last_name) tablespace indx;

8. BITMAP INDEX 가. DML 작업보다 SELECT 작업이 많이 일어나는 OLAP 또는 Data warehousing 환경에 적합한 인덱스 나. 데이터가 중복되는 값이 많을 수록(SELECT DISTINCT COUNT(*) 값이 낮을 수록) 적합하다 다. 키 값에 대응하는 비트맵을 각각 생성하며, DML 작업이 일어날 때마다 Map 수정이 발생한다.

CREATE BITMAP INDEX orders_region_idx ON orders(region_id)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
NOLOGGING
TABLESPACE indx;

9. B-Tree 인덱스와 BITMAP 인덱스의 비교 가. B-Tree INDEX – 큰 분포도(Cardinality)를 갖는 테이블에 적합 – 비교적 키의 갱신 비용이 적음 – OR 사용 Query문에 비효율적 – OLTP에 적합(Data가 실시간으로 변하는 환경) 나. BITMAP INDEX – 적은 분포도(Cardinality)를 갖는 테이블에 적합 – 인덱스 갱신비용이 매우 큼 – OR 사용 Query문에 효율적 – OLAP, Data warehousing 환경에 적합(통계 자료 등) 10. 인덱스 생성 시 참고사항 가. 테이블과 인덱스는 서로 다른 테이블스페이스에 저장한다(전용 인덱스 테이블스페이스 사용) 나. 대량의 데이터가 들어있는 테이블의 경우 NOLOGGING 옵션을 이용하면 인덱스 생성시간을 단축 시킬 수 있다.

참고 : INDEX 생성 시 NOLOGGING 옵션 인덱스 생성 시 오라클은 테이블의 모든 데이터를 DATABASE buffer cache에 올려 검사하며, 이 과정을 Redo LOG file에 기록한다. 인덱스가 생성되는 테이블에 대량의 데이터가 저장되어 있다면 인덱스 생성에 매우 많은 시간이 소요되는데 이러한 경우 NOLOGGING 옵션을 사용하여 인덱스 생성시간을 단축할 수 있다. NOLOGGING 옵션은 인덱스 생성 시 Redo LOG FILE에 최소한의 정보만 기록한다는 의미이다.

11. 인덱스의 Rebuild, DROP 가. REBUILD : 테이블에 DML 작업이 많이 일어나 인덱스의 성능이 떨어졌을 경우 인덱스를 정리해준다.

ALTER INDEX orders_region_idx REBUILD TABLESPACE indx02;

ALTER INDEX orders_region_idx REBUILD Online;

참고 : ONLINE 옵션 인덱스 REBUILD 작업중에는 해당 인덱스를 사용하지 못한다. 이 옵션을 명시해 줄 경우 인덱스 REBUILD 중에도 사용할 수 있게 해준다.그러나 옵션 사용시 인덱스 재생성에 너무 많은 시간이 소요되어 실제 활용도는 높지 않다.

나. DROP

  DROP INDEX hr.employees_idx;