Перенесення таблиці в інше табличний простір


Ця стаття присвячена порівнянню ефективності різних способів перенесення таблиці в інше табличний простір. За мотивами відповіді Тома Кайта на питання, спочатку заданий ще в квітні 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? Я знайшов твоє рішення на сайті, де рекомендується:



Але я хочу перенести тільки одну велику таблицю, а не всі таблиці. Після видалення всіх об'єктів, як мені імпортувати дані в два різних табличних простору?


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.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*