22.인덱스
인덱스 (index)
대량의 레코드가 저장된 테이블의 데이터를 빠르게 검색할수 있도록 지원해주는 객체(검색 속도 향샹의 목적으로 사용)
- 별도의 추가 저장 공간 필요
- 데이터와 데이터의 위치값을 포함한다
- 생성,삭제가능(다른 테이블이나 인덱스 영향을 주지 않는다)
-
select,update,delete 성능이 향샹된다
인덱스 관리
- 데이터에 변경이 발생하면 최신 상태 유지를 위해 관리가 필요하다
- 인덱스에 적용된 컬럼은 최신 데이터를 정렬된 상태로 유지
- insert update delete 발생하면 인덱스 관리
- insert:새로운 데이터에 대한 인덱스 추가
-
update 기존의 인덱스는 사용하도록 처리하고 수정된 데이터에 대한
인덱스 추가
-
delete: 삭제되는 인덱스는 사용하지 않도록 처리
인덱스 장점
- 조회 속도 향상
- 시스템 부하 감소
- 추가 저장공간 필요(전체 공간의 10% 필요)
-
인덱스를 잘못 사용하는 경우 성능이 저하 될 수 있다
-
인덱스 자료구조:B*Tree,B+ TREE구조
인덱스 조회 하기
SELECT * FROM user_indexes;
SELECT*
FROM user_indexes
WHERE table_name = 'EMPLOYEES';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX IOT_REDUNDANT_PKEY_ELIM DROPPED VISIBILITY DOMIDX_MANAGEMENT SEGMENT_CREATED
EMP_EMAIL_UK NORMAL HR EMPLOYEES TABLE UNIQUE DISABLED EXAMPLE 2 255 65536 1048576 1 2147483645 10 NO 0 1 107 1 1 19 VALID 107 0 22/03/31 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO YES NO NO NO VISIBLE YES
EMP_EMP_ID_PK NORMAL HR EMPLOYEES TABLE UNIQUE DISABLED EXAMPLE 2 255 65536 1048576 1 2147483645 10 NO 0 1 107 1 1 2 VALID 107 0 22/03/31 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO YES NO NO NO VISIBLE YES
EMP_DEPARTMENT_IX NORMAL HR EMPLOYEES TABLE NONUNIQUE DISABLED EXAMPLE 2 255 65536 1048576 1 2147483645 10 NO 0 1 11 1 1 9 VALID 106 0 22/03/31 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO YES NO NO NO VISIBLE YES
EMP_JOB_IX NORMAL HR EMPLOYEES TABLE NONUNIQUE DISABLED EXAMPLE 2 255 65536 1048576 1 2147483645 10 NO 0 1 19 1 1 8 VALID 107 0 22/03/31 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO YES NO NO NO VISIBLE YES
EMP_MANAGER_IX NORMAL HR EMPLOYEES TABLE NONUNIQUE DISABLED EXAMPLE 2 255 65536 1048576 1 2147483645 10 NO 0 1 18 1 1 8 VALID 106 0 22/03/31 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO YES NO NO NO VISIBLE YES
EMP_NAME_IX NORMAL HR EMPLOYEES TABLE NONUNIQUE DISABLED EXAMPLE 2 255 65536 1048576 1 2147483645 10 NO 0 1 107 1 1 15 VALID 107 0 22/03/31 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO YES NO NO NO VISIBLE YES
인덱스 컬럼 조회:인덱스는 컬럼을 이용해서 만든다
SELECT*
FROM user_ind_columns;
select*
from user_ind_columns
where table_name = 'EMPLOYEES';
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
EMP_EMAIL_UK EMPLOYEES EMAIL 1 25 25 ASC
EMP_EMP_ID_PK EMPLOYEES EMPLOYEE_ID 1 22 0 ASC
EMP_DEPARTMENT_IX EMPLOYEES DEPARTMENT_ID 1 22 0 ASC
EMP_JOB_IX EMPLOYEES JOB_ID 1 10 10 ASC
EMP_MANAGER_IX EMPLOYEES MANAGER_ID 1 22 0 ASC
EMP_NAME_IX EMPLOYEES LAST_NAME 1 25 25 ASC
EMP_NAME_IX EMPLOYEES FIRST_NAME 2 20 20 ASC
create table users(
user_id number not null primary key,
first_name varchar2(10) not null,
last_name varchar2(10) not null,
email varchar2(20),
phone_number varchar2(20),
reg_date date
);
insert로 아래와 같은 값을 추가하였을때
USER_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER REG_DATE
1 jh kim test@naver.com 010-1211-1111 22/01/01
2 sh lee test1@naver.com 010-1222-2222 22/02/01
3 jh song test2@naver.com 010-1333-3333 22/03/01
4 os park test3@naver.com 010-1444-4444 22/04/01
5 hu chol test4@naver.com 010-1255-5555 22/05/01
인덱스 조회 하기: 기본키는 인덱스가 자동 생성된다
select* from user_indexes
where table_name = 'USERS';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX IOT_REDUNDANT_PKEY_ELIM DROPPED VISIBILITY DOMIDX_MANAGEMENT SEGMENT_CREATED
SYS_C0011071 NORMAL HR USERS TABLE UNIQUE DISABLED USERS 2 255 65536 1048576 1 2147483645 10 YES VALID 1 1 NO N Y N DEFAULT DEFAULT DEFAULT NO NO NO NO NO VISIBLE YES
인덱스 생성
create index reg_date_idx
on users (reg_date);
select * from user_indexes
where table_name = 'USERS';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX IOT_REDUNDANT_PKEY_ELIM DROPPED VISIBILITY DOMIDX_MANAGEMENT SEGMENT_CREATED
SYS_C0011071 NORMAL HR USERS TABLE UNIQUE DISABLED USERS 2 255 65536 1048576 1 2147483645 10 YES VALID 1 1 NO N Y N DEFAULT DEFAULT DEFAULT NO NO NO NO NO VISIBLE YES
REG_DATE_IDX NORMAL HR USERS TABLE NONUNIQUE DISABLED USERS 2 255 65536 1048576 1 2147483645 10 YES 0 1 5 1 1 1 VALID 5 5 22/04/06 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO NO NO NO NO VISIBLE YES
select * from users
where reg_date = '22/01/01';
USER_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER REG_DATE
1 jh kim test@naver.com 010-1211-1111 22/01/01
댓글남기기