Быстрое изменение типа данных колонки в таблице Oracle без использования alter table modify
Invite pending
Чудес, конечно, не бывает, и этот метод подойдёт лишь в небольшом количестве случаев, но вчерашние обстоятельства подтолкнули меня к выводу, что он имеет право на жизнь.
Одному из наших подрядчиков срочно понадобилось изменить тип данных колонки в таблице, для чего он запустил alter table dbwr.ur_mv modify, и попросил проверить выполнение операции на предмет блокировок. Первым делом я оценил размер таблицы и обнаружил, что она занимает 1.5 ТБ, а следовательно окончания операции можно было ждать вечность, о чём я и сообщил подрядчику. Поскольку данные в колонках были не нужны, подрядчик предложил удалить их, и создать новые. А я вспомнил, что мы можем значительно ускорить удаление с помощью опции set unused.
В Oracle есть возможность пометить колонки как неиспользуемые (set unused). Эта операция, в действительности, не удаляет колонки, т.е., не производит освобождение пространства в блоке, а позволяет лишь скрыть их в таблице и в словаре. Поэтому удаление происходит очень быстро. Просмотреть таблицы с такими колонками можно с помощью представлений [dba|all|user]_unused_col_tabs.
Конечно, подход сработает только при двух условиях: вам не нужны данные в колонках; колонки не располагаются в середине таблицы, и вам важен порядок следования колонок. Подрядчику был важен порядок следования, но наши колонки располагались в конце и между ними была только одна мешающая колонка, которой тоже можно было пожертвовать.
Поэтому я убил сессию подрядчика, которая выполняла modify и запустил следующее:
Добавление колонок тоже происходит очень быстро.
Вся операция заняла меньше минуты. modify на тот момент работал уже, как минимум, пол-суток.
Реальное удаление колонок можно отложить до более удобного случая, и когда он настанет, надо будет выполнить:
Необходимо отметить, что последний DDL блокирует не всю таблицу, а только то количество строк, которое задано в параметре checkpoint (по умолчанию — 512). Этот параметр задаёт число строк между контрольными точками и служит для управления размером UNDO.
Одному из наших подрядчиков срочно понадобилось изменить тип данных колонки в таблице, для чего он запустил 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.