Меню

Oracle создание таблицы с партициями

Oracle создание таблицы с партициями

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

1) Создать новую партиционированную таблицу и залить данные из старой
CREATE TABLE . PARTITION BY (RANGE).
INSERT INTO . SELECT * FROM not_partitioned_table;
или
INSERT /*+ append */INTO . SELECT * FROM not_partitioned_table;

2) Создать новую партиционированную таблицу из старой таблицы
CREATE TABLE partitioned_table
PARTITION BY (RANGE) .
AS SELECT * FROM not_partitioned_table;

3) С помощью EXCHANGE PARTITION
ALTER TABLE partitioned_table
EXCHANGE PARTITION calls_01012008
WITH TABLE not_partitioned_table;

4) С помощью пакета DBMS_REDEFITION

Во всех вышеперечисленных вариантах партиционирования существующей таблицы для активной системы по любому понадобится даунтайм таблицы, кроме 4 пункта(использование DBMS_REDEFINITION).

Теперь, попробую более подробно описать каждый из вариантов.
Скажем, у нас есть партиционированная таблица NOT_PARTITIONED_TABLE, в которой хранятся данные о звонках.В итоге, у нас есть непартиционированная таблица NOT_PARTITIONED_TABLE с 1 миллионом записей о звонках с 1 по 7 января 2008 года:Наша цель: партиционировать эту таблицу с наименьшим даунтаймом таблицы.

Вариант 1: Создать новую партиционированную таблицу и залить данные из старой

План действий такой:
— Создаем новую партиционированную таблицу
— Можно заблокировать непартиционированной таблицу, чтоб никто не смог изменить данные во время заливки
— Заливаем данные из непартиционированной таблицу
— Удаляем непартиционированную таблицу
— Переименовываем новую партиционированную таблицуПеред заливкой заблокируем таблицу в режиме EXCLUSIVE командой LOCK TABLE, чтоб пока мы заливаем данные, никто не смог изменить данные в них или добавить новые.Теперь зальем данные командой INSERT. Сперва посмотрим заливку обычным INSERTом:Заливка 1 миллиона записей длилась почти 8 минут. Инсерт сгенерил 35 909 076 байт redo — это почти равно размеру непартиционированной таблицы, таблица весит 33 554 432 байт.Теперь попробуем залить те же данные прямым инсертом (direct path insert), командой INSERT /*+ APPEND */.

Direct Path Insert отличается от обычного инсерта тем, что вставка происходит:
1) в обход буферного кеша
2) новые блоки добавляются за отметкой HWM

То есть новые блоки данных подготавливаются в pga сессии и в обход буферного кеша добавляются ЗА отметкой HWM , если даже до отметки HWM есть свободное место для данных. Как объясняет Том Кайт, неправильно считать, что при таком инсерте (direct path insert) вообще не генерится redo. Redo по-любому генерится, но совсем малюсенький по сравнению с обычным инсертом, что существенно увеличивает скорость заливки.
После таких заливок следует сделать полный бэкап базы на всякий случай.Заливка «инсерт аппендом» закончилась за 3 минуты (почти в 3 раза быстрей обычного инсерта), и сгенерил 343352 байтов redo (почти в 100 раз меньше чем при обычном инсерте).

В три раза быстрей чем обычный инсерт, но как можно еще как-то ускорить заливку? Есть один способ: можно заранее выделить необходимый объем экстентов партициям , чтоб во время заливки на это не тратилось время.После предварительного выделения эктентов партициям, инсерт аппенд отработал всего за 5 секунд. А обычный инсерт отработал за 11 секунд:Для ускорения заливки и уменьшения даунтайма таблицы, можно заранее выделить необходимые эктенты партициям, чтоб во времы заливки на эту рекурсивную операцию не тратилось время.

Теперь осталось удалить старую таблицу и переименовать партиционированную таблицу.Можно и партиции переименовать (но их можно было бы создать с нужным именем изначально):
В следующие варианты — в следующем выпуске новостей.

Источник



Корпоративные хранилища данных. Интеграция систем. Проектная документация.

Oracle Partitioning: Оперативное перемещение и восстановление исторических данных

1. Введение

При решении задачи хранения и обеспечения доступа к историческим данным очень часто возникает задача выгрузки архивных данных на резервный носитель (например, на магнитную ленту) с возможностью оперативного восстановления этой информации и обеспечения доступа к ней пользователей. Эта проблема наиболее актуальна для хранилищ данных, хотя может применяться и для обработки архивных данных OLTP-систем.

В данной статье описывается способ решения этой проблемы с помощью опции Partitioning базы данных Oracle Database.

Ниже представлена иллюстрация данного подхода, который включает в себя: идентификацию исторических данных, их перемещение во временную таблицу, экспорт и копирование на резервный носитель.

Иллюстрация подхода перемещения исторических данных

Первым шагом является определение секций, содержащих исторические данные. Исторические данные – это данные за прошлые периоды, над которыми в будущем не будут проводиться операции изменения. Затем секции, содержащие исторические данные, перемещаются в заранее подготовленную временную таблицу. Следующим шагом производится экспорт метаданных для Transport Table Space (TTS). В заключении производится перенос файла с метаданными и файла табличного пространства на резервный носитель.

Далее будет детально рассматриваться процесс экспорта и импорта табличного пространства для одного раздела секционированной таблицы CALLS (информация о телефонных звонках клиентов) схемы DWH.

Описанный подход был принят как основной для задач перемещение и восстановление исторических данных хранилища корпоративной информации компании “ОАО Ростелеком”.

2. Определение исторических данных

Для выявления исторических данных, то есть тех данных которые не будут больше изменяться, администратор должен ежемесячно проводить мониторинг их появления. Перечень данных, которые следует признавать историческими, определяют бизнес-требования. Часто правило определения исторических данных сводится к такому условию: историческими признаются те данные, срок хранения которых превышает определенный лимит, например, 5 лет от текущего момента.

Для автоматизации выявления исторических данных в конкретной таблице фактов, возможно выполнение следующего запроса (обращение к словарю Oracle Database):

Данный запрос вернет перечень разделов (см. поле PARTITION_NAME) по таблицам, данные в которых являются историческими (срок хранения превышает 5 лет). Эти данные необходимо архивировать и перенести на резервный носитель.

3. Перемещение исторических данных

Для перемещения раздела таблицы с историческими данными будет использована технология перемещаемых табличных пространств (Transportable Tablespace). Для перемещения табличных пространств необходимо провести следующие действия:

  1. Создать временную таблицу, в которую будут перемещены исторические данные.
  2. Переместить во временную таблицуисторические данные путем смены разделов (exchange partition).
  3. Убрать все логические и физические связи табличного пространства и раздела таблицы со всеми объектами кроме временной таблицы.
  4. Сделать табличное пространство доступным только для чтения (read only).
  5. Сделать экспорт метаданных табличного пространства раздела с историческими данными (для успешного выполнения экспорта и импорта необходимо, чтобы пользователь, из-под которого выполняются данные операции, обладал правами exp_full_database и imp_full_database соответственно).
  6. Скопировать файл с метаданными и файлы данных табличного пространства с историческими данными в папку для переноса на резервный носитель.
  7. Сделать архив, включив в него: файл с метаданными, файлы табличного пространства, дополнительный файл с описанием.
  8. Удалить табличное пространство с историческими данными из БД.

Ниже приведена последовательность действий по перемещению исторических данных из раздела P_0106 таблицы CALLS.

Данные раздела P_0106 хранятся в табличном пространстве TBS_CALLS_0106_1, которое в свою очередь, состоит из двух файлов: TBS_CALLS_0106_1_001.dbf и TBS_CALLS_0106_1_002.dbf.

Ниже все скрипты будут выполняться из-под пользователя system.

4. Создание временной таблицы

Создадим временную таблицу, в которую в последствии переместим раздел с историческими данными.

5. Перемещение данных во временную таблицу

Выполняем команду смены раздела (exchange paertition) P_0106 (раздел с историческими данными) между таблицей CALLS и временной таблицей CALLS$EXP$P_0106.

6. Удаление связей

Сделать экспорт метаданных табличного пространства можно только тогда, когда оно не связано с другими объектами базы данных.

Для проверки наличия связей необходимо выполнить следующие процедуру и запрос (их необходимо выполнять из-под пользователя SYS):

Если запрос к представлению TRANSPORT_SET_VIOLATIONS возвращает записи, то это значит, что взаимосвязи раздела с другими объектами базы данных существуют. Необходимо, чтобы запрос к данному представлению НЕ возвращал строк. Для этого необходимо изменить табличные пространства для раздела P_0106 таблицы CALLS – переместить раздел в табличное пространство TBS_CALLS_0106_HIST и переместить метаданные о таблице CALLS$EXP$P_0106 в табличное пространство TBS_CALLS_0106_1:

