Ghost's Oracle Database !

■ Creating Normal B-Tree Indexes (일반 B-Tree 인덱스 생성)
예제)
CREATE INDEX hr.employees_last_name_idx
ON hr.employees(last_name)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
TABLESPACE indx;

※ 일반 B-Tree 인덱스 구문옵션
UNIQUE: 고유 인덱스 지정에 사용. (기본값은 Nonunique)
Schema: 인덱스/테이블 소유자입
Index: 인덱스 이름입
Table: 테이블 이름
Column: 열 이름입니다.
ASC/DESC: 인덱스가 오름차순으로 생성되는지 내림차순으로 생성되는지 여부를 나타냅니다.
TABLESPACE: 인덱스를 생성할 테이블스페이스를 식별합니다.
PCTFREE: 새로운 인덱스 항목을 수용하기 위해 생성 시 각 블록에 예약되는 공간의 양(전체 공간에서 블록 헤더를 뺀 백분율)입니다.
INITRANS: 각 블록에서 미리 할당하는 트랜잭션 항목의 수를 나타냅니다. (기본값과 최소값은 2입니다.)
MAXTRANS: 각 블록에 할당될 수 있는 트랜잭션 항목의 수를 제한합니다. (기본값은 255입니다.)
STORAGE 절: 인덱스에 확장 영역 할당하는 방법을 결정하는 저장 영역 절을 식별합니다.
LOGGING: 인덱스의 생성 및 인덱스에 대한 이후 작업을 리두 로그 파일에 기록함을 나타냅니다. (기본값입니다.)
NOLOGGING: 생성 및 특정 유형의 데이터 로드를 리두 로그 파일에 기록하지 않음을 나타냅니다.
NOSORT: 데이터베이스에 행이 오름차순으로 저장되므로 인덱스 생성 시 Oracle 서버가 행을 정렬하지 않아도 됨을 나타냅니다.

■ Creating Bitmap Indexes (비트맵 인덱스 생성)
구문
CREATE BITMAP INDEX [schema.]index
ON [schema.]table (column [ ASC | DESC ])
[ TABLESPACE tablespace ]
[ PCTFREE integer ]
[ INITRANS integer ]
[ MAXTRANS integer ]
[ storage-clause ]
[ LOGGING| NOLOGGING ]
[ NOSORT ]

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

CREATE_BITMAP_AREA_SIZE매개변수
초기화 매개변수인 CREATE_BITMAP_AREA_SIZE는 비트맵 세그먼트를 메모리에 저장하
는 데 사용하는 공간의 양을 결정하며 기본값은 8MB입니다. 값이 클수록 인덱스를 빨리 생
성할 수 있고 기수가 아주 작은 경우에는 이 값을 작은 값으로 설정할 수 있습니다. 예를 들
어, 기수가 겨우 2이면 값을 MB가 아닌 KB 순서로 나열하며 일반적으로 기수가 높은 경우
에는 메모리가 충분해야 최적의 성능을 낼 수 있습니다.

■ Changing Storage Parameters for Indexes (인덱스 저장 영역 매개변수 변경)
구문
ALTER INDEX [schema.]index
[ storage-clause ]
[ INITRANS integer ]
[ MAXTRANS integer ]

예제)
ALTER INDEX employees_last_name_idx
STORAGE(NEXT 400K
MAXEXTENTS 100);

■ Allocating and Deallocating Index Space (인덱스 공간 할당 및 할당 해제)
구문
ALTER INDEX [schema.]index
ALLOCATE EXTENT ([SIZE integer [K|M]]
[ DATAFILE 'filename' ])

ALTER INDEX [schema.]index
DEALLOCATE UNUSED [KEEP integer [ K|M ]]

예제)
ALTER INDEX orders_region_id_idx
ALLOCATE EXTENT (SIZE 200K
DATAFILE ‘/DISK6/indx01.dbf’);

ALTER INDEX orders_id_idx
DEALLOCATE UNUSED;

