Сжатие данных в целях экономии места и ускорения работы Oracle

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

При решении проблем с дисковым пространством, появившаяся в oracle 9i release 2 возможность сжатия таблицы может существенно сократить объем дискового пространства, используемого таблицами базы данных и, в некоторых случаях, повысить производительность запросов.

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

Как это реализовано

Возможность сжатия таблиц в oracle9i release 2 реализуется путем удаления дублирующихся значений данных из таблиц базы. Сжатие выполняется на уровне блоков базы данных. Когда таблица определена как сжатая, сервер резервирует место в каждом блоке базы данных для хранения одной копии данных, встречающихся в этом блоке в нескольких местах. Это зарезервированное место называют таблицей символов (symbol table). Помеченные для сжатия данные хранятся только в таблице символов, а не в строках данных. При появлении в строке данных, помеченных для сжатия, в строке, вместо самих данных, запоминается указатель на соответствующие данные в таблице символов. Экономия места достигается за счет удаления избыточных копий значений данных в таблице.

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

Для создания сжатой таблицы используется ключевое слово compress в операторе create table. Ключевое слово compress требует от сервера oracle, по возможности, хранить строки таблицы в сжатом виде. Ниже представлен пример оператора create table compress:

create table sales_history_comp (
part_id varchar2(50) not null,
store_id varchar2(50) not null,
sale_date date not null,
quantity number(10,2) not null
)
compress;


Можно также использовать оператор alter table для изменения атрибута сжатия существующей таблицы, как в следующем примере:

alter table sales_history_comp compress;


Чтобы узнать, использовалось ли ключевое слово compress в определении таблицы, выполните запрос к представлению user_tables словаря данных и проверьте значение столбца compression, как в следующем примере:

select table_name, compression from user_tables;

table_name compression
------------------ -----------
sales_history disabled
sales_history_comp enabled


Атрибут compress также может быть задан на уровне табличного пространства, как в момент его создания (с помощью оператора create tablespace), так и в дальнейшем (с помощью оператора alter tablespace). Атрибут compress наследуется аналогично параметрам хранения. При создании таблицы в табличном пространстве наследуется атрибут compress этого табличного пространства. Чтобы определить, задан ли для табличного пространства атрибут compress, выполните запрос к представлению dba_tablespaces словаря данных и проверьте значение столбца def_tab_compression, как в следующем примере:

select tablespace_name,
def_tab_compression
from dba_tablespaces;

tablespace_name def_tab_compression
--------------- -------------------
data_ts_01 disabled
index_ts_01 disabled


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

Загрузка данных в сжатую таблицу

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

Более того, чтобы гарантировать фактическое сжатие данных, надо использовать соответствующий метод загрузки или вставки данных в таблицу. Сжатие данных происходит только при массовой загрузке или в процессе массовой вставки, с помощью одного из следующих четырех методов:
Непосредственная загрузка sql*loader
Последовательные вставки insert с подсказкой append
Параллельный insert

create table ... as select


Метод непосредственной загрузки sql*loader - наиболее удобный способ загрузки данных в таблицу, если данные доступны в текстовом файле. Пример представлен ниже:

$sqlldr sanjay/sanjay@proddb control=sales_history.ctl direct=true


Если данные доступны в промежуточной (staging) таблице, можно использовать последовательные операторы insert с подсказкой append или параллельный insert.

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

insert /*+ append */
into sales_history_comp
select * from sales_history;


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

alter session enable parallel dml;
insert /*+parallel(sales_history_comp,4)*/
into sales_history_comp
select * from sales_history;


Учтите, что при использовании параллельного insert надо сначала включить распараллеливание операторов dml в сеансе с помощью команды alter session enable parallel dml.

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

Можно также использовать оператор create table ... as select для создания сжатой таблицы и вставки в нее данных за один шаг. Вот пример:

create table sales_history_comp
compress
as select * from sales_history;


Если не использовать соответствующий метод загрузки или вставки данных, данные в таблице окажутся не сжатыми, хотя для таблицы и определен атрибут compress. Например, если использовать обычную загрузку (conventional path) с помощью sql*loader или обычные операторы insert, данные не будут сжиматься.

Когда использовать сжатие таблиц

Применяемый сервером oracle алгоритм принятия решения о том, сжимать данные таблицы или не сжимать, приводит к определенным выводам об особенностях приложений, больше всего подходящих для сжатия таблиц. Как было описано выше, данные в таблице с атрибутом compress сжимаются только при непосредственной загрузке или при вставке с использованием подсказки append и распараллеливанием. Данные, вставленные обычными операторами insert, останутся не сжатыми.

В системах оперативной обработки транзакций (online transaction processing - oltp) данные обычно вставляются обычными операторами insert. В результате, от использования сжатия для соответствующих таблиц большого преимущества не будет. Сжатие таблиц больше всего подходит для таблиц только для чтения, данные в которые загружаются один раз, а читаются - многократно. Таблицы, используемые при организации хранилищ данных, например, прекрасно подходят для сжатия.

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

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

Сжатие существующей не сжатой таблицы

Уже существующую не сжатую таблицу можно сжать с помощью оператора alter table ... move. Например, не сжатую таблицу sales_history_temp можно сжать с помощью следующего оператора:

alter table sales_history_temp
move compress;


Оператор alter table ... move можно использовать и для отмены сжатия таблицы, как в следующем примере:

alter table sales_history_temp
move nocompress;


Учтите, что оператор alter table ... move устанавливает МОНОПОЛЬНУЮ блокировку таблицы, что предотвращает выполнение любых операторов dml с таблицей на время выполнения этого оператора. Этой потенциальной проблемы можно избежать за счет использования оперативного переопределения таблицы (online table redefinition), появившегося в oracle9i.

Сжатие материализованного представления

Материализованные представления можно сжимать точно так же, как и таблицы. Следующий оператор создает сжатое материализованное представление:
create materialized view mv_sales_comp compress as select p.part_name, h.store_id, h.sale_date, h.quantity from sales_history h, parts p where p.part_id = h.part_id;

Материализованные представления на основе соединений нескольких таблиц обычно хорошо поддаются сжатию, поскольку в них часто встречаются повторяющиеся компоненты данных. Атрибут сжатия для материализованного представления можно изменить с помощью оператора alter materialized view. Следующий оператор показывает, как сжать существующее не сжатое материализованное представление:

alter materialized view mv_sales compress;


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

Сжатие секционированной таблицы1

Вариантов использовании сжатия для секционированных таблиц много. Сжатие можно применять либо на уровне таблицы, либо на уровне секции. Например, оператор create table в Листинге 1 создает таблицу из четырех секций. Поскольку compress задается на уровне таблицы, все четыре секции будут сжиматься.

Поскольку сжатие может быть задано на уровне секции, можно некоторые секции сжать, а другие оставить не сжатыми. Пример в Листинге 2 демонстрирует, как задать сжатие на уровне секции.

В Листинге 2 две секции таблицы (sales_q1_03 и sales_q2_03) сжаты, а остальные две остаются не сжатыми. Учтите, что атрибуты сжатия, заданные на уровне секции, переопределяют атрибуты, заданные для этой же секции на уровне таблицы. Если атрибут сжатия для секции не задан, эта секция наследует значение из определения на уровне таблицы. В Листинге 2, поскольку атрибуты сжатия для секций sales_q3_03 и sales_q4_03 не указаны, эти две секции наследуют значение из определения таблицы (которое, в данном случае, стандартно - nocompress).

Секционированные таблицы обеспечивают совместно со сжатием одно уникальное преимущество. Один из полезных способов секционировать таблицы - поместить подлежащие изменению (вставке, обновлению и удалению) данные в отдельные секции, а данные только для чтения вынести в другие. Например, в определении таблицы в Листинге 2 данные о продажах секционированы по значению столбца sale_date, так что хронологическая информация о продажах в каждом квартале хранится в отдельной секции. В этом примере данные о продажах за первый (q1) и второй (q2) кварталы 2003 года не могут быть изменены, поэтому они помещены в сжатые секции sales_q1_03 и sales_q2_03. Данные о продажах за третий (q3) и четвертый (q4) кварталы все еще могут меняться, поэтому соответствующие секции, sales_q3_03 и sales_q4_03, оставлены не сжатыми.

Если в конце третьего квартала 2003 года данные в секции sales_q3_03 становятся доступными только для чтения, можно сжать эту секцию с помощью оператора alter table ... move partition, как показано ниже:

alter table sales_part_comp
move partition sales_q3_03 compress;


Чтобы узнать, какие секции таблицы сжаты, можно выполнить запрос к представлению user_tab_partitions словаря данных, как в следующем примере:

select table_name, partition_name,
compression
from user_tab_partitions;

table_name partition_name compression
---------------------------- -----------
sales_part_comp sales_q4_03 disabled
sales_part_comp sales_q1_03 enabled
sales_part_comp sales_q2_03 enabled
sales_part_comp sales_q3_03 enabled


Оценка преимуществ

Основной причиной использования сжатия таблицы является экономия дискового пространства. Таблица в сжатом виде обычно занимает меньше места. Чтобы проиллюстрировать это утверждение, рассмотрим следующий с двумя таблицами: одна не сжатая (sales_history), а другая - сжатая (sales_history_comp). В обе эти таблицы данные были загружены с помощью непосредственной загрузки утилитой sql*loader из текстового файла, содержащего два миллиона строк. После выполнения обеих загрузок оказалось, что сжатая таблица занимает на диске почти вдвое меньше места, чем не сжатая. Анализ представлен в Листинге 3.

Тот факт, что для хранения сжатой таблицы надо меньше блоков, приводит к экономии дискового пространства, но уменьшение количества блоков может приводить и к повышению производительности. Запросы к сжатой таблице в среде с ограниченной производительностью ввода-вывода часто будут выполняться быстрее, поскольку требуют прочтения меньшего количества блоков. Чтобы проиллюстрировать это утверждение, я выполнил запрос к сжатой и не сжатой таблице и проанализировал результаты sqltrace/tkprof. Эти результаты представлены в Листинге 4.

Отчет sqltrace/tkprof показывает, что мой запрос к сжатой таблице потребовал меньше операций физического и логического ввода-вывода, чем аналогичный запрос к не сжатой таблице, и, как следствие, выполняется быстрее.

Снижение производительности при загрузке

Поскольку сжатие таблицы выполняется при массовой загрузке, операции загрузки требуют дополнительной обработки - надо выполнять дополнительные действия. Чтобы измерить влияние сжатия на производительность, я выполнил тест, в котором загружал один миллион строк (с помощью непосредственной загрузки утилитой sql*loader) в две идентичных таблицы: со сжатием и без сжатия. В Таблице 1 представлены результаты, взятые из журнальных файлов sql*loader и показывающие, сколько времени потребовалось для загрузки данных в каждую из таблиц.
Имя таблицы Количест- во строк Способ загрузки Сжатая? Время загрузки
sales_history 1000000 Непосред- ственная Не сжатая 00:00:21.12
sales_history_comp 1000000 Непосред- ственная Сжатая 00:00:47.77

Таблица 1: Сравнение времени загрузки данных для сжатой и не сжатой таблиц

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

Заключение

Сжатие таблицы в oracle9i release 2 позволяет существенно сэкономить дисковое пространство, особенно в базах данных, содержащих большие таблицы только для чтения. Если учитывать дополнительные требования к загрузке и вставке данных, а также правильно выбрать таблицы-кандидаты для сжатия, сжатие таблиц может оказаться потрясающим способом экономии дискового пространства и, в некоторых случаях, повышения производительности запросов.

Статьи по теме

Оптимизация и тюнинг производительности MariaDB MySQL сервера внутри Docker

Содержание: Установка и знакомство с MySQLTuner Установка MySQL Tuning Primer Script Первичная оптимизация MySQL сервера ...

2023-11-08

Работа с базами данных MySQL в ISPManager 5 + Docker

В ISPManager 5 добавлена контейнерная виртуализация Docker для использования альтернативных версий MySQL. Цитата из документации ISPManager 5: ...

2023-10-27

«Облачные» хранилища данных: в ожидании доверия

Информационные технологии активно мигрируют в сторону «облаков». Эксперты прогнозируют, что одним из главных приоритетов для CIO в ближ...

2011-11-05

ГОСТ 34.602-89. на Техническое задание по разработке web проекта

ТЕХНИЧЕСКОЕ ЗАДАНИЕ НА СОЗДАНИЕ АВТОМАТИЗИРОВАННОЙ СИСТЕМЫ Information technology. Set of standards for automated systems. Technical directions ...

2010-12-28

Orange color: теория и практика

Знаете ли Вы, чем оранжевый цвет отличается от других ? Если нет, то знайте, что разница в скорости восприятия глазом отдельных цветов ра...

2010-12-27

FAQ по MS SQL 7.0

11. Как запускать-останавливать SQL Server через командную строку? Мне это нужно для использования в скриптах.Остановка:NET STOP MSSQLSERVERили если вы...

2010-09-17

Виртуальная реализация SQL Server 2005

Ранее в материале «Разбираем “айсберг” sql server 2005» читателям предлагалось воспользоваться опубликованной в internet серией информационных...

2010-09-17

SQL Server в вопросах и ответах

В: У меня на разных компьютерах установлено два sql server 7.0. Для обмена данными между ними я использовал data transformation services (dts), чтобы импортиро�...

2010-09-17

Один из вариантов соглашения об именах объектов MS SQL Server

Помните, что имена объектов sql сервера не могут превышать 30 символов. Не используйте зарезервированные слова, специальные символы, и не н...

2010-09-17

Создание и удаление БД в MS SQL Server

В sql server'е базы данных хранятся в виде обычных файлов на диске. Как минимум на одну БД приходится таких файлов 2: *.mdf и *.ldf. В первом хранятся...

2010-09-17

Создание и удаление таблиц в MS SQL Server

Все данные в БД sql server хранятся в таблицах.Таблицы состоят из колонок, объединяющих значения одного типа, и строк - записей в таблице. В од�...

2010-09-17

Единая аутентификация Windows NT/2000 и Oracle

При использовании Windows NT/2000 как платформы для работы Oracle, и на стороне сервера и на стороне клиентов, имеется возможность использовать ед...

2010-09-17

Oracle / PHP FAQ

Что такое PHP и что необходимо иметь для работы с Oracle?PHP - рекурсивный акроним "PHP Hypertext Preprocessor". Это интерпретируемый язык программирования ...

2010-09-17

Oracle и PHP - это очень просто

ВведениеСУБД Oracle, когда против желания фирмы-изготовителя, а когда согласно – в зависимости от направления политических ветров в конкр�...

2010-09-17

Динамические SQL-запросы Oracle для ускорения выборок данных

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

2010-09-16