Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как правильно создать секционированное представление?  [new]
uaggster
Member

Откуда:
Сообщений: 827
Коллеги, приветствую!

Есть несколько [десятков] региональных баз. Структура таблиц - аналогичная.
Хочется "виртуально" склеить их в одну БД, собрав в данные как секционированное представление, добавив колонку с кодом региона, типа:
Select *, 1 as region from db1.table1 
Union all
Select *, 2 as region from db2.table1 
Union all
...

Проблема в том, что в таблицах БД никакого кода региона нету. И, соответственно, констрейнт region between 1 and 1 в таблицу не вставишь. И условие Where region = 1 во вью тоже не поместишь.

И в результате этого запросы вида:
Select * from all_db.table1 a inner join all_db.table2 b on a.region=b.region and a.id=b.parent_id
Where a.region = 1

Генерируют планы с обращением во все таблицы.
Спасает, если добавить Where a.region = 1 and b.region = 1 Option (recompile), но в случае уже трех таблиц, например - это почему то не работает. Да и не всегда вариант.

Как быть то?
view нужны только на чтение.
28 янв 19, 11:02    [21795571]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно создать секционированное представление?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
uaggster
Как быть то?
region должна быть колонкой во всех таблицах, входить в ПК, и на неё в каждой таблице должен быть наложен соотв чек-констрейн (то есть в db1 - "region=1" и т.д.)
BOL
2.Partitioning column
•The partitioning column is a part of the PRIMARY KEY of the table.
•It cannot be a computed, identity, default, or timestamp column.
•If there is more than one constraint on the same column in a member table, the Database Engine ignores all the constraints and does not consider them when determining whether the view is a partitioned view. To meet the conditions of the partitioned view, there should be only one partitioning constraint on the partitioning column.
28 янв 19, 11:27    [21795606]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно создать секционированное представление?  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
uaggster
Как быть то?
Напишите инлайновую функцию
create function dbo.fnRegionalData
(
 @region int
)
returns table
as
return (
 Select *, @region as region from db1.table1 where @region = 1
 Union all
 Select *, @region from db2.table1 where @region = 2 
 Union all
...
);
28 янв 19, 11:43    [21795617]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно создать секционированное представление?  [new]
iap
Member

Откуда: Москва
Сообщений: 46977
Во-первых, странно видеть имя типа db1.table1. Где имя схемы, спрашивается?
Во-вторых, если написать инлайн-функцию, то можно будет в неё просто вставлять запись
и быть уверенным, что она автоматически вставится в нужную таблицу подобно секционированному view?
Очевидно, это невозможно. Плюс к этому и синтаксис обращения к функции будет отличаться от синтаксиса
обращения к таблице. Наверно, это никого не обрадует.
28 янв 19, 12:28    [21795666]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно создать секционированное представление?  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
iap
Во-вторых, если написать инлайн-функцию, то можно будет в неё просто вставлять запись
и быть уверенным, что она автоматически вставится в нужную таблицу подобно секционированному view?
uaggster
view нужны только на чтение.
28 янв 19, 12:35    [21795676]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно создать секционированное представление?  [new]
uaggster
Member

Откуда:
Сообщений: 827
iap
Во-первых, странно видеть имя типа db1.table1. Где имя схемы, спрашивается?

вторую точку пропустил, прошу прощения.
Читать как: "db1..table1"
28 янв 19, 12:41    [21795682]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно создать секционированное представление?  [new]
uaggster
Member

Откуда:
Сообщений: 827
alexeyvg
uaggster
Как быть то?
region должна быть колонкой во всех таблицах, входить в ПК, и на неё в каждой таблице должен быть наложен соотв чек-констрейн (то есть в db1 - "region=1" и т.д.)
BOL
2.Partitioning column
•The partitioning column is a part of the PRIMARY KEY of the table.
•It cannot be a computed, identity, default, or timestamp column.
•If there is more than one constraint on the same column in a member table, the Database Engine ignores all the constraints and does not consider them when determining whether the view is a partitioned view. To meet the conditions of the partitioned view, there should be only one partitioning constraint on the partitioning column.

