有分区视图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
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
解决方案 »
- 配置 SQL2005 群集实现容错?
- 读取和存储varchar型时遇到的问题
- [今天突然想到]关于某外企的面试题的进一步求解 !
- 如何在sqlserver中建立一log文件,记录自己所指定执行语句.
- 请教大虾一个简单的问题,谢谢
- sql server 2005express是真正免费的吗?在使用上有什么限制呀?[安装时注意什么]
- sql数据项拆分问题
- 如何在字段有索引的情况下修改字段的NULL约束
- [急******]如何实现一个自定义函数,接受sql表的一列整数值,求该列值处于40%百分率点的值?
- 关于DELPHI当中的一个函数的问题!
- vba和sql条件语法?
- 当批量删除或者更新数量超过10W时候,用id in(...)好还是用事物逐条操作好?
当前删除操作也不会删除表sales_fact_19990101中任何数据。---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Sequence
|--Clustered Index Delete(OBJECT:([YMW].[dbo].[sales_fact_20000101].[PK__sales_fact_20000__52593CB8]), WHERE:([YMW].[dbo].[sales_fact_20000101].[date_key] > (20001024)))
|--Clustered Index Delete(OBJECT:([YMW].[dbo].[sales_fact_19990101].[PK__sales_fact_19990__4F7CD00D]))
|--Constant Scan