Секционирование достигает совершенства
Источник: сайт корпорации 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, у которой много столбцов, включая два специальных — кандидатов для секционирования:
Пользователи запрашивают данные таблицы, отбирая их по обоим столбцам на равенство, и требования к архивированию также основаны на этих двух столбцах. Когда вы постигните принципы секционирования, то поймёте, что эти столбцы являются хорошими кандидатами на ключи секционирования.
В 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 (советчик по вопросам секционирования), который анализирует данные и методы доступа применительно к предполагаемым схемам секционирования. Об этом инструменте можно больше прочитать в руководстве по его инсталляции.
Заключение
- Ссылочное секционирование позволяет синхронно разделать на секции связанные таблицы одной базы данных, даже если столбцов нет в подчинённых таблицах.
- Интервальное секционирование реализовано так, как было весьма желательно в действиях «сделал-и-забыл» (fire-and-forget) — описывается интервал, и Oracle в дальнейшем берёт на себя заботу по поддержке.
- Расширения смешанного секционирования до диапазон-диапазон (range-range), список-диапазон (list-range), список-хэш (list-hash) и список-список (list-list) демонстрируют новые возможности большей свободы выбора секционирования и управления им.
- Data Pump теперь позволяет перемещать и подключать одиночную секцию таблицы; возможность, которая очень полезна в архивировании и хранении.
- Наконец, можно спроектировать наилучшую из возможных стратегий секционирования, которая отражает бизнес-потоки путём секционирования по виртуальным столбцам.
Стратегия «Разделяй и властвуй» («Divide and conquer») никогда не предполагает много вариантов выбора. Но представьте их себе как набор блестящих ножей для разделки тушки индейки на лучшие части!
Oracle: партицирование таблиц, как управлять секциями
Партицирование таблиц Oracle по диапазон у значений основывается на каком-либо столбце табличных данных, который содержит уникальные сведения. Создание отдельных табличных секций происходит по такому принципу:
CREATE TABLE MY.NEWTABLE ( ISN NEWNUMBERS. UPDATED NEWDATES) TABLESPACES HSTNEWDATA
PARTITION BY RANGE (ISN)
(PARTITION PARTISAN_01 VALUE LESS THAN (1500),
PARTITION PARTISAN_02 VALUE LESS THAN (2500),
PARTITION PARTISAN_03 VALUE LESS THAN (3500),
PARTITION PARTISAN_MAXIMUM VALUE LESS THAN (MAXVALUES)
) ENABLE ROW MOVEMENT;
Партицирование таблиц Oracle по спискам значений
Такой метод партицирования удобен, когда присутствует возможность определить список элементов конкретного столбца, чтобы по ним разбить табличное представление на отдельные области. Вот как это происходит на практике:
CREATE TABLE MY.NEWTABLE (ISN NEWNUMBER,UPDATED NEWDATES, L
PARTID AS (TO_NEWNUMBERS(TO_CHAR(UPDATEDS, ’ ’)))
) PARTITION BY LIST(PARTID)
( PARTITION TABLEPART_3 VALUES (3),
PARTITION TABLEPART_4 VALUES (4),
PARTITION TABLEPART_14 VALUES (14));
Партицирование по хеш-значению
Первые два способа партицирования наиболее популярны и часто используются. Все способы, которы е будут описаны ниже , применяются в специфич еских случаях, в том числе и разбивка на табличные секции по хеш-значению. Данный способ основывается на хеш-функциях, поэтому считается наиболее точным.
Вот как этот способ выглядит на практике:
CREATE TABLE MY.NEWTABLE (TASKSISN NEWNUMBERS, OBJECTISN NEWNUMBERS, K PARAMETRS NEWNUMBERS,
CONSTRAINT NEWPK_LISTIN PRIMARY NEWKEY(TASKSISN,OBJECTISN,OBJECTROWID,K PARAMETRS)
) MYORGANIZATION INDEX INCLUDING PARAMETRS OVERFLOW PARTITION BY HASH (TASKSISN) PARTITIONS 24
Составное партицирование
При таком методе внутри одной секции образу е тся несколько связанных подсекций. А вообще, такой метод понимает смешанное применение нескольк их других методов, описанных чуть выше , н апример , по списку значений и хеш-значениям и др. Причем сочетания способов мо гут быть различным и .
Вот как выглядит составное партицирование таблиц Oracle, где одновременно используются первые два способа, описанные сегодня в статье:
CREATE TABLE MYTABLE.NEWPAY_ORD_RECORDING ( ISN NEWNUMBERS, K
NEWPAY_NEWDATA NEWDATES, NEWPAYER_NEWNAMES VARCHAR3(255). K NEWSTATUS NEWNUMBERS ) TABLESPACE HSTNEWDATA
PARTITION BY RANGE (NEWPAY_NEWDATA)
INTERVAL (NEWINTERVAL ‘7’ DAYS)
SUBPARTITION BY LIST (NEWSTATUS)
SUBPARTITION NEWTEMPLATE (
SUBPARTITION NEWSTATUSK) VALUE0 (0) K TABLESPACE TRNEWDATA1,
SUBPARTITION NEWSTATUS_1 VALUE1 (1) K TABLESPACE TRNEWDATA2,
SUBPARTITION NEWSTATUSK VALUE2 (2) K TABLESPACE TRNEWDATA3 )
(PARTITION PK015KK1 VALUE LESS K
THAN(TO_NEWDATE(‘02.02.2022′,’DD.MM.YYYY’)))
ENABLE ROW MOVEMENT;
Заключение
Сегодня мы лишь поверхностно коснулись темы «Партиционирование таблиц Oracle» и привели простейшие практические примеры, чтобы вы могли ознакомит ь ся с тем , как оно выглядит. В следующих статьях мы подробнее остановимся на каждом отдельном методе, потому что по каждому из ни есть что рассказать.
Мы будем очень благодарны
если под понравившемся материалом Вы нажмёте одну из кнопок социальных сетей и поделитесь с друзьями.
13
Managing Partitioned Tables and Indexes
This chapter describes various aspects of managing partitioned tables and indexes, and includes the following sections:
What Are Partitioned Tables and Indexes?
Before attempting to create a partitioned table or index or perform maintenance operations on any partition, review the information about partitioning in Oracle8i Concepts.
Today’s enterprises frequently run mission-critical databases containing upwards of several hundred gigabytes and, in many cases, several terabytes of data. These enterprises are challenged by the support and maintenance requirements of very large databases (VLDB), and must devise methods to meet those challenges.
One way to meet VLDB demands is to create and use partitioned tables and indexes . Partitioned tables or indexes can be divided into a number of pieces, called subpartitions , which have the same logical attributes. For example, all partitions (or subpartitions) in a table share the same column and constraint definitions, and all partitions (or subpartitions) in an index share the same index options. Each partition (or subpartition) is stored in a separate segment and can have different physical attributes (such as PCTFREE, PCTUSED, INITRANS, MAXTRANS, TABLESPACE, and STORAGE).
Although you are not required to keep each table or index partition in a separate tablespace, it is to your advantage to do so. Storing partitions in separate tablespaces enables you to:
reduce the possibility of data corruption in multiple partitions
See Also: For more detailed information on partitioning concepts and benefits, see Oracle8i Concepts.
Partitioning Methods
There are three partitioning methods:
This section describes how to use each of these methods.
Using the Range Partitioning Method
You can use range partitioning to map rows to partitions based on ranges of column values. Range partitioning is defined by the partitioning specification for a table or index, and by the partitioning specifications for each individual partition.
The following example shows a table of four partitions (one for each quarter’s sales); a row with SALE_YEAR=1998, SALE_MONTH=8 and SALE_DAY=18 has partitioning key (1998, 8, 18), belongs in the third partition, and is stored in tablespace TSC. A row with SALE_YEAR=1998, SALE_MONTH=8 and SALE_DAY=1 has partitioning key (1998, 8, 1), and also belongs in the third partition, stored in tablespace TSC.
Maintaining Range Partitions
The only maintenance operation to perform on partitions created using the range partitioning method is the merging of partitions. You can use the ALTER TABLE. MERGE PARTITIONS command to merge the contents of two adjacent range partitions into one partition. You might want to do this to keep historical data online in larger partitions. For example, you might want to have daily partitions, with the oldest partition rolled up into weekly partitions, which can then be rolled up into monthly partitions, and so on.
See Also: For more details about range partitioning, see Oracle8i Concepts.
For more details about CREATE TABLE. PARTITION syntax, see the Oracle8i SQL Reference.
Using the Hash Partitioning Method
Hash partitioning controls the physical placement of data across a fixed number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key. Creating and using hash partitions gives you a highly tunable method of data placement.
The following example shows how to specify all storage attributes for partitions at the table level:
You can store hash partitions in specific tablespaces, as shown in the following statement:
Or, you can name and store each hash partition in a specific tablespace:
You can also specify partition-level tablespaces for hash-partitioned indexes:
Maintaining Hash Partitions
All current range partition maintenance operations are supported for hash partitions, except for the following:
ALTER TABLE. SPLIT PARTITION
Additionally, there are two maintenance operations specifically for partitions created using the has partitioning method:
Coalescing Hash Partitions
To remove a single hash partition and redistribute the data, use the following statement:
Note that the partition being coalesced is determined by the hash function. Also, when you coalesce a hash partition and redistribute the data, local indexes are not maintained. You can coalesce the hash partition in parallel.Local index partitions corresponding to partitions that absorbed rows must be rebuilt from existing partitions.
Adding Hash Partitions
To add a single hash partition and redistribute the data, use one of the following statements:
Local indexes are not maintained when you add a hash partition. You can also add the hash partition in parallel.
See Also: For detailed syntax information about the CREATE TABLE PARTITION. BY HASH and ALTER TABLE statements, see the Oracle8i SQL Reference.
For more details about hash partitioning, see Oracle8i Concepts.
Using the Composite Partitioning Method
Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. Composite partitions are ideal for both historical data and striping, and provide improved manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.
When creating a composite partition, you specify the following:
partitioning method (range)
You may also wish to use the STORE IN clause to specify tablespaces across which each table partition’s subpartitions will be spread.
The following statement creates a composite-partitioned table:
The following statement shows you can specify subpartition names and names of tablespaces in which subpartitions should be placed.
Maintaining Composite Partitions
You can perform all range partition maintenance operations on a composite partitioned table or index, and modify default attributes for table partitions.
Maintaining Composite Subpartitions
This section describes how to accomplish specific subpartition maintenance operations, including:
Modifying Subpartitions
You can mark a subpartition of a local index on a partitioned table marked unusable as follows.
You can also allocate or deallocate storage for a subpartition of a table or index using the MODIFY SUBPARTITION clause.
Rebuilding Subpartitions
You can rebuild a subpartition to regenerate the data in an index subpartition. The following statement rebuilds a subpartition of a local index on a table:
Note that in this example, the index is rebuilt in a different tablespace.
Renaming Subpartitions
You can assign new names to subpartitions of a table or index. The following statement shows how to assign a new name to a subpartition of a local index on a table:
This next statement simply shows how to rename a subpartition that has a system-generated name that was a consequence of adding a partition to an underlying table:
You can also assign a new name to a subpartition of a table:
Exchanging Subpartitions
The following statement shows how to convert a subpartition of a table into a nonpartitioned table:
Adding Subpartitions
The following statement shows how to add a subpartition to a partition of a table. The newly added subpartition is populated with rows rehashed from other subpartitions of the same partition as determined by the hash function:
Coalescing Subpartitions
The following statement shows how to distribute contents of a subpartition (selected by the RDBMS) of the specified partition of a table into one or more remaining subpartitions (determined by the hash function) of the same partition, and then destroy the selected subpartition. Basically, this operation is the inverse of the ALTER TABLE MODIFY PARTITION ADD SUBPARTITION statement:
Moving Subpartitions
The following statement shows how to move data in a subpartition of a table:
Truncating Subpartitions
The following statement shows how to truncate data in a subpartition of a table:
See Also: For more details about the syntax of statements in this section, see the Oracle8i SQL Reference.
Creating Partitions
Creating a partitioned table is very similar to creating a table or index: you must use the CREATE TABLE statement with the PARTITION by clause. Also, you must specify the tablespace name for each partition.
The following example shows a CREATE TABLE statement that contains four partitions, one for each quarter’s worth of sales. A row with SALE_YEAR=1998, SALE_MONTH=7, and SALE_DAY=18 has the partitioning key (1998, 7, 18), and is in the third partition, in the tablespace TSC. A row with SALE_YEAR=1998, SALE_MONTH=7, and SALE_DAY=1 has the partitioning key (1998, 7, 1), and also is in the third partition.
See Also: For more information about the CREATE TABLE statement and PARTITION clause, see Oracle8i SQL Reference.
For information about partition keys, partition names, bounds, and equipartitioned tables and indexes, see Oracle8i Concepts.
Maintaining Partitions
This section describes how to perform the following specific partition maintenance operations:
See Also: For information about the SQL syntax for DDL statements, see Oracle8i SQL Reference.
For information about the catalog views that describe partitioned tables and indexes, and the partitions of a partitioned table or index, see Oracle8i Reference.
For information about Import, Export and partitions, see Oracle8i Utilities.
For general information about partitioning, see Oracle8i Concepts .
Moving Partitions
You can use the MOVE PARTITION clause of the ALTER TABLE statement to:
re-cluster data and reduce fragmentation
Typically, you can change the physical storage attributes of a partition in a single step via a ALTER TABLE/INDEX. MODIFY PARTITION statement. However, there are some physical attributes, such as TABLESPACE, that you cannot modify via MODIFY PARTITION. In these cases you can use the MOVE PARTITION clause.
Moving Table Partitions
You can use the MOVE PARTITION clause to move a partition. For example, a DBA wishes to move the most active partition to a tablespace that resides on its own disk (in order to balance I/O). The DBA can issue the following statement:
This statement always drops the partition’s old segment and creates a new segment, even if you don’t specify a new tablespace.
When the partition you are moving contains data, MOVE PARTITION marks the matching partition in each local index, and all global index partitions as unusable. You must rebuild these index partitions after issuing MOVE PARTITION.
Moving Index Partitions
Some operations, such as MOVE PARTITION and DROP TABLE PARTITION, mark all partitions of a global index unusable. You can rebuild the entire index by rebuilding each partition individually using the ALTER INDEX REBUILD PARTITION statement. You can perform these rebuilds concurrently.
You can also simply drop the index and re-create it.
Adding Partitions
This section describes how to add new partitions to a partitioned table and how partitions are added to local indexes.
Adding Table Partitions
You can use the ALTER TABLE. ADD PARTITION statement to add a new partition to the «high» end (the point after the last existing partition). If you wish to add a partition at the beginning or in the middle of a table, or if the partition bound on the highest partition is MAXVALUE, you should instead use the SPLIT PARTITION statement.
When the partition bound of the highest partition is anything other than MAXVALUE, you can add a partition using the ALTER TABLE. ADD PARTITION statement.
For example, a DBA has a table, SALES, which contains data for the current month in addition to the previous 12 months. On January 1, 1999, the DBA adds a partition for January:
When there are local indexes defined on the table and you issue the ALTER TABLE. ADD PARTITION statement, a matching partition is also added to each local index. Since Oracle assigns names and default physical storage attributes to the new index partitions, you may wish to rename or alter them after the ADD operation is complete.
Adding Index Partitions
You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table.
You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. If you wish to add a new highest partition, use the ALTER INDEX. SPLIT PARTITION statement.
Dropping Partitions
This section describes how to use the ALTER TABLE DROP PARTITION statement to drop table and index partitions and their data.
Dropping Table Partitions
You can use the ALTER TABLE DROP PARTITION statement to drop table partitions.
If there are local indexes defined for the table, ALTER TABLE DROP PARTITION also drops the matching partition from each local index.
You cannot drop the only partition in a table.
Dropping Table Partitions Containing Data and Global Indexes
If, however, the partition contains data and one or more global indexes are defined on the table, use either of the following methods to drop the table partition:
Leave the global indexes in place during the ALTER TABLE. DROP PARTITION statement. In this situation DROP PARTITION marks all global index partitions unusable, so you must rebuild them afterwards.
The ALTER TABLE. DROP PARTITION statement not only marks all global index partitions as unusable, it also renders all nonpartitioned indexes unusable. You cannot rebuild the entire partitioned index in a single statement. If you wish to rebuild a partitioned index, you must write a separate REBUILD statement for each partition in the partitioned index. Here, sal1 is a nonpartitioned index.
This method is most appropriate for large tables where the partition being dropped contains a significant percentage of the total data in the table.
You can substantially reduce the amount of logging by setting the NOLOGGING attribute (using ALTER TABLE. MODIFY PARTITION. NOLOGGING) for the partition before deleting all of its rows.
For example, a DBA wishes to drop the first partition, which has a partition bound of 10000. The DBA issues the following statements:
This method is most appropriate for small tables, or for large tables when the partition being dropped contains a small percentage of the total data in the table.
Dropping Table Partitions Containing Data and Referential Integrity Constraints
If a partition contains data and the table has referential integrity constraints, choose either of the following methods to drop the table partition:
Disable the integrity constraints, issue the ALTER TABLE. DROP PARTITION statement, then enable the integrity constraints:
This method is most appropriate for large tables where the partition being dropped contains a significant percentage of the total data in the table.
This method is most appropriate for small tables or for large tables when the partition being dropped contains a small percentage of the total data in the table.
Dropping Index Partitions
You cannot explicitly drop a partition of a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.
If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX. DROP PARTITION statement.
If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. For example, a DBA wishes to drop the index partition P1 and P2 is the next highest partition. The DBA must issue the following statements:
You cannot drop the highest partition in a global index.
Coalescing Partitions
You can distribute contents of a partition (selected by the RDBMS) of a table partitioned using the hash method into one or more partitions determined by the hash function, and then destroy the selected partition.
The following statement reduces by one the number of partitions in a table by coalescing its last partition:
Modifying Partition Default Attributes
You can modify default attributes of a partition of a local index on tables created using the composite method (or a partition of a composite table).
The following statement changes the (partition-level default) PCTFREE attribute of a partition of a local index on a partitioned table:
Truncating Partitions
Use the ALTER TABLE. TRUNCATE PARTITION statement when you wish to remove all rows from a table partition. You cannot truncate an index partition; however, the ALTER TABLE TRUNCATE PARTITION statement truncates the matching partition in each local index.
Truncating Partitioned Tables
You can use the ALTER TABLE. TRUNCATE PARTITION statement to remove all rows from a table partition with or without reclaiming space. If there are local indexes defined for this table, ALTER TABLE. TRUNCATE PARTITION also truncates the matching partition from each local index.
Truncating Table Partitions Containing Data and Global Indexes
If, however, the partition contains data and global indexes, use either of the following methods to truncate the table partition:
Leave the global indexes in place during the ALTER TABLE TRUNCATE PARTITION statement.
The ALTER TABLE. TRUNCATE PARTITION statement not only marks all global index partitions as unusable, it also renders all nonpartitioned indexes unusable. You cannot rebuild the entire partitioned index in a single statement. If you wish to rebuild a partitioned index, you must write a separate REBUILD statement for each partition in the partitioned index. Here, sal1 is a nonpartitioned index.
This method is most appropriate for large tables where the partition being truncated contains a significant percentage of the total data in the table.
This method is most appropriate for small tables, or for large tables when the partition being truncated contains a small percentage of the total data in the table.
Truncating Table Partitions Containing Data and Referential Integrity Constraints
If a partition contains data and has referential integrity constraints, choose either of the following methods to truncate the table partition:
Disable the integrity constraints, issue the ALTER TABLE. TRUNCATE PARTITION statement, then re-enable the integrity constraints:
This method is most appropriate for large tables where the partition being truncated contains a significant percentage of the total data in the table.
You can substantially reduce the amount of logging by setting the NOLOGGING attribute (using ALTER TABLE. MODIFY PARTITION. NOLOGGING) for the partition before deleting all of its rows.
This method is most appropriate for small tables, or for large tables when the partition being truncated contains a small percentage of the total data in the table.
Splitting Partitions
This form of ALTER TABLE/INDEX divides a partition into two partitions. You can use the SPLIT PARTITION clause when a partition becomes too large and causes backup, recovery or maintenance operations to take a long time. You can also use the SPLIT PARTITION clause to redistribute the I/O load; note that you cannot use this clause for hash partitions.
Splitting Table Partitions
You can split a table partition by issuing the ALTER TABLE. SPLIT PARTITION statement. If there are local indexes defined on the table, this statement also splits the matching partition in each local index. Because Oracle assigns system-generated names and default storage attributes to the new index partitions, you may wish to rename or alter these index partitions after splitting them.
If the partition you are splitting contains data, the ALTER TABLE. SPLIT PARTITION statement marks the matching partitions (there are two) in each local index, as well as all global index partitions, as unusable. You must rebuild these index partitions after issuing the ALTER TABLE. SPLIT PARTITION statement.
Splitting a Table Partition: Scenario
In this scenario «fee_katy» is a partition in the table «VET_cats,» which has a local index, JAF1. There is also a global index, VET on the table. VET contains two partitions, VET_parta, and VET_partb.
To split the partition «fee_katy», and rebuild the index partitions, the DBA issues the following statements:
You must examine the data dictionary to locate the names assigned to the new local index partitions. In this particular scenario, they are SYS_P00067 and SYS_P00068. If you wish, you can rename them. Also, unless JAF1 already contained partitions fee_katy1 and fee_katy2, names assigned to local index partitions produced by this split will match those of corresponding base table partitions.
Splitting Index Partitions
You cannot explicitly split a partition in a local index. A local index partition is split only when you split a partition in the underlying table.
The following statement splits the global index partition containing data, QUON1:
You only need to rebuild if the index partition that you split was unusable.
Merging Partitions
You can merge the contents of two adjacent partitions of a range or composite partitioned table into one. The resulting partition inherits the higher upper bound of the two merged partitions.
The following statement merges two adjacent partitions of a range partitioned table:
Exchanging Table Partitions
You can convert a partition into a nonpartitioned table, and a table into a partition of a partitioned table by exchanging their data (and index) segments. Exchanging table partitions is most useful when you have an application using nonpartitioned tables which you want to convert to partitions of a partitioned table. For example, you may already have partition views that you wish to migrate into partitioned tables.
Converting a Partition View into a Partitioned Table: Scenario
This scenario describes how to convert a partition view (also called «manual partition») into a partitioned table. The partition view is defined as follows:
To Incrementally Migrate the Partition View to a Partitioned Table
Initially, only the two most recent partitions, ACCOUNTS_NOV98 and ACCOUNTS_DEC98, will be migrated from the view to the table by creating the partitioned table. Each partition gets a segment of 2 blocks (as a placeholder).
So now the placeholder data segments associated with the NOV98 and DEC98 partitions have been exchanged with the data segments associated with the ACCOUNTS_NOV98and ACCOUNTS_DEC98 tables.
See Also: For more information about the syntax and usage of the statements in this section, see Oracle8i SQL Reference.
Rebuilding Index Partitions
Some operations, such as ALTER TABLE. DROP PARTITION, mark all partitions of a global index unusable. You can rebuild global index partitions in two ways:
Rebuild each partition by issuing the ALTER INDEX. REBUILD PARTITION statement (you can run the rebuilds concurrently).
This method is more efficient because the table is scanned only once.
Moving the Time Window in a Historical Table
A historical table describes the business transactions of an enterprise over intervals of time. Historical tables can be base tables, which contain base information; for example, sales, checks, orders. Historical tables can also be rollup tables, which contain summary information derived from the base information via operations such as GROUP BY, AVERAGE, or COUNT.
The time interval in a historical table is a rolling window; DBAs periodically delete sets of rows that describe the oldest transaction, and in turn allocate space for sets of rows that describe the most recent transaction. For example, at the close of business on April 30, 1995 the DBA deletes the rows (and supporting index entries) that describe transactions from April 1994, and allocates space for the April 1995 transactions.
To Move the Time Window in a Historical Table
Now consider a specific example. You have a table, ORDER, which contains 13 months of transactions: a year of historical data in addition to orders for the current month. There is one partition for each month; the partitions are named ORDER_yymm, as are the tablespaces in which they reside.
The ORDER table contains two local indexes, ORDER_IX_ONUM, which is a local, prefixed, unique index on the order number, and ORDER_IX_SUPP, which is a local, non-prefixed index on the supplier number. The local index partitions are named with suffixes that match the underlying table. There is also a global unique index, ORDER_IX_CUST, for the customer name. ORDER_IX_CUST contains three partitions, one for each third of the alphabet. So on October 31, 1994, change the time window on ORDER as follows:
Back up the data for the oldest time interval.
Quiescing Applications During a Multi-Step Maintenance Operation
Ordinarily, Oracle acquires sufficient locks to ensure that no operation (DML, DDL, utility) interferes with an individual DDL statement, such as ALTER TABLE. DROP PARTITION. However, if the partition maintenance operation requires several steps, it is the DBA’s responsibility to ensure that applications (or other maintenance operations) do not interfere with the multi-step operation in progress.
For example, there are referential integrity constraints on the table ORDER, and you do not wish to disable them to drop the partition. Instead, you can replace Step 2 from the previous section with the following:
You can ensure that no one inserts new rows into ORDER between the DELETE step and the DROP PARTITION steps by revoking access privileges from an APPLICATION role, which is used in all applications. You can also bring down all user-level applications during a well-defined batch window each night or weekend.