[Oracle] Data access method, INDEX

Data access method

optimizer : SQL문을 수행하기 위한 실행계획을 만든다

책 = 테이블

page = block

문장 = 행(row)

 

특정한 단어(오라클)을 찾으려면

① full table scan : 테이블 첫번째 행부터 마지막 행가지 access하는 방식  

⇒ 느림(중간에 찾아도 계속 찾음)

    select * from employees where employee_id = 100; —인덱스 설정 X

 

② rowid scan : 행의 물리적 주소(rowid)를 가지고 찾는 방식. 데이터 access 방법 중 가장 빠름

by user rowid scan (rowid 직접검색)

select * from employees where rowid = 'AAAEAbAAEAAAADNAAA';

by index rowid scan(인덱스 설정)

select * from employees where employee_id = 100; —인덱스 설정 O

rowid : 물리적 row 주소(18)

AAAEAb : data object id select * from user_objects;

AAE : file id select * from dba_data_files;

AAAADN : block id select * from dba_extents where segment_name = 'EMPLOYEES';

AAA : row slot id

 

INDEX

-by index rowid scan 방식을 사용해서 검색속도를 높이기 위해서 사용되는 객체이다.

-인덱스를 이용해서 행을 검색하면 i/o(입출력)을 줄일 수 있다.

-인덱스는 테이블과는 독립적으로 생성된다.

-인덱스는 오라클이 자동으로 유지관리한다.

-primary key, unique 제약조건을 생성하면 unique index 자동으로 생성된다. 나머지는 수동생성

select * from user_constraints where table_name = 'EMP';

select * from user_cons_columns where table_name = 'EMP';

select * from user_indexes where table_name = 'EMP';

select * from user_ind_columns where table_name = 'EMP';

 

1. unique scan : 인덱스에 해당하는 값이 1개일 때

primary key 제약조건을 추가

alter table emp add constraint emp_id_pk primary key(employee_id);
select * from emp where employee_id = 100; --by index rowid scan, unique scan
① 100번에 해당하는 rowid를 emp_id_pk 인덱스에서 찾는다
② 찾은 rowid를 가지고 emp 테이블에 access 하고 종료

unique index 생성

unique index를 생성하게 되면 employee_id 컬럼의 값과 중복되는 값은 입력할 수 없다.
마치 unique 제약조건을 만든 것처럼 효과를 준다.(unique 제약조건 생성은 X)
create unique index emp_id_idx on emp(employee_id);
select * from emp where employee_id = 100; --by index rowid scan, unique scan

2. range scan : 인덱스에 해당하는 값이 여러개일 때

non unique index 생성

create index emp_last_name_idx on emp(last_name);
select * from emp where last_name = 'King'; —by index rowid scan, range scan
① 'King'에 해당하는 rowid를 emp_last_name_idx에 가서 찾는다
② 찾은 rowid를 가지고 emp 테이블에 access 한다.
③ 또 'King'에 해당하는 rowid를 emp_last_name_idx에 가서 찾는다(non unique)
④ 찾은 rowid를 가지고 emp 테이블에 access 한다.
⑤ 또 'King'에 해당하는 rowid를 emp_last_name_idx에 가서 찾고 없으면 종료
⑥ 결과집합을 유저한테 전달

조합 index 생성

create index emp_last_first_name_idx on emp(last_name, first_name);
select * from emp where last_name = 'King' and first_name = 'Steven'; --by index rowid scan, range scan
① last_name = 'King'과 first_name = 'Steven' 값이 같이 존재하는 rowid를 
  emp_last_first_name_idx에서 찾는다
② 찾은 rowid를 가지고 emp 테이블에 찾아간다. (결과집합을 만들기 위해서)
③ 또 last_name = 'King'과 first_name = 'Steven' 값이 같이 존재하는 rowid를 
  emp_last_first_name_idx에 가서 찾아보고 없으면 종료한 후 결과집합을 유저에게 전달