Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Column conversion from NULL to NOT NULL  [new]
Павел-П
Guest
Добрый день.
Есть вот такой вот вопрос к умным людям.
1. Есть таблица с большим числом строк (> 1.000.000.000)
2. В нее необходимо добавить 10-15 колонок, которые должны быть NOT NULL
Делаем следующим образом:
1. Добавляем поле как NULL
2. Заполняем поле NOT NULL значениями
3. Конвертируем колонку из NULL в NOT NULL

Сейчас наибольшее время занимает 3-ий шаг.
Т.е. SQL Server очень долго проверяет, действительно ли нет NOT NULL значений в колонке, прежде чем обновить структуру.
Хотя таких значений там действительно нет.

Соответственно вопрос - это можно как-то ускорить?
11 май 11, 13:23    [10634749]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
Павел-П
Соответственно вопрос - это можно как-то ускорить?

создавать сразу поле NOT NULL с дефолтом
11 май 11, 13:25    [10634765]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Индексами, я полагаю. Шаг 3 будет быстрее, но общее время вряд ли изменится.
11 май 11, 13:25    [10634773]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
Павел-П
1. Добавляем поле как NULL
добавляйте not null с default-значением
11 май 11, 13:27    [10634793]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Гавриленко Сергей Алексеевич
Индексами, я полагаю. Шаг 3 будет быстрее, но общее время вряд ли изменится.
Но таблица будет подоступней из-за того, что Sch-M лок на ней висеть будет гораздо меньше.
11 май 11, 13:27    [10634799]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Короче, все зависит от целей. Что у вас в приоритете, доступность таблицы или итоговое время на операцию?
11 май 11, 13:29    [10634813]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Павел-П
Guest
Knyazev Alexey
Павел-П
Соответственно вопрос - это можно как-то ускорить?

создавать сразу поле NOT NULL с дефолтом


Сразу видно человек не работал с большими объемами данных.
Поиграйтесь как-нибудь на таблице с миллиардами строк - добавить поле NOT NULL с DEFAULT.
Как вы думаете, что будет у Вас с Transaction Log?
11 май 11, 13:29    [10634823]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
iljy
Member

Откуда:
Сообщений: 8711
Павел-П,

можно попробовать схитрить. Повесить на колонку ограничение is not null with nocheck. Поможет или нет - не знаю, но вдруг. Хотя самый быстрый путь - это конечно создание сразу not null поля.
11 май 11, 13:30    [10634828]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Павел-П
Guest
iljy
Павел-П,

можно попробовать схитрить. Повесить на колонку ограничение is not null with nocheck. Поможет или нет - не знаю, но вдруг. Хотя самый быстрый путь - это конечно создание сразу not null поля.


Предлагаемый самый быстрый способ тупо умрет на больших объемах данных.
Неработающий самый быстрый способ - это неработающий способ.
11 май 11, 13:31    [10634844]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Павел-П
Guest
Гавриленко Сергей Алексеевич
Короче, все зависит от целей. Что у вас в приоритете, доступность таблицы или итоговое время на операцию?


Наверное, приоритеты такие
1. Доступность таблицы, где колонки гарантированно не могут быть NULL
2. Затем в приоритете update схемы до NOT NULL
11 май 11, 13:33    [10634863]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Есть еще экзотические варианты, типа балком в новую все перелить спокойно в один поток (чтобы потом ребилдить не пришлось), сохранить на это время историю изменений, смерджить и свапнуть.
11 май 11, 13:34    [10634871]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Тогда сторойте индексы по одному и конвертьте поля. Если, конечно, сервер 2005+.
UPD: индекс можно куда-нибудь на отдельный дисковый массив. Лог... Ну, в лог он залезет, ничего не поделаешь.

Сообщение было отредактировано: 11 май 11, 13:40
11 май 11, 13:38    [10634921]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Павел-П
Guest
Гавриленко Сергей Алексеевич
Есть еще экзотические варианты, типа балком в новую все перелить спокойно в один поток (чтобы потом ребилдить не пришлось), сохранить на это время историю изменений, смерджить и свапнуть.


