INDEX 인덱스

VIew table 생성(create or replace view view_name) 하는 것 처럼

create or replace UNIQUE index emp_id_idx on employees (employee_id);

employees 테이블의 employee_id 항목에 대한 emp_id_idx라는 인덱스를 생성한다.

 

 

 

다른 사용자의 테이블 가져오기 / 제약 조건 확인

SQL> grant select on employees to sh;
권한이 부여되었습니다.

SQL> commit;
커밋이 완료되었습니다.

select * from hr.employees
// 이렇게 권한을 부여해준 사용자(hr.)의 이름을 붙여주어야 받은 table을 사용할 수 있다.

---------------------------------------------------------------------------------------

oe 사용자 로그인
grant select on customers to hr;
oe 사용자가 hr 사용자에게 customers table에 대한 권한을 주겠다.

cmd / hr 사용자
SQL> create table cust_index
 2   as select customer_id, cust_last_name from oe.customers;
// 테이블 생성, hr사용자가 oe사용자의 customers table의 customer_id와 last_name을 가져온다.

desc cust_index;

// 제약조건 확인
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'CUST_INDEX';

 

INDEX 사용 여부를 위한 통계 정보

 

OE 관리자 로그인

Execute dbms_stats.gather_table_stats('HR','CUST_INDEX');
 // PL/SQL 처리가 정상적으로 완료되었습니다.
 
 select count(*) from cust_index;
 
 set autotrace on;
 set timing on; // 실행시간 표시
 select cust_last_name from cust_index where customer_id = '158';

 

 

set timing on;	// 실행시간 표시
create unique index cust_id_idx
on cust_index(customer_id)
// 인덱스 생성
EXECUTIVE dbms_stats.gather_table_stats ('HR','CUST_INDEX');
// 안되면,
EXECUTE dbms_stats.gather_table_stats('HR','CUST_INDEX');
PL/SQL 처리가 정상적으로 완료되었습니다.
경과 : 00:00:00.10

set autotrace on;
// 오류
select cust_last_name from cust_index
where customer_id = '158'
// 출력
// Execution Plan 어떠한 과정을 통해서 last_name을 출력했다는 내용

HR 사용자 (INDEX 생성 안함)

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

EMPLOYEE_ID	LAST_NAME	SALARY
120		Weiss		8000
153		Olsen		8000
159		Smith		8000
경 과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation       	  | Name     	 | Rows	   | Bytes | 	  Cost (%CPU)| Time      |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT 	  |          	 |       2 |    32 |   	      3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL	  | EMPLOYEES 	 |       2 |    32 |          3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SALARY"=8000)

INDEX column 확인

select table_name, column_name, index_name from USER_IND_COLUMNS
where table_name = 'EXPLOYEES'
// employees table에서 index column 및 name 확인

TABLE_NAME	COLUMN_NAME		INDEX_NAME
EMPLOYEES	EMAIL			EMP_EMAIL_UK
EMPLOYEES	EMPLOYEE_ID		EMP_EMP_ID_PK
EMPLOYEES	DEPARTMENT_ID	EMP_DEPARTMENT_IX
EMPLOYEES	JOB_ID			EMP_JOB_IX
EMPLOYEES	MANAGER_ID		EMP_MANAGER_IX
EMPLOYEES	LAST_NAME		EMP_NAME_IX
EMPLOYEES	FIRST_NAME		EMP_NAME_IX
7 개의 행이 선택되었습니다.

경 과: 00:00:00.11

Execution Plan
----------------------------------------------------------

INDEX 만든 후, 출력

create index emp_sal_idx
on employees (salary)

인덱스가 생성되었습니다.
경 과: 00:00:00.18           

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

// index 생성 후, 출력 결과가 달라짐
// index 생성 후, 동일하게 select 문을 넣었더니
//  2 |   INDEX RANGE SCAN        	 | EMP_SAL_IDX |  <- index 값이 추가가 됨

EMPLOYEE_ID LAST_NAME                                                                       SALARY
----------- --------------------------------------------------------------------------- ----------
        120 Weiss                                                                             8000
        153 Olsen                                                                             8000
        159 Smith                                                                             8000
경 과: 00:00:00.00
Execution Plan
----------------------------------------------------------

Plan hash value: 3490433412

-------------------------------------------------------------------------------------------
| Id  | Operation                  				 | Name          | 	Rows  |  Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          				 |               |      2 |     32 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES  | 	   	       2 |     32 | 2   (0)|   00:00:01 |
|*  2 |   INDEX RANGE SCAN        				 | 	 EMP_SAL_IDX |      2 |        |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SALARY"=8000)

 

SH 사용자 로그인

create table sales_demo
as select * from sales
where to_char(time_id, 'yyyy') != '1998';
// sales_demo table 생성 index 생성을 위해.

select table_name, column_name, index_name from USER_IND_COLUMNS
where table_name = 'SALES_DEMO';
// 인덱스 확인

create index sales_cust_time_idx
on sales_demo (cust_id, time_id)
// (cust_id, time_id) 두개의 인덱스 생성