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

Пусть есть таблица BigLog,
create table BigLog (
  id bigint,
  eventtime datetime,
  eventcode varchar(666)
)

Я хочу, чтобы:
1. По полю eventtime был создан кластерный индекс
2. По нему же производилось секционирование
3. ID был некластерным праймери кеем.

Надеюсь, это в принципе возможно, т.к. если по eventtime будет кластерный индекс, то это поле будет неявно входить в primary key, так что условие, согласно которому столбец секционирования должен содержаться в ключе уникального индекса, фактически выполняется.

Но как это синтаксически записать?

(опыта работы в секционированными таблицами нет)
8 фев 16, 13:38    [18787281]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
Glory
Member

Откуда:
Сообщений: 104760
Примуса починяю
Но как это синтаксически записать?

Взять пример из хелпа ?
Статья так и называется
Creating Partitioned Tables and Indexes
8 фев 16, 13:41    [18787299]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
Mairos
Member

Откуда:
Сообщений: 555
Примуса починяю, вот тут статья на русском по тому материалу, что Glory вам посоветовал
[url=]https://www.sql.ru/articles/mssql/2005/073102partitionedtablesandindexes.shtml[/url]
8 фев 16, 14:32    [18787762]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
Примуса починяю
Guest
Glory
Примуса починяю
Но как это синтаксически записать?

Взять пример из хелпа ?
Статья так и называется
Creating Partitioned Tables and Indexes

Нет там подходящего примера.
Проблема не в том, чтобы создать файловые группы, функцию и схему секционирования.
Проблема в том, чтобы выполнялись пункты 1,2,3 (см.выше).

Т.е. я почему-то могу написать так:
create table BigLog (
  id bigint identity,
  eventtime datetime,
  eventcode varchar(666)
) on partition_scheme(eventtime) 
create clustered index ic_BigLog on BigLog(eventtime) on partition_scheme(eventtime)
alter table BigLog add constraint PK_BigLog primary key nonclustered (id,eventtime) on partition_scheme(eventtime)
, но не могу так:
create table BigLog (
  id bigint identity,
  eventtime datetime,
  eventcode varchar(666)
) on partition_scheme(eventtime) 
create clustered index ic_BigLog on BigLog(eventtime) on partition_scheme(eventtime)
alter table BigLog add constraint PK_BigLog primary key nonclustered (id) on partition_scheme(eventtime)

Возникает ошибка Partition columns for a unique index must be a subset of the index key.
8 фев 16, 16:24    [18788615]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Примуса починяю,

3. ID, eventtime
Что значит "почему-то"? А как Вы предполагаете натянуть не секционированный по времени индекс на секционированную (разделённую на части как торт) по времени таблицу?

Если Вам это не нравится - не секционируйте кластерный индекс (т.е. таблицу), секционируйте только некластерные индексы. Кластерный Вы же создали с какой-то целью не первичным ключом.
8 фев 16, 18:12    [18789295]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Примуса починяю
Возникает ошибка Partition columns for a unique index must be a subset of the index key.
Потому что столбец секционирования нужно указывать в явном виде. И в вашем случае это не означает, что он будет дважды присутствовать в ключе индекса.
8 фев 16, 18:53    [18789518]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
Примуса починяю
Guest
Mairos,

спасибо, познавательно.

Владислав Колосов
Что значит "почему-то"? А как Вы предполагаете натянуть не секционированный по времени индекс на секционированную (разделённую на части как торт) по времени таблицу?


Я исходил из того, что поле, по которому создан кластерный индекс, автоматически включается в некластерные (key lookup производится по нему), и, таким образом, я пытаюсь натянуть СЕКЦИОНИРОВАННЫЙ по времени индекс на секционированную же по времени таблицу.

invm
Потому что столбец секционирования нужно указывать в явном виде. И в вашем случае это не означает, что он будет дважды присутствовать в ключе индекса.