Вариант интересный. Но в таблице могут быть индексы, которые повлияют на длительность процесса перезаливки.
Или их прийдется пересоздать после заливки. Еще надо смотреть что быстрее.
История изменений не беспокоит. Их гарантированно не будет в процессе обновления схемы.
11 май 11, 13:39    [10634933]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Павел-П
Guest
Гавриленко Сергей Алексеевич,

Не могли бы Вы мне подсказать, как будет блокироваться таблица при создании индекса?
11 май 11, 13:40    [10634946]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Павел-П
Гавриленко Сергей Алексеевич,

Не могли бы Вы мне подсказать, как будет блокироваться таблица при создании индекса?
При онлайновом построении - никак.
UPD: Хотя, Sch-S должна возникать в какие-то моменты.

Сообщение было отредактировано: 11 май 11, 13:41
11 май 11, 13:41    [10634958]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
Павел-П
как будет блокироваться таблица при создании индекса?


зависит от версии и редакции
11 май 11, 13:42    [10634964]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Павел-П
Guest
Гавриленко Сергей Алексеевич,

Простите за занудность, просто хочу убедиться, что понимаю правильно
1. если создам индекс
2. затем буду ковертировать

В этом случае доступность таблицы будет лучше, ибо не будет накладываться блокировка по изменению схемы.
11 май 11, 13:45    [10635010]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Павел-П
Guest
Павел-П,

Не так долго будет накладываться
Сорри за неточность.
11 май 11, 13:45    [10635019]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Павел-П
Гавриленко Сергей Алексеевич,

Простите за занудность, просто хочу убедиться, что понимаю правильно
1. если создам индекс
2. затем буду ковертировать

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

Я, кстати, сам не делал, я эти поля в null оставляю. Но если самому понадобилось бы, делал бы именно так. В общем, не вижу препятствий, почему такая схема не должна работать. Был бы признателен, если бы вы сообщили, получилось или нет. ;)

Сообщение было отредактировано: 11 май 11, 13:49
11 май 11, 13:48    [10635040]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Павел-П
Guest
Гавриленко Сергей Алексеевич,

Поиграюсь, обязательно сообщу.
11 май 11, 13:51    [10635077]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Павел-П
Guest
Гавриленко Сергей Алексеевич,

Забыл сказать БОЛЬШОЕ СПАСИБО за дельные советы.
11 май 11, 13:52    [10635091]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Павел-П
Guest
Гавриленко Сергей Алексеевич
Тогда сторойте индексы по одному и конвертьте поля. Если, конечно, сервер 2005+.
UPD: индекс можно куда-нибудь на отдельный дисковый массив. Лог... Ну, в лог он залезет, ничего не поделаешь.


Вообщем вариант с добавлением индекса не получился.
Не получился из-за того, что SQL Server не позволит Вам конвертнуть колонку из NULL в NOT NULL при наличии на нее индекса.
Вы получите сообщение:
Msg 5074, Level 16, State 1, Line 1
The index 'IX_NullColumn' is dependent on column 'NullColumn'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN NullColumn failed because one or more objects access this column.

Поэтому остается два варианта:
1. Действуем в лоб, т.е. просто вызывает прямую конвертацию каждой из колонок из NULL в NOT NULL.
Недостаток с блокировкой схемы описан выше.
2. Перезаливка таблицы.
Тут недостатки и преймущества тоже думаю понятны.

Какой вариант выбирать - будет зависеть от особенностей вашего окружения и того, как используется Ваша БД.

Вообщем можно фирме Microsoft подсказать вопрос, о котором можно подумать в следующих релизах. :-)
16 май 11, 11:18    [10656923]     Ответить | Цитировать Сообщить модератору
 Re: Column conversion from NULL to NOT NULL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Павел-П
Вообщем вариант с добавлением индекса не получился.
Не получился из-за того, что SQL Server не позволит Вам конвертнуть колонку из NULL в NOT NULL при наличии на нее индекса.
Вы получите сообщение:
Msg 5074, Level 16, State 1, Line 1
The index 'IX_NullColumn' is dependent on column 'NullColumn'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN NullColumn failed because one or more objects access this column.
Логично. :(

Спасибо за информацию.
16 май 11, 12:16    [10657464]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить