Перенос таблицы в другое табличное пространство
Эта статья посвящена сравнению эффективности различных способов переноса таблицы в другое табличное пространство. По мотивам ответа Тома Кайта на вопрос, первоначально заданный еще в апреле 2001 года.
Как выполняется alter table ... move tablespace?
Том!
Не Мог бы ты разъяснить, как реализован оператор alter table t_name move tablespace. Это можно делать в оперативном режиме (online) и без журнализации (с опцией nologging). Но как данные переностяся из одного табличного пространства в другое? Приходится ли серверу формировать операторы insert и передавать данные через буферный кэш как при обычной вставке или происходит что-то типа непосредственной вставки?
Я столкнулся со следующей проблемой, для которой хотел бы найти быстрый способ решения:
Есть таблица размером 2,5 Гбайта. Я хочу перенести ее из табличного пространства a в b. Все файлы a и b разбиты на полосы и находятся на разных дисках. Эта таблица - независима. Я имею ввиду, что для нее не заданы триггеры и ограничения целостности.
Первый способ:
alter table t1 t_name move tablespace b nologging;
Второй способ:
create table temp_table tablespace b as select * from t1; alter table temp_table nologging; insert /* APPEND */ into temp_table select * from t1; drop table t1; rename t1 temp_table ;
Будет ли второй способ работать быстрее, чем первый, за счет использования непосредственной вставки?
Какой сопособ будет работать быстрее, и почему? Допускает ли первый способ распараллеливание? Булет ли существенная разница в использовании сегмента отката и пространства для сортировки?
Ответ Тома Кайта
Действие move в оперативном режиме может выполняться ТОЛЬКО для таблицы, организованной по индексу (index organized table - IOT), но не для обычной таблицы, организованной в виде кучи.
Имеет смысл делать так:
alter table T nologging; (чтобы изменения таблицы не записывались в журнал) alter table T move tablespace b; alter table T logging; (чтобы восстановить журнализацию)
Оператор alter table t move tablespace b nologging; перенесет таблицу (с журнализацией, если она была установлена), а затем установит атрибут nologging.
При выполнении move для переноса таблицы SQL не используется. Никакие вставки не выполняются.
Такой перенос хорош тем, что все индексы, привилегии и т.п. остаются. Необходимо только перестроить (но не пересоздать) индексы после переноса.
Второй способ может сработать быстрее, если использовать параллельные вставки (проверьте, что для задания подсказок оптимизатору используется /*+, а не просто /*.) Но для этого вам придется больше потрудиться. Для таблицы размером 2,5 Гбайт я не уверен, что оно того стоит - может потребоваться больше времени на разработку процедуры переноса, чем на сам перенос.
Перенос табличного пространства с опцией nologging
В Oracle Enterprise Edition 8.1.7.2 я делаю следующее:
alter table T move tablespace b nologging; select table_name, logging from dba_tables where table_name='T'; table_name LOG ---------- --- T YES
Я не знаю, работает это с журнализацией или без, но после переноса таблица в режим nologging не переводится. А в версии Oracle 8.1.6 результат другой?
Ответ Тома Кайта
Хорошо, параметр logging/nologging имеет два значения, в зависимости от контекста.
В том контексте, где вы использовали его выше, вы попросили перенос выполнять без журнализации, если объект допускает работу без журналлизации.
Если выполнить оператор "alter table t nologging", происходит изменение атрибута logging/nologging.
Как всегда, понять это поможет пример. Мы создадим таблицу, перенесем ее и посмотрим, сколько данных повторного выполнения будет сгенерировано при использовании различных методов переноса:
ops$tkyte@ORA8I.WORLD> create table t tablespace UTILS 2 as 3 select * from all_objects; Table created.
Итак, есть тестовая таблица. Она первоначально создана в табличном пространстве UTILS и:
ops$tkyte@ORA8I.WORLD> select table_name, logging from user_tables where table_name = 'T'; TABLE_NAME LOG ------------------------------ --- T YES
ее режим журнализации - YES (журнализация установлена). Теперь давайте посмотрим, сколько данных повторного выполнения уже сгенерировал сеанс, и сохраним это значение в подставляемой переменной V
ops$tkyte@ORA8I.WORLD> column value new_val V ops$tkyte@ORA8I.WORLD> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and a.name = 'redo size' 5 and b.value > 0 6 / NAME VALUE ------------------------------ ---------- redo size 3904956
А теперь выполним вашу команду. Эту команду можно на русском языке сформулировать так: "Перенести таблицу T в табличное пространство users, и, кстати, если можно, БЕЗ ЖУРНАЛИЗАЦИИ". В частности, эта команда НЕ говорит: "Перенести таблицу и изменить режим журнализации".
ops$tkyte@ORA8I.WORLD> alter table t move tablespace users NOLOGGING; Table altered. ops$tkyte@ORA8I.WORLD> select a.name, b.value, b.value-&V diff 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and a.name = 'redo size' 5 and b.value > 0 6 / old 1: select a.name, b.value, b.value-&V diff new 1: select a.name, b.value, b.value- 3904956 diff NAME VALUE DIFF ------------------------------ ---------- ---------- redo size 7790212 3885256
Итак, мы видим, что сгенерировано порядка 4 Мбайт данных повторного выполнения - кажется, что действие, фактически, журнализируется. Это можно подтвердить, ИЗМЕНИВ режим журнализации:
ops$tkyte@ORA8I.WORLD> select table_name, logging from user_tables where table_name = 'T'; TABLE_NAME LOG ------------------------------ --- T YES ops$tkyte@ORA8I.WORLD> alter table t nologging; Table altered. ops$tkyte@ORA8I.WORLD> select table_name, logging from user_tables where table_name = 'T'; TABLE_NAME LOG ------------------------------ --- T NO
и снова перенеся эту таблицу:
ops$tkyte@ORA8I.WORLD> alter table t move tablespace UTILS NOLOGGING; Table altered. ops$tkyte@ORA8I.WORLD> select a.name, b.value, b.value-&V diff 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and a.name = 'redo size' 5 and b.value > 0 6 / old 1: select a.name, b.value, b.value-&V diff new 1: select a.name, b.value, b.value- 7790212 diff NAME VALUE DIFF ------------------------------ ---------- ---------- redo size 7816656 26444
Теперь мы сгенерировали всего лишь 26 Кбайт данных повторного выполнения - этого достаточно для регистрации изменений в словаре данных, но не изменений перенесенных блоков. Мы перенесли объект без журнализации всех изменений.
Фактически мы выяснили, что нельзя одновременно переносить объект и изменять его любым другим способом (эти опции взаимоисключающие - вы либо переносите объект, либо изменяете его другим образом - одновременно это делать нельзя)
ops$tkyte@ORA8I.WORLD> alter table t logging move tablespace utils;
alter table t logging move tablespace utils
*
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations
Можно ли восстановить действие, если используется nologging?
Если происходит сбой базы данных и придется восстанавливать ее после использования nologging для переноса, можно ли будет восстановить это действие...? А после восстановления будет ли тавблица в исходном табличном пространстве?
Ответ Тома Кайта
Это зависит от причины сбоя и других обстоятельств.
После выполнения НЕ ЖУРНАЛИЗИРУЕМОГО действия в базе данных, работающей в режиме ARCHIVELOG (в котором и должны работать практически все производственные базы данных) рекомендуется выполнить горячее резервное копирование затронутых табличных пространств. Это позволит выполнить восстановление после сбоя носителя (media recovery) для этих табличных пространств.
Если этого не сделать И произойдет сбой носителя (а не просто сбой экземпляра) после выполнения не журнализируемого действия - данные будут потеряны. Их нельзя восстановить из архива, поскольку их в архиве нет. При выполнении действий без журнализации следует быть осторожным и согласовать действия с теми, кто отвечает за резервное копирование базы данных. В противном случае, легко потерять данные.
В базе данных, работающей в режиме noarchivelog, поскольку восстанавливаться можно только на момент последней полной резервной копии, выполненной в холодном режиме, при сбое носителя этот вопрос вообще не актуален (потому и надо работать в режиме archivelog!).
Что значит "таблица, огранизованная в виде кучи"?
Ответ Тома Кайта
Вот небольшая цитата из моей книги на эту тему, подробнее - читайте книгу ( Я привожу цитату по моему переводу на русский - В.К. ):
Таблицы, организованные в виде кучи
Таблицы, организованные в виде кучи, используются приложениями в 99 (если не более) процентах случаев, хотя со временем это может измениться за счет более интенсивного использования таблиц, организованных по индексу, - ведь по таким таблицам теперь тоже можно создавать дополнительные индексы. Таблица, организованная в виде кучи, создается по умолчанию при выполнении оператора CREATE TABLE. Если необходимо создать таблицу другого типа, это надо явно указать в операторе CREATE.
"Куча" - классическая структура данных, изучаемая в курсах программирования. Это по сути большая область пространства на диске или в памяти (в случае таблицы базы данных, конечно же, на диске), используемая произвольным образом. Данные размещаются там, где для них найдется место, а не в определенном порядке. Многие полагают, что данные будут получены из таблицы в том же порядке, в каком туда записывались, но при организации в виде кучи это не гарантировано. Фактически гарантировано как раз обратное: строки будут возвращаться в абсолютно непредсказуемом порядке. Это очень легко продемонстрировать. Создадим такую таблицу, чтобы в моей базе данных в блоке помещалась одна полная строка (я использую блоки размером 8 Кбайт). Совсем не обязательно создавать пример с одной строкой в блоке. Я просто хочу продемонстрировать предсказуемую последовательность событий. Такое поведение будет наблюдаться для таблиц любых размеров и в базах данных с любым размером блока:
...
Списки свободных мест
Я перенес таблицы в новое локально управляемое табличное пространство, а затем проанализировал таблицы. Мне интересно, почему столбец NUM_FREELIST_BLOCKS=0 в dba_tables? Во всех таблицах есть неиспользуемые блоки, а в одном блоке - лишь несколько строк.
Ответ Тома Кайта
Потому, что блоки, в которых НИКОГДА не было данных, будут выше отметки максимального уровня, а не в списках свободных мест.
В списки свободных мест блоки попадают после использования - если они никогда не использовались, то в списке свободных мест их не будет.
Сразу после пересоздания, как в вашем случае, вполне естественно, что в списке свободных мест блоков МАЛО, если вообще они там есть. Это просто означает, что все существующие блоки данных "упакованы" - в них больше нельзя вставлять строки. После изменения/удаления данных некоторые блоки окажутся в списке свободных мест.
Рассмотрим следующий пример (табличное пространство system управляется по словарю, а табличное пространство users - локально управляемое):
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;
Table dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t tablespace system as select * from all_objects;
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;
Table analyzed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select num_freelist_blocks from dba_tables
2 where owner = user and table_name = 'T';
NUM_FREELIST_BLOCKS
-------------------
0
Плотно упакованная таблица - никаких блоков в списке свободных мест пока нет...
ops$tkyte@ORA817DEV.US.ORACLE.COM> delete from t where rownum < 100;
99 rows deleted.
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;
Table analyzed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select num_freelist_blocks from dba_tables
2 where owner = user and table_name = 'T';
NUM_FREELIST_BLOCKS
-------------------
1
А теперь - есть; мы добавили блоки в списки свободных мест, удалив некоторые строки.
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t move tablespace users;
Table altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;
Table analyzed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select num_freelist_blocks from dba_tables
2 where owner = user and table_name = 'T';
NUM_FREELIST_BLOCKS
-------------------
0
Теперь их снова нет - все свободные блоки находятся выше отметки максимального уровня (HWM), а не в списках свободных мест.
ops$tkyte@ORA817DEV.US.ORACLE.COM> delete from t where rownum < 100;
99 rows deleted.
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;
Table analyzed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select num_freelist_blocks from dba_tables
2 where owner = user and table_name = 'T';
NUM_FREELIST_BLOCKS
-------------------
1
а вот опять появились - таблица больше не "упакована", так как при удалении часть места освободилась
Перенос таблиц в 7.3.4 Parallel Server
Привет, Том!
Мы используем Oracle 7.3.4 Parallel Server в ОС NCR SVR4 (на неформатированных дисках). Я также использовал второй подход для переноса таблиц в другие табличные пространства, поскольку в версии 7.3.4 оператор alter table move tablespace. Я делал так:
SQL> create table tempfoo tablespace ekatsdat 2 as select * from orgfoo; Table created. SQL> drop table orgfoo cascade constraints; Table dropped. SQL> rename tempfoo to orgfoo; Table renamed.
Затем я снова создал индексы по таблице orgfoo. Мне хотелось бы знать:
a) Хорошее ли это решение для версии 7.3.4? Я нашел твое решение на сайте, где рекомендуется:
- Экспортировать схему пользователя
- Удалить все объекты пользователя
- Отобрать привилегию unlimited tablspace у пользователя
- Изменить стандартное табличное пространство для пользователя
- Импортировать данные пользователя
Но я хочу перенести только одну большую таблицу, а не все таблицы. После удаления всех объектов, как мне импортировать данные в два различных табличных пространства?
b) После переименования таблицы, надо ли пересоздать все представления до запуска приложения?
c) Для таблицы orgfoo (в табличном пространстве EHISTDAT) выделено 250 Мбайт. Эту информацию я получил из dba_data_files и dba_free_space перед удалением таблицы orgfoo.
SVRMGR> Connected. SVRMGR> select fs.tablespace_name,file_name,sum(fs.bytes)/1024/1024 remaining,df.bytes/1024/1024 Total_space from dba_free_space fs, dba_data_files df where fs.file_id=df.file_id group by fs.tablespace_name,file_name,df.bytes order by fs.tablespace_name,file_name; TABLESPACE_NAME FILE_NAME REMAINING TOTAL_SPAC ------------------------------------------------------- EHISTDAT /dev/rdsk/c3t3d2sd 108.708984 795 EKATSDAT /dev/rdsk/c3t3das4 391.285156 395
А при запросе после создания таблицы tempfoo в EKATSDAT и удаления таблицы orgfoo, я получил следующий результат:
TABLESPACE_NAME FILE_NAME REMAINING TOTAL_SPAC ------------------------------------------------------- EHISTDAT /dev/rdsk/c3t3d2sd 358.708984 795 EKATSDAT /dev/rdsk/c3t3das4 281.285156 395
Количество экстентов и блоков в представлении dba_extents тоже отличается. В табличном пространстве EHISTDAT освободилось 250 Мбайт, а в табличном пространстве было выделено не 250, а всего лишь 110 Мбайт. Не могли бы это объяснить? Считаете ли вы подобные действия полезными для экономии места на диске?
Ответ Тома Кайта
Ваш метод вполне приемлем. Можно сделать экспорт отдельной ТАБЛИЦЫ, а не всей схемы - это тоже подойдет, но и ваш метод отлично подходит (если только пересоздать все ограничения, триггеры, привилегии и т.п. - все это утилита EXP делает автоматически).
После переименования таблицы ничего делать не нужно. Представления сами о себе позаботятся, как и хранимые процедуры.
Что касается различия "размеров" - вновь созданная таблица заново "упакована". В результате, она вполне может оказаться "меньше". А вот насчет "полезности для экономии места" - я так не думаю. Через пару недель/месяцев таблица снова вырастет до прежнего размера. Это как когда садятся на диету - вес немного уменьшается, но в конечном итоге он снова увеличивается до "комфортного". Регулярная реорганизация таблиц:
a) мне не кажется нужной
b) мною не рекомендуется (при этом часто приходится слышать "блин, часть данных потеряна" из-за ошибок по ходу реогранизации)
c) место на диске "экономит" на пару дней, а со временем размер снова увеличивается до прежнего стабильного уровня.
Сбой экземпляра при переносе таблицы с опцией nologging
Что произойдет при сбое экземпляра по ходу переноса таблицы с опцией nologging? Мы данные не потеряем? Это не опасно?
Ответ Тома Кайта
Нет, nologging влияет только на восстановление после сбоя НОСИТЕЛЯ, но не после сбоя экземпляра.
При переносе таблицы с опцией nologging таблица копируется из постоянного сегмента во ВРЕМЕННЫЙ сегмент. В самом конце этого действия, временный сегмент преобразуется в постоянный - вото тогда копия и становится реальной таблицей.
Если сбой экземпляра произойдет по ходу переноса, процесс SMON просто очистит временный сегмент, и все будет выглядеть так, как если бы мы вообще таблицу не трогали (постоянный сегмент остается на месте).
Если сбой экземпляра произойдет после переноса - все в порядке, поскольку данные писались непосредственно на диск и восстанавливать их при восстановлении экземпляра не нужно.
Если после переноса часть данных изменена, изменение зафиксировано и произошел сбой - тоде все в порядке, поскольку данные повторного выполнения для этих изменений доступны и их можно восстановить.
Если после переноса и ДО резервного копирования файлов, которые были затронуты действием с опцией nologging произойдет СБОЙ ДИСКА - тогда да, "у нас проблемы". Вот почему в производственной среде есть основания опцию NOLOGGING не использовать, а если уж использовать, то:
- сначала создать резервную копию объектов;
- выполнить действие без журнализации;
- снова создать резервную копию объектов.
Итак, проблем при сбое экземляра вообще не возникает!
У меня есть вопрос вдогонку: поскольку вы сказали, что по ходу переноса постоянный сегмент не трогают, это означает, что запросы продолжают читать данные из постоянной таблицы, а не из временной, не так ли? А можно ли применять к таблице операторы ЯМД? Или таблица блокируется исключительной блокировкой?
Ответ Тома Кайта
Да, по ходу выполнения alter table move данные таблицы можно читать.
Выполнять операторы ЯМД можно только если действие выполняется "online" (alter index rebuild online, например, alter table move online - но только для таблиц, организованных по индексу).
В Oracle9i есть пакет dbms_redefinition для пересоздания в режиме online большинства объектов (что позволяет выполнять операторы ЯМД по ходу переноса).
Комментарий читателя от 4 октября 2002 года
Я видел твой пример переноса таблицы. В нем вместо 4 Мбайт данных повторного выполнения (если при переносе была включена журнализация) генерировалось всего 26 Кбайт.
Я попытался сделать то же самое, но не увидел разницы. Не мог бы ты сказать, что я делаю не так. Вот мои результаты:
... Пропущены, так как не имеют отношения к делу (см. в оригинале. В.К.) ...
Как видишь, когда таблица журнализировалась, было сгенерировано 54320 байта данных повторного выполнения, в без журнализации - 53908 байт. Даже больше на 412 байт.
Я использую Oracle 8.1.7 на Windows 2000.
Ответ Тома Кайта
Вы работаете в режиме noarchivelog.
В этом режиме для этого действия не нужно генерировать данные повторного выполнения - вот они и не генерируются, независимо от установки logging/nologging...
Распараллеливание?
Итак, если необходимо "перенести" таблицу в другое табличное пространство (например, из управляемого по словарю в локально управляемое) быстрее будет использовать INSERT /*+ APPEND*/, перевести таблицу в режим nologging, а не использовать move (с опцией nologging)?
А как насчет распараллеливания в Oracle 8.1.6 STANDARD? Можно ли использовать нечто вроде:
alter table X move tablespace NEW parallel 4;
Команда работает, но я не знаю, как проверить, было ли распараллеливание при выполнении...
Посоветуйте, с точки зрения только производительности, что лучше - INSERT с APPEND nologging или move nologging.
Ответ Тома Кайта
Распараллеливание возможно только в EE и PE. См. в документации
Так что, в SE распараллеливание недоступно.
Но почему вам показалось, что insert /*+ append */ должно быть лучше?
Я бы просто перевел таблицу T в режим nologging и перенес ее:
ops$tkyte@ORA920> alter table t move nologging parallel 4; Table altered.
Это проще, чем insert append, при этом не теряются привилегии и индексы.
(Пороверить, что действие распараллеливается, можно выполнив запрос к v$px_processes по ходу выполнения действия)
Комментарий читателя от 12 июня 2003 года
Я протестировал оба способа, но не в среде SQL*Plus, так что прощу прощения, что не могу просто вырезать и вставить "всю правду".
Я создал два табличных пространства. Я создал таблицу на базе dba_objects и удваивал ее пока в ней не оказалось ~ 1,8 миллиона строк. ~350 Мбайт при размере блока 16 Кбайт.
Затем я перевел таблицу в режим nologging (без распараллеливания).
Тестовая машина - двухпроцессорная, с Oracle 8.1.6 EE и обычными дисками (без RAID). При тестировании каждый тест выполнялся минимум дважды:
alter table move выполняется в среднем 137 секунд. Insert /*+ append*/ выполняется в среднем 125 секунд. alter table move parallel выполняется в среднем 150 seconds, и порождает 4 процесса. alter table move parallel 2 выполняется 60 секунд insert /*+ append parralel */ выполняется около 130 секунд.
Если надо, я повторю эти же тесты в среде SQL*Plus и скопирую результаты.
Итак, почему insert /*+ append*/ выполняется быстрее, чем move?
Данных повторного выполнения генерировалось от 200 до 350 Kбайт.
Ответ Тома Кайта
Я бы сказал, что разница между 137 и 125 секундами (общего времени выполнения) не существенна. 12 секунд ни о чем не говорят - особенно на компьютере, выполняющем еще какие-то действия.
Но, как показывапет ваш же тест, alter table move parallel 2 работает в 2 раза быстрее (первая попытка распараллеливания могла работать медленнее из-за того, что пришлось запускать дочерние процессы PQ - а поскольку запуск 4 процессов занял так много времени, возможно, были конфликты при доступе к исходному или целевому диску)...
Я бы не делал на этом основании вывода, что insert append работает быстрее. Я бы сказал, что это намного сложнее, менее удобно, и вообще неправильно.
Комментарий читателя от 18 июня 2003 года
Интересно, раз таблица была перенесена, то ее индексы стали недействительными и их надо перестраивать, - а как индекс перестраивается? По таблице с новыми значениями rowid (я так думаю) или по существующему индексу (rowid в котором больше нельзя использовать)???
Ответ Тома Кайта
Необходимо обратиться к таблице, чтобы получить значения rowid.