Как стать автором
Обновить

Быстрое изменение типа данных колонки в таблице Oracle без использования alter table modify

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

Одному из наших подрядчиков срочно понадобилось изменить тип данных колонки в таблице, для чего он запустил alter table dbwr.ur_mv modify, и попросил проверить выполнение операции на предмет блокировок. Первым делом я оценил размер таблицы и обнаружил, что она занимает 1.5 ТБ, а следовательно окончания операции можно было ждать вечность, о чём я и сообщил подрядчику. Поскольку данные в колонках были не нужны, подрядчик предложил удалить их, и создать новые. А я вспомнил, что мы можем значительно ускорить удаление с помощью опции set unused.

В Oracle есть возможность пометить колонки как неиспользуемые (set unused). Эта операция, в действительности, не удаляет колонки, т.е., не производит освобождение пространства в блоке, а позволяет лишь скрыть их в таблице и в словаре. Поэтому удаление происходит очень быстро. Просмотреть таблицы с такими колонками можно с помощью представлений [dba|all|user]_unused_col_tabs.

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

Поэтому я убил сессию подрядчика, которая выполняла modify и запустил следующее:

alter table dbwr.ur_mv set unused (ena_break_s, enb_segm, enb_break_s);

alter table dbwr.ur_mv add (ena_break_s number(20,4), enb_segm varchar2(12), enb_break_s number(20,4));


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

Реальное удаление колонок можно отложить до более удобного случая, и когда он настанет, надо будет выполнить:

alter table dbwr.ur_mv drop unused columns;

Необходимо отметить, что последний DDL блокирует не всю таблицу, а только то количество строк, которое задано в параметре checkpoint (по умолчанию — 512). Этот параметр задаёт число строк между контрольными точками и служит для управления размером UNDO.
Теги:
Хабы:
Данная статья не подлежит комментированию, поскольку её автор ещё не является полноправным участником сообщества. Вы сможете связаться с автором только после того, как он получит приглашение от кого-либо из участников сообщества. До этого момента его username будет скрыт псевдонимом.