3) IOT (index organizaion table) 테이블 구성
- table random 엑세스가 발생하지 않도록 테이블을 아예 인덱스 구조로 생성한 object
⑴ IOT 의 장점
- 인위적으로 클러스터링 팩터를 좋게 만드는 방법중에 하나여서 Logical read를 줄일 수 있다.
- 기존에는 인덱스 + 테이블이였다면, lOT는 인덱스만 만들면 되므로, 저장 공간이 절약된다.
⑵ IOT의 단점
- 데이터 입력할 때, 성능이 느리다.
⑶ IOT 생성 및 데스트
① create table emp_iot
(empno, ename, job, mgr, hiredate, sal, comm, deptno, constraint pk_emp_iot primary key(empno) )
organization index
as
select * from emp ;
* IOT 생성시 pirmary key 가 없으면 테이블이 생성되지 않는다.
* 기존 테이블에 primary key가 걸려 있어도 의미가 없다. 새로 생기는 table에 primary key를 만들어 주어야 한다.
(ctas 사용시 not null 제약을 제외한 나머지 제약은 따라오지 않는다.)
② select * from emp_iot ;
-- consistent gets 5
-- index fast full scan
⑷ IOT 저장 영역
① PK 속성 영역 : 자주 조회 조건에서 검색되는 컬럼들이 저장된 저장 영역
② 오버 플로우 영역 : 값은 필요해서 저장해두지만 출력이나 조회조건으로 거의 사용되지 않는 컬럼들을 저정하는 저장 영역
* 영역을 나누어서 사용하는 이유는 검색 속도를 좀 더 높이기 위해서
* IOT는 table이 아니라 index로 저장 된다. 검색시에는 object_type 이나 segment_type을 index로 하여야 찾을 수 있다.
* 그러나 drop 이나 truncate 명령어를 사용할 때에는 table 옵션을 사용한다.
4) 클러스터 테이블
⑴ 클러스터 테이블이란
- 클러스터 키 값이 같은 레코드가 한 블럭에 모이도록 저장하는 구조의 테이블을 말한다.
- 클러스터 테이블을 이용하면, 여러테이블의 레코드를 심지어 하나의 물리적 공간에 같이 저장할 수 있다
- 여러 테이블들을 서로 조인된 상태로 저장해 두는 것이다.
* 단점은 DML 속도가 상당히 느려진다.
⑵ 클러스터 테이블의 종류 2가지
① 인덱스 클러스터 테이블
- 단일 테이블 인덱스 클러스터
- 다중 테이블 인덱스 클러스터
② 해쉬 클러스터 테이블
⑶ 단일 클러스터
- 클러스터 인덱스는 컬럼 값을 하나만 저장하기 때문에 인덱스의 크기가 크지 않아 검색속도가 높아진다.
- 클러스터 테이블은 클러스터 키값에 따라서 최대한 같은 블럭안에 같은 키값들을 저장히기 때문에 검색속도가 높아진다.
.png)
⑷ 단일 클러스터 테이블 생성 테스트
① 클러스터를 생성한다.
CREATE CLUSTER objs_cluster# (object_type VARCHAR2(20) ) INDEX ;
ㅣ ㅣ
클러스터 이름 클러스터 key 값
② 클러스터 인덱스를 생성한다.
CREATE INDEX objs_cluster_idx ON CLUSTER objs_cluster# ;
ㅣ ㅣ
클러스터 인덱스 이름 클러스터 이름
③ 클러스터 테이블을 생성한다.
CREATE TABLE objs_cluster
CLUSTER objs_cluster# (object_type)
AS SELECT *
FROM all_objects
ORDER BY DBMS_RANDOM.VALUE;
* dbms_random.value를 사용해서 정렬해서 데이터를 저장하면
실제로 논리적으로는 object_type 별로 저장되어있지 않고 데이터가 무분별하게 흩어져서 저장되어져있는 것처럼 보이겠지만
실제로 물리적으로는 object_type 별로 같은 블럭안에 데이터가 저장된다.
* 생성시에는 cluster, table 모두 create 하지만 truncate 시에는 cluste만 truncate 하면 자동으로 table의 내용이 삭제 된다.
drop시에는 table, cluster 모두 삭제 해야 한다.
* 클러스터 테이블을 생성하기 위해서는 클러스터 생성시 클러스터 키값은 기존 테이블의 컬럼의 데이터 타입과 길이의 범위가 같아야 한다.
그렇지 않을 시 클러스터 테이블 생성시 error가 발생한다.
④ 일반 테이블과 클러스터 테이블의 성능을 비교한다.
CREATE TABLE objs_regular
AS SELECT *
FROM objs_cluster
ORDER BY DBMS_RANDOM.VALUE;
CREATE INDEX objs_regular_idx ON objs_regular(object_type) ;
ALTER TABLE objs_regular MODIFY object_name NULL;
ALTER TABLE objs_cluster MODIFY object_name NULL;
결과 ) -- consistent gets , physical read 순
SELECT *
FROM objs_regular
WHERE object_type='TABLE';
-- 429
-- 235
-- full table access
SELECT /*+ index(objs_regular objs_regular_idx) */*
FROM objs_regular
WHERE object_type='TABLE';
-- 549
-- 235
-- index range scan
SELECT *
FROM objs_cluster
WHERE object_type ='TABLE';
-- 226
-- 199
-- index unique scan
-- table access cluster
5) 다중 클러스터 테이블
- 여러 테이블의 레코드를 물리적으로 같은 블럭안에 저장되게 하는 테이블을 말한다.
- 즉, 여러테이블들을 서로 조인된 상태로 저장해두는 것을 말한다.
.png)
⑴ 다중 클러스터 테이블 구현
① 클러스터를 생성한다.
create cluster c_deptno# on ( deptno number(20) ) index ;
② 클러스터 인덱스를 생성한다.
create index i_deptno# on cluster c_deptno# ;
③ 클러스터 테이블을 생성한다.
create table emp_cluster
cluster c_deptno#(deptno) -----------------------> 같은 클러스터 사용
as select *
from emp ;
create table dept_cluster
cluster c_deptno#(deptno) -----------------------> 같은 클러스터 사용
as select *
from dept ;
* 두개의 테이블이 같은 블럭안에 존재한다는 것을 확인해보시오 !
break on deptno skip 1 ;
select d.deptno, e.empno, e.ename,
dbms_rowid.rowid_block_number(d.rowid) deptno_block_no,
dbms_rowid.rowid_block_number(e.rowid) emp_block_no
from dept_cluster d , emp_cluster e
where e.deptno = d.deptno
order by d.deptno ;
④ 일반 테이블과 클러스터 테이블과 성능을 비교한다.
select e.ename, d.loc, e.sal, e.hiredate, d.dname, e.deptno, e.mgr
from emp e, dept d
where e.deptno = d.deptno and d.loc ='DALLAS';
* 순서대로 1회 실행과 2회 실행 시
-- 11 -> 7
-- 4 -> 0
-- table acess full
-- hash join
select e.ename, d.loc, e.sal, e.hiredate, d.dname, e.deptno, e.mgr
from emp_cluster e, dept_cluster d
where e.deptno = d.deptno and d.loc ='DALLAS';
-- 26 -> 10
-- 6 -> 0
-- table access cluster
-- table acess full
-- nested loop
문제 34. 사원 번호가 7788 번인 사원의 모든 컬럼의 데이터를 가져오는 쿼리의 성능을 emp 와 emp_iot 로 비교하시오
SELECT /*+ full(emp) */ *
from emp
WHERE empno=7788 ;
-- consistent gets 4
-- full table scan
select *
from emp_iot
WHERE empno=7788 ;
-- consistent gets 1
-- index unique scan
문제 35. rn 이라는 컬럼이 있는 sales500 테이블을 rn 컬럼을 primary key 로 해서 iot를 생성하는데 이름을 sales500_iot로 구현하시오 !
1) rn 컬럼 추가
CREATE TABLE SALES500
AS SELECT ROWNUM rn, b.*
FROM SALES b ;
2) IOT 구성
CREATE TABLE sale500_iot
(RN ,PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD,
CONSTRAINT sale500_iot_pk PRIMARY KEY(rn) )
organization index
as
select * from sales500 ;
문제 36. 아래의 쿼리의 검색 속도를 비교하시오!
select *
from sales500
where rn = 384756 ;
-- consistent gets 5076
-- full table scan
select *
from sale500_iot
where rn = 384756 ;
-- consistent gets 3
-- index unique scan
* rn 컬럼에 primary key가 걸려 있기 때문에 자동으로 unique index가 생성되었다.
문제 37. sales500 table + sales500_rn index 와 sales500_iot table 의 크기 차이를 비교하시오 !
SELECT segment_name, segment_type, bytes/1024/1024 MB
FROM user_segments
WHERE segment_name LIKE 'SALE%' ;
.png)
- sales500 (table) + sales500_rn_pk(index) = 41 + 16 = 57
- sales500_iot_pk = 39
문제 38. SALES500 테이블과 SALES500_IOT 테이블에 insert 할 때의 속도 차이를 비교해보시오!
- 행의 갯수 918843
첫 실험
- sales500 : 7 초
- sales500_IOT :31 초
* 사람들 마다 차이가 있어서 여러번 실험해보아야 한다.
* 여러번 실험할 수록 차이는 줄어 든다. 그 이유는 insert 시에 high water mark를 높여 놨기 때문이다.
* 제대로 실험해보기 위해서는 해당 table을 drop 한 후 다시 생성하고 buffer cache를 지우고 실험해보아야 한다.
문제 39. sales500 테이블을 클러스터 테이블로 구성하는데 클러스터 키컬럼을 amount_sold로 해서 생성하시오 !
CREATE CLUSTER sales500_cluster# (amount_sold VARCHAR2(22) ) INDEX ;
CREATE INDEX sales500_cluster_idx ON CLUSTER sales500_cluster# ;
CREATE TABLE sales500_cluster
CLUSTER sales500_cluster# (amount_sold)
AS SELECT *
FROM sales500
ORDER BY DBMS_RANDOM.VALUE;
CREATE TABLE sales500_regular
AS SELECT *
FROM sales500_cluster
ORDER BY DBMS_RANDOM.VALUE;
CREATE INDEX sales500_regular_idx ON sales500_regular(amount_sold) ;
문제 40. sales500 테이블과 sales500_cluster 테이블과 성능을 비교하시오
* 각각 실험 마다 flush buffer_cache 실행
* 1차, 2차 실행
select /*+ full(sales500_regular) */*
from sales500_regular
where amount_sold=136.64 ;
-- 5079 -> 5079
-- 5070 -> 5069
select /*+ index(sales500 sales500_regular_idx) */ *
from sales500_regular
where amount_sold=136.64 ;
-- 94 -> 90
-- 88 -> 0
select *
from sales500_cluster
where amount_sold=136.64 ;
-- 9 -> 9
-- 3 -> 0
문제 41. 아래의 조인문장의 성능이 좋아지도록 SALES 테이블과 PRODUCTS 테이블을 다중 클러스터 테이블과 구성하시오 !
create table sales create table products
as as
select * from sh.sales ; select * from sh.products ;
select /*+ leading(p s) use_nl(p) */ p.prod_name, sum(s.amount_sold)
from sales s, products p
where s.prod_id = p.prod_id
and p.prod_id = 40
group by p.prod_name ;
-- 4597 -> 4441
-- 4576 -> 4433
-- table acess full
-- table acess full
-- hash join
SELECT p.prod_name, sum(s.amount_sold)
from sales_cluster s, product_cluster p
where s.prod_id = p.prod_id
and p.prod_id = 40
group by p.prod_name ;
-- 1238 -> 244
-- 619 -> 0
-- TABLE ACCESS CLUSTER -> INDEX UNIQUE SCAN
-- TABLE ACCESS CLUSTER -> INDEX UNIQUE SCAN
-- NESTED LOOPS
* 점심시간 문제 :
create table emp_iot2
(empno, ename, job, mgr, hiredate, sal, comm, deptno,
CONSTRAINT emp_iot2_pk PRIMARY KEY (empno,ename) )
organization INDEX
INCLUDING sal
OVERFLOW tablespace ts01
as
select * from emp ;
-- 조회를 * 로 함
-- where 조건 including 밖
SELECT *
FROM emp_iot
WHERE deptno = 10;
-- 5
SELECT *
FROM emp_iot2
WHERE deptno = 10 ;
-- 7
-- 조회를 * 로 함
-- where 조건 including 안
SELECT *
FROM emp_iot
WHERE job = 'SALESMAN';
-- 5
SELECT *
FROM emp_iot2
WHERE job = 'SALESMAN';
-- 7
-- 조회를 * 로 함
-- where 조건 PK 컬럼
SELECT *
FROM emp_iot
WHERE empno = 7788;
-- 1
SELECT *
FROM emp_iot2
WHERE empno = 7788;
-- 3
* 아래부터는 할 때마다 alter system flush buffer_cache ; (buffer cache 내용 지우기) 를 실행함
* 그래서 consistent get, physical read 둘다 기록
-- where 조건 pk 컬럼
-- including 전 컬럼까지 조회
SELECT empno, ename, job
FROM emp_iot
WHERE empno = 7788;
-- 1
-- 8
SELECT empno, ename, job
FROM emp_iot2
WHERE empno = 7788 ;
-- 7
-- 6
-- where 조건 pk 컬럼 x
-- including 전 컬럼까지 조회
SELECT empno, ename, job
FROM emp_iot
WHERE deptno = 10;
-- 10
-- 6
SELECT empno, ename, job
FROM emp_iot2
WHERE deptno = 10 ;
-- 13
-- 14
-- where 조건 pk 컬럼
-- including 이외의 컬럼까지 조회
SELECT empno, ename, deptno
FROM emp_iot
WHERE empno = 7788;
-- 1
-- 8
SELECT empno, ename, deptno
FROM emp_iot2
WHERE empno = 7788 ;
-- 3
-- 16
-- where 조건 pk 컬럼 x
-- including 이외의 컬럼까지 조회
SELECT empno, ename, deptno
FROM emp_iot
WHERE deptno = 10;
-- 10
-- 6
SELECT empno, ename, deptno
FROM emp_iot2
WHERE deptno = 10 ;
-- 13
-- 14
* 오늘의 마지막 문제
SALES500_IOT 와 SALES_CLUSTER 와 SALES 테이블 3개의 DML 속도를 비교하시오 !
* 모두 truncate 로 아무 것도 없던 상태에서 작업
* db도 테스트하기 전에 startup force 실행
* 각 테스트가 끝나면 flush buffer_cache 후 다시 테스트 진행함
INSERT INTO SALEs500
SELECT *
FROM sales500_backup ;
-- 5초 13
INSERT INTO sales500_iot
SELECT *
FROM sales500_backup ;
-- 26초 71
INSERT INTO sales500_cluster
SELECT *
FROM sales500_backup ;
-- 45초 59
* sales500 에도 sales500_iot와 동일한 조건을 주기 위해서 rn 컬럼에 pk 제약을 건 후 다시 테스트
* 이번에는 좀 더 정확한 결과를 위해서 각 테스트 마다 startup 을 새로함
INSERT INTO SALEs500
SELECT *
FROM sales500_backup ;
-- 5초 07
INSERT INTO sales500_iot
SELECT *
FROM sales500_backup ;
-- 22초 71
INSERT INTO sales500_cluster
SELECT *
FROM sales500_backup ;
-- 40초 49
- 조금 씩 위의 결과보다 차이는 나지만 큰 차이는 발생하지 않았다.