为什么视图无法利用到索引？

[progame]

表
oxJob(JobID) jobid为聚集索引
oxOrder(OrderID,JobID) 两个字段都分别建有索引

CREATE VIEW dbo.vw_test

AS

select jobid, operationid = jobid from dbo.oxjob
union all
select jobid, orderid as operationid from dbo.oxorder

查询：
select jobid from vw_test where operationid = @id

此时oxJob的索引无法正常利用到

sql server分解的查询是先按聚集索引得到jobid的一个结果集
再在此结果集上进行filter

如果view 不union oxOrder的话 没有这个问题
是否当一个select中有重复column出现时 则重新命名的column上无法利用索引？
select jobid from vw_test where jobid= @id 这个查询是可以正常利用索引的

请达人指点


以下是测试script:
CREATE TABLE [dbo].[C] (
[a] [varchar] (50) NOT NULL ,
[b] [varchar] (50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[D] (
[a] [varchar] (50) NOT NULL ,
[c] [varchar] (50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[C] WITH NOCHECK ADD
CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED
(
[a]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[D] WITH NOCHECK ADD
CONSTRAINT [PK_D] PRIMARY KEY CLUSTERED
(
[a]
) ON [PRIMARY]
GO

CREATE INDEX [IX_C_b] ON [dbo].[C]([b]) ON [PRIMARY]
GO



CREATE VIEW dbo.vw_Test
AS
SELECT a,b from C
union all
select a,a from D

[pbsql]

可以在视图上建索引

CREATE VIEW dbo.vw_Test
AS
SELECT a,b from C
union all
select a,a from D
--这里都是取全部数据，你想用索引与不用又有什么区别？？？都是要扫描全表！！！

[progame]

我只是一个测试数据

我的查询是select * from vw_Test where b = 'a'这样的

视图建索引是有限制的
有UNION不行
有重复列名出现不行

[caiyunxia]

建立索引视图
如果不是索引视图
是使用基表的索引

[progame]

--查询.结果只在表c进行了Index Seek---------------------

select * from dbo.vw_Test
where a='a'

之前没有分区视图这个查询也是可以index seek的
我要的是查询 where b = 'a'时也可以index seek

[progame]

再次申明一下 是要在select * from dbo.vw_Test
where b='a'时 可以对表D进行index seek

[zlp321002]

--索引视图
--try

create view vw_Test WITH SCHEMABINDING
as
SELECT a,b from C
union all
select a,a from D

go


CREATE UNIQUE CLUSTERED INDEX

idx_uc_a ON vw_Test (a,b)

[progame]

希望大家看清楚我的要求

再次说清楚一下：
1、此视图无法创建索引 不是 SCHEMABINDING的问题 而是因为union和重复列导致的

2、基表已经有索引，只是针对视图的查询无法正确利用到索引

[mschen]

按照你的需求,你应该建立分区视图,而不是索引视图.

[progame]

现在是对已有数据的查询 而不是设计表
而且分区视图同样有很多限制 像我这里所说的情况 就是分区视图也无法创建

如果可以满足分区视图的创建规则 (即没有重复列)， 那么我现在的vw_test同样的可以利用到基表的索引的 如：
create view vw_Test as
SELECT a,b from C
union all
select a,c from D

现在的问题是D表的a列在select中出现了(而且实际应用中也确实需要)两次，
导致对view的b进行过滤时无法正确应用D的索引

[mschen]

--分区视图测试---------------------------------------


--创建表c--------------------------------------------

CREATE TABLE [dbo].[C] (
[a] [varchar] (50) NOT NULL ,
[b] [varchar] (50) NULL
) ON [PRIMARY]
GO

--创建表d---------------------------------------------

CREATE TABLE [dbo].[D] (
[a] [varchar] (50) NOT NULL ,
[c] [varchar] (50) NULL
) ON [PRIMARY]
GO

--c表添加主键约束----------------------------------------

ALTER TABLE [dbo].[C] WITH NOCHECK ADD
CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED
(
[a]
) ON [PRIMARY]
GO

--c表添加check约束---------------------------------------
alter table dbo.c add
constraint chk_c check(a between 'a' and 'g')


--d表添加主键约束-----------------------------------------

ALTER TABLE [dbo].[D] WITH NOCHECK ADD
CONSTRAINT [PK_D] PRIMARY KEY CLUSTERED
(
[a]
) ON [PRIMARY]
GO

--d表添加check约束--------------------------------------

alter table dbo.d add
constraint chk_d check(a between 'h' and 'z')

--插入数据-----------------------------------------------
insert c select 'a','a'
insert c select 'b','b'
insert c select 'e','e'



insert d select 'h','h'
insert d select 'i','i'
insert d select 'z','z'

--创建视图---------------------------------------------
CREATE VIEW dbo.vw_Test
AS
SELECT a,b from C
union all
select a,a from D

--查询.结果只在表c进行了Index Seek---------------------

select * from dbo.vw_Test
where a='a'

[TigerSuper]

UP

[rouqu]

我觉得这个问题讨论的复杂了！索引不是总能用上的 创建视图中的语句
select jobid, operationid = jobid from dbo.oxjob
如果改成
select jobid, operationid = （1000－jobid） from dbo.oxjob

你说
select jobid from vw_test where operationid = @id能不能用上oxJob的簇集索引？

[rouqu]

而且 从（簇级）索引的物理结构上说 能不能利用上索引 首先看这部分字段上有没有索引？

生成select jobid, operationid = jobid from dbo.oxjob的结果集时
operationid列数据会产生在temp数据库 但是SQL会建立或者复制Jobid的索引页吗？显然不会

[Yang_]

这个问题索引没法解决

只有用函数或者存储过程来实现如下查询：

select jobid, operationid = jobid from dbo.oxjob where jobid=@Id
union all
select jobid, orderid as operationid from dbo.oxorder where orderid=@Id

当然，这个方法搂主自己能想到。

[ashzs]

progame (www.progame.org) ，说的有道理！！！！ 我来说说我的实验结果。

我把你的实验代码跑了一次：

CREATE TABLE [dbo].[C] (
[a] [varchar] (50) NOT NULL ,
[b] [varchar] (50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[D] (
[a] [varchar] (50) NOT NULL ,
[c] [varchar] (50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[C] WITH NOCHECK ADD
CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED
(
[a]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[D] WITH NOCHECK ADD
CONSTRAINT [PK_D] PRIMARY KEY CLUSTERED
(
[a]
) ON [PRIMARY]
GO

CREATE INDEX [IX_C_b] ON [dbo].[C]([b]) ON [PRIMARY]
GO



CREATE VIEW dbo.vw_Test
AS
SELECT a,b from C
union all
select a,a from D

go

--（1）
select a,b from vw_Test where b = 'a'

--（2）
select a,b from vw_Test where a = 'a'


在SQL2000下，（1）和（2）的执行计划如下：

（1）的执行计划

|--Concatenation
|--Index Seek(OBJECT:([aa].[dbo].[C].[IX_C_b]), SEEK:([C].[b]='a') ORDERED FORWARD)
|--Filter(WHERE:([D].[a]='a'))
|--Compute Scalar(DEFINE:([D].[a]=[D].[a]))
|--Clustered Index Scan(OBJECT:([aa].[dbo].[D].[PK_D]))

（2）的执行计划

|--Concatenation
|--Clustered Index Seek(OBJECT:([aa].[dbo].[C].[PK_C]), SEEK:([C].[a]='a') ORDERED FORWARD)
|--Compute Scalar(DEFINE:([D].[a]=[D].[a]))
|--Clustered Index Seek(OBJECT:([aa].[dbo].[D].[PK_D]), SEEK:([D].[a]='a') ORDERED FORWARD)



对于C表的执行计划大家应该都没有什么异议。关键在扫描D表的执行计划有了不同。按道理来说（1）和（2）对于D表的执行计划应该差不多才对，但是为什么会多出来一个filter?正常来

说虽然在映相虚拟字段之前进行了索引扫描，但是执行分析器应该可以预知的，应该直接在[D].[PK_D]中进行seek，而不需要在scan以后再进行一次filter。如果（1）和（2）的执行成本差不多的话，也可以容忍这个多余的步骤。但是在估计成本比较的时候我发现语句（1）和语句（2）的估计成本比为77:23。虽然只是估计成本，但是相差如此之大，总是让人感觉不爽！所以说SQL2000的执行分析器在映相虚拟字段方面还是比较薄弱。所以我感觉楼主的疑问是有道理的！！！


接着，我把楼主的语句拿到了SQL2005下面，看看结果有什么不同，以下是SQL2005（版本号：9.0.1314）下面的（1）和（2）的执行计划

（1）的执行计划

|--Concatenation
|--Index Seek(OBJECT:([aa].[dbo].[C].[IX_C_b]), SEEK:([aa].[dbo].[C].[b]='a') ORDERED FORWARD)
|--Compute Scalar(DEFINE:([aa].[dbo].[D].[a]=[aa].[dbo].[D].[a]))
|--Clustered Index Seek(OBJECT:([aa].[dbo].[D].[PK_D]), SEEK:([aa].[dbo].[D].[a]='a') ORDERED FORWARD)


（2）的执行计划

|--Concatenation
|--Clustered Index Seek(OBJECT:([aa].[dbo].[C].[PK_C]), SEEK:([aa].[dbo].[C].[a]='a') ORDERED FORWARD)
|--Compute Scalar(DEFINE:([aa].[dbo].[D].[a]=[aa].[dbo].[D].[a]))
|--Clustered Index Seek(OBJECT:([aa].[dbo].[D].[PK_D]), SEEK:([aa].[dbo].[D].[a]='a') ORDERED FORWARD)


两条语句对于D表的执行计划是一样的！而且（1）和（2）的估计成本比为50：50。看来SQL2005对执行计划分析器已经做了改进！这可能是楼主想要的！

最后结论：SQL2005的执行计划分析器在映相虚拟字段方面比SQL2000有了很大改进！不是广告！：）

[zjcxc]

可以使用index提示强制指定要使用的索引。

[ashzs]

SQL Server的索引视图其实就是Oracle的实体化视图，但是功能要弱很多（包括SQL2005）。你的问题如果用Oracle基本就没问题了！

如果没有优化，你的视图应用效率很差吗？如果效率可以的话，不用再优化了。而且在SQL2005中虽然仍然有分区视图，但是只是为了兼容，更多的使用了分区表去做这个工作。很遗憾SQL2000如果表设计不合理，后期回旋余地很小。祝好运！

[progame]

多谢大家

看来SQL2005确实改进了 因为我想它的智能优化应该可以做到我想要的，但2000没有做到

我的解决方案是添加了一个计算列给D表 b (formula= a) 然后给b创建了索引

但是我的这个view union了8个表，如果每个表的索引SEEK是n开销的话
一个执行开销= 8 * n(假设每个表的索引数据一样)

如果可以给view创建一个index的 那么开销只会为 n + 3

可惜union的view不支持index

这几个表分区索引也是不行的 里面的不同太多

当然如果当初创建表结构的话能够做更多的考虑就好了