오라클 SQL - ROWID와 INDEX

 

 

ROWID

 

데이터 처리 방법은 크게 두 종류로 나뉜다.

1. full table scan
2. by user rowid scan

여기서 by user rowid scan은 row마다 갖고있는 고유 주소(rowid)를 찾아가는 방법이다.

{HR SESSION}

rowid를 확인하는 방법은 다음과 같다. :

create table emp
as select * from employees;

select rowid, employee_id 
from emp;

 

rowid를 사용하면 원하는 데이터를 직접 찾을 수 있다. :
select * from emp where rowid = 'AAAE/5AAEAAAAJTAAD';

 

F10을 눌러서 실행계획을 보자.

처리 방식이 변한것을 확인할 수 있다.

(기존 fullscan 방식의 실행계획 :

)

 

rowid는 18글자 분석

'AAAE/5AAEAAAAJTAAD' 일때 :

1) AAAE/ -----------> data object id
2) 5AA -------------> file id : 언젠간 바뀔 수 있음
3) EAAAAJT --------> block 번호(block id) : file id 에 따라 바뀔 수 있음
4) AAD -------------> row slot 번호

1)
객체 번호(data object id) 확인하는 방법 :
select * from user_objects;

테이블을 생성할때 data object id가 만들어진다.
문자로 보이는 이유는 암호화된것.

 

2)
file id 확인하는 방법 : 
select * from user_tables;

{SYS SESSION}

select * from dba_data_files;

 

3)
header block : 오라클이 세그맨트를 관리하기 위한 번호
select * from dba_segments where segment_name = 'EMP';

block id 찾는법 :
select * from dba_extents where segment_name = 'EMP';

extents의 첫번째 블락 번호(고유번호)

 

4)
row slot : block안에서 중복되지 않는 row들마다 붙는 slot번호
select rowid, employee_id
from emp;
이걸로 확인

 

데이터 처리 방법에 관한 더 자세한 설명인 이곳을 참고해주세요.

https://truman.tistory.com/58

오라클 SQL - Data 처리 방법

 

---

 

INDEX

 

우리가 각각의 rowid를 다 기억할 수는 없습니다.

그래서 이걸 대신 기억해주는가 index입니다.

인덱스 생성 : 테이블의 소유자는 인덱스를 생성할수있다. / 별도의 권한이 필요없음

{HR}

인덱스 생성 :
create index emp_idx on emp(employee_id);

인덱스 생성 확인 :
select * from user_indexes where table_name = 'EMP';

생성한 인덱스 정보 확인 :
select * from user_ind_columns where table_name = 'EMP';

1

인덱스는 오름차순으로 정렬되어있음.

 

select * from emp where employee_id = 100;

인덱스 내부에서는 데이터를 어떻게 처리할까?

1) 인덱스에서 employee_id = 100가 있는지 확인하고 (색인 페이지에서 단어를 찾고 쪽번호를 기억)

2) 있으면 emp에서 로우아이디를 찾아다가 검색한다.(쪽번호에 가서 그 단어 찾기)

데이터 처리 순서는 밑에서 위로 해석해야한다.

 

range scan

: 처음 색은 페이지에서 단어를 찾고 쪽번호를 기억하고 쪽번호에 가서 그 단어 찾는다.

그다음 다시 색인 페이지에와서 또 있는지 확인한다. 하지만 데이터가 없다면 쓸데없는 짓한거나 다름없다.

애초에 employee_id는 유일값이므로 또 찾아가야할 이유가 없다.

이때 primary key와 unique key를걸면 unique index가 만들어지는걸 이용해보자.

 

drop index emp_idx;

실행계획 확인 (F10) :
select * from emp where employee_id = 100;

Filter Predicates : 찾는 row가 어느 block에 있는지 모르니까 직접가서 보는 방식(첫번째 블락부터 다 뒤져보는거임/처음부터끝까지 전부 뒤져보기)

Access Predicates : 실제 block을 알고 찾아가는거임

 

primary key걸기 : 
alter table emp add constraint emp_id_pk primary key(employee_id);

 

확인 :
select * from user_constraints where table_name = 'EMP';
select * from user_indexes where table_name = 'EMP';

 

다시 실행계획 확인 (F10) :
select * from emp where employee_id = 100;

 

---

 

unique index 생성

제약조건을 삭제해보자 :
alter table emp drop constraints emp_id_pk;

 

unique index를 생성 :
create unique index emp_id_idx
on hr.emp(employee_id);

 

제약조건을 확인 :
select * from user_constraints where table_name = 'EMP';

해보면 없음. 인덱스를 만든거지 제약조건을 만든게 아니기 때문

 

index를 확인해보면 있음 :
select * from user_indexes where table_name = 'EMP';

 

확인 :
select * from emp where employee_id = 100;

unique index를 걸면 unique 제약조건을 건것과 같은 효과를 준다.

 

여기서 다시 primary key를 추가하면 :
alter table emp add constraint emp_id_pk primary key(employee_id);

select * from user_constraints where table_name = 'EMP';

index가 그대로 사용된다.

unique index를 만들고 primary key를 생성하면 인덱스를 그냥 그대로 가져다가 사용한다.

 

다시 primary key 삭제하기 :
alter table emp drop constraints emp_id_pk;

 

만든 unique index도 삭제하기 :
drop index emp_id_idx;

 

다시 primary key 만들기 :
alter table emp add constraint emp_id_pk primary key(employee_id);

 

확인:
select * from user_constraints where table_name = 'EMP';
select * from user_indexes where table_name = 'EMP';

 

 

index이름과 제약조건 이름을 분리하고 싶을땐

index를 만들고 primary key를 만들면 된다.

 

 

index를 만들면 내부적으로 아래와 같은 코드가 돌아가는 것이다.

select employee_id, rowid
from emp
order by 1;