[ORACLE] INDEX(인덱스)

이번엔 인덱스를 알아 볼 것 이다.

---

• INDEX(인덱스)
    인덱스는 테이블에 있는 데이터에 보다 빠르게 접근하기 위해서 설계된 기능이다. 인덱스는 테이블과 관련이 있지만 Oracle에서의 데이터베이스 저장 공간(네임스페이스)에 저장되며 Oracle이 자동으로 관리한다. 데이터베이스 튜닝(tuning)은 바로 이 인덱스를 조정하는 작업이라고 해도 과언이 아니다. 인덱스는 테이블의 특정 컬럼(열)에 지정해서 만들어 준다. 
    인덱스는 인덱스로 지정된 컬럼이 속해 있는 레코드에 대한 디스크의 물리적 저장 위치를 나타내는 ROWID와 해당 컬럼 값의 집합으로 이뤄지므로 Oracle은 ROWID로 원하는 테이블의 데이터에 접근한다. 인덱스는 B-Tree 알고리즘으로 데이터 접근법을 구현 한 것이다. 
  
    
• 인덱스는 인덱스로 지정된 열_명을 중심으로 모든 데이터들이 재정렬을 수행하기 때문에 작은 데이터가 있는 곳에서는 도움이 되지만 큰 데이터에서는 index로 지정한 열_명으로 모든 데이터들을 재정렬 시간이 오래 걸리기 때문에 오히려 효율이 떨어진다는 지적도 있다. 조건을 만족하는 대상이 되는 데이터가 전체 데이터에서 차지하는 비율에 따라서 Optimizer는 실행계획을 다르게 하므로 컬럼의 순서에 따라 인덱스의 사용이 달라질 수 있다. 보통 전체에서 2%에 해당되는 정보에 접근해야 할 때에는 인덱스가 유리하다고 한다(employees 테이블의 경우, 총 107명이 있는데, 이중에서 월급이 $10000 이상인 사원들을  쿼리해서 3여명이 예상된다면). 즉, 소규모 데이터를 쿼리할 때에는 인덱스를 사용하는 것이 좋다라는 의미이다.
  →간단히 정리하면 특정 항목에 대한 인덱스를 생성한 뒤 해당 항목으로 쿼리하면 인덱스가 적용되어 쿼리가 빨라진다. 
  
    
• 또 예를 들어서 Index 없이도 
  select * from employees where last_name='Smith'; 보다
  select * from employees where employee_id=111; 이 훨신 나은 쿼리법이다. 
  =>위의 것은 전체 테이블을 스캐닝하기 때문이고, 아래 것은 PK를 index로 해서 rowid로 찾기 때문에 111을 훨씬 빠르게 찾는다.
  
   create or replace view view_name
   ...... 하듯이 
   create or replace UNIQUE index emp_id_idx
   on employees (employee_id); 하면 employees 테이블의  employee_id 항목에 대한 emp_id_idx라는 인덱스를 생성한다. 
  
  
•   Index 사용 여부를 위한 통계정보
    통계정보는 Oracle Optimizer가 SQL 쿼리에 대해서 가장 최적의 데이터를 추출할 수 있는 경로를 결정해주는 오라클의 내부구조이다. 여기에는 Oracle이 미리 정한 일정한 순서대로 실행되는 규칙_기반과 분석된 데이터베이스의 현재 상태를 기반으로 비용(cost)이 가장 적게 드는 실행 경로로 실행되는 비용_기반이 있다. 일반적으로 비용_기반이 규칙_기반보다 더 나은 성능을 제공한다고 한다. 관리자가 인덱스로 작업하는 경우는 대부분 비용_기반으로 작업하게 된다.
    이 비용을 알아내기 위해서 통계정보가 필요하다.
  
  
• a. 이어서 통계정보를 위해서 customers_index로부터 보다 나은 실행 계획을 얻을 수 있도록 설정하는데
  EXECUTE dbms_stats.gather_table_stats('HR', 'CUSTOMERS_INDEX'); 해주면 ‘PL/SQL 처리가 정상적으로 완료되었습니다’로 메시지를 보인다. 
• b. select count(*) from customers_index; 해서 이 테이블이 가지고 있는 객체수를 보면 321건의 데이터를 가지고 있다. 데이터 수가 적기 때문에 index를 생성하는 것이 좋다!!