О, чудесно! Как раз дошел до вопроса,
alter table SomeTable add constraint PK_SomeTable primary key nonclustered (ID)
и
alter table SomeTable add constraint PK_SomeTable primary key nonclustered (ID,ClusterfedField)
- это два разных физически индекса, или один. Т.е. можно писать
alter table BigLog add constraint PK_BigLog primary key nonclustered (id,eventtime) on partition_scheme(eventtime)
и не париться, я Вас правильно понял?
8 фев 16, 19:23    [18789723]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Примуса починяю
я Вас правильно понял?
Да, правильно. По метаданным - разные. Физически, по составу столбцов ключа - одинаковые.
8 фев 16, 20:01    [18789890]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
msLex
Member

Откуда:
Сообщений: 8204
Примуса починяю,

Нужно понимать простую вещь, ключ секционирования всегда входит в ключ индекса.

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


Если индекс уникальный, как в вашем случае, сервер не имеет права "самовольно" менять ключ уникального индекса с id на id + eventtime, т.к. это изменит логику. Поэтому он требует от вас сделать соответствующее изменение явно.
8 фев 16, 20:06    [18789908]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Примуса починяю,

а по какому признаку распилить индекс? По времени. А как его распилить, если времени нет в индексе? Никак. Поэтому ошибка.
9 фев 16, 11:35    [18791913]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
Примуса починяю
Guest
ALL

Спасибо за обсуждение.

Владислав Колосов
Примуса починяю,

а по какому признаку распилить индекс? По времени. А как его распилить, если времени нет в индексе? Никак. Поэтому ошибка.


Время есть в индексе. Если по времени кластерный индекс, время в индексе есть, даже если явно не написано. Для примера сделаем тестовую таблицу
create table test(id bigint,eventtime datetime)
create index ix_test on test(id)
;with d(i)as (select 0 union all select i+1 from d where i<99)
insert into test select i, getdate()-i from d

и посмотрим план запроса
select * from test with (index=ix_test) where id=50
. Мы видим Index Seek и Lookup.
Потом делаем кластерный индекс
create clustered index ic_test on test(eventtime)
и смотрим план еще раз. Теперь мы видим только Index Seek, без Lookup. Поcкольку поле eventtime появилось в индексе ix_test.
9 фев 16, 18:43    [18795448]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Примуса починяю
Glory
пропущено...

Взять пример из хелпа ?
Статья так и называется
Creating Partitioned Tables and Indexes

Нет там подходящего примера.
Проблема не в том, чтобы создать файловые группы, функцию и схему секционирования.
Проблема в том, чтобы выполнялись пункты 1,2,3 (см.выше).

Т.е. я почему-то могу написать так:
create table BigLog (
  id bigint identity,
  eventtime datetime,
  eventcode varchar(666)
) on partition_scheme(eventtime) 
create clustered index ic_BigLog on BigLog(eventtime) on partition_scheme(eventtime)
alter table BigLog add constraint PK_BigLog primary key nonclustered (id,eventtime) on partition_scheme(eventtime)
, но не могу так:
create table BigLog (
  id bigint identity,
  eventtime datetime,
  eventcode varchar(666)
) on partition_scheme(eventtime) 
create clustered index ic_BigLog on BigLog(eventtime) on partition_scheme(eventtime)
alter table BigLog add constraint PK_BigLog primary key nonclustered (id) on partition_scheme(eventtime)

Возникает ошибка Partition columns for a unique index must be a subset of the index key.


Потому что нельзя секционировать индекс по колонке, которой в этом индексе нет.

Точно также как вы не можете секционировать таблицу по колонке из другой таблицы.
9 фев 16, 19:25    [18795661]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
invm
Примуса починяю
и не париться, я Вас правильно понял?
Да, правильно. По метаданным - разные. Физически, по составу столбцов ключа - одинаковые.
Ага. И еще по логике совсем разные. Толку от такого primary key (id,eventtime), если он не будет обеспечивать уникальность по ID? Можно тогда просто index создать, зачем обязательно primary key?
9 фев 16, 22:23    [18796444]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
invm
Member

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

