orとunionテスト
23211 ワード
1
/*
2
or union ： union ，
3
or ：union SARG
4
*/
5
6
7
CREATE
TABLE
fact_sales(date_id
int
, product_id
int
, store_id
int
, quantity
int
, unit_price numeric(
7
,
2
), other_data
char
(
1000
))
8
GO
9
CREATE
CLUSTERED
INDEX
ci
ON
fact_sales(date_id);
10
GO
11
PRINT
'
Loading...
'
;
12
SET
NOCOUNT
ON
;
13
14
DECLARE
@i
int
;
15
SET
@i
=
1
;
16
17
WHILE
(
@i
<
100000
)
18
BEGIN
19
INSERT
INTO
fact_sales
20
VALUES
(
20080800
+
(
@i
%
30
)
+
1
,
@i
%
10000
,
@i
%
200
,
RAND
()
*
25
, (
@i
%
3
)
+
1
,
''
);
21
22
SET
@i
+=
1
;
23
END
;
24
GO
25
DECLARE
@i
int
;
26
SET
@i
=
1
;
27
28
WHILE
(
@i
<
10000
)
29
BEGIN
30
INSERT
INTO
fact_sales
31
VALUES
(
20080900
+
(
@i
%
30
)
+
1
,
@i
%
10000
,
@i
%
200
,
RAND
()
*
25
, (
@i
%
3
)
+
1
,
''
);
32
33
SET
@i
+=
1
;
34
END
;
35
PRINT
'
Done.
'
;
36
GO
37
set
statistics
io
on
38
go
39
select
distinct
date_id
40
from
fact_sales
41
where
store_id
=
23
and
(unit_price
=
2.00
or
product_id
=
23
)
42
go
43
select
date_id
from
fact_sales
where
store_id
=
23
and
(unit_price
=
2.00
)
44
union
45
select
date_id
from
fact_sales
where
store_id
=
23
and
(product_id
=
23
)
46
go
47
select
date_id
from
fact_sales
where
store_id
=
23
and
(unit_price
=
2.00
)
48
union
all
49
select
date_id
from
fact_sales
where
store_id
=
23
and
(product_id
=
23
)
50
go
51
set
statistics
io
off
52
53
--
create index idx_product_id on fact_sales(product_id)
54
--
create index idx_store_id on fact_sales(store_id)
55
--
drop index fact_sales.idx_store_id
56
--
create index idx_store_id on fact_sales(unit_price)