---

●예제

예를 들어서 index 없이도 
select * from employees where last_name='Smith'; 보다
select * from employees where employee_id=111; 이 훨씬 나은 쿼리법이다.
=> 위의 것은  전체 테이블 스캐닝하기 때문이고, 아래것은 PK를 index로 해서 rowid로 찾기 때문에 111을 훨씬 빠르게 찾는다.

grant select on customers to hr; 권한 넘기기// 관리자 권한은 사용자에서 줄수 없다.

coomit; 으로 완료 시키고

create table cust_index
AS select customer_id, cust_last_name from oe.customers;// hr 에서 oe 테이블 select 가능

■index 사용여부를 위한 통계정보■
●set autotrace on;
●set timing on;
●select cust_last_name from cust_index
where customer_id = 150;

●create unique index cust_id_idx
on cust_index (customer_id); // 저장 공간 할당

set autotrace on;
set timing on;
select cust_last_name from cust_index
where customer_id = 150;

create unique index cust_id_idx
on cust_index (customer_id); // 저장 공간 할당

EXECUTE dbms_stats.gather_table_stats('HR', 'CUST_INDEX);

●select cust_last_name from cust_index
where customer_id = 150;

●set autotrace on;
●set timing on;
●select employee_id, last_name, salary from employees
where salary = 8000;

●select table_name, column_name, index_name from USER_IND_COLUMNS
where table_name = 'EMPLOYEES';

EXECUTE dbms_stats.gather_table_stats('HR', 'CUST_INDEX);

select cust_last_name from cust_index
where customer_id = 150;

set autotrace on;
set timing on;
select employee_id, last_name, salary from employees
where salary = 8000;

select table_name, column_name, index_name from USER_IND_COLUMNS
where table_name = 'EMPLOYEES';

●CREATE INDEX emp_sal_idx
ON EMPLOYEES (salary);// 사원 인덱스를 월급 기준으로 만듬

●set autotrace on;
●execute dbms_stats.gather_table_stats('HR', 'EMPLOYEES');

CREATE INDEX emp_sal_idx
ON EMPLOYEES (salary);// 사원 인덱스를 월급 기준으로 만듬

set autotrace on;
execute dbms_stats.gather_table_stats('HR', 'EMPLOYEES');

● select table_name, column_name, index_name from USER_IND_COLUMNS
where table_name = 'EMPLOYEES';

 

●create index emp_sal_idx
on employees (salary);

 

●set autotrace on;
●execute dbms_stats.gather_table_stats('HR', 'EMPLOYEES');  

 select table_name, column_name, index_name from USER_IND_COLUMNS
where table_name = 'EMPLOYEES';
create index emp_sal_idx
on employees (salary);
set autotrace on;
execute dbms_stats.gather_table_stats('HR', 'EMPLOYEES');  */

●select employee_id, last_name, salary from employees
where salary = 8000;

select employee_id, last_name, salary from employees
where salary = 8000;

●create table sales_demo
as select * from sales
where to_char(time_id, 'yyyy') !='1998';

● create table sales_demo
as select * from sales

where to_char(time_id, 'yyyy') !='1998'; 

 

●select table_name, column_name, index_name fromuser_ind_columns
where table_name = 'SALES_DEMO'; 

 

●create index sales_cust_time_idx
on sales_demo (cust_id, time_id);

 

●select table_name, column_name, index_name from user_ind_columns
where table_name = 'SALES_DEMO'; 

●set autotrace on explain;
set timing on;

●select * from sales_demo
where cust_id = 12774 and time_id = '99/01/01';

create table sales_demo
as select * from sales
where to_char(time_id, 'yyyy') !='1998';

/* create table sales_demo
as select * from sales
where to_char(time_id, 'yyyy') !='1998'; 
select table_name, column_name, index_name fromuser_ind_columns
where table_name = 'SALES_DEMO'; 
create index sales_cust_time_idx
on sales_demo (cust_id, time_id);*/
select table_name, column_name, index_name from user_ind_columns
where table_name = 'SALES_DEMO'; 

set autotrace on explain;
set timing on;

select * from sales_demo
where cust_id = 12774 and time_id = '99/01/01';