반응형
/*******************************************************************************************************************
-- Title : [SQL2017] Columnstore Index 테스트
-- Reference : 디비랑
-- Key word : columnstore index column store index 컬럼 스토어 인덱스 컬럼스토어 인덱스
*******************************************************************************************************************/
■ 테스트 테이블 정보
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
/*
기본 테이블 정보
disk : SSD
row count : 21,415,393
main (w/ pk)
main2 (w/ pk and several index)
main_cci (w/ clustered columnstore index, add ix)
*/
set statistics io on;
set statistics time off;
select top 100 * from main;
select top 100 * from main2;
select top 100 * from main_cci;
-----------------------------------------------------------
-- clustered columnstore index 생성
-----------------------------------------------------------
create clustered columnstore index main_cci
on main_cci;
-----------------------------------------------------------
-- 00) CCI 생성 후 테이블 크기와 인덱스 확인
-----------------------------------------------------------
exec sp_spaceused main;
exec sp_spaceused main2;
exec sp_spaceused main_cci;
select * from sys.indexes where object_id= object_id('main');
select * from sys.indexes where object_id= object_id('main2');
select * from sys.indexes where object_id= object_id('main_cci');
|
cs |
■ TOP 10000 비교
|
1
2
3
4
5
|
-----------------------------------------------------------
-- 01) top 10000
-----------------------------------------------------------
select top 10000 * from main
select top 10000 * from main_cci
|
cs |
■ 전체행 집계 비교
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
-----------------------------------------------------------
-- 02) 전체 집계 함수
-----------------------------------------------------------
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
select sum(cl_cnt) "sum_cl_cnt", avg(cl_cnt) "avg_cl_cnt"
, max(cl_cnt) "max_cl_cnt", min(cl_cnt) "min_cl_cnt"
from main;
print('====================================================================');
select sum(cl_cnt) "sum_cl_cnt", avg(cl_cnt) "avg_cl_cnt"
, max(cl_cnt) "max_cl_cnt", min(cl_cnt) "min_cl_cnt"
from main_cci;
|
cs |
■ 조건별 집계 비교
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
-----------------------------------------------------------
-- 03) 조건 집계 함수
-----------------------------------------------------------
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
CREATE NONCLUSTERED INDEX ix_main2_ctry_num
ON [dbo].[main2] ([ctry_num])
INCLUDE ([appl_dt],[cl_cnt]);
select count(*) "cnt"
, sum(cl_cnt) "sum_cl_cnt", avg(cl_cnt) "avg_cl_cnt"
, max(cl_cnt) "max_cl_cnt", min(cl_cnt) "min_cl_cnt"
from main
where ctry_num = 3
group by appl_dt;
print('-------------------------------------------------------------------');
select count(*) "cnt"
, sum(cl_cnt) "sum_cl_cnt", avg(cl_cnt) "avg_cl_cnt"
, max(cl_cnt) "max_cl_cnt", min(cl_cnt) "min_cl_cnt"
from main2
where ctry_num = 3
group by appl_dt;
print('===================================================================');
select count(*) "cnt"
, sum(cl_cnt) "sum_cl_cnt", avg(cl_cnt) "avg_cl_cnt"
, max(cl_cnt) "max_cl_cnt", min(cl_cnt) "min_cl_cnt"
from main_cci
where ctry_num = 3
group by appl_dt;
|
cs |
■ 조건별 집계 비교-2
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
-----------------------------------------------------------
-- 04) 조건 집계 함수
-----------------------------------------------------------
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
CREATE NONCLUSTERED INDEX ixi_main2_appl_dt
ON [dbo].[main2] ([appl_dt])
INCLUDE ([cl_cnt]);
select count(*) "cnt"
, sum(cl_cnt) "sum_cl_cnt", avg(cl_cnt) "avg_cl_cnt"
, max(cl_cnt) "max_cl_cnt", min(cl_cnt) "min_cl_cnt"
from main
where appl_dt between '20050101' and '20151231'
group by appl_dt;
print('--------------------------------------------------------------------');
select count(*) "cnt"
, sum(cl_cnt) "sum_cl_cnt", avg(cl_cnt) "avg_cl_cnt"
, max(cl_cnt) "max_cl_cnt", min(cl_cnt) "min_cl_cnt"
from main2
where appl_dt between '20050101' and '20151231'
group by appl_dt;
print('====================================================================');
select count(*) "cnt"
, sum(cl_cnt) "sum_cl_cnt", avg(cl_cnt) "avg_cl_cnt"
, max(cl_cnt) "max_cl_cnt", min(cl_cnt) "min_cl_cnt"
from main_cci
where appl_dt between '20050101' and '20151231'
group by appl_dt;
|
cs |
■ PK조건 집계 비교
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
-----------------------------------------------------------
-- 05) PK 조건
-----------------------------------------------------------
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
create nonclustered index ix_main_cci
on main_cci(skey);
select *
from main
where skey in (
3515353000095,1516001002701,7681907004719,2216043000021,7406027004509
,5415021007428,7486908000378,6715383003732,8417011026828,6616121002365);
print('====================================================================');
select *
from main_cci
where skey in (
3515353000095,1516001002701,7681907004719,2216043000021,7406027004509
,5415021007428,7486908000378,6715383003732,8417011026828,6616121002365);
|
cs |
■ PK조건 집계 비교-2
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
-----------------------------------------------------------
-- 06) PK 조건
-----------------------------------------------------------
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
select *
from main
where skey in (
3515353000095,1516001002701,7681907004719,2216043000021,7406027004509
,5415021007428,7486908000378,6715383003732,8417011026828,6616121002365);
print('====================================================================');
select *
from main_cci with(index(0))
where skey in (
3515353000095,1516001002701,7681907004719,2216043000021,7406027004509
,5415021007428,7486908000378,6715383003732,8417011026828,6616121002365);
|
cs |
■ PK조건 집계 비교-3
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
-----------------------------------------------------------
-- 07) PK 조건
-----------------------------------------------------------
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
select *
from main_cci
where skey in (
3515353000095,1516001002701,7681907004719,2216043000021,7406027004509
,5415021007428,7486908000378,6715383003732,8417011026828,6616121002365);
print('====================================================================');
select *
from main_cci with(index(0))
where skey in (
3515353000095,1516001002701,7681907004719,2216043000021,7406027004509
,5415021007428,7486908000378,6715383003732,8417011026828,6616121002365);
|
cs |
반응형