Выполним проверку наличия взаимосвязей повторно.

В представлении TRANSPORT_SET_VIOLATIONS записи отсутствуют – взаимосвязей нет.

Читайте также:  Таблица очков формулы один

7. Атрибут «только для чтения»

Сделать экспорт метаданных табличного пространства можно только тогда, когда оно находится в режиме «только для чтения». Сделать табличное пространство доступным только для чтения можно, выполнив следующую команду:

8. Экспорт табличного пространства

Произведем экспорт метаданных табличного пространства. Для этого будет использована технология DataPump и, соответственно, утилита expdp.

В командной строке необходимы выполнить команду экспорта (см. скрипт – export.sh) в директорию определенною в переменной DATA_PUMP_DIR базы данных.

Перейдем в директорию, которую определяет переменная DATA_PUMP_DIR.

Просмотрим ее содержимое.

9. Копирование файлов

Скопируем файл с метаданными TBS_CALLS_0106_1.DMP и файлы данных БД TBS_CALLS_0106_1_001.dbf, TBS_CALLS_0106_1_002.dbf в директорию /backup/DWH/TBS_CALLS_0106_1_HIST, предназначенную для временного хранения архивов, перед переносом на резервный носитель. Предварительно директорию TBS_CALLS_0106_1_HIST необходимо создать в /backup/DWH/.

Рекомендуется создать текстовый файл /backup/DWH/TBS_CALLS_0106_1.txt, в котором описать месторасположение файлов с данными экспортируемого табличного пространства. И затем включить данный текстовый файл в архив.

Для создания файла с описанием можно выполнить следующие действия (в операционной системе Unix):

  • Создать файл: touch TBS_CALLS_0106_1.txt.
  • Открыть файл на редактирование: cat > TBS_CALLS_0306_1.txt.
  • Внести в файл текст.
  • По окончанию редактирования файла нажать Cntr+D.

10. Создание архива

Создадим архив с содержимым директории TBS_CDR_0306_1_HIST, используя утилиту tar. Этот архив, впоследствии, и будет перемещен на резервный носитель.

Архив создан. Теперь можно удалить исторические данные из таблицы БД.

11. Удаление табличного пространства

Удалим табличное пространство TBS_CALLS_0106_1

Вместе с табличным TBS_CALLS_0106_1 пространством удалится и временная таблица CALLS$EXP$P_0106.

Для облегчения в дальнейшем процесса восстановления в таблице с данными (в нашем примере это таблица CALLS) раздел, в котором были исторические данные, лучше оставить.

12. Восстановление исторических данных

Для восстановления исторических данных из архива необходимо провести следующие действия:

  1. Скопировать архив с историческими данными с резервного носителя в директорию для восстановления.
  2. Распаковать архив.
  3. Скопировать файл с метаданными в папку для восстановления и файлов с данными в папку (или папку) сервера базы данных, где они находились до проведения экспорта.
  4. Импорт исторических данных во временную таблицу.
  5. Смена табличных пространств.

13. Копирование и распаковка архива

Скопируем архив с историческими данными с резервного носителя в директорию для восстановления. В нашем примере это будет директория /backup/Restore. Обычно эту функцию выполняет администратор системы резервного копирования.

Подключимся к серверу, на котором работает наша СУБД, под пользователем операционной системы oracle, используя командную строку.

Извлечём файлы из архива.

14. Копирование файлов

Скопирем файл с метаданными TBS_CALLS_0106_1.DMP в директорию /u01/app/oracle/product/10.2.0/db_1/admin/DWH/dpdump/,

файл данных TBS_CALLS_0106_1_001.dbf в директорию /wh/oracle/disk1/DWH/;

файл данных TBS_CALLS_0106_1_002.dbf в директорию /wh/oracle/disk0/DWH/.

15. Импорт исторических данных

Выполним команду экспорта метаданных табличного пространства (см. скрипт – import.sh) в директорию, определенную в переменной DATA_PUMP_DIR базы данных.

После окончания импорта метаданных табличного пространства в схеме DWH появится таблица CALLS$EXP$P_0106.

16. Смена табличных пространств

Осуществим смену (partitio6 exchange) между таблицей CALLS$EXP$P_0106 и таблице CALLS.

17. Заключение

База данных Oracle Database предоставляет гибкий механизм управления табличными пространствами секционированных таблиц, что позволяет достаточно просто организовать управление архивными данными, как в OLTP-системах, так и в хранилищах данных.