■ Rebuilding Indexes (인덱스 재구축)
구문
ALTER INDEX [schema.]index REBUILD
[ TABLESPACE tablespace ]
[ PCTFREE integer ]
[ INITRANS integer ]
[ MAXTRANS integer ]
[ storage-clause ]
[ LOGGING| NOLOGGING ]
[ REVERSE | NOREVERSE ]

예제)
ALTER INDEX orders_region_id_idx REBUILD
TABLESPACE indx02;

※ 인덱스 재구축 특성
• 기존 인덱스를 데이터 소스로 사용하여 새 인덱스를 구축합니다.
• 기존 인덱스를 사용하여 인덱스를 구축할 경우에는 정렬이 필요하지 않으므로 성능이 향상됩니다.
• 새 인덱스를 구축하고 나면 이전 인덱스는 삭제되며 재구축 중에는 이전 인덱스 및 새 인덱스를 각 테이블스페이스에 모두 수용할 수 있는 충분한 공간이 필요합니다.
• 결과 인덱스는 삭제한 항목을 포함하지 않으므로 이 인덱스는 공간을 더 효율적으로 사용합니다.
• 새 인덱스를 구축하는 동안에는 질의에서 기존 인덱스를 계속 사용할 수 있습니다.

※ 재구축이 필요한 상황
• 기존 인덱스를 다른 테이블스페이스로 이동해야 할 경우로 인덱스가 테이블과 동일한 테이블스페이스에 있거나 객체를 디스크에 재분배해야 할 경우에는 이 작업이 필요할 수 있습니다.
• 인덱스에 삭제한 항목이 많이 포함되어 있는 경우로 이러한 현상은 완료된 주문은 삭제하고 새로운 주문을 높은 번호로 테이블에 추가하는 주문 테이블의 주문 번호 인덱스와 같이 변하는 인덱스에서 나타나는 일반적인 문제입니다.
  오래된 소수의 주문을 아직 처리하지 않은 경우 항목 일부만 삭제한 인덱스 최하위 블록이 몇 개 있을 수도 있습니다.
• 기존의 일반 인덱스를 역방향 키 인덱스로 변환해야 할 경우로 이전 릴리스의 Oracle 서버에서 응용 프로그램을 이전할 경우 재구축할 수 있습니다.
• 인덱스의 테이블을 ALTER TABLE ... MOVE TABLESPACE 명령을 사용하여 다른 테이블스페이스로 이동한 경우

■ Rebuilding Indexes Online (온라인 인덱스 재구축)
구문
ALTER INDEX [schema.]index REBUILD ONLINE;

예제)
ALTER INDEX orders_id_idx REBUILD ONLINE;

※ 제한사항
• 임시 테이블의 인덱스는 재구축할 수 없습니다.
• 분할된 인덱스 전체는 재구축할 수 없으므로 분할 영역 또는 서브 분할 영역을 각각 재구축해야 합니다.
• 사용되지 않은 공간은 할당을 해제할 수 없습니다.
• 해당 인덱스에 대한 PCTFREE 매개변수의 값을 전체적으로 변경할 수 없습니다.

■ Coalescing Indeses (인덱스 병합)
구문
ALTER INDEX [schema.]index COALESCE;

예제)
ALTER INDEX hr.employees_idx COALESCE;

■ Checking Indexes and Their Validity
구문
ANALYZE INDEX [schema.]index VALIDATE STRUCTURE

예제)
ANALYZE INDEX orders_region_id_idx
VALIDATE STRUCTURE;

※ 인덱스 및 유효성 검사
• 모든 인덱스 블록에 대해 손상된 블록이 있는지 확인합니다.
  이 명령을 수행해도 인덱스 항목이 테이블의 데이터에 대응되는지 여부는 확인되지 않습니다.
• INDEX_STATS 뷰를 인덱스 정보로 채웁니다.

■ Dropping Indexes (인덱스 삭제)
구문
DROP INDEX [schema.]index;

예제)
DROP INDEX hr.deptartments_name_idx;

