문제581. c##scott 유져가 소유하고 있는 인덱스 리스트를 확인하시오 !
select *
from user_indexes;
select index_name, table_name, uniqueness
from user_indexes
where table_name ='EMP';
- 특정 테이블의 인덱스 리스트를 확인: SQLdeveloper ---> 테이블 탐색기 ---> emp ----> 인덱스 클릭 emp 테이블에 emp_hiredate 인덱스가 존재하는지 확인했습니다.
문제582. 아래의 SQL을 튜닝하시오 !
튜닝전: select ename, hiredate
from emp
where to_char(hiredate,'RR/MM/DD') = '81/12/11';
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 32 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 2 | 32 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
튜닝후: select ename, hiredate
from emp
where hiredate = to_date('81/12/11','RR/MM/DD');
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 32 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 2 | 32 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_HIREDATE | 2 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------설명: where 절에 인덱스 컬럼을 가공하면 인덱스를 사용할 수 없고 full table scan 을 하게 됩니다.
- 인덱스의 구조 : 1. 컬럼값 + rowid
2. 컬럼값은 ascending 하게 정렬이됨 오라클에서는 과도한 정렬작업을 수행하게 되면 성능이 느려집니다.
오라클이 정렬작업을 하려면 별도의 메모리에서 정렬 작업을 수행합니다.
그 메모리 공간이 넉넉하지 않기 때문에 과도한 정렬 작업이 들어오면
성능이 느려집니다. 정렬작업을 최소화 할 수 있게 SQL 튜닝을 해줘야 합니다.
이미 정렬되어있는 인덱스의 데이터를 이용하면 됩니다.
문제583. 아래의 SQL을 튜닝하시오 ! ( 인덱스의 구조를 이해하기 위한 문제 )
튜닝전 : select ename, sal
from emp
order by sal asc ;
순서를 정렬하는 것이 성능을 떨어뜨린다. pmom **PGA라는 것이 정렬**을 한다...
정렬을 메모리 공간(PGA)이 협소해서 잘못하기 때문에 디스크로 내려서
정렬하고 왔다갔다 하면서 DISK I/O가 발생한다.
하나의 세션에서 사용할 수 있는 PGA의 영역이 크지 않다.
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 280 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 280 | 4 (25)| 00:00:01 | --------> 성능이 저하된다.
| 2 | TABLE ACCESS FULL| EMP | 14 | 280 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
튜닝후 : select /*+ index_asc(emp emp_sal) */ ename, sal
from emp
where sal >=0 ;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 280 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 14 | 280 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_SAL | 14 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
설명: **이미 인덱스는 데이터를 정렬해서 저장하고 있으므로 인덱스에서
정렬된 데이터를 읽어오는게 더 성능이 좋습니다.**
order by 를 수행하는것보다 더 성능이 좋습니다.1. 힌트 /*+ index_asc(테이블명 인덱스 이름) */ : 인덱스를 ascending 하게 스캔
2. 힌트 /*+ index_desc(테이블명 인덱스 이름) */ : 인덱스를 descending 하게 스캔문제584. 아래의 SQL을 튜닝하시오 !
튜닝전: select ename, job, sal
from emp
where job='SALESMAN'
order by sal desc ;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 104 | 3 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 4 | 104 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 4 | 104 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_JOB | 4 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
튜닝후: select /*+ index_desc(emp emp_sal) */ ename, job, sal
from emp
where sal >= 0 and job='SALESMAN';
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 104 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 4 | 104 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING | EMP_SAL | 13 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------문제585. 아래의 SQL을 튜닝하시오 !
튜닝전: select ename, hiredate
from emp
order by hiredate desc ;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 224 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 224 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 224 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
튜닝후: select /*+ index_desc(emp emp_hiredate) */ ename, hiredate
from emp
where hiredate < to_date('9999/12/31','RRRR/MM/DD');
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 208 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 13 | 208 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING | EMP_HIREDATE | 13 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
**※ where 절에 검색하고자 하는 컬럼명이 반드시 있어야 관련된 힌트가 작동합니다.
※ 인덱스의 데이터를 스캔하기 위해 WHERE 절에 해당 인덱스 컬럼이
있어야하는데 인덱스의 데이터를 읽어오기 위한 조건들**
1. 문자 > ' '
2. 숫자 >= 0
3. 날짜 < to_date('9999/12/31', 'RRRR/MM/DD')※ 인덱스의 구조를 위한 SQL튜닝 방법 :
1. where 절의 좌변을 가공하지 말아라
2. order by 절 대신에 인덱스를 이용해라 !- 인덱스가 생성되는 방식 2가지 ? p11-36
1. 수동으로 : create index emp_deptno
on emp(deptno);
2. 자동으로 : primary key 제약이나 unique 제약을 걸면
제약(contraint)은 테이블의 데이터의 품질을 높이기 위해서
반드시 필요한 db 기능입니다. **primary key 제약을 생성한 컬럼은
중복된 데이터와 null 값이 입력안됩니다.**- emp 테이블에 empno 에 primary key 제약을 생성합니다.
alter table emp
add constraint emp_empno_pk primary key(empno);
insert into emp(empno, ename, sal)
values( null, 'jane', 4000) ;
ORA-01400: NULL을 ("C##SCOTT"."EMP"."EMPNO") 안에 삽입할 수 없습니다insert into emp(empno, ename, sal )
values( 7788, 'jack', 5000);
ORA-00001: 무결성 제약 조건(C##SCOTT.EMP_EMPNO_PK)에 위배됩니다- emp 테이블과 관련해서 생성된 인덱스를 확인하시오 !
select index_name, uniqueness
from user_indexes
where table_name='EMP';
EMP_EMPNO_PK UNIQUE <- 중복된 data 가 없어야 걸리는 제약
중복된 data 가 없어야 생선되는 인덱스
- 우리반 테이블의 ename 에 unique 제약을 거시오 !
alter table emp17
add constraint emp17_ename_un unique(ename);- 우리반 테이블에 ename 에 인덱스가 있는지 확인하시오 !
select index_name, uniqueness
from user_indexes
where table_name='EMP17';문제586. 우리반 테이블에서 이름이 김동휘 학생의 이름과 나이와 주소를
출력하고 그 쿼리문의 실행계획을 확인하시오 !
explain plan for
select ename, age, address
from emp17
where ename='김동휘';
select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP17 | 1 | 33 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP17_ENAME_PK | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------select ename, rowid
from emp17
where ename > ' ';
김기찬 AAASQ/AAHAAAACnAAD
김동휘 AAASQ/AAHAAAACnAAM
김정명 AAASQ/AAHAAAACnAAC
김준혁 AAASQ/AAHAAAACnAAP
김지혜 AAASQ/AAHAAAACnAAI
김하람 AAASQ/AAHAAAACnAAG
김희선 AAASQ/AAHAAAACnAAT
서원길 AAASQ/AAHAAAACnAAH
송현민 AAASQ/AAHAAAACnAAJ
신예환 AAASQ/AAHAAAACnAAA
안초룡 AAASQ/AAHAAAACnAAQ
양희림 AAASQ/AAHAAAACnAAL
윤성해 AAASQ/AAHAAAACnAAF
윤영민 AAASQ/AAHAAAACnAAB
이상화 AAASQ/AAHAAAACnAAS
이승휘 AAASQ/AAHAAAACnAAE
이해인 AAASQ/AAHAAAACnAAK
이효진 AAASQ/AAHAAAACnAAO
정범우 AAASQ/AAHAAAACnAAR
최서우 AAASQ/AAHAAAACnAAN설명: unique 인덱스는 이름의 중복된 데이터가 없음이 확실히 보장된
인덱스이므로 김동휘 하나의 인덱스 행만 검색하고 바로 테이블 엑세스를
하러 가고 끝납니다. 그러나 non unique 인덱스이면 그 다음행인 김정명
행을 엑세스 합니다.
그래서 옵티마이져가 아래와 같은 SQL이 있고 2개의 인덱스가 있으면
unique 인덱스를 더 선호합니다.
select ename, age, address
from emp17
where age= 33 and ename='김동휘';
↑ ↑
create index emp17_age alter table emp17
on emp17(age) ; add constraint emp17_un unique(ename);문제587. 여러분 자리에 옵티마이져가 똑똑한 옵티마이져인지 확인하시오 !
create index emp17_age
on emp17(age);
alter table emp17
add constraint emp17_un unique(ename);
확인 한번 해보세요.
select index_name, table_name, uniqueness
from user_indexes; -> 확인
explain plan for
select ename, age, address
from emp17
where age= 33 and ename='김동휘';
select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP17 | 1 | 33 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP17_UN | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
※ 옵티마이져는 더 좋은 인덱스를 엑세스를 하는데 만약 덜 좋은 인덱스를
엑세스 했다면 dba 가 힌트를 줘서 튜닝을 해주면 됩니다.
문제588. 아래의 SQL의 실행계획이 emp17_age 인덱스를 엑세스 하도록
힌트를 주고 실행계획을 확인하시오 !
select **/*+ index(emp17 emp17_age) */** ename, age, address
from emp17
where age= 33 and ename='김동휘';
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP17 | 1 | 33 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP17_AGE | 2 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------설명: 여러개의 인덱스중에 특정 인덱스를 사용하게 하고 싶을 때
아래의 index 힌트를 이용합니다.
/*+ index( 테이블명 인덱스이름) */- 인덱스 생성 (p 11-37 )
* 데이터의 값이 unique 한지 unique 하지 않은지에 따라 2가지 ?
1. unique index : primary key 제약 또는 unique 제약을 걸었을때
alter table emp17
add constraint emp17_un unique(empno)
primary key(empno)
또는
create unique index emp17_empno
on emp17(empno);
2. non unique index : create index emp_job
on emp(job);
- 인덱스를 구성하려는 컬럼의 갯수에 따라
1. 단일 컬럼 인덱스 : 인덱스를 구성하는 컬럼의 갯수 1개
문법: create index emp_sal
on emp(sal);
2. 결합 컬럼 인덱스 : 인덱스를 구성하는 컬럼의 갯수가 2개 이상
문법 : create index emp_job_sal
on emp(job, sal) ;SQL> @init_emp.sql <--- 이 스크립트를 수행하면 emp 이 테이블이
drop 되어서 emp 테이블과 연관된 인덱스들도 다 drop 됩니다.
select *
from user_indexes
where table_name='EMP';
예제. 아래의 결합 컬럼 인덱스를 생성하고 이 결합 컬럼 인덱스의 구조를 확인
하시오 !
create index emp_job_sal
on emp( job, sal );
예제. 위의 결합 컬럼이 인덱스의 구조를 확인하시오 !
인덱스 구조 : 1. 컬럼값 + rowid
2. 컬럼값이 ascending 하게 정렬되어있습니다.
select job, sal, rowid
from emp
where job > ' ';
JOB SAL ROWID
------------------ ---------- ------------------
ANALYST 3000 AAASUvAAHAAAYZtAAJ
ANALYST 3000 AAASUvAAHAAAYZtAAL
CLERK 800 AAASUvAAHAAAYZtAAK
CLERK 950 AAASUvAAHAAAYZtAAH
CLERK 1100 AAASUvAAHAAAYZtAAM
CLERK 1300 AAASUvAAHAAAYZtAAN
MANAGER 2450 AAASUvAAHAAAYZtAAC
MANAGER 2850 AAASUvAAHAAAYZtAAB
MANAGER 2975 AAASUvAAHAAAYZtAAD
PRESIDENT 5000 AAASUvAAHAAAYZtAAA
SALESMAN 1250 AAASUvAAHAAAYZtAAE
SALESMAN 1250 AAASUvAAHAAAYZtAAI
SALESMAN 1500 AAASUvAAHAAAYZtAAG
SALESMAN 1600 AAASUvAAHAAAYZtAAFdrop index emp_job_sal
SQL > @init_emp.sql
create table emp_num
as
select empno, ename, sal
from emp
where 1 = 2 ;
create sequence seq5
increment by 1
start with 1
maxvalue 10000
nocycle
cache 20;
insert into emp_num
select seq5.nextval, ename, sal
from emp;
select * from emp_num;문제589. emp 테이블의 job 컬럼에 인덱스를 생성하시오 !
( 인덱스 이름은 emp_job 으로 하세요 )
create index emp_job
on emp(job);
문제590. 직업이 CLERK 인 사원들의 직업과 월급을 출력하는 쿼리문의
실행계획을 확인하시오 !
explain plan for
select job, sal
from emp
where job='CLERK';
select * from table(dbms_xplan.display);
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 76 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 4 | 76 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_JOB | 4 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
직업이 CLERK인 사원을 찾는데, 직업 뿐만아니라 sal도 찾아야 되기 때문에 index 뿐만 아니라
table ACCESS도 필수적으로 이루어 져야 한다.
JOB ROWID
------------------ ------------------
ANALYST AAASUyAAHAAAYZ9AAJ
ANALYST AAASUyAAHAAAYZ9AAL
CLERK AAASUyAAHAAAYZ9AAH
CLERK AAASUyAAHAAAYZ9AAK
CLERK AAASUyAAHAAAYZ9AAM
CLERK AAASUyAAHAAAYZ9AAN
MANAGER AAASUyAAHAAAYZ9AAB
MANAGER AAASUyAAHAAAYZ9AAC
MANAGER AAASUyAAHAAAYZ9AAD
PRESIDENT AAASUyAAHAAAYZ9AAA
SALESMAN AAASUyAAHAAAYZ9AAE
SALESMAN AAASUyAAHAAAYZ9AAF
SALESMAN AAASUyAAHAAAYZ9AAG
SALESMAN AAASUyAAHAAAYZ9AAI
ROWID JOB SAL
------------------ ------------------ ----------
AAASUyAAHAAAYZ9AAA PRESIDENT 5000
AAASUyAAHAAAYZ9AAB MANAGER 2850
AAASUyAAHAAAYZ9AAC MANAGER 2450
AAASUyAAHAAAYZ9AAD MANAGER 2975
AAASUyAAHAAAYZ9AAE SALESMAN 1250
AAASUyAAHAAAYZ9AAF SALESMAN 1600
AAASUyAAHAAAYZ9AAG SALESMAN 1500
AAASUyAAHAAAYZ9AAH CLERK 950
AAASUyAAHAAAYZ9AAI SALESMAN 1250
AAASUyAAHAAAYZ9AAJ ANALYST 3000
AAASUyAAHAAAYZ9AAK CLERK 800
AAASUyAAHAAAYZ9AAL ANALYST 3000
AAASUyAAHAAAYZ9AAM CLERK 1100
AAASUyAAHAAAYZ9AAN CLERK 1300
설명: 위의 SQL의 실행계획은 JOB 에 대한 DATA 는 emp_job 에 있지만
select 절에서 요구하는 sal 에 대한 data 는 emp_job 에 없으므로
emp 테이블을 엑세스 하러 가야 하는 실행계획이 나왔습니다.
만약 인덱스에 sal 까지도 다 구성했으면 테이블 엑세스하러 갈 필요가
없었을 것 입니다.문제591. emp 테이블에 job 과 sal 에 다음과 같이 결합 컬럼 인덱스를
생성하시오 !
create index emp_job_sal
on emp(job, sal);
문제592. 아래의 SQL의 실행계획을 다시 확인하시오 !
explain plan for
select job, sal
from emp
where job='CLERK';
select * from table(dbms_xplan.display);
1. emp_job
2. emp_job_sal
둘중에 어떤 인덱스를 탈지 궁금하다 -> 당연히 아래와 같이 emp_job_sal을 탄다!!
(table access를 하지 않고 그냥 인덱스만 읽고 끝난다! 속도가 더 빠르다!)
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 76 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EMP_JOB_SAL | 4 | 76 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
설명: emp_job_sal 결합 컬럼 인덱스에서 job 과 sal 에 대한 data 를
다 얻어냈기 때문에 emp 테이블을 엑세스 하지 않는 실행계획이
나왔습니다.
인덱스 구조 확인
select job, sal, rowid
from emp
where job > ' ';
JOB SAL ROWID
------------------ ---------- ------------------
ANALYST 3000 AAASUyAAHAAAYZ9AAJ
ANALYST 3000 AAASUyAAHAAAYZ9AAL
CLERK 800 AAASUyAAHAAAYZ9AAK
CLERK 950 AAASUyAAHAAAYZ9AAH
CLERK 1100 AAASUyAAHAAAYZ9AAM
CLERK 1300 AAASUyAAHAAAYZ9AAN
MANAGER 2450 AAASUyAAHAAAYZ9AAC
MANAGER 2850 AAASUyAAHAAAYZ9AAB
MANAGER 2975 AAASUyAAHAAAYZ9AAD
PRESIDENT 5000 AAASUyAAHAAAYZ9AAA
SALESMAN 1250 AAASUyAAHAAAYZ9AAE
SALESMAN 1250 AAASUyAAHAAAYZ9AAI
SALESMAN 1500 AAASUyAAHAAAYZ9AAG
select empno, ename, sal, job, deptno
from emp
where empno = 7788;
create index emp_idx7
on emp(empno, ename, sal, job, deptno);
문제593. emp 테이블과 관련된 인덱스를 조회하시오 !
select index_name, uniqueness
from user_indexes
where table_name = ‘EMP’;
문제594. 아래의 SQL을 튜닝하시오 !
튜닝전 : select ename, job, sal
from emp
where job='CLERK'
order by sal desc;
튜닝후: select /+ index_desc(emp emp_job_sal) / ename, job, sal
from emp
where job='CLERK' and sal > ‘ ‘; --> 결합컬럼의 index에서 where 절에 job 이 잘 있으므로.. 쓸필요가 없다. 힌트과 검색조건을 다 썼기 때문에..
문제595. deptno 와 sal 을 결합 컬럼 인덱스로 생성하시오 !
( 인덱스 이름은 emp_deptno_sal 로 하시오 )
create index emp_deptno_sal
on emp( deptno, sal );
문제596. emp_deptno_sal 인덱스의 구조를 확인하시오 !
**인덱스의 구조 : 1. 컬럼값 + rowid
2. 컬럼값이 ascending 하게 정렬이 됨**
select deptno, sal, rowid
from emp
where deptno >= 0;
DEPTNO SAL ROWID
---------- ---------- ------------------
10 1300 AAASUyAAHAAAYZ9AAN
10 2450 AAASUyAAHAAAYZ9AAC
10 5000 AAASUyAAHAAAYZ9AAA
20 800 AAASUyAAHAAAYZ9AAK
20 1100 AAASUyAAHAAAYZ9AAM
20 2975 AAASUyAAHAAAYZ9AAD
20 3000 AAASUyAAHAAAYZ9AAJ
20 3000 AAASUyAAHAAAYZ9AAL
30 950 AAASUyAAHAAAYZ9AAH
30 1250 AAASUyAAHAAAYZ9AAE
30 1250 AAASUyAAHAAAYZ9AAI
30 1500 AAASUyAAHAAAYZ9AAG
30 1600 AAASUyAAHAAAYZ9AAF
30 2850 AAASUyAAHAAAYZ9AAB문제597. 아래의 SQL을 튜닝하시오 ! ( order by 절을 사용하지 말고 수행하시오)
emp_deptno_sal 인덱스를 활용
튜닝전 : select ename, deptno, sal
from emp
where deptno = 20
order by sal desc;
튜닝후:
explain plan for
select /+ index_desc( emp emp_deptno_sal ) / ename, deptno, sal
from emp
where deptno = 20;
select * from table(dbms_xplan.display);
■ 인덱스 생성 지침
- 다음의 경우에 인덱스를 생성합니다.
1. 테이블의 크기가 클때 :
EMP 테이블 처럼 작은 테이블은 인덱스가 필요없습니다.
인덱스 없이도 검색속도가 빠릅니다.
2. null 값이 많은 컬럼에 인덱스를 걸면 좋다.
인덱스 구성시 null 값은 구성되지 않습니다. 책에 빈페이지는 목차에 구성 안됩니다.
3. where 절에 자주 검색되는 컬럼에 인덱스를 걸어줘야 합니다.
select empno, ename, sal
from emp
where ename='SCOTT';
select job, deptno, ename
from emp
where ename='ALLEN';
4. 검색하려는 데이터의 행이 전체 테이블의 2~4% 미만인 컬럼에
인덱스를 거는게 유용하다.- 다음의 경우는 인덱스를 생성하지 마세요 !
1. where 절에 자주 검색되지 않는 컬럼
2. 테이블이 작거나 검색하는 행이 테이블 전체의 2~4% 이상의 컬럼
3. **자주 갱신되는 컬럼(★) (특히 insert)**
테이블의 데이터를 변경하면 인덱스도 같이 변경해줘야합니다.
책의 내용을 변경하면 목차도 다시 변경해야합니다.
그런데 책은 그냥 변경하면 간단하지만 목차는 ㄱㄴㄷ 순서이기 때문에
변경할 때 시간이 많이 걸립니다.
인덱스가 있는 테이블에 insert, update 를 하면 insert, update 속도가 느려집니다
4. 인덱스화 된 열이 컬럼에 표현식으로 사용되는 경우 (가공이 된 경우)
select ename, sal
from emp
where sal * 12 = 36000;문제598. 인덱스가 생성되어있는 테이블과 생성되어있지 않은 테이블에
대량의 데이터를 insert 할때 속도를 확인하시오 !
-
다음 2개의 테이블을 생성합니다.
create table sales_no_index
as
select * from sales where 1 = 2; ---> 구조만 만든다!!!!create table sales_with_index
as
select * from sales where 1 = 2 ; -
위의 2개의 테이블중에 sales_with_index 테이블에만 인덱스를 생성하시오
create index sales_index_1
on sales_with_index(amount_sold, promo_id); -
아래와 같이 대량의 데이터를 2개 테이블에 각각 입력하고 속도를 비교하시오!
set timing on
insert into sales_no_index
select *
from sales;경 과: 00:00:00.57
insert into sales_with_index
select *
from sales;경 과: 00:00:01.84
설명: 테이블에 인덱스가 있으면 insert 가 느려집니다.
문제599. emp 테이블에 관련된 인덱스가 무엇인지 조회하시오 !
select index_name
from user_indexes
where table_name='EMP';
문제600. emp 테이블과 관련된 모든 인덱스를 다 삭제하시오 !
drop index emp_job;
drop index emp_job_sal;
drop index emp_idx7;
drop index emp_deptno_sal;
설명: emp 테이블을 삭제하면 emp 테이블과 관련된 인덱스들도 다 삭제 됩니다.
▣ 099 동의어(synonym)
동의어 ? 특정 객체에 대한 다른 이름
c##scott (dba) <-- 테이블을 만든 사람은 해당 테이블에 drop 권한도 있습니다.
emp
dept
c##smith ( 개발자 ) <-- emp 와 dept 테이블에 대해서 select , update ,
insert 정도의 권한만 줘야합니다.문제601. c##scott 유져에서 c##smith 유져를 만들고 접속할 수 있는 권한을
부여하고 c##smith 유져로 접속하시오 !
create user c##smith
identified by tiger ;
grant connect to c##smith;
--> select * from c##scott.emp; 유저명이 노출된다.
grant select on emp to c##smith;
create public synonym emp
for c##scott.emp;
설명: 개발 dba 가 emp 테이블을 생성했으면 위와 같이 synonym 도 생성합니다.
synonym 을 만들어야 c##smith 유져가 emp 테이블 select 할때
테이블명 앞에 c##scott 을 접두어로 안붙여도 되기 때문입니다
문제602. c##smith 유져가 다음과 같이 dept 테이블과 saglrade 테이블을
조회할 수 있게 하시오 !
select from dept;
select from salgrade;
답:
grant select on dept to c##smith;
grant select on salgrade to c##smith;
create public synonym dept for c##scott.dept;
create public synonym salgrade for c##scott.salgrade;
문제603. 시너님을 생성하는데 employees 를 e 로 생성하시오 !
설명: c##scott 만 사용한 시너님이면 public 을 안 써도 됩니다.
create synonym e
for employees;
select * from e;
설명: 별도의 테이블이 생성되는게 아니라 employees 테이블에 대한 다른 이름이
생성되는것 입니다.
※ 개발dba 로 일할때 시너님 생성 tip
hr 계정이 가지고 있는 모든 테이블들을 c##smith 유져가 select 할 수 있게 하시오
grant select on dept to c##smith;
grant select on salgrade to c##smith;
:
create public synonym dept for c##scott.dept;
create public synonym salgrade for c##scott.salgrade;
:
예제1. hr 계정이 소유하고 있는 테이블 리스트를 확인하시오 !
select table_name
from dba_tables
where owner='HR';
예제2. hr 계정의 7개의 테이블을 select 할 수 있는 권한을 c##smith 에게
부여하는 스크립트를 생성하시오 !
select 'grant select on hr.' || table_name || ' to c##smith; '
from dba_tables
where owner='HR';
grant select on hr.REGIONS to c##smith;
grant select on hr.COUNTRIES to c##smith;
grant select on hr.LOCATIONS to c##smith;
grant select on hr.DEPARTMENTS to c##smith;
grant select on hr.JOBS to c##smith;
grant select on hr.EMPLOYEES to c##smith;
grant select on hr.JOB_HISTORY to c##smith;
예제3. 이번에는 public synonym 을 생성하시오 !
select ' create public synonym ' || table_name || ' for hr.' || table_name || ';'
from dba_tables
where owner='HR';
create public synonym REGIONS for hr.REGIONS;
create public synonym COUNTRIES for hr.COUNTRIES;
create public synonym LOCATIONS for hr.LOCATIONS;
create public synonym DEPARTMENTS for hr.DEPARTMENTS;
create public synonym JOBS for hr.JOBS;
create public synonym EMPLOYEES for hr.EMPLOYEES;
create public synonym JOB_HISTORY for hr.JOB_HISTORY;
문제604. employees 시너님을 삭제하시오 !
drop public synonym employees;
▣ 099 실수로 지운 데이터 복구하기 1(FLASHBACK QUERY)
▣ 100 실수로 지운 데이터 복구하기 2(FLASHBACK TABLE)
▣ 101 실수로 지운 데이터 복구하기 3(FLASHBACK DROP)
▣ 102 실수로 지운 데이터 복구하기 4(FLASHBACK VERSION QUERY)
▣ 103 실수로 지운 데이터 복구하기 5(FLASHBACK TRANSACTION QUERY)
▣ 104 데이터의 품질 높이기 1(PRIMARY KEY)
FUND1 → sg2 p.80
- 제약(contraint) ? 테이블에 data의 품질을 높이기 위해서 제한거는 기능 예: 데이터가 함부로 엉뚱한 데이터로 입력되지 않게하고
null 값이나 중복된 data 를 허용하지 못하게 막는 기능
- 제약의 종류 5가지?
1. primary key : 중복된 data 와 null을 허용하지 못하게 막는 제약
2. unique : 중복된 data 를 허용하지 못하게 막는 제약
3. not null : null 값을 허용하지 못하게 막는 제약
4. check : 지정된 data 만 입력되고 수정될 수 있도록 막는 제약
5. foreign key : 자식키에 해당하는 컬럼에 거는 제약오늘의 마지막 문제를 풀기위한 환경을 다음과 같이 구성합니다.
명령 프롬프트창 열고 @init_emp.sql 를 돌려주세요 !
문제605. 사원 테이블의 월급이 인덱스를 걸고 아래의 SQL의 MAX 함수
사용하지 말고 결과를 출력하시오 !
( order by 뿐만 아니라 max와 min 은 정렬작업이 내부적으로 발생
하므로 max 를 쓰지 말고 인덱스를 활용하게 해야합니다.)
create index emp_sal on emp(sal);
튜닝전 : select max(sal)
from emp;
튜닝후: select /+ index_desc( emp emp_sal) / sal
from emp
where sal >= 0 and rownum = 1;
문제606. (오늘의 마지막 문제) 아래의 SQL을 튜닝하시오 !
튜닝전: select /+ gather_plan_statistics / ename, sal
from emp
where sal = ( select max(sal)
from emp );
SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
버퍼의 갯수 : 3개
튜닝후: SQL 과 버퍼의 갯수
select /+ gather_plan_statistics index_desc(emp emp_sal) / ename, sal
from emp
where sal >= 0 and rownum = 1;
SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
