Нечетный план выполнения при добавлении ограничения в представление раздела

Question

У меня странная проблема с секционированным представлением, которое я настроил.

Например, ради, скажем, у меня есть таблица Orders & Orders_Archive, доступ к которой осуществляется через секционированное представление. Каждый заказ имеет OrderType, доступ к которому осуществляется через внешний ключ и который хранится в таблице просмотра.

Заказы имеют поле, чтобы указать, активен он или нет: [ArchiveYearMonth] char(5), (Я понимаю, что хранить дату в строке не оптимально, но проигнорируйте это ради примера).

Схема для трех таблиц выглядит следующим образом:

CREATE TABLE Orders
(
    Order_ID uniqueidentifier  DEFAULT NEWSEQUENTIALID(),
    OrderType_ID int        NOT NULL,
    ArchiveYearMonth AS ('9999/12') PERSISTED,
    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED  (Order_ID )
)
CREATE TABLE Orders_Archive
(
    Order_ID uniqueidentifier  DEFAULT NEWSEQUENTIALID(),
    OrderType_ID int       NOT NULL,
    CompletedOn datetime2(3) NOT NULL,
    ArchiveYearMonth AS (dbo.[ConvertToYearMonth](CompletedOn)) PERSISTED,
    CONSTRAINT [PK_Orders_Archive] PRIMARY KEY CLUSTERED  (Order_ID )
)
CREATE TABLE OrderTypes
(
    OrderType_ID int IDENTITY(1,1),
    OrderType varchar(100),
    CONSTRAINT [PK_OrderTypes] PRIMARY KEY CLUSTERED  (OrderType_ID )
)
GO

Я настроил вид так:

CREATE VIEW AllOrders_ProperFilter
AS
    SELECT Order_ID, OrderType, ArchiveYearMonth
    FROM Orders o INNER JOIN OrderTypes oT on o.OrderType_ID = oT.OrderType_ID
    WHERE ArchiveYearMonth = '9999/12'
    UNION ALL
    SELECT  Order_ID, OrderType, ArchiveYearMonth
    FROM Orders_Archive o INNER JOIN OrderTypes oT on o.OrderType_ID = oT.OrderType_ID
    WHERE ArchiveYearMonth <>'9999/12'
GO

Что работает, как и ожидалось, при прямом запросе к нему:

SELECT * FROM AllOrders_ProperFilter WHERE ArchiveYearMonth = '9999/12'

Фактический план выполнения приводит к тому, что сканируются только заказы и типы заказов:

Таблица "Типы заказов". Сканирование 1, логическое чтение 52, физическое чтение 0, чтение с опережением 0, логическое чтение с 0, физическое чтение с 0, чтение с опережением 0.
Таблица "Заказы". Сканирование 1, логическое чтение 102, физическое чтение 0, чтение с опережением 0, логическое чтение с 0, физическое чтение с 0, чтение с опережением 0.

Однако, когда я присоединяюсь к разделенному представлению против другой таблицы, я получаю неожиданное поведение в плане выполнения.

Установите небольшую таблицу с некоторыми записями из таблиц Orders и Order_Archive:

SELECT * INTO #tempTbl
FROM
(
    SELECT TOP (10) Order_ID
    FROM Orders
    UNION ALL 
    SELECT TOP (10) Order_ID
    FROM Orders_Archive
) z

Теперь объедините исходный вид с временной таблицей:

SELECT * 
FROM AllOrders_ProperFilter pView
WHERE Order_ID IN (SELECT Order_ID FROM #tempTbl) AND ArchiveYearMonth  = '9999/12'
option (recompile)

С результатами все в порядке, и только таблица заказов запрашивается, но план выполнения ужасен. Обе таблицы Orders & OrderTypes сканируются, объединяются, а затем соединяются с временной таблицей. (Таблица заказов содержит 20000 записей; временная таблица содержит 20).

План выполнения для AllOrders_ProperFilter

Теперь, если я удаляю разделение из представления и запрашиваю его, я получаю другой результат:

CREATE VIEW AllOrders
AS
    SELECT Order_ID, OrderType, ArchiveYearMonth
    FROM Orders o INNER JOIN OrderTypes oT on o.OrderType_ID = oT.OrderType_ID
    WHERE ArchiveYearMonth = '9999/12'
    UNION ALL
    SELECT  Order_ID, OrderType, ArchiveYearMonth
    FROM Orders_Archive o INNER JOIN OrderTypes oT on o.OrderType_ID = oT.OrderType_ID
 --   WHERE ArchiveYearMonth <>'9999/12'
GO

План выполнения теперь сканирует временную таблицу, использует PK для поиска по таблице Orders, а затем присоединяется к OrderTypes. Ожидается, что он также выполняет запросы к таблице Orders_Archive, поскольку ограничение снято.

План выполнения против AllOrders

Почему представление с обоими ограничениями ([AllOrders_ProperFilter]) работает так плохо, когда к нему присоединяется другая таблица?

Примечание. Я получаю те же результаты, когда добавляю фактические ограничения к таблицам:

ALTER TABLE Orders ADD CONSTRAINT
    CK_NOTARCHIVED CHECK (ArchiveYearMonth = '9999/12')
ALTER TABLE Orders_Archive ADD CONSTRAINT
    CK_ARCHIVED CHECK (ArchiveYearMonth <> '9999/12')

Очевидно, я не могу опубликовать более двух ссылок, но у меня есть пример сценария, который воссоздает проблему с фиктивными данными.