Полный архив скриптов можно загрузить по данной ссылке.

18. Дополнительная информация

  • Oracle Database Utilities 10g Release 2 (10.2) Part Number B14215-01 (раздел посвященный DataPump).
  • Doc ID: 09585.1 от 04.09.2002 на Oracle Metalink.
  • Doc ID: 114915.1 от 30.03.2008 на Oracle Metalink.

Oracle Magazine Online — Русское издание. Ковтун М.В. 2009

Источник

Секционирование достигает совершенства

(Partitioning to Perfection, by Arup Nanda )

Аруп Нанда,
Член-директор коллегии Oracle ACE

Источник: сайт корпорации Oracle, серия статей «Oracle Database 11g: The Top New Features for DBAs and Developers»
(«Oracle Database 11g: Новые возможности для администраторов и разработчиков»), статья 2
http://www.oracle.com/technetwork/articles/sql/11g-partitioning-084209.html

В Oracle Database 11g выбор способа секционирования теперь практически не ограничен.

«Разделяй и властвуй» («Divide and conquer») — этот фигуральный принцип никогда не был проиллюстрирован лучше, чем в возможностях секционирования в Oracle Database. Начиная с версии 8, таблицу или индекс можно разделить на несколько секций, которые затем поместить в различные табличные пространства. Таблица по-прежнему является логической сущностью, в то время как отдельные секции хранятся как отдельные сегменты, что позволяет легко манипулировать данными.

В версии 11 такие новшества, как: ссылочное секционирование (reference partitioning), интервальное секционирование (interval partitioning), секционирование по виртуальным столбцам (partitioning virtual columns) и расширенное смешанное секционирование (extended composite partitioning), дают безграничные возможности проектирования и обеспечения управления секциями.

Если вам необходимо понакомиться с основами секционирования и факторами, влияющими на решение по выбору столбцов или схемы секционирования, смотрите, пожалуйста, мою статью http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56partition-090450.html в Oracle Magazine за сентябрь/октябрь 2006.

Расширенное смешанное секционирование (Extended Composite Partitioning)

При смешанном секционировании — эта схема известна с Oracle8i Database — можно создавать подсекции секций, позволяя ещё больше измельчать таблицу. Однако в этой версии можно было создавать подсекции таблиц с диапазонными секциями только хэш-методом. В Oracle9i смешанное секционирование было расширено включением диапазон-списка подсекций.