다음 시나리오에서는 인덱스를 삭제해야 할 필요가 있습니다.
• 응용 프로그램에서 더 이상 사용하지 않는 인덱스는 삭제할 수 있습니다.
• 대량 로드를 수행하기 전에 인덱스를 삭제할 수 있으며 데이터를 대량으로 로드하기 전에 인덱스를 삭제하고 로드한 다음 다시 생성하면 다음 결과를 얻을 수 있습니다.
– 로드 성능이 향상됩니다.
– 인덱스 공간을 더 효율적으로 사용할 수 있습니다.
• 주기적으로만 사용하는 인덱스가 특히 휘발성 테이블에 기반을 두고 있을 경우에는 불필요하게 유지 관리하지 않아도 되며 대개 연말 또는 분기 말의 검토 회의에 사용할 정보를 모으기 위해 임시 질의를 생성하는 OLTP 시스템의 경우에는 불필요하게 유지 관리하지 않아도 됩니다.
• 로드 작업 같은 특정 유형의 작업 중에 인스턴스 실패가 발생하는 경우에는 인덱스를 INVALID로 표시하는데 이러한 경우에는 인덱스를 삭제하고 다시 생성해야 합니다.
• 인덱스가 훼손된 경우

■ Identifying Unused Indexes (사용되지 않은 인덱스 식별)
예제)
- 인덱스 사용에 대한 모니터링 시작
ALTER INDEX hr.dept_id_idx
MONITORING USAGE

- 인덱스 사용에 대한 모니터링 중지
ALTER INDEX hr.dept_id_idx
NOMONITORING USAGE

※ V$OBJECT_USAGE 열
INDEX_NAME: 인덱스 이름입니다.
TABLE_NAME: 해당 테이블입니다.
MONITORING: 모니터를 ON으로 설정할지 OFF로 설정할지 여부를 나타냅니다.
USED: 모니터하는 동안 인덱스가 사용되었는지 여부를 YES 또는 NO로 나타냅니다.
START_MONITORING: 인덱스에 대한 모니터의 시작 시간을 나타냅니다.
END_MONITORING: 인덱스에 대한 모니터의 중지 시간을 나타냅니다.

■ Data file 및 Tablespace 조회
SQL> select tablespace_name, bytes, file_name from dba_data_files;

SQL> select tablespace_name, status, contents, extent_management, segment_space_management
  2  from dba_tablespaces;


■ Tablespace 생성
- 일반 Tablespace
SQL> create tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기
  3  segment space management auto;

SQL> create tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기
  3  extent space management local;

- UNDO Tablespace
SQL> create undo tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기;

- TEMP Tablespace
SQL> create temporary tablespace tablespace_name
  2  tempfile 'path/data_file_name' size 크기;

- Tablespace Block 지정
SQL> create tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기
  3  blocksize [2K | 4K | 8K | 16K | 32K]
  4  segment space management auto;


■ Tablespace 확장
SQL> alter tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기;

SQL> alter database datafile 'path/data_file_name' resize 크기;


■ Tablespace 관리
SQL> alter tablespace tablespace_name offline;
SQL> alter tablespace tablespace_name online;

SQL> alter tablespace tablespace_name rename
  2  datafile 'path/data_file_name(원본 Datafile)'
  3  to 'path/data_file_name';

SQL> alter database rename
  2  file 'path/data_file_name(원본 Datafile)'
  3  to 'path/data_file_name';


■ Tablespace 삭제
SQL> drop tablespace tablespace_name
  2  including contents and datafiles cascade constraints;

Redo log group은 최소 2개, member 는 그룹당 최소 1개이다.

■ 수동 로그 스위치
SQL> alter system switch logfile;

■ 수동 checkpoint
SQL> alter system checkpoint;

■ 리두 로그 그룹 추가
SQL> alter database add logfile group 그룹번호
  2  '경로/파일명.log' size 크기;

■ 리두 로그 그룹별 멤버 추가
SQL> alter database add logfile member
  2  '경로/파일명.log' to group 그룹번호;

■ 리두 로그 그룹 삭제
SQL> alter database drop logfile group 그룹번호;

■ 리두 로그 그룹의 멤버 삭제
SQL> alter database drop logfile member
  2  '경로/파일명_b.log';

■ log file 상태 조회 (그룹번호, 멤버, 용량, 상태)
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  INACTIVE

SQL>