Да понимаю я это.
Никакого обходного трюка нет?
28 янв 19, 12:46    [21795687]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно создать секционированное представление?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
uaggster
Никакого обходного трюка нет?
Не знаю такого :-(
очень уж это сложно, видимо, разложить данные по таблицам по таким критериям в общем виде.
Конечно, мы видим глазами, как секционируются данные, но серверу понять, что, оказывается, какая то константа "1 as region", в качестве поля, является колонкой секционирования...
Видимо, это нереально сложно.
28 янв 19, 14:04    [21795844]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно создать секционированное представление?  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
uaggster
Никакого обходного трюка нет?
Вам трюк был показан.
Таблица может быть в плане, но в реальности обращений к ней нет. Именно так работает этот "трюк".
28 янв 19, 14:24    [21795873]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно создать секционированное представление?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4727
uaggster,

Поставьте Вертику и не изобретайте велосипед
28 янв 19, 14:53    [21795907]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно создать секционированное представление?  [new]
uaggster
Member

Откуда:
Сообщений: 827
invm
uaggster
Никакого обходного трюка нет?
Вам трюк был показан.
Таблица может быть в плане, но в реальности обращений к ней нет. Именно так работает этот "трюк".

Да, я его обдумываю. Отпугивает изменение синтаксиса запросов.
28 янв 19, 17:03    [21796068]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно создать секционированное представление?  [new]
uaggster
Member

Откуда:
Сообщений: 827
a_voronin
uaggster,

Поставьте Вертику и не изобретайте велосипед

Да с тем же успехом можно создать отдельную базу с таблицами с кластерным колумнстором, и перегружать данные в нее. Ну или вообще как то перегружать данные в специализированное хранилище.
Это то как раз не проблема (в смысле не проблема понять, что так можно сделать).
Хочется быстро, наивно и прозрачно.
28 янв 19, 17:08    [21796074]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно создать секционированное представление?  [new]
uaggster
Member

Откуда:
Сообщений: 827
alexeyvg
uaggster
Никакого обходного трюка нет?
Не знаю такого :-(
очень уж это сложно, видимо, разложить данные по таблицам по таким критериям в общем виде.
Конечно, мы видим глазами, как секционируются данные, но серверу понять, что, оказывается, какая то константа "1 as region", в качестве поля, является колонкой секционирования...
Видимо, это нереально сложно.

Т.е., играть только по-честному.
Добавить колонку регион not null в каждую таблицу, добавить ее же в кластерный индекс (в конец индекса, чтобы не сломала запросы в региональных базах), добавить констрейнты в каждую таблицу Constraint c_region check region = 1 with check, и, потом, по-честному, в view писать where region = 1.
... и при этом, возможно, на не-энтерпрайзе должным образом всё равно не заработает (было там какое-то говно по секционированным представлениям, не помню какое).
ЧУдно.
:-(
28 янв 19, 17:20    [21796092]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно создать секционированное представление?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7757
uaggster,

это действительно наивно, т.к. при выведения одной из баз ил итаблиц из доступности всё приложение перестанет работать.
28 янв 19, 18:05    [21796138]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно создать секционированное представление?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4727
uaggster
a_voronin
uaggster,

Поставьте Вертику и не изобретайте велосипед

Да с тем же успехом можно создать отдельную базу с таблицами с кластерным колумнстором, и перегружать данные в нее. Ну или вообще как то перегружать данные в специализированное хранилище.
Это то как раз не проблема (в смысле не проблема понять, что так можно сделать).
Хочется быстро, наивно и прозрачно.


Быстро, наивно и прозрачно делается с помощью Вертики или Кликхауса, где все уложено в колумсторы и расшардено по нодам. Из коробки. Вертика до 1Мб бесплатна. Кликхаус бесплатен, но более убог.
28 янв 19, 19:51    [21796207]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно создать секционированное представление?  [new]
uaggster
Member

Откуда:
Сообщений: 827
Владислав Колосов
uaggster,

это действительно наивно, т.к. при выведения одной из баз ил итаблиц из доступности всё приложение перестанет работать.

Это снепшоты, и вся эта конструкция - используется для эпизодических отчетов. Так что это не очень существенно.
Но да. Это проблема.
29 янв 19, 09:09    [21796481]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно создать секционированное представление?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7757
uaggster,

Достаточно кому-то запросом залочить одну из таблиц в одной из баз. Я бы подумал о сборщике данных, тем более что при добавлении-убавлении новых баз не придется переписывать код.
29 янв 19, 11:53    [21796634]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить