Where do i find the 'throw out the not needed table' in the plan

Dan Holmes 2015-08-31 20:03:04

Given the following statements, where in the SELECT statement's plan do you see that it doesn't need the customer_2 table?

CREATE TABLE dbo.customer_1 (id INT PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100), nick_name VARCHAR(100));
CREATE TABLE dbo.customer_2 (id INT PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100), nick_name VARCHAR(100));
 
INSERT INTO dbo.customer_1 VALUES (1, 'dan', 'white', NULL);
INSERT INTO dbo.customer_2 VALUES (1, 'dan', 'white', NULL);
GO
CREATE VIEW dbo.customer
AS
SELECT 1 tbl_nbr, * FROM dbo.customer_1
UNION ALL
SELECT 2 tbl_nbr, * FROM dbo.customer_2
 
SELECT * FROM dbo.customer WHERE id =1 AND tbl_nbr = 1

(edit)
Also interesting is the difference between the SELECT plan and the UPDATE plan when partitioned views are built based on the MSDN docs. https://technet.microsoft.com/en-US/library/ms190019(v=SQL.105).aspx. The SELECT doesn't show the 'other' table, similar to the above but the UPDATE does.

CREATE TABLE dbo.customer_1 (id INT NOT NULL, first_name VARCHAR(100), last_name VARCHAR(100), nick_name VARCHAR(100), system_id INT NOT NULL DEFAULT 1 CHECK (system_id= 1), PRIMARY KEY (id, system_id));
CREATE TABLE dbo.customer_2 (id INT NOT NULL, first_name VARCHAR(100), last_name VARCHAR(100), nick_name VARCHAR(100), system_id INT NOT NULL DEFAULT 2 CHECK (system_id= 2), PRIMARY KEY (id, system_id));
 
INSERT INTO dbo.customer_1 VALUES (1, 'dan', 'white', NULL, 1);
INSERT INTO dbo.customer_2 VALUES (1, 'dan', 'white', NULL, 2);
GO
CREATE VIEW dbo.customer
AS
SELECT * FROM dbo.customer_1
UNION ALL
SELECT * FROM dbo.customer_2
 
UPDATE dbo.customer SET nick_name = 'adsf' WHERE id = 1 AND system_id = 1
SELECT * FROM dbo.customer WHERE id =1 AND system_id = 1

I was hoping the first version would work with an update. Other than the facts don't support it in MSDN, it really seems like it should work based on the fact that the constant in the view mimics the check constraint.

SQLkiwi 2015-09-01 08:54:54
> *where in the SELECT statement's plan do you see that it doesn't need the customer_2 table?*

You don't, because the contradiction 1 = 2 was detected during optimization, and that part of the tree was thrown away. This is also part of the reason the query wasn't subject to simple parameterization. If you set the database option FORCED PARAMETERIZATION ON, you will see the more general form of the plan, with start-up filters below the union all.

I was hoping the first version would work with an update.

There is still some simplification performed (hence the Constant Scan). Contradiction detection does not go as far as removing the sequence though. This is simply because a rule to perform that empty set removal hasn't been implemented.

Again, with forced parameterization set, you see a more general plan without simplifications based on particular values, featuring start-up filters again.

Plans can often be simplified by the action of generic contradiction detection; specific rules may then be able to remove guaranteed-empty subtrees, though not all possibilities are covered. Partitioned views never received much love in this area. Update plans in general often don't simplify as much because of the risks involved (update processing is ridiculously complex already).