or 和 union 测试结论：当都有索引时union效率更高， 3 如果没有索引or效率高因为：union 符合 SARG条件
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)
