分区视图执行计划

Question

以下是测试表和分区视图。

use tempdb
go
SET STATISTICS IO ON
create table T1 (C int primary key); 
create table T2 (C int primary key); 
create table T3 (C int primary key); 
create table T4 (C int primary key); 
create table T5 (C int primary key); 
create table T6 (C int primary key); 
create table TA (c int primary key, id int); 
insert into dbo.T1(C) values (1),(2),(3),(4),(5),(6),(7),(8)
insert into dbo.T2(C) values (1),(2),(3),(4),(5),(6),(7),(8)
insert into dbo.T3(C) values (1),(2),(3),(4),(5),(6),(7),(8)
insert into dbo.T4(C) values (1),(2),(3),(4),(5),(6),(7),(8)
insert into dbo.T5(C) values (1),(2),(3),(4),(5),(6),(7),(8)
insert into dbo.T6(C) values (1),(2),(3),(4),(5),(6),(7),(8)
insert into TA values (1, 1), (2, 2)
go
create view V(Id, C)
as
select 1, * from T1 union all
select 2, * from T2 union all
select 3, * from T3 union all
select 4, * from T4 union all
select 5, * from T5 union all
select 6, * from T6

但是，以下视图将扫描所有表格？避免它的最佳方法是什么？

SELECT * FROM dbo.V join TA on TA.c = v.c
where ta.c = 2 and ta.id = v.id
option (recompile)

Answer 1

此重写将带有启动谓词的筛选器添加到计划中，并避免访问其他表。

SELECT *
FROM   TA
       OUTER APPLY (SELECT *
                    FROM   dbo.V
                    WHERE  TA.c = v.c
                           AND ta.id = v.id) CA
WHERE  TA.c = 2 

STATISTICS IO的结果是

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0
Table 'T2'. Scan count 0, logical reads 2, physical reads 0
Table 'TA'. Scan count 0, logical reads 2, physical reads 0

与的原始结果比较

Table 'T6'. Scan count 0, logical reads 2, physical reads 0
Table 'T5'. Scan count 0, logical reads 2, physical reads 0
Table 'T4'. Scan count 0, logical reads 2, physical reads 0
Table 'T3'. Scan count 0, logical reads 2, physical reads 0
Table 'T2'. Scan count 0, logical reads 2, physical reads 0
Table 'T1'. Scan count 0, logical reads 2, physical reads 0
Table 'TA'. Scan count 0, logical reads 2, physical reads 0