or 和 union 测试

 

/*
 or 和 union 测试结论：当都有索引时union效率更高，
如果没有索引or效率高因为：union 符合 SARG条件 
 */ 


 CREATE TABLE fact_sales(date_id int, product_id int, store_id int, quantity int, unit_price numeric(7,2), other_data char(1000)) 
GO 
CREATE CLUSTERED INDEX ci ON fact_sales(date_id); 
GO 
PRINT 'Loading...'; 
SET NOCOUNT ON; 

DECLARE @i int; 
SET @i = 1; 

WHILE (@i<100000) 
BEGIN 
    INSERT INTO fact_sales 
    VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, ''); 

    SET @i += 1; 
END; 
GO 
DECLARE @i int; 
SET @i = 1; 

WHILE (@i<10000) 
BEGIN 
    INSERT INTO fact_sales 
    VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, ''); 

    SET @i += 1; 
END; 
PRINT 'Done.'; 
GO 
set statistics io on 
go 
select distinct date_id 
from fact_sales 
where store_id = 23 and (unit_price = 2.00 or product_id = 23) 
go 
select date_id from fact_sales where store_id = 23 and (unit_price = 2.00) 
union 
select date_id from fact_sales where store_id = 23 and (product_id = 23) 
go 
select date_id from fact_sales where store_id = 23 and (unit_price = 2.00) 
union all 
select date_id from fact_sales where store_id = 23 and (product_id = 23) 
go 
set statistics io off 

--create index idx_product_id on fact_sales(product_id) 
--create index idx_store_id on fact_sales(store_id) 
--drop index fact_sales.idx_store_id 
--create index idx_store_id on fact_sales(unit_price)