Re: Partioned views /update/insert issue
From: Steve Kass (skass_at_drew.edu)
Date: 12/31/04
- Next message: Mike Labosh: "Re: BULK INSERT"
- Previous message: Mike Labosh: "Re: BULK INSERT"
- In reply to: Abraham: "Re: Partioned views /update/insert issue"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 30 Dec 2004 20:54:25 -0500
Abraham,
I can't explain it, but this equivalent UPDATE statement works the way
it should:
update members
set Last_modify_Date = '12/27/2004'
where status = 1 and members_id=100 and status=1
Since the INSERT is not a query, that may be trickier to work around.
I'll let you know what else I find in any case.
SK
Abraham wrote:
>Hi Steve,
>Thanks for taking time to help me here . Below the sql for creating the
>scenario.
>Thanks
>----------------------------------------------------------------------------------------
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[members_11]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>drop table [dbo].[members_11]
>GO
>CREATE TABLE [dbo].[members_11] (
> [members_id] [int] NOT NULL ,
> [Resp_ID] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Status] [tinyint] NOT NULL ,
> [Status_Count] [tinyint] NOT NULL ,
> [Site_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Click_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WM_ID] [smallint] NULL ,
> [Create_Date] [datetime] NOT NULL ,
> [Create_User] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Last_Modify_Date] [datetime] NULL ,
> [Modify_User] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CHECK ([STATUS] = 1)
>)
>GO
>ALTER TABLE [dbo].[members_11] WITH NOCHECK ADD
> CONSTRAINT [PK_members_11] PRIMARY KEY CLUSTERED
> (
> [members_id],
> [Status]
> )
>
>GO
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[members_22]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>drop table [dbo].[members_22]
>GO
>CREATE TABLE [dbo].[members_22] (
> [members_id] [int] NOT NULL ,
> [Resp_ID] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Status] [tinyint] NOT NULL ,
> [Status_Count] [tinyint] NOT NULL ,
> [Site_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Click_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WM_ID] [smallint] NULL ,
> [Create_Date] [datetime] NOT NULL ,
> [Create_User] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Last_Modify_Date] [datetime] NULL ,
> [Modify_User] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CHECK ([STATUS] >1)
>)
>GO
>ALTER TABLE [dbo].[members_22] WITH NOCHECK ADD
> CONSTRAINT [PK_members_22] PRIMARY KEY CLUSTERED
> (
> [members_id],
> [Status]
> )
>
>GO
>
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[members]') and OBJECTPROPERTY(id, N'IsView') = 1)
>drop view [dbo].[members]
>GO
>CREATE VIEW members as
> select * from members_11 union all
> select * from members_22
>GO
>insert into members
>values(100,'abc@test.com',1,0,'10','13',1000,getdate(),'admin',getdate(),'admin')
>insert into members
>values(101,'abc@test.com',6,0,'10','13',1000,getdate(),'admin',getdate(),'admin')
>insert into members
>values(102,'abc@test.com',2,0,'10','13',1000,getdate(),'admin',getdate(),'admin')
>insert into members
>values(103,'abc@test.com',1,0,'10','13',1000,getdate(),'admin',getdate(),'admin')
>insert into members
>values(104,'abc@test.com',8,0,'10','13',1000,getdate(),'admin',getdate(),'admin')
>GO
>---------- select which works as expected ------------------------------
>select * from members where status=1
>select * from members where status=2
>GO
>--------------- update working NOT as expected ---------------
>update members
>set Last_modify_Date = '12/27/2004'
>where status=1 and members_id=100
>GO
>--------------- insert working NOT as expected ---------------
>insert into members
>values(107,'abc@test.com',1,0,'10','13',1000,getdate(),'admin',getdate(),'admin')
>GO
>------------------------------------------------------------------------------------------------
>
>"Steve Kass" wrote:
>
>
>
>>Can you provide the CREATE TABLE statements and some sample data, so I
>>can take a closer look? See http://www.aspfaq.com/etiquette.asp?id=5006
>>
>>SK
>>
>>Abraham wrote:
>>
>>
>>
>>>Yes , in actual plan it include all the tables!!!!
>>>
>>>"Steve Kass" wrote:
>>>
>>>
>>>
>>>
>>>
>>>>The estimated plan may include all tables, but the actual
>>>>plan may not. Check the actual plan to see if it
>>>>includes all tables.
>>>>
>>>>SK
>>>>
>>>>Abraham wrote:
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>>I have a partioned view called members.
>>>>>Partined based on status of the members.
>>>>>Tables : members_11 ( check status=1)
>>>>> members_22 (check status>1)
>>>>>
>>>>>primary key ( memberid , status)
>>>>>
>>>>>View: members -- create view members as select * from members_11 union all
>>>>>select * from members_22
>>>>>
>>>>>Thinks are working as expected for select queries.
>>>>>But for inser and update it scans both the underlying tables.
>>>>>
>>>>>Here is the scenario:
>>>>>
>>>>>select * from members where status=1 and memberid=1002946452
>>>>>
>>>>>execution plan : just scan members_11 and get the result.
>>>>>---------------------------------
>>>>>update members
>>>>>set Last_modified_Date = '12/27/2004'
>>>>>where status=1 and memberid=1002946452
>>>>>
>>>>>execution plan : scan members_11 and members_22 .
>>>>>---------------------------------------------
>>>>>insert into members
>>>>>values(99999999,'hh@test.com',1,1,'1','1',1,'','',1,1,1,1,'','','','12')
>>>>>
>>>>>
>>>>>execution plan : scan members_11 and members_22 .
>>>>>-----------------------------------------------
>>>>>
>>>>>
>>>>>Is this by design . Partion view can take advandage only for select queries
>>>>>. No adv for update/insert?
>>>>>Or I'm missing something here.
>>>>>Thanks
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
- Next message: Mike Labosh: "Re: BULK INSERT"
- Previous message: Mike Labosh: "Re: BULK INSERT"
- In reply to: Abraham: "Re: Partioned views /update/insert issue"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
