Партиции в oracle что это

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» и привели простейшие практические примеры, чтобы вы могли ознакомит ь ся с тем , как оно выглядит. В следующих статьях мы подробнее остановимся на каждом отдельном методе, потому что по каждому из ни есть что рассказать.

Мы будем очень благодарны

если под понравившемся материалом Вы нажмёте одну из кнопок социальных сетей и поделитесь с друзьями.

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

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

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

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

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

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

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

 SQL> CREATE TABLE DWH.CALLS ( 2 CALLS_ID NUMBER (15) NOT NULL, 3 STRT_DT_KEY DATE NOT NULL, 4 BSN_EV_TP_ID NUMBER (5) NOT NULL, 5 STRT_TM DATE NOT NULL, 6 END_TM DATE NOT NULL, 7 CTY_FR NUMBER (15) NOT NULL, 8 CTY_TO NUMBER (15) NOT NULL, 9 A_NUM VARCHAR2 (20) NOT NULL, 10 B_NUM VARCHAR2 (20) NOT NULL, 11 PRICE_AMT NUMBER (15,4) NOT NULL, 12 CHG_AMT NUMBER (15,4) NOT NULL, 13 CHG_CALL_DUR NUMBER (15) NOT NULL, 14 CALL_DUR NUMBER (15) NOT NULL, 15 IS_DEL_IND NUMBER (1) NOT NULL, 16 UPD_DT DATE NOT NULL, 17 PPN_DT DATE NOT NULL, 18 SRC_STM_ID NUMBER (5) NOT NULL 19 ) 20 TABLESPACE TBS_CALLS 21 PARTITION BY RANGE (STRT_DT_KEY) 22 SUBPARTITION BY LIST (BSN_EV_TP_ID) 23 SUBPARTITION TEMPLATE ( 24 SUBPARTITION "SP_BSNEV1" values ( 1 ), 25 SUBPARTITION "SP_BSNEV2" values ( 2 ), 26 SUBPARTITION "SP_BSNEV3" values ( 3 ), 27 SUBPARTITION "SP_BSNEV4" values ( 4 ), 28 SUBPARTITION "SP_BSNEV5" values ( 5 ), 29 SUBPARTITION "SP_BSNEV6" values ( 6 ), 30 SUBPARTITION "SP_BSNEV7" values ( 7 ), 31 SUBPARTITION "SP_BSNEV8" values ( 8 ), 32 SUBPARTITION "SP_BSNEV9" values ( 9 )) 33 ( 34 PARTITION P_0106 VALUES LESS THAN (TO_DATE('2006-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPACE TBS_CALLS_0106_1, 35 PARTITION P_0206 VALUES LESS THAN (TO_DATE('2006-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPACE TBS_CALLS_0206_1, 36 PARTITION P_0306 VALUES LESS THAN (TO_DATE('2006-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPACE TBS_CALLS_0306_1, 37 PARTITION P_MAXV VALUES LESS THAN (MAXVALUE) TABLESPACE TBS_CALLS_PMAXV 38 ); Table created

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

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

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

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

 select COUNT_DAY, TABLE_OWNER, TABLE_NAME, PARTITION_NAME from (select TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE, 'MM.YYYY'), 'MM.YYYY') - TO_DATE(substr(t.partition_name, 3, 2)||'.20'|| substr(t.partition_name,5,2),'MM.YYYY')) AS COUNT_DAY, T.TABLE_OWNER, T.TABLE_NAME, T.PARTITION_NAME from all_tab_partitions t ) where COUNT_DAY > 1825 /* 5 лет в днях */;

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

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

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

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

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

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

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

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

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

 SQL> create table DWH.CALLS$EXP$P_0106 2 TABLESPACE TBS_CALLS_0106_HIST 3 PARTITION BY LIST ("BSN_EV_TP_ID") 4 ( 5 PARTITION "SP_BSNEV1" values ( 1 ) TABLESPACE TBS_CALLS_0106_HIST, 6 PARTITION "SP_BSNEV2" values ( 2 ) TABLESPACE TBS_CALLS_0106_HIST, 7 PARTITION "SP_BSNEV3" values ( 3 ) TABLESPACE TBS_CALLS_0106_HIST, 8 PARTITION "SP_BSNEV4" values ( 4 ) TABLESPACE TBS_CALLS_0106_HIST, 9 PARTITION "SP_BSNEV5" values ( 5 ) TABLESPACE TBS_CALLS_0106_HIST, 10 PARTITION "SP_BSNEV6" values ( 6 ) TABLESPACE TBS_CALLS_0106_HIST, 11 PARTITION "SP_BSNEV7" values ( 7 ) TABLESPACE TBS_CALLS_0106_HIST, 12 PARTITION "SP_BSNEV8" values ( 8 ) TABLESPACE TBS_CALLS_0106_HIST, 13 PARTITION "SP_BSNEV9" values ( 9 ) TABLESPACE TBS_CALLS_0106_HIST 14 ) 15 as select * from DWH.CALLS where 1=2; Table created

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

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

 SQL> alter table DWH.CALLS exchange partition P_0106 with table DWH.CALLS$EXP$P_0106 without validation; Table altered

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

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

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

 SQL> conn sys/pass@DWH as sysdba Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 Connected as SYS SQL> SQL> EXECUTE DBMS_TTS.transport_set_check('TBS_CALLS_0106_1', TRUE); PL/SQL procedure successfully completed SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; VIOLATIONS -------------------------------------------------------------------------------- Default Partition (Table) Tablespace TBS_CALLS for CALLS not contained in transp Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not 19 rows selected SQL>

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

 SQL> ALTER TABLE DWH.CALLS MODIFY default attributes FOR PARTITION P_0106 tablespace TBS_CALLS_0106_HIST; Table altered SQL> ALTER TABLE DWH.CALLS$EXP$P_0106 MODIFY default attributes tablespace TBS_CALLS_0106_1; Table altered SQL>

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

 SQL> EXECUTE DBMS_TTS.transport_set_check('TBS_CALLS_0106_1', TRUE); PL/SQL procedure successfully completed SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; VIOLATIONS -------------------------------------------------------------------------------- SQL>

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

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

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

 SQL> ALTER TABLESPACE TBS_CALLS_0106_1 READ ONLY; Tablespace altered SQL>

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

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

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

 $ expdp system/pass@DWH DIRECTORY=DATA_PUMP_DIR DUMPFILE=TBS_CALLS_0106_1.DMP TRANSPORT_TABLESPACES=TBS_CALLS_0106_1 TRANSPORT_FULL_CHECK=Y LOGFILE= TBS_CALLS_0106_1.log; Export: Release 10.2.0.4.0 - 64bit Production on Friday, 17 April, 2009 10:39:42 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 With the Partitioning, Oracle Label Security, OLAP and Data Mining options Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02": system/********@DWH DIRECTORY=DATA_PUMP_DIR DUMPFILE=TBS_CALLS_0106_1.DMP TRANSPORT_TABLESPACES=TBS_CALLS_0106_1 TRANSPORT_FULL_CHECK=Y LOGFILE= TBS_CALLS_0106_1.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02" successfully loaded/unloaded ************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_02 is: /u01/app/oracle/product/10.2.0/db_1/admin/DWH/dpdump/TBS_CALLS_0106_1.DMP Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02" successfully completed at 10:40:11 $

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

 $ cd /u01/app/oracle/product/10.2.0/db_1/admin/DWH/dpdump/ $

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

 $ ls TBS_CALLS_0106_1.DMP TBS_CALLS_0106_1.log $

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/.

 $ cd /backup/DWH/ $ mkdir TBS_CALLS_0106_1_HIST $ ls TBS_CALLS_0106_1_HIST $ $ cp /u01/app/oracle/product/10.2.0/db_1/admin/DWH/dpdump/TBS_CALLS_0106_1.DMP /backup/DWH/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1.DMP $ cp /wh/oracle/disk1/DWH/TBS_CALLS_0106_1_001.dbf /backup/DWH/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1_001.dbf $ cp /wh/oracle/disk0/DWH/TBS_CALLS_0106_1_002.dbf /backup/DWH/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1_002.dbf $ $ cd TBS_CALLS_0106_1_HIST/ $ ls TBS_CALLS_0106_1.DMP TBS_CALLS_0106_1_001.dbf TBS_CALLS_0106_1_002.dbf $

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

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

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

 $ touch TBS_CALLS_0106_1.txt $ cat > TTBS_CALLS_0106_1.txt /wh/oracle/disk1/DWH/TBS_CALLS_0106_1_001.dbf /wh/oracle/disk0/DWH/TBS_CALLS_0106_1_002.dbf $

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

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

 $ cd backup/DWH/ $ tar -cf - TBS_CALLS_0106_1_HIST | gzip -c > TBS_CALLS_0106_1_HIST.tar.gz $ ls TBS_CALLS_0106_1_HIST TBS_CALLS_0106_1_HIST.tar.gz $

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

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

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

 SQL> drop tablespace TBS_CALLS_0106_1 including contents and datafiles; Tablespace dropped SQL>

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

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

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

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

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

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

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

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

 login as: oracle Using keyboard-interactive authentication. Password:

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

 $ cd /backup/Restore/ $ gunzip -c TBS_CALLS_0106_1_HIST.tar.gz | tar -xf - $

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/.

 $ cp /bkup/Restore/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1.DMP /u01/app/oracle/product/10.2.0/db_1/admin/DWH/dpdump/TBS_CALLS_0106_1.DMP $ $ cp /bkup/Restore/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1_001.dbf /wh/oracle/disk1/DWH/TBS_CALLS_0106_1_001.dbf $ $ cp /bkup/Restore/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1_002.dbf /wh/oracle/disk0/DWH/TBS_CALLS_0106_1_002.dbf $

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

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

 $ impdp system/pass@DWH DIRECTORY=DATA_PUMP_DIR DUMPFILE=TBS_CALLS_0106_1.DMP TRANSPORT_DATAFILES=/wh/oracle/disk1/DWH/TBS_CALLS_0106_1_001.dbf, /wh/oracle/disk0/DWH/TBS_CALLS_0106_1_002.dbf; Import: Release 10.2.0.4.0 - 64bit Production on Friday, 17 April, 2009 11:08:44 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 With the Partitioning, Oracle Label Security, OLAP and Data Mining options Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@DWH DIRECTORY=DATA_PUMP_DIR DUMPFILE=TBS_CALLS_0106_1.DMP TRANSPORT_DATAFILES=/wh/oracle/disk1/DWH/TBS_CALLS_0106_1_001.dbf, /wh/oracle/disk0/DWH/TBS_CALLS_0106_1_002.dbf Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 11:08:51 $

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

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

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

 SQL> alter table DWH.CALLS exchange partition P_0106 with table DWH.CALLS$EXP$P_0106 without validation; Table altered SQL> В случае, если это необходимо, можно изменить атрибут «только для чтения». SQL> ALTER TABLESPACE TBS_CALLS_0106_1 READ WRITE; Tablespace altered SQL>

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

Секционирование таблиц в Oracle на практике

Oracke Database: секционирование таблиц

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

Задачи, решаемые секционированием

Прежде чем приступить к секционированию, надо четко определить задачи, которые предполагается решить

Первой и наиболее часто решаемой задачей при секционировании является повышение производительности работы SQL-запросов и DML-операций по модификации строк таблицы. Это достигается за счет того, что поиск и модификация строк в таблице идут не по всей таблице, а только в ее части (в одной или нескольких секциях). Кроме того, разбиение таблицы на секции позволяет увеличит скорость обработки таблицы за счет использования параллелизма.
Вторая задача, которая нашла широкое применение в нашей организации, — это быстрое удаление значительного числа строк в больших таблицах за счет выполнения операции truncate секций. Другим широким применением секционирования является освобождение табличного пространства, занимаемого таблицей, после удаления строк из таблицы командой delete. Использование команд Shrink (сжатие таблицы) или Move (перемещение в табличное пространство) для освобождения табличного пространства в большой несекционированной таблице может занимать значительное время. В секционированных таблицах выполнение таких команд в пределах секции будет выполниться существенно быстрее.
Третьей задачей секционирования является разбиение большой таблицы на оперативную и архивную части. Особенно это эффективно, если оперативная часть в виде секции интенсивно пополняется и модифицируется, а архивная часть (секции) менее подвержена изменениям, и существенно реже из нее извлекается информация. Строки таблицы из оперативной секции со временем могут быть переведены в архивные секции, при этом архивные секции могут периодически очищаться.
Четвертой задачей является существенное снижение конкуренции за строки и индексы таблицы, в том числе уменьшения вероятности блокировок. Так в результате секционирования одной из таблиц по HASH-методу полностью была решена задача множественных блокировок, возникающих в таблице.
Пятой задачей является обеспечение устойчивости функционирования таблиц. Поскольку секция — это поименованный самостоятельный фрагмент памяти на дисках, то при возникновении проблем в одних секциях другие продолжают успешно функционировать. Устойчивости функционирования способствует также хранение секций в различных табличных пространствах и на различных физических носителях. Это особенно важно для таблиц, которые обеспечивают работу множества других таблиц (например. справочники, к которым идет интенсивное обращение). Кроме того, секционирование позволяет осуществлять независимое копирование и резервирование секций, оперативное восстановление секций, а также возможности более быстрой и более частой перестройки индексов наиболее активной секции, не затрагивая индексы пассивных секций.

Ключ секционирования

Следующим важным шагом в создании секционированной таблицы является определение ключа секционирования. В качестве ключа секционирования может выступить столбец или несколько столбцов, относительно значений которых будет делаться разнесение таблицы на секции. К потенциальным столбцам для создания ключа секционирования относятся столбцы типа date (например, столбец created — дата создания строки или updated — дата изменения строки) для секционирования по методам Range и List . Столбцы типа number с высокой степенью уникальности значений хорошо подходят для секционирования по методам Range и Hash . Столбцы, имеющие список фиксированных значений, подходят для секционирования по списку List .

В Oracle 11g появилась возможность в качестве ключа секционирования использовать виртуальный столбец (virtual column), построенный на функции к реальному столбцу таблицы. Виртуальный столбец в действительности не хранится в таблице, а каждый раз вычисляется при обращении к нему во время ввода данных в таблицу. Для создания виртуального столбца используется фраза generated always as. после которой идет функция, выполняемая над реальным столбцом таблицы, а далее идет обязательная фраза virtual. Например, PARTID generated always AS (to_char(UPDATED,’MM’)) virtual . Возможен вариант создания виртуального столбца более короткой фразой PARTID AS (to_char(UPDATED,’MM’)) .

Увидеть, какой столбец в таблице виртуальный позволяет запрос:

SELECT OWNER, TABLE_NAME, COLUMN_NAME, VIRTUAL_COLUMN, J DATAJTYPE, DATA_DEFAULT FROM ALL_TAB_COLS WHERE J OWNER"’ИМЯ СХЕМЫ' AND TABLE_NAME“'ИМЯ ТАБЛИЦЫ';

Замечание. При вводе данных в таблицу с виртуальным столбцом следует указать в insert и values перечень столбцов, иначе будет ошибка ORA-00947: not enough value .

Методы секционирования таблиц

Секционирование повышает эффективность работы с таблицами и индексами

Выбранный ключ секционирования, как правило, определяет методы секционирования. В настоящее время имеются следующие методы секционирования таблиц:

Range -секционирование по диапазону ключа,
List — секционирование по списку ключа.
Hash — хеш-секционирование,
составное секционирование.
интервальное секционирование.
ссылочное секционирование,
системное секционирование

Последние три появились в Oracle 11g. вместе с тем последние два у нас пока не нашли большого применения.

Секционирование методом Range по диапазону ключа

В практике секционирования по методу Range используем два вида секционирования: по диапазону дат и по диапазону значений.

Секционирование методом Range по диапазону дат

При секционировании этим методом нами используются секционирование по дням, месяцам и по годам. Секционирование этим методом покажем на примере таблицы HISTLG в схеме AIF. Ключом секционирования выступает столбец updated (дата корректировки строки), при этом секции создаются с шагом секций в один месяц. Команда создания секционированной таблицы create имеет вид:

CREATE TABLE AIF.HISTLG ( ISN NUMBER, UPDATED DATE) TABLESPACE HSTDATA PARTITION BY RANGE (UPDATED) (PARTITION PARTMM_2015_01 VALUES LESS THAN J (TO_DATE('01.01.2015','DD.MM.YYYY')) COMPRESS, PARTITION PARTMM_2015_02 VALUES LESS THAN J (TO__DATE (' 01.02.2015 ', 'DD.MM.YYYY')) , PARTITION PARTMM_2015_03 VALUES LESS THAN J (TO__DATE ('01.03.2015', ' DD .MM. YYYY ’)) , PARTITION PARTMM_MAX VALUES LESS THAN (MAXVALUE) ) ENABLE ROW MOVEMENT;

В команде CREATE указаны табличное пространство TABLESPACE HSTDATA, в котором будет находиться таблица, метод секционирования и ключ секционирования PARTITION BY RANGE (UPDATED) , имена секций и максимальное значение диапазона ключевого столбца этой секции. Например, первая секция PARTITION PARTMM_2015_01 VALUES LESS THAN TO_DATE(‘01.01.20157DD.MM.YYYY’) говорит о том, что все значения столбца update меньше 01.01.2015 попадут в первую секцию, а значения update меньше 01.02.2015 попадут во вторую секцию и т.д. В таблице создана последняя секция PARTITION PARTMM_MAX VALUES LESS THAN (MAXVALUE) , позволяющая при превышении значения ключа значения диапазона предпоследней секции размещать строки таблицы в эту последнюю секцию (это подстраховка на случай, если забыли создать новую секцию). Фраза COMPRESS определяет, что первая секция будет сжата.

Следует обратить особое внимание на последнюю фразу ENABLE ROW MOVEMENT , которая позволяет переходить строкам таблицы из секции в секцию. В отсутствии этой фразы Oracle выдаст ошибку. Переход строк по секциям может происходить автоматически при изменении значения ключа (например, столбец updated в результате операции update изменит значение на то. при котором он должен уже принадлежать другой секции) или может происходить специально, например, для перевода строк из оперативной секции в архивную секцию путем изменения значения ключевого столбца. Если не указали эту фразу при создании таблицы, то, чтобы избежать ошибки, следует выполнить команду ALTER TABLE ИМЯ ТАБЛИЦЫ ENABLE ROW MOVEMENT .

Читать:

Сколько стоит уаз патриот бу

Увидеть секции таблицы можно по запросу:

SELECT * FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME*'ИМЯ J ТАБЛИЦЫ’ AND TABLE_OWNER-'ИМЯ СХЕМЫ' ORDER BY J PARTITION_POSITION;

А содержимое секции по запросу:

SELECT * FROM ИМЯ_СХЕМЫ.ИМЯ_ТАБЛИЦЫ PARTITION (ИМЯ СЕКЦИИ);

Секционирование методом RANG по диапазону значений

Секционирование по диапазону значений похоже на секционирование по диапазону дат, только вместо ключа по дате используется ключ по столбцу, принимающему числовое значение (желательно имеющее равномерное распределение по всему диапазону значений). Для этого хорошо подходит столбец с уникальным значением. Рассмотрим на примере той же таблицы AIF.HISTLG, секционированной выше по диапазону дат. В качестве ключа секционирования используется столбец ISN с уникальными значениями. Команда создания таблицы имеет вид:

CREATE TABLE AIF.HISTLG ( ISN NUMBER. UPDATED DATE) TABLESPACE HSTDATA PARTITION BY RANGE (ISN) (PARTITION PARTISN_01 VALUES LESS THAN (1000), PARTITION PARTISN_02 VALUES LESS THAN (2000), PARTITION PARTISN_03 VALUES LESS THAN (3000), PARTITION PARTISN_MAX VALUES LESS THAN (MAXVALUE) ) ENABLE ROW MOVEMENT;

секционированные таблицы (PARTITION) в базе данных Oracle

где PARTITION BY RANGE (ISN) говорит о секционировании no RANGE при ключе секционирования ISN, интервал создания секции через 1000 значений.

Создание новой секции в секционированной таблице по методу Range

Каждый раз при создании секционированной таблицы возникает непростой вопрос: как создавать новые секции. До Oracle 11g было три варианта создания новой секции.

Первый вариант — это в команде create таблицы вручную создается множество секций (например, на несколько лет вперед). Однако, как показала практика, этот метод приводит к тому, что через несколько лет о том, что таблица была секционирована, могут забыть. Когда об этом вспоминают, то оказывается, что информация длительное время пишется в одну и ту же последнюю секцию THAN (MAXVALUE) . В результате секционированная таблица практически превратилась в обычную таблицу. В этой ситуации надо либо снова создавать новую секционированную таблицу, либо по команде Split разбивают последнюю секцию на несколько секций. Например, для таблицы AIF.HISTLG (секционированной по дате) команда Split по созданию новой секции PARTMM_2016_01 на основе расщепления последней PARTMM.MAX секции имеет вид:

--диапазон новой секции ALTER TABLE AIF.HISTLG SPLIT PARTITION PARTM_MAX J AT (TO_DATE(’01.01.2016','DD.MM.YYYY')) INTO (PARTITION PARTMM_2016_01 , --имя новой секции PARTITION P_MAX) UPDATE GLOBAL INDEXES;

Фраза UPDATE GLOBAL INDEXES обеспечивает исправность индексов после команды Split.

Второй вариант — создать процедуру, которая автоматически образует новую секцию. Такая универсальная процедура для секционирования по дням и месяцам была нами разработана. Данная процедура запускается Job Sheduler ежедневно для секционирования по дням или ежемесячно для секционирования по месяцам.

Данные процедуры успешно работают уже несколько лет, своевременно создавая новые секции. Основой процедуры являются представление ALL_TAB_PARTITIONS ДЛЯ поиска последней секции таблицы и команда Split для расщепления этой секции по команде ALTER , указанной выше.

Третий вариант (разработан нашими специалистами и успешно применяется в течение несколько лет) — это создание секционированной таблицы с секциями, используемыми по циклу. Под секционированием таблиц по циклу понимаются секционирование, выполненное в соответствии с двумя правилами. Первое правило — таблица должна содержать фиксированное количество секций, равное либо максимальному числу дней в месяце (31 секция), либо максимальному число дней в году (366 секций), либо числу месяцев в году (12 секций). Второе правило: данные в одну и ту же секцию попадают с определенной периодичностью (цикличностью).

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

В Oracle 11g появилась новая замечательная возможность автоматического создания секций с использованием при создании таблицы фразы INTERVAL (такой подход называется интервальное секционирование Interval Partitioning). Тогда при создании секций методом Range по интервалу дат с использованием фразы Interval команда создания секционированной таблицы примет вид:

CREATE TABLE AIF.HISTLG ( ISN NUMBER, UPDATED DATE) J TABLESPACE HSTDATA PARTITION BY RANGE (UPDATED) INTERVAL (INTERVAL '1' MONTH) (PARTITION PARTMM_01 VALUES LESS THAN J (TO_DATE('01.01.2015*,'DD.MM.YYYY')) ) ENABLE ROW MOVEMENT;

где фраза INTERVAL (INTERVAL ‘1’ MONTH) указывает, что секции будут автоматически создаваться каждый месяц (та же фраза может иметь вид INTERVAL (NUMTOYMINTERVAL (1. ‘MONTH’) . Для секционирования по дням используется фраза INTERVAL (INTERVAL ‘1’ DAY) , а по годам — INTERVAL (INTERVAL ‘1’ YEAR) . При автоматическом создании секций методом Range по интервалу значений с использованием фразы Interval команда создания таблицы примет вид:

CREATE TABLE AIF.HISTLG ( ISN NUMBER, UPDATED DATE) J TABLESPACE HSTDATA PARTITION BY RANGE (ISN) INTERVAL (1000) (PARTITION PARTISN_01 VALUES LESS THAN (1000) ) J ENABLE ROW MOVEMENT;

где фраза INTERVAL(1000) задает режим автоматического создания секции через 1000 значений ISN.

Следует учесть, что новые секции создаются в процессе ввода данных. Следует также иметь в виду, что имя новой автоматически создаваемой секции будет иметь вид SYS_PNNNNN, например, SYS_P28981. При этом при интервальном секционировании не нужно создавать последнюю секцию VALUES LESS THAN (MAXVALUE) . иначе появится ошибка ORA-14761.

Таким образом, в Oracle 11g у команды create создания секционированной таблицы существенно меньшее число строк, а о создании новой секции своевременно позаботится Oracle.

Секционирование по списку ключей LIST

Секционирование по списку применяется, если есть возможность указать конкретный перечень дискретных значений столбца, по которому происходит разбиение на секции. При секционировании по LIST в команде create указываются метод секционирования LIST (PARTITION BY LIST) , ключ секционирования и имена секций, в которых указывается одно или несколько дискретных значений.

В качестве примера проведем секционирование таблицы AIF.AGREEM. используя в качестве ключа виртуальный столбец partid. При каждом вводе строки в таблицу в виртуальном столбце формируется числовой номер месяца по функции to_number(to_char(updated,’MM’)) . Таблицу разбиваем на 12 секций, кроме того, используем подход секционирования по циклу, когда в следующем году строки января вводятся в ту же секцию января, а перед этим секция за январь чистится от старых данных по delete или по truncate. Команда создания секции примет вид:

--виртуальный столбец CREATE TABLE AIF.AGREEM (ISN NUMBER,UPDATED DATE, J PARTID AS (TO_NUMBER(TO_CHAR(UPDATED, ’ ’))) ) PARTITION BY LIST(PARTID) ( PARTITION PART_1 VALUES (1), PARTITION PART_2 VALUES (2), PARTITION PART_12 VALUES (12));

Вместо виртуального столбца может быть введен реальный столбец partid (тип number) , заполняемый при вводе строки в таблицу. Указанный выше вариант эффективно использовался в таблицах как с 366 секциями, так и с 12 с очисткой последних по truncate, поскольку информация в таблицах хранится меньше года. Достоинство этого подхода в том, что создавать новые секции не приходится, а табличное пространство старых секций ежемесячно быстро освобождается по truncate .

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

ALTER TABLE ИМЯ_СХЕМЫ.ИМЯ_ТАБЛИЦЫ MODIFY PARTITION J ИМЯ_СЕКЦИИ SHRINK SPACE CASCADE; ALTER TABLE ИМЯ_СХЕМЫ.ИМЯ_ТАБЛИЦЫ MOVE PARTITION ИМЯ_СЕКЦИИ J TABLESPACE ИМЯ_ТАБЛИЧНОГО_ПРОСТРАНСТВА NOLOGGING;

Другие стандартные варианты секционирования по методу LIST изложены в различных источниках.

Хеш-секционирование HASH

Как правило, если не получается секционировать по диапазону RANGE или LIST , то применяется хешсекционирование, основанное на хеш-функции. В этом случае строки таблицы равномерно распределяются между секциями на основании внутренних алгоритмов хеширования Oracle. При этом чем уникальнее значения столбца в таблице, по которому идет секционирование, тем лучше будет распределение данных по разделам. Первичный ключ или уникальный столбец (столбцы) является самым хорошим хеш- ключом. Oracle рекомендует число секций N как степень 2, т.е. N=2,4,8,16,32 и т.д. При этом добавление или удаление какой-то хеш-секции вызывает перезапись всех данных в другие секции. Рассмотрим HASH секционирование на примере индексноорганизованной таблицы LISTIN. Целью HASH секционирования таблицы было добиться существенного снижение числа блокировок, возникающих в этой таблице. Эта цель была успешно реализована за счет секционирования таблицы по 16 секциям (фраза PARTITIONS 16 ), где ключом секционирования выступал столбец TASKISN. Команда создания таблицы имеет вид:

CREATE TABLE AIF.LISTIN (TASKISN NUMBER, OBJISN NUMBER, J PARAM NUMBER, CONSTRAINT PK_LISTIN PRIMARY KEY(TASKISN,OBJISN,OBJROWID,J PARAM) ) ORGANIZATION INDEX INCLUDING PARAM OVERFLOW PARTITION BY HASH (TASKISN) PARTITIONS 16

Следует заметить, что если в качестве ключа секционирования используется столбец, в котором имеем очень неравномерное распределение значения столбца (малая уникальность), то применение хеш-секционирования не целесообразно. При этом число секций не имеет особого значения, поскольку все значения ключевого столбца «свалятся» в одну-две секции.

Замечание. Увидеть размер секций в mb по всем указанным выше методам можно по запросу:

SELECT P.TABLE_OWNER, Р.TABLE_NAME,Р.PARTITION_POSITION POS, J P.PARTITION_NAME, P.HIGH_VALUE, P.SEGMENT_CREATED, J (SELECT ROUND(S.BYTES/1024/1024,1) J FROM DBA_SEGMENTS S WHERE S.OWNER-P.TABLE_OWNER J AND S.SEGMENT_NAME“P.TABLE_NAME AND J S.PARTITION_NAME=P.PARTITION_NAME) MB J FROM DBA_TAB_PARTITIONS P J WHERE TABL?_OWNER='ИМЯ СХЕМЫ’ J AND TABLE_NAME“’ИМЯ ТАБЛИЦЫ' J ORDER BY PARTITION_POSITION;

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

При составном секционировании внутри секции создаются подсекции Однако в версиях до Oracle 11g смешанное секционирование разрешалось только по RANGE методу для секции и методам HASH или LIST для подсекции. В Oracle 11g варианты методов секций-подсекций были существенно расширены, и в настоящее время можно осуществлять составное секционирование в следующих комбинациях: Range-Range, Range-Hash , Range-List, List-Range, List-Hash или Ust-List. Надо отметить, что при составном секционировании данные физически хранятся в подсекциях, а секции высту-пают только в роли логических контейнеров.

Рассмотрим смешанное секционирование на примере таблицы платежей AIF.PAY_ORD_RECORD с делением таблицы на секции по методу RANGE , а на подсекции по методу LIST . Ключом секционирования по секциям выступает столбец PAY_DATA (тип date), а ключом секционирования подсекции выступает столбец STATUS (тип number), принимающий три значения: 0. 1,2. Команда создания секционированной таблицы в Oracle 11g с секционированием по месяцам примет вид:

CREATE TABLE AIF.PAY_ORD_RECORD ( ISN NUMBER, J PAY_DATA DATE, PAYER_NAME VARCHAR2(255). J STATUS NUMBER ) TABLESPACE HSTDATA PARTITION BY RANGE (PAY_DATA) INTERVAL (INTERVAL '1' MONTH) SUBPARTITION BY LIST (STATUS) SUBPARTITION TEMPLATE ( SUBPARTITION STATUSJ) VALUES (0) J TABLESPACE TRDATA1, SUBPARTITION STATUS_1 VALUES (1) J TABLESPACE TRDATA2, SUBPARTITION STATUSJ VALUES (2) J TABLESPACE TRDATA3 ) (PARTITION PJ015JJ1 VALUES LESS J THAN(TO_DATE('01.01.2015','DD.MM.YYYY'))) ENABLE ROW MOVEMENT;

где разбиение по секциям задает фраза PARTITION BY RANGE (PAY_DATA) , а по подсекциям фраза SUBPARTITION BY LIST (STATUS) . Далее идет список подсекций со своими значениями: STATUS_0 VALUES (0) . STATUSJ VALUES (1) . STATUSJ? VALUES (2) . Для каждой подсекции может быть задано свое табличное пространства, которое может отличаться от табличного пространства таблицы HSTDATA. С гомощью предложения SUBPARTITION TEMPLATE один и тот же набор подсекций будет автоматически использоваться во всех секциях. Однако создание подсекций можно сделать вручную, указав все подсекции для каждого секции. Просмотреть созданные подсекции по имени таблицы можно по запросу:

SELECT P.TABLE_OWNER, Р.TABLE_NAME, Р.PARTITION_NAME, J P.PARTITION_POSITION, P.HIGHJALUE, J P.SUBPARTITION_COUNT, S.SUBPARTITION_NAME, J S.HIGH_VALUE, S.SUBPARTITION_POSITION SUBJOS, J S.TABLESPACE_NAME, S.SEGMENT_CREATED FROM ALL_TABJARTITIONS P, ALL_TAB_SUBPARTITIONS S WHERE P.TABLEJWNER-S.TABLE_OWNER AND J P.TABLE_NAME-S.TABLE JJAME AND P. PARTITION JJAME-S. PARTITION JJAME AND P.TABLEJ)WNER-'AIF' AND P.TABLE JJAME-'PAY_ORD_RECORD' ORDER BY P.PARTITION POSITION, S.SUBPARTITION POSITION;

Системное секционирование (system partitioning)

Появилось в Oracle 11 g и применяется, как правило, для таблиц, которые не могут быть секционированы никакими другими методами. В этом методе Oracle сам управляет, какую строку таблицы в какую секцию помещать. Для этого метода необходимо просто написать название секций, например, секции Р1, Р2, РЗ:

CREATE TABLE AIF,PAY_ORD_RECORD_SYS (ISN NUMBER, PAY_DATA DATE, PAYERJJAME VARCHAR2(255), J STATUS NUMBER) PARTITION BY SYSTEM (PARTITION PI, PARTITION P2, PARTITION P3);

Увидеть разбиение таблицы на секции можно по запросу:

SELECT * FROM ALLJTABJARTITIONS J WHERE TABLE JJAME- ’ PAY_ORD_RECORD_SYS ’ ;

Увидеть метод секционирования, что он именно SYSTEM , можно по запросу:

SELECT PART IT I ON ING_T YPE FROM ALLJART_TABLES J WHERE TABLE JJAME-'PAY_ORD_RECORD_SYS’;

Следует заметить, что для правильного ввода данных в таблицу надо, помимо имени таблицы, указать еще имя сегмента, иначе будет ошибка ORA-14701 . Тоже для ускоренной выборки данных по запросу следует указать имя сегмента.

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

Выводы

Секционирование повышает эффективность работы с таблицами и индексами, позволяя решать задачи, приведенные в начале статьи.
Важным шагом секционирования является определение ключа секционирования. В Oracle 11g возможно в качестве ключа секционирования использовать виртуальный столбец. В случае невозможности выявить ключ секционирования следует рассмотреть варианты использования методов HASH или системного секционирования.
Выбор метода секционирования в значительной степени определяется выбором ключа секционирования, а число методов секционирования существенно расширено в Oracle 11g.
Важным моментом секционирования таблицы является выбор подхода по созданию новой секции. В статье предлагается три подхода: разработка процедуры автоматического создания секции, запускаемой периодически из JOB, следующий подход — это использование секционирования по циклу или использование интервального секционирования.
Секционирование таблицы позволяет, помимо деления таблицы на секции, создавать внутри секции еще подсекции, при этом в Oracle 11g число комбинаций методов секция-подсекция значительно расширено.

Oracle. Ещё один способ партиционирования больших и нагруженных таблиц

Всем привет! Меня зовут Ольга и я разработчик в Ингосстрахе. В этой статье-туториале хочу поделиться способом партиционирования оооочень большой таблицы в Oracle 12c. Итак, погнали!

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

Условия

У нас есть Oracle 12c, небольшая таблица на 15 миллиардов строк, общим объемом около 2 Тб без учета индексов;
Скорость поставки данных в таблицу в час: от 40 тысяч глухой ночью, до полутора миллионов днем.

Задача

Партиционировать таблицу таким образом, чтобы от 20 до 50 сессий в минуту ночью и до 500 днем ничего не заметили;
Урезать ~~осетра~~ данные до глубины 3 года.

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

Варианты решений

Рассматривать вариант с переносом необходимых данных на архивный сервер с последующим удалением на проде мы не стали, ибо отсекание всего лишнего (80%) дарит непроходящую радость от конкуренции на индексе как пользователям, так и DBA.
Пробовать партиционирование всей таблицы в лоб – энтузиастов не нашлось. Это в лучшем случае очень длительная операция, а если имеются LOB-поля, то можно и вообще никогда в жизни не дождаться.
Вариант с использованием реорганизации таблиц DBMS_REDEFINITION хорош, но требует очень много места.
Оставалось одно: создать транзитную таблицу, быстро загрузить в нее необходимые данные и произвести подмену текущей таблицы транзитной. Самый симпатичный способ подобной подмены Exchanging Partitions. В этом случае операция выполняется над словарем данных, практически мгновенно, без инвалидаций и простоев.

Работает эта прелесть так

Короче, к делу (а для особо озорных в скрытом тексте полный скрипт с созданием исходной таблицы и генерацией 2 Тб данных для неё).

Ладно-ладно, я пошутила, 300 Гб вполне достаточно, чтобы прочувствовать…

CREATE TABLE Scott.TEST_EXCHANGE ( ISN NUMBER NOT NULL, HISTESTIMATIONISN NUMBER NOT NULL, SOURCECODE CHAR(1 BYTE) NOT NULL, SOURCEISN NUMBER NOT NULL, VALUECODE CHAR(1 BYTE) NOT NULL, VALUENAME VARCHAR2(255 BYTE), DATAVALUE CLOB, CREATED DATE NOT NULL, CREATEDBY NUMBER NOT NULL, UPDATED DATE NOT NULL, UPDATEDBY NUMBER NOT NULL ) LOB (DATAVALUE) STORE AS BASICFILE ( TABLESPACE TDATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION ) tablespace TDATA ; alter session enable parallel query; alter session enable parallel DML; declare V_size number := 0.300; -- объем в Тб V_rows number := 5000000; /* количество записей, с которого перепроверяем полученный объем, чтобы не опрашивать на каждом проходе и не считать лишнего*/ v_pks number := 100000; -- размер пачки i number := 0; s number; v_sql varchar2(32000); begin /*Сгенерируем нужный(V_size) объем данных в табличку, надеюсь ваша база не совсем пустая, так как данные я планирую позаимствовать из нее что бы не перенапрягать генераторы случайных значений*/ loop for r in ( /* Для большей наглядности воспользуемся вложенными запросами, а то много скобок и не так красиво. Далее в этом запросе читать комментарии снизу вверх: */ Select owner, table_name, column_name, num_rows , nvl(varchar2_col,'null') varchar2_col, n , RPAD (ncols, LENGTH (ncols) + (3 - n) * 2, ',0') ncols --если колонок меньше 3 достраиваем нулями. From (Select owner, table_name, column_name, ' '||ncols ncols , num_rows, varchar2_col , nvl((LENGTH(ncols) - LENGTH(TRANSLATE (ncols, 'x,', 'x')))/2 ,0) n /* считаем сколько у нас в этой таблице вышло пригодных для инсерта колонок*/ From (Select l.owner, l.table_name, l.column_name , t.num_rows, varchar2_col , NVL ( SUBSTR (l.ncols ,1 ,INSTR (l.ncols, ',', 1, 3*2+1) - 1) , l.ncols) ncols /* отбрасываем в списке колонок лишнее, нам нужно только 3 числа, первичный ключ мы присвоим уникальный, а ключ будущего партиционирования HISTESTIMATIONISN будем генерить */ From (Select l.owner , l.table_name , l.column_name , max(case when l.column_name = c.column_name then c.data_type end) lob_type , max(case when c.data_type = 'VARCHAR2' then c.column_name end) varchar2_col , LISTAGG (Case When c.data_type = 'NUMBER' Then ',nvl(' ||c.column_name||',0)' End) --так как not null полей может не хватить прицепим nvl Within Group (Order By c.nullable Desc, c.column_id) ncols -- собираем колонки с типом NUMBER From dba_lobs l Join all_tab_columns c On c.owner = l.owner And c.table_name = l.table_name /*воспользуемся таблицами с лобами и вытащим из них лобы и числа, что бы не дергать dbms_random и не кипятить проц*/ Group By l.owner, l.table_name, l.column_name) l join dba_tables t on t.table_name = l.table_name and t.owner = l.owner and num_rows>0 join all_tab_privs p on p.table_name = t.table_name and p.table_schema = t.owner --что бы на наступить на гранты where lob_type='CLOB')) order by case when num_rows > v_pks then 1 else 0 end desc ) loop v_sql := 'INSERT /*+ APPEND PARALLEL(16) */ INTO Scott.TEST_EXCHANGE (ISN, HISTESTIMATIONISN, CREATEDBY, UPDATEDBY, SOURCEISN, DATAVALUE, VALUECODE, SOURCECODE, VALUENAME, CREATED, UPDATED)'|| chr(10)|| /* ключики себе генерим для ПК последовательно, для будущего ключа партиционирования c разбросом ~50 значений по пачке d 100 000 (v_pks)*/ 'SELECT rownum+:i, floor((rownum+:i)/(:v_pks/50)) ' ||r.ncols||' ,'||r.column_name|| --чтобы не писать много двойных кавычек используем конструкцию q'' q'' ||r.varchar2_col||',1,200) ,sysdate,sysdate FROM '||r.owner||'.'||r.table_name ||' SAMPLE BLOCK ('||case when ceil(100*v_pks/r.num_rows)> 100 then 99 else ceil(100*v_pks/r.num_rows) end||') --'||r.num_rows||' WHERE ROWNUM = V_rows then /*с этого количества строк начинаем проверять размерчик*/ select sum(bytes)/power(1024,4) into s from (select sum(bytes) bytes from dba_segments where owner = 'Scott' and segment_name = 'TEST_EXCHANGE' union select sum(bytes) from dba_lobs l Join dba_segments s on l.OWNER = s.OWNER and l.SEGMENT_NAME = s.SEGMENT_NAME where l.owner = 'Scott' and l.TABLE_NAME = 'TEST_EXCHANGE'); exit when s >=v_size; end if; end loop; --если табличек с лобами не достаточно, придется по ним еще раз пробежаться select sum(bytes)/power(1024,4) into s from (select sum(bytes) bytes from dba_segments where owner = 'Scott' and segment_name = 'TEST_EXCHANGE' union select sum(bytes) from dba_lobs l Join dba_segments s on l.OWNER = s.OWNER and l.SEGMENT_NAME = s.SEGMENT_NAME where l.owner = 'Scott' and l.TABLE_NAME = 'TEST_EXCHANGE'); exit when s >=v_size; end loop; exception when others then DBMS_OUTPUT.put_line (v_sql); raise; end; CREATE UNIQUE INDEX Scott.PK_TEST_EXCHANGE ON Scott.TEST_EXCHANGE (ISN) parallel 32; Alter index Scott.PK_TEST_EXCHANGE noparallel; CREATE INDEX Scott.X_TEST_EXCHANGE_HISTORY ON Scott.TEST_EXCHANGE (CREATED, SOURCECODE, VALUECODE, VALUENAME) parallel 32; Alter index Scott.X_TEST_EXCHANGE_HISTORY noparallel; ALTER TABLE Scott.TEST_EXCHANGE ADD ( CONSTRAINT CHK_TEST_EXCHANGE_VALUECODE CHECK (ValueCode in ( 'V', 'R', 'A', 'D', 'P', 'B', 'T', 'W', 'E','S','I','H','C')) ENABLE NOVALIDATE , CONSTRAINT PK_TEST_EXCHANGE PRIMARY KEY (ISN) USING INDEX Scott.PK_TEST_EXCHANGE ENABLE VALIDATE);

/*Создаем промежуточную таблицу по структуре аналогичную партиционируемой, но с необходимым партицоинированием*/ Create Table Scott.TEST_EXCHANGE_buf ( isn Number Not Null , histestimationisn Number Not Null , sourcecode Char (1) Not Null , sourceisn Number Not Null , valuecode Char (1) Not Null , valuename Varchar2 (255) , datavalue Clob , created Date Not Null , createdby Number Not Null , updated Date Not Null , updatedby Number Not Null ) Tablespace TDATA Partition By Range (histEstimationIsn)(Partition p_maxvalue Values Less Than (maxvalue)) Enable Row Movement; /*Для скорости одним куском*/ begin /*Перенос данных за последний час*/ Lock Table Scott.TEST_EXCHANGE In Exclusive Mode Nowait; insert into Scott.TEST_EXCHANGE_buf select /*+ index (c X_TEST_EXCHANGE_HISTORY) parallel(32) */ * from Scott.TEST_EXCHANGE where created>sysdate-1/24; /*меняем местами промежуточную и основную*/ execute immediate 'Alter table Scott.TEST_EXCHANGE_BUF Exchange partition p_maxvalue With table Scott.TEST_EXCHANGE Without validation'; /*требуется если на исходной или на промежуточной таблицах есть констрейнты, только так операция будет быстрой*/ end; /*партиционируем маленькую таблицу*/ Alter table Scott.TEST_EXCHANGE modify PARTITION BY RANGE (HISTESTIMATIONISN) INTERVAL (2000000) (Partition part_1 values less than (2000000)) COMPRESS Online Parallel 16 Enable row movement; /* Создание секций заранее вставкой суррогатных данных существенно снизит конкуренцию при переносе данных в PARALLEL_EXECUTE. расчетное количество секций 2970.*/ begin commit; for i in 1..2970 loop Insert Into scott.TEST_EXCHANGE Values ( 28803, 21364838103 + (i * 2000000), 'F', 100, 'R' , '', '', date'2021-11-07', 0, date'2021-11-07', 0); rollback; end loop; end; /*перенос из старой таблицы в новую данных за последние 3 года*/ Declare misn Number; v_job_name Varchar2 (500) := 'RUN_COPY_TEST_EXCHANGE'; n Number; Begin /*ограничиваем по Isn перенос, чтобы не наступить на уже перенесенный последний час */ Select MIN (isn) Into misn From Scott.TEST_EXCHANGE; /*перенос из старой таблицы в новую данных за последние 3 года*/ Select COUNT (1) Into n From dba_parallel_execute_tasks Where task_name = 'copy TEST_EXCHANGE'; If n > 0 Then Dbms_Parallel_Execute.drop_task ('copy TEST_EXCHANGE'); End If; Dbms_Parallel_Execute.create_task ('copy TEST_EXCHANGE'); Dbms_Parallel_Execute.create_chunks_by_rowid ('copy TEST_EXCHANGE', 'Scott' , 'TEST_EXCHANGE_BUF', True , 1000000); Dbms_Parallel_Execute.run_task ('copy TEST_EXCHANGE' , 'begin insert into Scott.TEST_EXCHANGE select /*+rowid(h)*/ * from Scott.TEST_EXCHANGE_BUF h where rowid between :start_id and :end_id and created>=add_months(trunc(sysdate),-36) and isn < ' || misn || '; end;', Dbms_Sql.native, parallel_level =>64); End; /*создание индекса */ CREATE INDEX Scott.X_TEST_EXCHANGE_HISTESTIMATIONISN ON Scott.TEST_EXCHANGE(HISTESTIMATIONISN) LOCAL TABLESPACE IDXDATA COMPRESS parallel 32; Alter index Scott.X_TEST_EXCHANGE_HISTESTIMATIONISN noparallel; alter table Scott.TEST_EXCHANGE noparallel;

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

партиционирование
oracle
секционирование
субд
exchange partition