Создание секционированного представления таблиц сведений, когда ПРОВЕРКА находится в таблицах заголовков

Я читал документацию и просматривал часто задаваемые вопросы и не нашел ответа на этот вопрос, что, вероятно, означает, что это невозможно сделать. Моя реальная ситуация немного сложнее, но я попытаюсь упростить ее для этого вопроса. Для каждого из прошлых лет у меня есть таблицы заголовков/деталей с внешним ключом, связывающим их. Дата года указана в заголовке! Я хочу иметь возможность запрашивать все таблицы, объединенные в разные годы.

Я настроил представления, соответствующие формату «SELECT + UNION ALL». Я также наложил проверочные ограничения на таблицы заголовков, чтобы ограничить их значения соответствующим годом. Это позволяет оптимизатору запросов SQL Server запрашивать только определенные таблицы при выполнении запроса, ограниченного предложением WHERE. Потрясающий. До этого момента эту информацию можно было найти везде и всюду, выполнив поиск Partitioned Views.

Я хочу выполнить такую ​​же оптимизацию запросов с подробными таблицами, но не могу понять. В подробной записи нет ничего, что указывало бы, к какому году она принадлежит, без соединения с записью заголовка; Это означает, что ограничение внешнего ключа — единственное ограничение, от которого я должен отказаться.

Единственное решение, о котором я подумал, — это добавить столбец «год» в подробные таблицы, а затем добавить еще одно предложение where к запросам. Могу ли я что-нибудь сделать, чтобы создать секционированное представление таблиц сведений, используя существующее ограничение внешнего ключа?

Вот некоторые DDL для справки:

CREATE TABLE header2008 (
    hid INT PRIMARY KEY,
    dt DATE CHECK ('2008-01-01' <= dt AND dt < '2009-01-01')
)

CREATE TABLE header2009 (
    hid INT PRIMARY KEY,
    dt DATE CHECK ('2009-01-01' <= dt AND dt < '2010-01-01')
)

CREATE TABLE detail2008 (
    did INT PRIMARY KEY,
    hid INT FOREIGN KEY REFERENCES header2008(hid),
    value INT
)

CREATE TABLE detail2009 (
    did INT PRIMARY KEY,
    hid INT FOREIGN KEY REFERENCES header2009(hid),
    value INT
)

GO
CREATE VIEW headerAll AS
SELECT * FROM header2008 UNION ALL
SELECT * FROM header2009
GO

CREATE VIEW detailAll AS
SELECT * FROM detail2008 UNION ALL
SELECT * FROM detail2009
GO

--This only hits the header2008 table (GOOD)
SELECT * 
FROM headerAll h
WHERE dt = '2008-04-04'

--This hits the header2008, detail2008, and detail 2009 tables. (BAD)
SELECT * 
FROM headerAll h
INNER JOIN detailAll d ON h.hid = d.hid
WHERE dt = '2008-04-04'

sql-server partitioning database-partitioning
person Marty Neal    schedule 23.05.2011    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Поскольку вы не собираетесь использовать секционированные таблицы, я предполагаю, что вы не можете ориентироваться на 2005+ Enterprise Edition или выше.

Вот альтернатива добавлению нового физического столбца в ваши таблицы:

CREATE VIEW detailAll AS
    SELECT 2008 AS Year, * FROM detail2008
    UNION ALL
    SELECT 2009, * FROM detail2009

потом,

SELECT * 
    FROM headerAll h
    INNER JOIN detailAll d ON h.hid = d.hid
    WHERE dt = '2008-04-04' AND d.Year = 2008

Прежде чем вы убежите и реализуете это, есть одна загвоздка; ну, два улова на самом деле.

Это решение, как и представление headerAll в том виде, в каком оно написано, не может вместить параметры в столбце разделения и по-прежнему устраняет разделение. Использование предиката поиска WHERE dt = @date AND d.Year = YEAR(@date) приводит к сканированию всех таблиц в оба представлениях, поскольку оптимизатор запросов предполагает, что @date является произвольным значением (и исправить это невозможно). Это прямой путь к падению производительности, если представление открыто для всех в API вашей базы данных: нет ограничений на параметризацию в запросах, и большинство авторов запросов и ORM склонны использовать параметризованные запросы везде, где это возможно (это почти всегда хорошо!) .

Чтобы представления выполняли устранение разделов в реальном приложении, вам придется прибегнуть к динамическому выполнению строк. То, как вы это сделаете, будет зависеть от ваших бизнес-требований, требований к данным и архитектуры приложения. Будет немного сложнее, если вы собираете данные за несколько лет.

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

person Jon Seigel    schedule 03.07.2011
comment
Спасибо за ваш ответ. К сожалению, там, где я работаю, дела идут быстро, и я занят другим проектом. Я узнал из вашего ответа и, надеюсь, он будет полезен и другим. - person Marty Neal; 10.08.2011