Вы правы. Стормозил я...
9 фев 16, 22:47    [18796550]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
Примуса починяю
Guest
invm
Mind,

Вы правы. Стормозил я...

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

Гкх.

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

msLex
Нужно понимать простую вещь, ключ секционирования всегда входит в ключ индекса.

Не сразу осознал силу данного требования.
10 фев 16, 13:18    [18798718]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Примуса починяю
Получается, что для секционированной таблицы вообще нельзя создать суррогатный ключ, гарантирующий уникальность каждой записи в таблице? Это новость космического масштаба.


Налейте себе кофейку и подумайте как следует.

Ваша таблица порезана на сегменты в соответствии с определенной функцией. Вот приходит новая запись. Как движок БД будет определять, а) что записи с таким ключем уже нет б) куда ему её вставлять? Нарисуйте себе ключ секционирования, уникальный ключ, сегменты и помозгуйте.
10 фев 16, 13:47    [18798905]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Уникальность гарантируется в пределах секции данных. Однако, никто не заставляет Вас секционировать данные, можно секционировать только индексы. Тогда данные будут состоять из одной секции, а внутри секции будут уникальными по ключу.
10 фев 16, 15:41    [18799713]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
Примуса починяю
Guest
a_voronin
Как движок БД будет определять, а) что записи с таким ключем уже нет б) куда ему её вставлять?

"Как" - вопрос не ко мне, мы помним, что sql является декларативным, а не императивным языком. За "как" отвечают ребята из Редмонда. Моя задача сформулировать запрос, предоставив достаточную для ответа информацию. И я не вижу, чего в моих "исходных" не хватает.

Допустим, есть секционированная по eventtime таблица. Пришел святой Пигидий, создал уникальный индекс по ID. Теперь приходит запись (id,eventtime). Можно ли определить:
а) что записи с таким ключем еще нет
- да, можно (комментарий не требуется)
б) куда ему её вставлять
- разобьем вопрос на две части
1) в какую секцию таблицы - в ту, на которую укажет функция секционирования, получив eventtime. Ваш Капитан Очевидность.
2) в какую секцию индекса - т.к. в индексе ЕСТЬ eventime, та же функция секционирования точно так же укажет нам нам нужную секцию.

Или, верней, могла бы указать, если бы реализацией секционирования занимались действительно разработчики Microsoft, а не китайцы-аутсорсеры. Другое объяснение не просматривается :(.

С точки зрения логики, ситуация секционированными индексами аналогична ситуации с фильтрованными. И там и там индекс строится для подмножества данных. Но для фильтрованных индексов не существует требования вставлять поля, входящие в секцию where, в список ключей. И все почему-то работает:
create table test(id bigint,eventtime datetime,eventcode varchar(666))
create clustered index ic_test on test(eventtime)
create unique index if_test on test(id) where (eventtime in ('20150213','20150313','20151113','20160513')) 

А для секционированных - не работает.

Владислав Колосов
Уникальность гарантируется в пределах секции данных.

Что тут сказать? Охренеть.

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

Первоначальная идея была в том, чтобы секционировать именно данные :(
10 фев 16, 17:50    [18800741]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Примуса починяю
Но для фильтрованных индексов не существует требования вставлять поля, входящие в секцию where, в список ключей. И все почему-то работает
Внезапно не всегда. (Хоть и оффтоп).
10 фев 16, 17:56    [18800780]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по секционированной таблице  [new]
msLex
Member

Откуда:
Сообщений: 8204
Примуса починяю
Первоначальная идея была в том, чтобы секционировать именно данные :(



Секционируйте, кто вам мешает

делаете секционированный кластерный неуникальный индекс ( тот что у вас есть)
и некластерный невыровненный индекс по id
create clustered index ic_BigLog on BigLog(eventtime) on partition_scheme(eventtime)
create unque index iu_id on BigLog(id) on [primary]
10 фев 16, 17:58    [18800800]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить