Начиная с Oracle 11 размер любой партиции, представленной на диске, по умолчанию равен 8МБ, даже если она не имеет в себе никаких данных (До 11 версии размер по умолчанию был 65КБ).
Отсюда важность указания STORAGE INITIAL при создании партиционированной таблицы.
Рассмотрим на примере. Таблица из 80 млн записей партиционирована RANGE-INTERVAL по месяцу и субпартиционирована LIST по региону. Месячные интервалы с 2013 года - это 6+12=18, и каждая партиция по 100 субпартиций регионов.
Отсюда не сложно высчитать минимальный размер такой таблицы 18*100*8МБ= 140ГБ.
Что явный перебор при исходном размере таблицы без секций = 2ГБ.
Такой размер имеет ряд минусов:
- Сложность переноса бд (экспорта/импорта) - база будет весить терабайты.
- Ограниченность tablespace по размеру: Таблиц много, в DWH такая таблица должна быть продублирована несколько раз (Таблица источник - зеркало, Преобразованная таблица, Итоговая фактическая таблица). Доступное пространство быстро закончится.
- !Увеличение времени чтения партиции с диска при сканировании по партициям.
Мое решение: необходимо уменьшить размер партиции.
Я обычно пользуюсь таким решением:
--размер блока в байтах
select value from v$parameter where name = 'db_block_size';
VALUE
--------------------------------------------------------------------------------
8192
--узнаю реальный средний физический размер партиции
select AVG(blocks*8192/1024/1024) as mb, COUNT(*) from SYS.ALL_TAB_SUBPARTITIONS WHERE TABLE_NAME = :TAB;
MB COUNT(*)
---------- ----------
0,6846 3404
Размер партиции можно взять с небольшим запасом (1МБ):
CREATE TABLE T_TBL STORAGE(INITIAL 1048576 NEXT 1048576 ) PARTITION BY RANGE (DT_COL) INTERVAL (NUMTOYMINTERVAL(1,''MONTH'')) SUBPARTITION BY LIST(REG_COL) SUBPARTITION TEMPLATE ...
Минимальный размер такой таблицы вырастет в сравнении с плоской незначительно на (1-0,68/1)*100% = 32%
Замечу один минус:
Совсем маленькой партиции тоже не стоит делать - это увеличить число физических обращений к диску. Т.к. для сканирования секции понадобится не одно обращение, а несколько.
2. Настройка параметров PCTFREE и PCTUSED для уменьшения размера таблицы
Размер таблицы можно значительно уменьшить, если задать эти параметры:
PCTFREE - % блока под резерв изменений. Если таблица редко обновляется (или только чистится и заполняется заново), то этот параметр можно ставить близким к 0.
PCTUSED - % блока под данные, если размер данных в блоке превышает этот %, то новые данные туда не будут вставляться. Если опять же данные редко обновляются, то для максимального уплотнения этот параметр можно ставить ближе к 100%.
+ Уменьшение размера таблицы сопоставимо изменению параметров PCTFREE/PCTUSED
- При update строки с увеличением его размера, в случае нехватки свободного места в блоке (PCTFREE) строка целиком будет перенесена из текущего блока в новый. В старом блоке будет проставлена ссылка на новое расположение. Т.е. при чтении данных из таблицы нужно будет выполнить дополнительное рекурсивное чтение, что значительно увеличит стоимость запроса.
- При большом числе параллельных сессии к одному блоку сильно разрастается информация о заинтересованных сессиях к строкам блока (ITL). Если место в блоке закончится, то невозможно будет расширить ITL, что приведет к ошибке обновления блока.
3. Быстрое обновление таблиц через PARTITION EXCANGE.
Частая задача в DWH - инкрементальное обновление данных. Допустим нужно залить новые данные за прошедший месяц.
Традиционный способ INSERT /*+ APPEND */ достаточно медленный.
Есть альтернативный, выполняющийся доли секунды.
Для этого нужна вспомогательная таблица, куда будет класться очередная порция для обновления.
У таблиц должна совпадать полностью структура, включая размерности и последовательность столбцов.
После этого можно перекинуть из этой таблицу в целевую партиционированную одной командой.
Пример перекидки из промежуточной таблицы в RANGE-INTERVAL партиционированную таблицу.
--создаем интервальную партицию, если ее еще нет
LOCK TABLE T_TBL PARTITION FOR (TO_DATE('...','DD.MM.YYYY')) IN SHARE MODE;
--обмениваемся партициями
ALTER TABLE T_TBL EXCHANGE PARTITION FOR (TO_DATE('...','DD.MM.YYYY')) WITH TABLE T_TBL_PT WITHOUT VALIDATION;
Этой командой произойдет обмен данными между таблицами: партиция заполнится данными таблицы T_TBL_PT, а таблица данными пустой партиции T_TBL.
Аналогично можно делать обмены с субпартициями
ALTER TABLE T_TBL EXCHANGE SUBPARTITION FOR (TO_DATE('<партиция>','DD.MM.YYYY'),<субпартиция>) WITH TABLE T_TBL_PT WITHOUT VALIDATION;
4. Узнать имя партиции по произвольному фильтру
Такое может понадобится, допустим, для сбора статистики только по нужной секции, т.к. в dbms_stat нужно указать физическое имя.
select uo.subobject_name from user_objects uo where uo.data_object_id = dbms_rowid.rowid_object(:ROWID);
execute immediate('select /*+ FIRST_ROWS(1) */ MAX( (select /*+ NO_UNNEST */ uo.subobject_name from user_objects uo where dbms_rowid.rowid_object(t.rowid) = uo.data_object_id ) ) as part_name
from '||P_TABLE||' partition for ('||p_some_value||') t
where t.'||V_SOME_COL||' = '||p_some_value||' and rownum = 1') INTO v_part;
Если известно значение в колонке секций, то нужно воспользоваться конструкцией:
select * from T_TBL PARTITION FOR (:P_PART_VAL)
5. System партицирование
Нет указания колонки при создании, из-за этого нужно указывать конкретную партицию при вставке или выборке
6. Reference партицирование
Возможность создания детализированной таблицы с наследованием партицирования от родительской:
create table orders (id integer, sm number, odate date, CONSTRAINT opk PRIMARY KEY (id) PARTITION BY RANGE(odate) (...); create table order_items (oid integer, sm number, CONSTRAINT ofk FK to orders) PARTITION BY REFERENCE(ofk)В строках нет даты, она автоматом подтягивается по фк из заголовка и партицируется по ней.
Цена этому увеличение нагрузки в 10 раз: https://jonathanlewis.wordpress.com/2018/03/19/reference-costs/
7. Изменение параметров партиции на основании статистики использования
На основании статистики использования партиций (DBA_HEAT_MAP_SEG_HISTOGRAM) можно включить компрессию
ALTER TABLE T MODIFY [PART] ILM ADD POLICY COMPRESS ADVANCED ROW AFTER 30 DAYS OF NO MODIFICATION;
Хочу рассказать сегодня как получить список писем, аттачей из Windows Exchange и программно отсылать письма через протокол Simple MAPI.