[.dmp 파일 imp하기]
1. SCOTT 유저 삭제
drop user scott cascade;2. 새로 SCOTT 생성
create user scott identified by oracle quota unlimited on users;
grant create session to scott;
grant connect, resource, dba to scott;
alter user scott quota unlimited on system;3. imp 수행 (cmd에서)
imp system/oracle full=y file=SCOTT.DMP4. 데이터 확인
select table_name from dba_tables where owner = 'SCOTT';5. 사이즈 확인
select segment_name, bytes/1024/1024 as "SIZE"
from dba_segments
where owner='SCOTT'
order by 2 desc;[index suppressing error]
index가 설계되어 있음에도 index를 스캔하지 못하는 현상
1. 데이터 타입 불일치
2. 인덱스 구성 컬럼 변형
3. null에 대한 비교 시
4. 부정 연산자(not between 제외)
5. 시작을 모르는 like 연산자(맨 앞이 _ %)
실습
예제1) 수주날짜가 2006년 1월 16일인 수량 전체 총합을 구하라.
select *
from scott.s_suju;
alter session set statistics_level = all;
-- 인덱스 현황 조회
select i1.owner, i1.index_name, i1.table_name, i2.column_name, i2.column_position
from dba_indexes i1, dba_ind_columns i2
where 1=1
and i1.owner = i2.index_owner
and i1.index_name = i2.index_name
and i1.table_name='S_SUJU'
order by i1.owner, i1.index_name, i2.column_position;
-- 인덱스 현황 조회 (인덱스별로 컬럼 순서대로 결합하여 출력)
select i1.owner, i1.index_name, i1.table_name, listagg(i2.column_name,',') within group(order by i2.column_position) as 인덱스상세,
'create index '||i1.owner||'.'||i1.index_name||' on '||i1.owner||'.'||i1.table_name||'('||listagg(i2.column_name,',') within group(order by i2.column_position)||');'
from dba_indexes i1, dba_ind_columns i2
where 1=1
and i1.owner = i2.index_owner
and i1.index_name = i2.index_name
and i1.table_name='S_SUJU'
group by i1.owner, i1.index_name, i1.table_name
order by i1.owner, i1.index_name;
-- 데이터 타입 불일치
select sum(suryang)
from scott.s_suju
where jumun_dt=20060116;
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced'));
-- 데이터 타입 일치
select sum(suryang)
from scott.s_suju
where jumun_dt='20060116'; -- 데이터 타입 일치시켜야 인덱스를 사용함.
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced'));
예제2) 주문날짜가 2006년 1월인 수주 수량 전체 총합
-- between 사용
select sum(suryang)
from scott.s_suju
where jumun_dt between '20060101' and '20060131';
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced'));
-- like 사용
select sum(suryang)
from scott.s_suju
where jumun_dt like '200601%';
-- 문자 타입 컬럼의 범위 연산은 between 보다 like가 더 유리하다.
-- between은 범위 검색을 한 뒤에 index range scan을 하지만 like는 index range scan만 하기 때문.
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced'));
— 예제3) scott.big_emp 에서 comm이 null이 아닌 직원의 이름, 사번, 급여, comm 출력
update scott.big_emp set comm = null;
update scott.big_emp set comm = 100 where empno = 29945;
update scott.big_emp set comm = 200 where empno = 29946;
commit;
-- 인덱스 조회
select i1.owner, i1.index_name, i1.table_name, i2.column_name, i2.column_position
from dba_indexes i1, dba_ind_columns i2
where 1=1
and i1.owner = i2.index_owner
and i1.index_name = i2.index_name
and i1.table_name='BIG_EMP'
order by i1.owner, i1.index_name, i2.column_position;
-- 인덱스 생성
create index scott.idx_big_emp_comm on scott.big_emp(comm);
-- is not null 사용 - index full scan 사용.
select *
from scott.big_emp
where comm is not null;
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced'));
-- index range scan 사용.
select *
from scott.big_emp
where comm > 0; -- 숫자 컬럼
-- 문자 컬럼의 경우에는 ename > ' ' 이런식으로 하면 됨.
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced'));
— 예제4) 부정 연산자 사용
아래같은 상황은 답이 없음. 그냥 table access full 해야 됨.
create index scott.idx_big_emp_ename on scott.big_emp(ename);
select sum(sal) -- 이건 그냥 fatch all rows 하기 귀찮아서 이렇게 함.
from scott.big_emp
where ename != 'SMITH';
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced'));
-- 근데 집합연산을 쓰면 더 빠를 수 있다!
-- 이건 optimizer가 index안 쓰고 table access full 쓴 경우
select sal
from scott.big_emp
minus
select sal
from scott.big_emp
where ename = 'SMITH';
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced'));
** 실행 계획 읽는 순서 : 3->2->5->4->1->0. 같은 들여쓰기 블럭에서는 더 위에 있는 것부터 읽어야 됨.
-- 이건 optimizer한테 hint 줘서 index 강제로 쓰게 함.
select sal
from scott.big_emp
minus
select /*+ index(e idx_big_emp_ename) */ sal
from scott.big_emp e
where ename = 'SMITH';
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced'));
** 3-2-6-5-4-1-0
** 이 경우에는 index 안 쓰는 게 더 cost가 효율적인 경우임.
[data block access 방식]
1. full table scan
– 순차적 블록 액세스
– 멀티 블록 I/O 및 병렬화 가능. 그래서 full table scan이 항상 느리지 않다.
2. index scan
– 인덱스 블록 액세스 후 rowid를 통해 데이터 블록 획득
– 비 순차적인 블록 액세스
– 멀티 블록 I/O 사용 불가
3. index fast full scan (FFS)
– 질의에 필요한 모든 컬럼이 인덱스에 포함된 경우
– 멀티 블록 I/O 및 병렬화 가능.
– 인덱스를 통한 정렬은 불가능하다.
scan종류
1. table full scan
– 데이터가 저장된 블록을 처음부터 끝까지 전체 scan.
– multi block I/O, 병렬 처리 가능.
– 과반 이상의 데이터를 조회 시 index scan보다는 full table scan이 유리함.
– hint : /*+ full(테이블명) */
select /*+ full(e) */ *
from scott.big_emp e
where comm>=0;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last advanced'));
-- full table scan이 됨.
select /*+ full(e) */ *
from scott.big_emp e
where comm>=0;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last advanced'));
-- full table scan이 됨.2. index unique scan
– unique index를 생성했을 경우 데이터를 찾는 방식.
create unique index scott.idx_emp_empno on scott.emp(empno);
select *
from scott.emp
where empno=7788;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last advanced'));
-- index unique scan됨.3. index range scan
– 가장 일반적인 스캔 방식
– 범위를 순차적으로 찾아가는 방식
– 동등 비교, 대소 비교 검색 조건에 의해 발생함.
– 결합 인덱스의 선두컬럼이 검색조건에 포함된 경우만 발생.
– hint : /*+ index(테이블명 인덱스명) */
create index scott.idx_emp_sal on scott.emp(sal);
select /*+ index(e idx_emp_sal) */ *
from scott.emp e
where sal >= 3000;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last advanced'));
-- index range scan됨.4. index full scan
– index range scan은 불가하지만 table full scan 보다 유리한 경우 발생
– multi block I/O를 지원하지 않기 때문에 index fast full scan보다 느림
– 순차적 접근 -> 정렬 보장
– hint : /*+ index_fs(테이블명 인덱스명) */
select max(sal)
from scott.emp;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last advanced'));
-- index full scan (MIN/MAX) 됨.5. index fast full scan
– index range scan이 불가, table full scan 보다 유리한 경우 주로 사용
– index에 select 절의 컬럼이 모두 포함된 경우 발생
– multi block I/O 지원
– 비순차적 접근 -> 정렬 보장 불가
– hint : /*+ index_ffs(테이블명 인덱스명) */
select /*+ index_ffs(e idx_emp_sal) */ sal
from scott.emp e
where sal > 0;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last advanced'));
-- index fast full scan 됨.6. index skip scan
– 검색조건에 결합인덱스의 선두컬럼이 생략된 경우, 선두컬럼의 selectivity가 낮을 때(cardinality가 높을 때) 주로 발생.
ex) index : grade + score 일 때 where score>=90; 인 경우에 어차피 아래쪽에 90점이상이 있을 거기 때문에 skip하면서 scan한다. skip scan을 하려면 선두컬럼(grade)의 selectivity가 낮아야한다. 1234학년인 경우 4군데만 들어가서 scan하면 되니깐. 만약 동아리명 같은 selectivity 높은 게 선두컬럼이면 매우 많은 곳을 들어가면서 scan해야 되기 때문에 안 좋다.
– hint : /*+ index_ss(테이블명 인덱스명) */
update scott.large_emp set deptno = 10 where deptno is null;
update scott.large_emp set deptno = 10 where deptno <= 19;
update scott.large_emp set deptno = 20 where deptno between 20 and 40;
update scott.large_emp set deptno = 40 where deptno > 40;
-- index 생성
create index scott.idx_large_emp_deptno_sal on scott.large_emp(deptno,sal);
select /*+ index_ss(e idx_large_emp_deptno_sal) */ *
from scott.large_emp e
where sal = 3000;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last advanced'));
-- index skip scan 됨.[인덱스 종류]
1. unique index
2. non unique index
3. descending index
– 내림차순 정렬 결과로 index 생성
– 내림차순 정렬을 필요로 하는 검색조건에 유리
create index scott.idx_emp_hiredate on scott.emp(hiredate desc);
select *
from dba_indexes
where index_name = 'IDX_EMP_HIREDATE';
-- FUNCTION-BASED NORMAL 라고 출력됨
-- descending index 는 FBI 형태로 만들어짐4. Function Based Index (FBI)
– 변형된 컬럼 형태로 만든 인덱스
create index scott.fbi_emp_hiredate on scott.emp(extract(year from hiredate));
select *
from scott.emp
where extract(year from hiredate) = 1980;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last advanced'));
5. reverse index
– 거꾸로 만들어진 값을 가지고 인덱스를 생성하는 것.
– 잦은 데이터 삭제로 인덱스 밸런스가 깨지는 경우.
select ename
from scott.emp
order by ename;
ex) 이름을 거꾸로 만든 후 인덱스 생성
-- ADAMS -> SMADA
-- ALLEN -> NELLA
-- BLAKE -> EKALB6. IOT(Index Organization Table)
– 테이블인데 테이블 내부에 인덱스 정보를 같이 가지고 있는 형태.
– 대용량 테이블인 경우 index scan -> table access(by rowid)의 부하를 줄이기 위해 테이블 자체에 rowid값을 갖는 경우를 설계하는 것을 고려함.
** 주의
1) primary key 필수
2) unique key 생성 불가
3) 일반 인덱스 추가
4) long type 컬럼 포함 불가
create table 테이블명(
컬럼1 데이터타입,
컬럼2 데이터타입,
...
primary key(...))
organization index;[인덱스 밸런스]
B*Tree 인덱스의 경우 좌우 대칭형인 상태가 가장 인덱스 성능이 좋다.
잦은 delete 시 인덱스 균형이 깨짐 -> 주기적 관리가 필요.
실습
-- 테이블 생성
create table scott.big_emp_test
as
select * from scott.big_emp;
-- 인덱스 생성
create index scott.idx_big_emp_test_empno on scott.big_emp_test(empno);
-- 밸런스 확인
1) 통계정보 수집
analyze index scott.idx_big_emp_test_empno validate structure;
2) 밸런스 확인
select DEL_LF_ROWS_LEN / LF_ROWS_LEN * 100 as 밸런스값
from index_stats; -- 통계정보 수집 시 저장됨.
-- 0 나옴
-- 밸런스 값이 0이면 가장 좋은 성능, 20% 넘는 경우 성능 저하 가능성
-- 데이터 삭제
delete scott.big_emp_test where empno < 29000;
commit;
-- 밸런스 재확인
1) 통계정보 수집
analyze index scott.idx_big_emp_test_empno validate structure;
2) 밸런스 확인
select DEL_LF_ROWS_LEN / LF_ROWS_LEN * 100 as 밸런스값
from index_stats;
-- 96.47 나옴
-- 재배치
alter index scott.idx_big_emp_test_empno coalesce;
-- 밸런스 재확인
1) 통계정보 수집
analyze index scott.idx_big_emp_test_empno validate structure;
2) 밸런스 확인
select DEL_LF_ROWS_LEN / LF_ROWS_LEN * 100 as 밸런스값
from index_stats;
-- 0 나옴