在执行计划中，“常量扫描”是什么意思？

2011-04-26 04:36:04 239 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

有分区视图sales_fact，执行删除数据操作

delete from sales_fact

 where date_key > 20001024

后消息提示
表 'sales_fact_19990101'。扫描计数 0，逻辑读取 0 次...
表 'sales_fact_20000101'。扫描计数 1，逻辑读取 2 次...

其中，sales_fact_19990101保存date_key在19990101 和 19991231之间的数据
sales_fact_20000101保存date_key在20000101 和 20001231之间的数据
现在问题是：删除19991231以后的数据，为什么会扫描sales_fact_19990101，而且扫描计数为0？
执行计划里边显示对表sales_fact_19990101扫描时为“常量扫描”，这是什么意思？

表，分区视图，测试代码如下

if	object_id('[dbo].[sales_fact_19990101]') is not null

drop table [dbo].[sales_fact_19990101]

if	object_id('[dbo].[sales_fact_20000101]') is not null

drop table [dbo].[sales_fact_20000101]

if	object_id('[dbo].[sales_fact_20010101]') is not null

drop table [dbo].[sales_fact_20010101]

CREATE TABLE [dbo].[sales_fact_19990101] (

   [date_key] [int] NOT NULL 

CHECK ([date_key] BETWEEN 19990101 AND 19991231),

   [product_key] [int] NOT NULL ,

   [customer_key] [int] NOT NULL ,

   [promotion_key] [int] NOT NULL ,

   [store_key] [int] NOT NULL ,

   [store_sales] [money] NULL ,

   [store_cost] [money] NULL ,

   [unit_sales] [float] NULL 

) 

ALTER TABLE [sales_fact_19990101]

ADD PRIMARY KEY (

[date_key], [product_key], [customer_key], [promotion_key], [store_key])

;

-- 创建 2000 年事实表

CREATE TABLE [dbo].[sales_fact_20000101] (

   [date_key] [int] NOT NULL 

CHECK ([date_key] BETWEEN 20000101 AND 20001231),

   [product_key] [int] NOT NULL ,

   [customer_key] [int] NOT NULL ,

   [promotion_key] [int] NOT NULL ,

   [store_key] [int] NOT NULL ,

   [store_sales] [money] NULL ,

   [store_cost] [money] NULL ,

   [unit_sales] [float] NULL 

) 

ALTER TABLE [sales_fact_20000101]

ADD PRIMARY KEY (

[date_key], [product_key], [customer_key], [promotion_key], [store_key])

;

-- 创建 2001 年事实表

CREATE TABLE [dbo].[sales_fact_20010101] (

   [date_key] [int] NOT NULL 

CHECK ([date_key] BETWEEN 20010101 AND 20011231),

   [product_key] [int] NOT NULL ,

   [customer_key] [int] NOT NULL ,

   [promotion_key] [int] NOT NULL ,

   [store_key] [int] NOT NULL ,

   [store_sales] [money] NULL ,

   [store_cost] [money] NULL ,

   [unit_sales] [float] NULL 

) 

ALTER TABLE [sales_fact_20010101]

ADD PRIMARY KEY (

[date_key], [product_key], [customer_key], [promotion_key], [store_key])

;



--创建视图

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[sales_fact]'))

DROP VIEW [dbo].[sales_fact]

go

Create VIEW [dbo].[sales_fact]

as

select * from [dbo].[sales_fact_20010101]

union all 

select * from [dbo].[sales_fact_20000101]

union all 

select * from [dbo].[sales_fact_19990101]

go





INSERT INTO [sales_fact]

VALUES (19990125, 347, 8901, 0, 13, 5.3100, 1.8585, 3.0)



INSERT INTO [sales_fact]

VALUES (19990324, 576, 7203, 0, 13, 2.1000, 0.9450, 3.0)



INSERT INTO [sales_fact]

VALUES (19990604, 139, 7203, 0, 13, 5.3700, 2.2017, 3.0)



INSERT INTO [sales_fact]

VALUES (20000914, 396, 8814, 0, 13, 6.4800, 2.0736, 2.0)



INSERT INTO [sales_fact]

VALUES (20001113, 260, 8269, 0, 13, 5.5200, 2.4840, 3.0)

select * from sales_fact

 where date_key > 20000324 

delete from sales_fact

 where date_key > 20001024