Эти схемы удовлетворяет большинству случаев, но не всем. Допустим, например, есть таблица SALES, у которой много столбцов, включая два специальных — кандидатов для секционирования:

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

    В Oracle Database 11g можно решить проблему очень легко. Эта версия не ограничена смешанным секционированием по схеме диапазон-хэш или диапазон-список. Напротив, выбор совершенно неограничен, вы можете создавать смешанные секции в любых комбинациях.

    В этом примере можно выбрать LIST-секционирование таблицы по product_code, так как этот столбец имеет более дискретные значения, а затем создать подсекции по state_code также по списку. Приведенный ниже код примера показывает, как это сделать:

    Варианты не ограничиваются теми, что здесь показаны. Можно также создать смешанные секции LIST-RANGE. Предположим, что в примере выше код продукта не дискретный, а выбирается из некоего диапазона. Вы можете создать секции по списку по столбцу state_code, а затем подсекции по product_code. Ниже приведен код, который демонстрирует это.

    Можно создать смешанные подсекции типа диапазон-диапазон, которые будут очень полезны, когда имеются два поля-даты. Рассмотрим, например, таблицу для системы обработки продаж, в которой есть дата транзакции и дата доставки. Можно создать секции по диапазону одной даты и также подсекции по диапазону другой. Эта схема позволяет выполнять резервное копирование, архивирование и очистку, основываясь на этих датах.

    Как итог, в Oracle Database 11g можно создавать следующие типы смешанных секций:

    • Диапазон-диапазон (Range-range)
    • Диапазон-хэш (Range-hash)
    • Диапазон-список (Range-list)
    • Список-диапазон (List-range)
    • Список-хэш (List-hash)
    • Список-список (List-list)

    Ссылочное секционирование (Reference Partitioning)

    Вот типичная проблема в проектировании схем секционирования: не все таблицы имеют одни и те же столбцы, которые нам нужны для секционирования. Предположим, вы создаёте систему продаж с двумя простыми таблицами sales и customers:

    Таблица sales создана, как показано ниже. Это подчинённая таблица для таблицы customers.

    В идеале надо бы секционировать таблицу sales так же, как таблицу customers: секции по списку значений столбца rating. Однако возникает серьёзная проблема: в таблице sales нет столбца rating! И как же секционировать её по несуществующему столбцу? В Oracle Database 11g можно использовать новую возможность Reference Partitioning (Ссылочное секционирование). Вот пример, показывающий, как применить эту возможность к таблице sales:

    create table sales ( sales_id number primary key, cust_id number not null, sales_amt number, constraint fk_sales_01 foreign key (cust_id) references customers ) partition by reference (fk_sales_01);

    Читайте также:  Элементы третьей подгруппы периодической таблицы менделеева

    В этом случае создаются секции, идентичные таблице-мастеру, то есть customers. Заметьте, что столбца rating по прежнему нет, хотя таблица секционирована именно по этому столбцу. В выражении partition by reference (fk_sales_01) указано название внешнего ключа в описании секции. Oracle Database 11g показывает, что секционирование выполнено по схеме мастер-таблицы (parent table) — в данном случае, customers. Заметьте, что ограничение целостности по столбцу cust_id — NOT NULL; это требование к ссылочному секционированию.

    Проверим границы секций таблицы sales:

    Значение high value пусто, а это означает, что границы наследуются из мастер-таблицы. Секции имеют такие же названия, как и у мастер-таблицы. Тип секционирования можно проверить, выполнив запрос по представлению user_part_tables. Специальный столбец ref_ptn_constraint_name показывает название ограничения целостности для внешнего ключа.

    Применение ссылочных секций весьма полезно, когда нужно секционировать подчинённую таблицу так же, как мастер-таблицу, но в подчинённой таблице нет тех же столбцов, и вы не хотите создавать их исключительно ради секционирования. В этом случае нет необходимости явно объявлять длинное выражение для секционирования каждой подчинённой таблицы.

    Интервальное секционирование (Interval Partitioning)

    Интервальное секционирование позволяет создавать секции, основанные на диапазонах значений столбца-ключа секционирования. Вот пример таблицы с интервальным секционированием:

    В ней определены секции только для января 2007 и февраля 2007, поэтому что будет, если вставляемая в таблицу запись имеет sales_dt за март 2007? Вставка не произойдет, будет выдан сообщение с ошибкой:

    Очевидно, что перед тем, как вставлять запись, необходимо добавить секцию за март 2007. Однако часто это легче сказать, чем сделать. Иногда нет возможности предусмотреть создание множества секций заранее, и некоторые из них могут возвращать эту ошибку.

    Не проще ли будет, если бы Oracle как-нибудь автоматически распознавал необходимость новых секций и создавал их? Oracle Database 11g это умеет делать для механизма Interval Partitioning (интервального секционирования). В примере ниже определяются не секции и их границы, а только интервал, который определяет границы каждой секции. Вот демонстрационный пример такого интервального секционирования:

    Заметьте, что интервал следует за интервалом. Это из инструкции Oracle по созданию интервалов для каждого месяца. Создаётся также начальная секция p0701 для января 2007. Теперь предположим, что вставляется запись за июнь 2007:

    Oracle не возвращает ошибку; наоборот, он успешно выполняет предложение. И где же тогда находится вставленная запись? Секция p0701 не может содержать такую запись, а секция за июнь 2007 не описывалась. Однако проверим секции таблицы ещё раз:

    Заметьте, что секция SYS_P1 с верхним значением 1 июля 2007 будет накапливать данные до конца июня. Эта секция создана динамически Oracle и имеет имя, сгенерированное системой.

    Теперь предположим, что вводится значение меньше максимального, например 1 мая 2007. Оно идеально соответствует его собственной секции, так как секционный интервал — это месяц.

    Заметьте, что новая секция SYS_P42 имеет верхнюю границу 1 июня — такая секция может содержать данные за май 2006. Эта секция создана делением секции SYS_P41 (за июнь). Таким образом, Oracle автоматически создаёт и управляет секциями, когда описана схема интервального секционирования. Если секции необходимо создавать в отдельных табличных пространствах, следует использовать выражение store in:

    тогда секции сохраняются в табличных пространствах TS1, TS2 и TS3 по очереди по кругу.

    Как разработчик приложения может обратиться к какой-либо секции? Один из известных способов — по названию — может быть невозможным и даже часто приводящим к ошибкам, как вы знаете. Для обеспечения доступа к некоторой секции Oracle Database 11g предлагает новый синтаксис запросов:

    Заметьте, что новое выражение for (значение) позволяет напрямую ссылаться на секции без явного обращения по их точному названию. Если необходимо очистить или удалить секцию, можно использовать этот дополнительный синтаксис.

    Когда таблица создана так, как показано выше, столбец PARTITIONING_TYPE представления DBA_PART_TABLES показывает значение INTERVAL.

    Системное секционирование (System Partitioning)

    Хотя Oracle предполагает, что лишь немногие будут использовать на практике эту возможность, я хочу описать её, потому что очень уж она хороша.

    Это редкое, но не невообразимое применение: представьте, что у вас есть таблица, которая не может быть секционирована никаким логическим путём. В итоге – огромная монолитная таблица, которая озадачивает такими проблемами, как необходимость расширенного индексирования и других операций.

    Поэтому разработчики могут принять следующее решение: они обещают, что если как-нибудь таблицу можно будет секционировать, то они по-умному создадут секции. А пока приложение само управляет, какую запись в какую секцию помещать. Администратору базы данных необходимо просто описать секции. Например:

    Заметьте, что не описано ни ключей секционирования, ни границ. Поэтому таблица физически разделена на два сегмента, хотя это целая логическая таблица. Когда описан такой метод, база данных создаёт два табличных сегмента вместо одной монолитной таблицы. Это можно проверить:

    Когда создается локальный индекс, он секционируется таким же способом.

    Тип секционирования можно проверить по user_part_tables:

    Результат показывает SYSTEM, что, конечно же, обозначает системное секционирование. Отметим то обстоятельство, что столбец high_value имеет значение NULL для таблиц такого типа.

    А вот интересный вопрос: если нет ключа или схемы секционирования таких, как диапазон, список или хэш, то как Oracle узнает, в какую секцию поместить входящую запись?

    Ответ: Oracle этого не делает. Вот пример того, что происходит, если необходимо вставить запись в таблицу:

    Границы секций неизвестны, поэтому приложение должно обеспечить эту информацию, используя секция-подобный синтаксис при вставке данных. Предложение необходимо переписать:

    При удалении не обязательно использовать этот синтаксис — но помните, границы секций отсутствуют. Поэтому при выполнении предложения типа:

    Oracle должен просканировать все секции, чтобы увидеть, где расположена строка. Чтобы этого избежать, следует написать так:

    Также выполняются и изменение данных. Такой способ ограничивает перечень секций, по которым выполняется поиск.

    Системные секции имеют гигантские преимущества, когда таблица не может быть секционирована никаким логическим путём. Они позволяют использовать преимущества секционирования, позволяя освободить разработчиков от решения, в какую секцию помещать запись.

    Табличное пространство транспортируется с одной секцией

    В ранних версиях Oracle Database появилась возможность перемещать табличное пространство, а потом подключать его к различным базам данных или к той же самой. Процесс заключается в копировании файлов данных, поскольку это самый быстрый способ перемещения данных между базами данных. Однако до настоящего времени не было возможности перемещать табличное пространство с одиночной секцией, а затем подключать его обратно. В Oracle Database 11g это можно.

    Предположим, что есть таблица SALES5 с секциями CT, NY и т.д.

    Теперь необходимо переместить секцию CT с помощью команды, показанной ниже:

    Теперь можно взять два файла — p_ct.dmp и ts1_01.dmp — и в другой системе попытаться подключить их к базе данных. Для изучения давайте попробуем подключить их к той же самой базе данных. Сначала нужно удалить таблицу, а затем табличное пространство ts1.

    Теперь подключим табличное пространство к базе данных. В этом месте, однако, возникает небольшая проблема: таблица sales5 больше не существует, а экспортирована была только одна её секция (ct), а не вся таблица. И как же теперь импортировать эту секцию несуществующей таблицы? В Oracle Database 11g имеется новая опция утилиты командной строки Data Pump Import с называнием partition_options, которая делает это возможным. Если указать значение departition, то Data Pump создаст новую таблицу из экспортированной секции. По ходу дела он «удаляет» секции, поэтому и называется соответственно десекционированием. Давайте посмотрим, как этот прием работает.

    Это SQL-предложение создаёт таблицу sales5_ct, которая ни что иное, как секция ct таблицы SALES5, экспортированной ранее в транспортируемом табличным пространстве. Название таблицы, как можно видеть, — это комбинация названий таблицы и секции. Наличие соответствующего сегмента можно увидеть в представлении DBA_SEGMENTS.

    Читайте также:  Таблица сколько стоит старинные монеты ссср

    Возможность транспортирования табличных пространств с одиночной секцией можно использовать для подключения таблицы к другой базе данных. После подключения можно выполнить операцию обмена секциями, чтобы преобразовать её в секцию какой-либо таблицы.

    Секционирование по виртуальным столбцам (Partitioning on Virtual Columns)

    Давайте рассмотрим другую распространённую проблему. В таблице sales есть следующие столбцы:

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

    если sale_ amt в пределах a cust_id в диапазоне тогда sale_category
    0-10000 любой LOW
    10001-100000 0-100 LOW
    10001-100000 101-200 MEDIUM
    10001-100000 >200 HIGH
    100001-1000000 0-100 MEDIUM
    100001-1000000 101-200 HIGH
    100001-1000000 >200 ULTRA
    >1000000 любой ULTRA

    Эту таблицу необходимо секционировать по столбцу sale_category, но вот проблема: столбца sale_category нет. Он в основном зависит от столбца sale_amt. И как же можно секционировать эту таблицу?

    В ранних версиях Oracle нужно было добавить в таблицу столбец sale_category и использовать триггер для заполнения столбца, используя логику, показанную в таблице. Однако наличие этого нового столбца повлияет на производительность вследствие работы триггера.

    В Oracle Database 11g новая возможность Virtual Columns (виртуальные столбцы) позволяет создать столбец, который не хранится в таблице, а вычисляется во время работы. По этому столбцу можно также выполнять секционирование. Использование этой возможности слегка «покачает» секционирование этой таблицы.

    Теперь при попытке вставить записи получаем:

    Каждая запись помещена в соответствующую секцию.

    Секционирование по виртуальным столбцам позволяет создавать секции, которые имеют смысл в бизнесе, даже если самого столбца не существует. Здесь использовалось очень простое вычисление по виртуальному столбцу, однако оно может быть настолько сложным, насколько вам понадобится. В таких случаях секционирование по виртуальным столбцам становится ещё более полезным.

    Советчик по вопросам секционирования (Partition Advisor)

    Надо полагать, что больше всего споров при проектировании секционирования возникает при выборе схемы секционирования и столбца (-ов) секционирования. Пусть эта задача лучше достанется бывалым профессионалам, занимающимся всесторонним анализом рабочей нагрузки, но даже они могут сделать это не правильно. Вы в Oracle Database 11g же получите помощь от нового советчика Partition Advisor (советчик по вопросам секционирования), который анализирует данные и методы доступа применительно к предполагаемым схемам секционирования. Об этом инструменте можно больше прочитать в руководстве по его инсталляции.

    Заключение

    Секционирование всегда было одним из наиболее полезных инстументов, а в Oracle Database 11g оно становится ещё более полезным:

    • Ссылочное секционирование позволяет синхронно разделать на секции связанные таблицы одной базы данных, даже если столбцов нет в подчинённых таблицах.
    • Интервальное секционирование реализовано так, как было весьма желательно в действиях «сделал-и-забыл» (fire-and-forget) — описывается интервал, и Oracle в дальнейшем берёт на себя заботу по поддержке.
    • Расширения смешанного секционирования до диапазон-диапазон (range-range), список-диапазон (list-range), список-хэш (list-hash) и список-список (list-list) демонстрируют новые возможности большей свободы выбора секционирования и управления им.
    • Data Pump теперь позволяет перемещать и подключать одиночную секцию таблицы; возможность, которая очень полезна в архивировании и хранении.
    • Наконец, можно спроектировать наилучшую из возможных стратегий секционирования, которая отражает бизнес-потоки путём секционирования по виртуальным столбцам.

    Стратегия «Разделяй и властвуй» («Divide and conquer») никогда не предполагает много вариантов выбора. Но представьте их себе как набор блестящих ножей для разделки тушки индейки на лучшие части!

    Источник

    Скахин Алексей / pihel

    Личный блог. Заметки о программировании и не только

    Страницы

    • Главная страница
    • Резюме
    • SQL
    • BigData

    пятница, 19 июня 2015 г.

    ORACLE: Оптимизация работы секционированных таблиц

    1. Важность указания STORAGE INITIAL — размера партиции по умолчанию.

    Начиная с Oracle 11 размер любой партиции, представленной на диске, по умолчанию равен 8МБ, даже если она не имеет в себе никаких данных (До 11 версии размер по умолчанию был 65КБ).
    Отсюда важность указания STORAGE INITIAL при создании партиционированной таблицы.

    Рассмотрим на примере. Таблица из 80 млн записей партиционирована RANGE-INTERVAL по месяцу и субпартиционирована LIST по региону. Месячные интервалы с 2013 года — это 6+12=18, и каждая партиция по 100 субпартиций регионов.

    Отсюда не сложно высчитать минимальный размер такой таблицы 18*100*8МБ= 140ГБ.
    Что явный перебор при исходном размере таблицы без секций = 2ГБ.

    Такой размер имеет ряд минусов:

    • Сложность переноса бд (экспорта/импорта) — база будет весить терабайты.
    • Ограниченность tablespace по размеру: Таблиц много, в DWH такая таблица должна быть продублирована несколько раз (Таблица источник — зеркало, Преобразованная таблица, Итоговая фактическая таблица). Доступное пространство быстро закончится.
    • !Увеличение времени чтения партиции с диска при сканировании по партициям.

    Мое решение: необходимо уменьшить размер партиции.
    Я обычно пользуюсь таким решением:

    Размер партиции можно взять с небольшим запасом (1МБ):

    Минимальный размер такой таблицы вырастет в сравнении с плоской незначительно на (1-0,68/1)*100% = 32%

    Замечу один минус:
    Совсем маленькой партиции тоже не стоит делать — это увеличить число физических обращений к диску. Т.к. для сканирования секции понадобится не одно обращение, а несколько.

    2. Настройка параметров PCTFREE и PCTUSED для уменьшения размера таблицы
    Размер таблицы можно значительно уменьшить, если задать эти параметры:
    PCTFREE — % блока под резерв изменений. Если таблица редко обновляется (или только чистится и заполняется заново), то этот параметр можно ставить близким к 0.
    PCTUSED — % блока под данные, если размер данных в блоке превышает этот %, то новые данные туда не будут вставляться. Если опять же данные редко обновляются, то для максимального уплотнения этот параметр можно ставить ближе к 100%.

    + Уменьшение размера таблицы сопоставимо изменению параметров PCTFREE/PCTUSED
    — При update строки с увеличением его размера, в случае нехватки свободного места в блоке (PCTFREE) строка целиком будет перенесена из текущего блока в новый. В старом блоке будет проставлена ссылка на новое расположение. Т.е. при чтении данных из таблицы нужно будет выполнить дополнительное рекурсивное чтение, что значительно увеличит стоимость запроса.
    — При большом числе параллельных сессии к одному блоку сильно разрастается информация о заинтересованных сессиях к строкам блока (ITL). Если место в блоке закончится, то невозможно будет расширить ITL, что приведет к ошибке обновления блока.

    3. Быстрое обновление таблиц через PARTITION EXCANGE.

    Частая задача в DWH — инкрементальное обновление данных. Допустим нужно залить новые данные за прошедший месяц.
    Традиционный способ INSERT /*+ APPEND */ достаточно медленный.
    Есть альтернативный, выполняющийся доли секунды.

    Для этого нужна вспомогательная таблица, куда будет класться очередная порция для обновления.
    У таблиц должна совпадать полностью структура, включая размерности и последовательность столбцов.
    После этого можно перекинуть из этой таблицу в целевую партиционированную одной командой.

    Пример перекидки из промежуточной таблицы в RANGE-INTERVAL партиционированную таблицу.

    Этой командой произойдет обмен данными между таблицами: партиция заполнится данными таблицы T_TBL_PT, а таблица данными пустой партиции T_TBL.

    Аналогично можно делать обмены с субпартициями

    4. Узнать имя партиции по произвольному фильтру
    Такое может понадобится, допустим, для сбора статистики только по нужной секции, т.к. в dbms_stat нужно указать физическое имя.
    Если известно значение в колонке секций, то нужно воспользоваться конструкцией:
    5. System партицирование
    Нет указания колонки при создании, из-за этого нужно указывать конкретную партицию при вставке или выборке

    6. Reference партицирование
    Возможность создания детализированной таблицы с наследованием партицирования от родительской: В строках нет даты, она автоматом подтягивается по фк из заголовка и партицируется по ней.
    Цена этому увеличение нагрузки в 10 раз: https://jonathanlewis.wordpress.com/2018/03/19/reference-costs/

    7. Изменение параметров партиции на основании статистики использования
    На основании статистики использования партиций (DBA_HEAT_MAP_SEG_HISTOGRAM) можно включить компрессию

    Источник

    Adblock
    detector