index 생성, 확인 실습

select ic.index_name, ic.column_name, ic.column_position col_pos, ix.uniqueness, ic.table_name

from user_indexes ix, user_ind_columns ic

where ic.index_name = IX.index_name

and ic.table_name = ix.table_name;

 

--index 칼럼 확인

select *

from user_ind_columns

where table_name = upper('departments2')

order by table_name;

  

create table employees3 as select * from employees;

 

--제약조건 확인

select *

from user_constraints

where table_name = upper('employees2');

 

--employees2 : employee_id 컬럼에 pk설정

alter table employees2

add constraint employees2_employee_id_pk primary key(employee_id);

 

--인덱스 생성

create index employees2_last_name_idx

on employees2(last_name);

 

create table departments2 as select * from departments;

 

--departments2 : department_id에 fk 설정

--우선 department_id를 primary key로 설정

alter table departments2

add constraint departments2_department_id_pk primary key(department_id);

 

alter table employees2

add constraint employees2_department_id_fk foreign key(department_id)

references departments2(department_id) on delete cascade;

 

--full scan

select *

from employees3

where employee_id = 198;

 

--index 사용

select *

from employees2

where employee_id = 198;

 

select *

from employees2

where last_name like 'K%';

 

select *

from employees e, departments d

where e.department_id = d.department_id

and e.employee_id = 198;