Как в sql сделать таблицу с компрессией

Включение сжатия таблицы или индекса

В этой статье описано, как включить сжатие данных для существующей таблицы или индекса в SQL Server с помощью SQL Server Management Studio или Transact-SQL. Сведения о включении сжатия данных при создании таблицы или индекса см. в разделах Создание сжатого индекса и Создание таблицы, использующей сжатие строк.

ограничения

В системных таблицах не может быть включено сжатие.
Если таблица является кучей, операция перестроения для ONLINE режима является одним потоком. Используйте OFFLINE режим для операции перестроения куч с несколькими потоками. Операции перестроения не указаны OFFLINE , если этот параметр не указан ONLINE . Полные сведения о выполнении перестроения см. в разделе «Выполнение операций ONLINE с индексами в Сети».
Если в таблице есть невыровненные индексы, настройку сжатия для отдельной секции изменить нельзя.
Несколько типов данных не влияют на сжатие данных. Дополнительные сведения см. в статье Влияние сжатия строк на хранение.

Разрешения

Необходимо разрешение ALTER для таблицы или индекса.

Использование среды SQL Server Management Studio

В обозревателе объектов разверните базу данных, в которой содержится таблица, подлежащая сжатию, а затем разверните папку Таблицы .
Чтобы сжать индекс, разверните таблицу, содержащую нужный индекс, затем разверните папку Индексы .
Щелкните правой кнопкой мыши таблицу или индекс для сжатия, выберите хранение, а затем Управление сжатием.
В мастере сжатия данных на странице Добро пожаловать в мастер сжатия данных нажмите кнопку Далее.
На странице Выбор типа сжатия выберите тип сжатия, который будет применен к каждой выбранной секции таблицы или индекса. По завершении выберите Далее. Следующие параметры доступны на странице Выбор типа сжатия :
- флажокИспользовать один тип сжатия для всех секций Выберите этот вариант, чтобы задать одинаковые параметры для всех секций. Он включает флажок выбора и делает недоступным столбец Тип сжатия в сетке. При его выборе в списке рядом появляются параметры: Нет, Строкаи Страница.
- Номер секции Перечисляет каждую секцию в таблице или индексе. Этот столбец доступен только для чтения.
- Тип сжатия Выберите параметры сжатия для каждой секции. Недоступно, если выбран один и тот же тип сжатия для всех секций . Список параметров: Нет, Строкаи Страница.
- Граница Отображает границу секции. Этот столбец доступен только для чтения.
- Количество строк Количество строк в данной секции. Этот столбец доступен только для чтения.
- Текущий размер Текущий размер, занимаемый секцией в мегабайтах (МБ). Этот столбец доступен только для чтения.
- Запрошенное сжатое пространство После нажатия кнопки Вычислитьв этом столбце будет показан приблизительный размер каждой секции после сжатия на основе значения в столбце Тип сжатия. Этот столбец доступен только для чтения.
- Вычислить Нажмите эту кнопку, чтобы получить размер каждой секции после сжатия на основе значения в столбце Тип сжатия.

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

Вывести скрипт в файл
Создает скрипт в виде .sql файла. Введите имя и местоположение файла в поле Имя файла или нажмите кнопку Обзор, чтобы открыть диалоговое окно Расположение файла скрипта. В разделе Сохранить каквыберите Текст в Юникоде или Текст ANSI.
Вывести скрипт в буфер обмена
Сохранение скрипта в буфере обмена.
Вывести скрипт в новое окно запроса
Скрипт создается в новом окне редактора запросов. Это параметр выбирается по умолчанию.
При выборе Расписание щелкните Изменить расписание.

В диалоговом окне Создание расписания задания в поле Имя введите имя расписания задания.
В списке Тип расписания выберите тип расписания:
- Запускать автоматически при запуске агента SQL Server
- Запускать при бездействии процессоров
- Повторяющееся. Выберите этот параметр, если новая секционированная таблица регулярно обновляется с учетом новых данных.
- Однократно. Этот параметр выбран по умолчанию.
Установите или снимите флажок Включен , чтобы включить или отключить расписание.

При выборе Повторяющееся:

В разделе Частотав списке Выполняется укажите частоту выполнения:
- При выборе Ежедневнов поле Выполняется каждые укажите частоту повторного выполнения расписания задания в днях.
- При выборе Еженедельнов поле Выполняется каждые укажите частоту повторного выполнения расписания задания в неделях. Выберите день или дни недели, в которые выполняется расписание задания.
- При выборе Ежемесячнощелкните День или Определенный.
  - При выборе Деньвведите дату месяца, в которую должно выполняться расписание задания, и укажите частоту повторного выполнения расписания задания в месяцах. Например, если требуется, чтобы расписание задания выполнялось 15 числа каждого второго месяца, выберите День и введите в первом поле 15 и 2 — во втором поле. Наибольшее число, допустимое во втором поле, — «99».
  - При выборе Определенныйвыберите определенный день недели в месяце, в котором должно выполняться расписание задания, и укажите частоту повторного выполнения расписания задания в месяцах. Например, если требуется, чтобы расписание задания выполнялось в последний день недели каждого второго месяца, выберите День, затем последний в первом списке и рабочий день во втором списке, а потом введите «2» во втором поле. Еще можно выбрать первый, второй, третийили четвертый, а также конкретные дни недели (например, воскресенье или среду) в первых двух списках. Наибольшее число, допустимое в последнем поле, — «99».
В поле Сколько раз в деньукажите частоту повторного выполнения расписания задания в день запуска расписания задания:
- При выборе Выполнять раз вукажите определенное время дня для запуска расписания задания в поле Выполнять раз в . Укажите время дня: час, минуту и секунду.
- При выборе Выполняется каждыеукажите частоту выполнения задания в выбранный день в поле Частота. Например, если требуется, чтобы расписание задания выполнялось каждые 2 часа в день запуска расписания задания, выберите Выполняется кажд., введите «2» в первом поле, а затем выберите в списке часы. В этом списке также можно выбрать минуты и секунды. Наибольшее число, допустимое в первом поле, — «100». В поле Начинать в введите время для начала запуска расписания задания. В поле Заканчивать в введите время для завершения повторного выполнения расписания задания. Укажите время дня: час, минуту и секунду.
В разделе Длительность, в области Дата началавведите дату начала запуска расписания задания. Выберите Дата окончания или Без даты окончания , чтобы указать дату завершения выполнения расписания задания. При выборе Дата окончаниявведите дату завершения запуска расписания задания.

После завершения работы с этой страницей нажмите кнопку Далее.

Сведения Сведения о событии, состоянии и любых сообщениях, которые возвращены в результате действий мастера.
Действие Задает тип и имя каждого действия.
Состояние Указывает, вернуло ли действие мастера в целом значение Успешно или Ошибка.
Сообщение Любые сообщения об ошибках или предупреждения от процесса.
Отчет Создание отчета, содержащего результаты мастера создания секций. Доступные параметры: Просмотреть отчет, Сохранить отчет в файл, Копировать отчет в буфер обменаи Отправить отчет по электронной почте.
Просмотреть отчет Открытие диалогового окна Просмотр отчета , которое содержит текстовый отчет о работе мастера создания секций.
Сохранить отчет в файл Открытие диалогового окна Сохранить отчет как .
Копировать отчет в буфер обмена Копирование результатов отчета о работе мастера в буфер обмена.
Отправить отчет по электронной почте Копирование результатов отчета о состоянии мастера в сообщение электронной почты.

По завершении нажмите кнопку Закрыть.

Использование Transact-SQL

SQL Server

В SQL Server запустите sp_estimate_data_compression_savings , а затем включите сжатие для таблицы или индекса. См. следующие разделы.

Включение сжатия таблицы

В обозревателе объектов подключитесь к экземпляру ядра СУБД.
На стандартной панели выберите пункт Создать запрос.
Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить. В примере сначала выполняется хранимая процедура sp_estimate_data_compression_savings , чтобы вернуть предполагаемый размер объекта, если он использовал ROW параметр сжатия. Затем этот пример включает ROW сжатие для всех секций в указанной таблице.

USE AdventureWorks2022; GO EXEC sp_estimate_data_compression_savings 'Production', 'TransactionHistory', NULL, NULL, 'ROW'; ALTER TABLE Production.TransactionHistory REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW); GO

Включение сжатия индекса

В обозревателе объектов подключитесь к экземпляру ядра СУБД.
На стандартной панели выберите пункт Создать запрос.
Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить. В примере сначала формируется запрос к представлению каталога sys.indexes для получения имени и index_id для каждого индекса таблицы Production.TransactionHistory . Затем он выполняет хранимую процедуру sp_estimate_data_compression_savings , чтобы вернуть предполагаемый размер указанного идентификатора индекса, если бы он использовал PAGE параметр сжатия. Наконец, в примере выполняется перестроение индекса 2 ( IX_TransactionHistory_ProductID ), указывающее PAGE сжатие.

USE AdventureWorks2022; GO SELECT name, index_id FROM sys.indexes WHERE OBJECT_NAME (object_id) = N'TransactionHistory'; EXEC sp_estimate_data_compression_savings @schema_name = 'Production', @object_name = 'TransactionHistory', @index_id = 2, @partition_number = NULL, @data_compression = 'PAGE'; ALTER INDEX IX_TransactionHistory_ProductID ON Production.TransactionHistory REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE); GO

В базе данных SQL Azure

База данных SQL Azure не поддерживает хранимую процедуру sp_estimate_data_compression_savings . Следующие скрипты обеспечивают сжатие без оценки объема сжатия.

Включение сжатия таблицы

В обозревателе объектов подключитесь к экземпляру ядра СУБД.
На стандартной панели выберите пункт Создать запрос.
Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить. Этот пример включает ROW сжатие для всех секций в указанной таблице.

USE AdventureWorks2022; GO ALTER TABLE Production.TransactionHistory REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW); GO

Включение сжатия индекса

В обозревателе объектов подключитесь к экземпляру ядра СУБД.
На стандартной панели выберите пункт Создать запрос.
Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить. В примере сначала формируется запрос к представлению каталога sys.indexes для получения имени и index_id для каждого индекса таблицы Production.TransactionHistory . Наконец, в примере выполняется перестроение индекса 2 ( IX_TransactionHistory_ProductID ), указывающее PAGE сжатие.

USE AdventureWorks2022; GO SELECT name, index_id FROM sys.indexes WHERE OBJECT_NAME (object_id) = N'TransactionHistory'; ALTER INDEX IX_TransactionHistory_ProductID ON Production.TransactionHistory REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE); GO

См. также

Сжатие данных
Реализация сжатия строк
Реализация сжатия страниц
sp_estimate_data_compression_savings (Transact-SQL)
Создание сжатого индекса
Создание таблицы, используюющей сжатие строк

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

Сжатие данных

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

В плане сжатия данных в Oracle Database нет ничего нового, но в релизе 11g эта функциональность принимается на новый уровень в связи с появлением возможностей усовершенствованного (Advanced) сжатия и гибридного сжатия столбцов (Hybrid Columnar Compression)

Усовершенствованное сжатие (Advanced Compression)

Сжатие не является чем-то новым для Oracle; эта функциональность появилась в Oracle9i Database в формате COMPRESS BASIC (обычная компрессия).

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

Теперь в Oracle Database 11g (но только, если имеется лицензия на опцию Advanced Compression), можно сделать следующее:

create table my_compressed_table (col1 number(20),col2 varchar2(300), . ) compress for all operations

Фраза «compress for all operations» («сжатие для всех операций») включает сжатие при всех DML-операциях, как то: INSERT, UPDATE, etc. Сжатие выполняется при всех DML-действиях, а не только на прямом пути вставки записи, как это было в предыдущих версиях.

Это замедлит DML-операцию? Не обязательно. Это то место, где новая функция работает лучше всего. Сжатие не происходит, когда строка вставляется в таблицу. Напротив, строки вставляются стандартным способом в несжатом виде. Когда же вставлено (или обновлено) несжатым способом определенное число строк, взбрыкивает (kick) алгоритм сжатия и сжимает все несжатые строки в блоке. Другими словами, сжимается блок, а не строка. Порог, при котором совершается сжатие, определен во внутреннем коде RDBMS.

Механизм сжатия

Рассмотрим таблицу ACCOUNTS, записи которой показаны ниже:

Предположим, что в базе данных в каком-то блоке есть все показанные выше строки.

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

Отметим, что значения были вырезаны из строк и вставлены в специальную область наверху «Symbol Table» («Таблица символов»). Каждому значению в столбцах присваивается символ, который замещает фактические значения в строках. Поскольку такие символы занимают меньше места, чем фактические значения, размеры записей также значительно уменьшаются по сравнению с оригиналом. Чем больше повторяющихся данных имеется в строке, тем более эффективна таблица символов и, следовательно, блок.

Поскольку сжатие — инициируемое событие (но не при вставке строк), то во время обычного DML-процесса воздействие сжатия на производительность — нуль. Когда же сжатие инициировано, конечно, потребность в CPU становится высокой, но в остальное время — нуль, что делает сжатие приемлемой функциональностью для OLTP-приложений. Прямо-таки лакомство (sweetspot), предоставленное сжатием в Oracle Database 11g.

Кроме уменьшения занимаемого пространства, сжатые данные требуют меньше времени для прохождения по сети, используют меньше места при резервном копировании и делают возможной поддержку полных копий производственной базы данных при тестировании и в QA (quality assurance — обеспечение качества).

Гибридное столбцовое сжатие (Hybrid Columnar Compression)
Только Release 2 в Oracle Exadata

В Oracle Database 11g Release 2 (но только, если она предварительно установлена на Oracle Exadata v2) технология сжатия была доведена до уровня Hybrid Columnar Compression (HCC Гибридного Столбцового Сжатия).

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

Это именно те данные, для которых подходит HCC. Обычно сжатие работает, заменяя повторяющиеся значения символами меньшего объема, тем самым уменьшая потребление пространства. Например, предположим, что строки несжатых данных выглядят так (столбцы разделяются “|”):

Row1: Quite_a_large_value1|Quite_a_long_value1|Another_quite_long_value1 Row2: Quite_a_large_value2|Quite_a_long_value1|Another_quite_long_value1 Row3: Quite_a_large_value1|Quite_a_long_value2|Another_quite_long_value1 Row4: Quite_a_large_value1|Quite_a_long_value1|Another_quite_long_value2

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

Quite_a_large_value1 Quite_a_large_value2 Quite_a_long_value1 Quite_a_long_value2 Another_quite_long_value1 Another_quite_long_value2

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

Оригинальное значение	Заменяющие символы
Quite_a_large_value1	A1
Quite_a_large_value2	A2
Quite_a_long_value1	B1
Quite_a_long_value2	B2
Another_quite_long_value1	C1
Another_quite_long_value2	C2

Теперь записи выглядят так:

Row1: A1|B1|C1 Row1: A2|B1|C1 Row1: A1|B2|C1 Row1: A1|B1|C2

Общая длина: 32 байта, существенное сокращение по сравнению с прежними 264 байтами, приблизительно на 88%. (Конечно, процент сокращения зависит от данных, в особенности, от наличия различных значений, но принцип — тот же самый.) Отношение между уникальными кодами (A1, A2 и т.д.) и значениями, которые они представляют (“Quite_a_large_value1” и т.д.) сохранено в Symbol Directory (Директория Символов), а также в заголовке блока. Каждый заголовок блока хранит коды, используемые в этом блоке. Естественно, будут повторения, так как одни те же значения повторяются в блоках.

Если посмотреть на реальные данные, то можно заметить, что значения обычно чаще повторяются в столбцах, а не в строках. Например, в столбце FIRST_NAME присутствуют значения John, Jack и т.д., тогда как в другом столбце CITY_NAME находятся New York и Los Angeles. Естественно, что значение New York не ожидается в столбце first_name. Поскольку повторения значений более часто встречаются в столбцах, а не в строках, может быть всего лишь одна директория символов на столбец. И поскольку директории символов также занимают место, значительное сокращение их числа уменьшит общее требуемое пространство по сравнению с традиционным сжатием, где у каждого блока должна быть директория символов.

Hybrid Columnar Compression использует этот метод. Этот метод осуществляет существенное сжатие, но влияет на производительность DML-предложений. Поэтому его лучше всего использовать для табличных запросов, которые не являются DML.

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

create table trans_comp nologging compress for query low as select * from trans;

Фраза “compress” вызывает сжатие (компрессию) таблицы. Фраза “for query” указывает на применение механизма гибридного столбцового сжатия. Фраза “low” задает наиболее мягкое сжатие. При этом занимается больше места, но при сжатии и распаковке используется меньше CPU. Более агрессивное сжатие может быть задано при замене значения «low» на «high». Этот тип HCC известен как сжатие хранилищ данных ( warehouse compression), так как оно полезно в хранилищах данных, где хранится много данных, но они не часто запрашиваются.

Если доступ к таблице очень редкий, то её можно сжать ещё сильнее, используя фразу “for archive”:

create table trans_comp nologging compress for archive low as select * from trans;

Это ещё больше уменьшит использование пространства , но за счет CPU. Так же как в фразе “for query” (“для запроса”), здесь имеется два значения: high (высоко) и low (низко). Это известно как сжатие архива, где к данным нечасто получают доступ. Вот пример коэффициентоов сжатия на наборе презентативных данных. Конечно, коэффициенты будут значительно различаться в зависимости от данных..

Гибридное Столбцовое Сжатие (Hybrid Columnar Compression) позволяет сжимать таблицы, которые не слишком активны в плане DML-обращений к ним, но чьи данные не могут быть отброшены по юридическим или другим причинам. Теперь можно сохранять такие таблицы, используя гораздо меньше места для их хранения, и, возможно, также меньше используя CPU. Отметим еще раз, что эта функциональность доступна только на Oracle Exadata Storage Server v2. Для получения дополнительной информации обратитесь к этому отчету.

SQL Server 2008: обзор нововведений

Microsoft SQL Server – это проприетарная система управления базами данных,
обеспечивающая сетевой многопользовательский доступ,
использует расширенный язык запросов T-SQL.
Ведет свою историю с 1989 года, первоначальная версия создана Sybase.
В предыдущей 2005 версии была введена поддержка CLR, которая позволяла
писать процедуры с использованием языков, работающих на платформе .Net.

История версий:
• 1992 — SQL Server 4.2
• 1993 — SQL Server 4.21 под Windows NT
• 1995 — SQL Server 6.0, кодовое название SQL95
• 1996 — SQL Server 6.5, кодовое название Hydra
• 1999 — SQL Server 7.0, кодовое название Sphinx
• 1999 — SQL Server 7.0 OLAP, кодовое название Plato
• 2000 — SQL Server 2000 32-bit, кодовое название Shiloh (версия 8.0)
• 2003 — SQL Server 2000 64-bit, кодовое название Liberty
• 2005 — SQL Server 2005, кодовое название Yukon (версия 9.0)
• 2008 — SQL Server 2008, кодовое название Katmai (версия 10.0)

SQL Server используется на многих предприятиях, причем 2000 версию до сих пор используют многие компании,
а многие DBA говорят, что лучше 2000 пока еще Microsoft не смогла сделать,
особенно это относится к Management Studio.

SQL Server 2008 появился в августе 2008 года.

Более подробно о истории MSSQL можно прочитать в WikiPedia

1. Присвоение переменных в одну строку.

Теперь вместо:
DECLARE @myVar intSET @myVar = 5

Можно писать так:
DECLARE @myVar int = 5

2. Математический синтаксис

DECLARE @myVar int = 5
SET @myVar += 1

3. Компрессия.

a) Можно включить компрессию для таблиц/партиций таблиц, индексов.
b) Уровней компрессий — 2: 1) Строчное, 2) Страничное
c) В страничное сжатие включается строчное.
В страничном сжатии есть тип сжатия основанный на «column prefix» matching
d) Бекапы автоматически сжимаются.

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

Включить сжатие можно через Management Studio:
Вместо PAGE можно использовать ROW и выбирать партиции таблицы

И сгенерированный скрипт сжатия:

ALTER TABLE [dbo].[TestTable] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE)

CREATE NONCLUSTERED INDEX IX_INDEX ON TestTable (TestTableVarFieldOne)
WITH ( DATA_COMPRESSION = PAGE ) ;

Дополнительную информация
Минусы лицензии: Сжатие доступно только в выпусках SQL Server 2008 Enterprise и Developer.

4. Появились индексы с фильтрацией.

CREATE NONCLUSTERED INDEX IX_TestTable_OneON TestTable(TestTableVarFieldOne)
WHERE TestTableVarFieldOne = ‘SampleText’

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

Тут описаны примеры: [1][2]

Для чего это нужно:

Например мы знаем что по колонке очень часто идет какой-нибудь текстовый запрос из разряда = ‘SampleText’, ставим этот индекс, и наш план выполнения запроса становится вкусный и быстрый.

5. В SQL Server 2008 есть автоматический аудит.
Вызывается он из Management Studio из группы «Security»

Создадим правило аудита, кстати, параметр «Maximum», идущий после параметра «File Path», означает, сколько файлов будет создаваться в папке.

Теперь ставим аудит на сервер в целом или Базу данных.

Например, нам нужно поставить аудит на то, кто смотрел данные из таблицы «TestTable», обладающий правами «db_datareader»:

Для этого определим тип действия (их, кстати, порядка 30), класс объекта (БД/Схема/Объект), имя объекта и группу прав.

Это же можно сделать и в T-SQL:

CREATE DATABASE AUDIT SPECIFICATION [TestTableAuditOnView]
FOR SERVER AUDIT [TestAudit]
ADD (SELECT ON OBJECT::[dbo].[TestTable] BY [db_datareader])WITH (STATE = OFF)GO

Ну и потом не забудем сделать наш аудит «Enable»
Можно почитать про аудит больше тут или тут

6. Новый дебаггер.

Собственно дебаггер простой напоминает сильно дебаггер в Visual Studio,
ходит по брякам, и по Step Into/Step Out, можно ставить Watch, смотреть Call Stack,
автоматически заходить в триггеры.

В 2005 версии это можно было делать либо из Visual Studio,
либо, насколько помню, из Business Intelligence Studio и что не очень удобно,
и права для девелоперов придеться дать не больше, не меньше как «sysadmin»

7. Прозрачное шифрование БД.

Прозрачное шифрование оно же TDE, очень полезная фича, но скорее всего она полезна будет только либо на специфичных задачах, когда данные являются критическими с точки зрения безопасности, либо когда требуется решить проблему:
«Кто будет охранять самих сторожей» — когда требуется одним администраторам дать доступ к одному, вторым ко второму, а третьему к третьему и если кто-нибудь из этой тройки утащит журнал или бекап или mdf файлы, они будут бесполезны ввиду шифрования.

Почитать подробнее: [тут] или [тут]

Минусы лицензии: Доступно только в Developer/Enterprise версиях.

8. Замораживание плана запросов (Plan freezing).

Как вы знаете SQL Server иногда пытается менять план запроса, в зависимости от того, как поменялись данные (схема БД).

Необходимо это для двух вещей:
1) Для того, чтобы сервер не тратил время, пересчитывая план,
2) для того чтобы сервер не «разоптимизировал» план
Это довольно большой обьем работы, поэтому лучше посмотрите Virtual Lab.

Позднее если эта тема будет интересна ее можно будет раскрыть.

9. Resource Governor (Разделение и властвование над ресурсами сервера).

В Management Studio пункт меню находится в группе «Management», в окне Object Explorer.

Почитать можно [тут]
А посмотреть [тут]

10. Новые типы данных (DATE, TIME, DATETIMEOFFSET, DATETIME2, Hierarchyid, GEOMETRY, GEOGRAPHY, FILESTREAM)

a) DATE – храним только дату
b) TIME – храним только время
c) DATETIMEOFFSET – храним дату и время со смещениями «+» или «-»
d) DATETIME2 – храним дату и время от January 1, 0001 до December 31, 9999
e) HierarchyId – храним данные иерархий причем дерево иерархий будет довольно компактным.

Кстати хорошо описан данный тип в статье XaocCPS ( http://habrahabr.ru/blogs/sql/27774/ )

f) Geometry и Geography это специальные типы, которые содержат в себе векторные объекты:

Object	Descripton
Point	A location
MultiPoint	A series of points.
LineString	A series of zero or more points connected by lines.
MultiLineString	A set of linestrings
Polygon	A contiguous region described by a set of closed linestrings.
MultiPolygon	A set of polygons.
GeometryCollection	A collection of geometry types.

g) FileStream – храним данные в файловой системе
Почитать можно [тут]

Отличия в том, что расстояния в типе Geography выражены в виде градусах долготы и широты, а Geometry в специфичных Unit.

11. Table Value Parameters (можно передавать таблицы как параметр)

Для чего может быть нужно передавать таблицу из приложения в Базу Данных?

1) Для того чтобы уменьшить кол-во INSERT/UPDATE операций,
2) Для того чтобы некоторые части слоя бизнеслогики перенести на сервер

Плюсы:
1) Строгая типизация
2) Сортировка
3) Мы можем в этих таблицах использовать индексы (первичный ключ)
4) Удобство

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

Пример SQL кода:

Use testDatabase
GO
CREATE TYPE Customer AS TABLE (id int, CustomerName nvarchar(50), postcode nvarchar(50));
GO
CREATE TABLE Customers (id int, CustomerName nvarchar(50));
GO
CREATE TABLE CustomerPostCodes(id int, postcode nvarchar(50));
GO
/*
INSERT INTO Customers VALUES (1, ‘Bob’)
INSERT INTO CustomerPostCodes VALUES (1, ‘ASD’)
INSERT INTO Customers VALUES (2, ‘Jack’)
INSERT INTO CustomerPostCodes VALUES (2, ‘QWE’)
INSERT INTO Customers VALUES (3, ‘Gill’)
INSERT INTO CustomerPostCodes VALUES (3, ‘ZXC’)
GO
*/
CREATE Procedure AddCustomers (@customer Customer READONLY)
AS
INSERT INTO Customers
SELECT id, CustomerName FROM @customer

INSERT INTO CustomerPostCodes
SELECT id, postcode FROM @customer
GO

DECLARE @myNewCustomer Customer;
INSERT INTO @myNewCustomer VALUES (1, ‘Harry’, ‘NEW’)
EXEC AddCustomers @myNewCustomer
GO

SELECT * FROM Customers
SELECT * FROM CustomerPostCodes
GO

Drop table Customers;
go

Drop table CustomerPostCodes;
go

Drop procedure AddCustomers;
go

Drop type Customer;
go

Из C# соответственно передается параметром DataTable.
Посмотреть скринкаст

12. Вкусности новой Management Studio:

a) IntelliSense (дожили таки)

b) Удобная подсветка (тултипы):

К сожалению «+=» это только математический оператор ((

c) Она поддерживает Addin-ы.

d) Интегрированы новые фишечки сервера и удобства — типо дай мне «только 1000 строк»

e) Обновился Activity Monitor
Проще написать, что тут можно увидеть «картину в целом», хотя все же частные детали лучше смотреть SQL Profiler, он для этого более приспособлен, а счетчики эти частично заимствованы из Windows Server 2008/Vista «Мониторинг производительности».

Вообщем смотрим кто, зачем, куда, что и сколько это будет стоить нм ресурсов.

Довольно приятный «логгер последних ресурсоемких запросов»:

Особенно приятно просмотреть сразу план выполнения запроса:

Минусы: Нельзя убрать ненужные колонки или менять их местами…

f) Мульти-серверные запросы.

Можно выполнить запрос (ы) на группе серверов, для этого нужно открыть пункт меню (View -> Registered Servers -> New Query).

SQL Server 2008 по использованию памяти мне кажется более эффективный нежели 2005, Management Studio стала более удобной, новые типы данных могут дать больше удобства в разработке, также я вижу что многое добавлено, но около еще года на продакшн серверах не буду ставить в силу того что буду ждать многочисленных отзывов о продукте, особенно о отзывах тех кто будет использовать TDE, DataTable в виде параметра и стабильности.

Сильно не минусуйте, первый пост, картинки позже сделаю меньше, побежал на работу.

Как в sql сделать таблицу с компрессией

Включение сжатия таблицы или индекса

ограничения

Разрешения

Использование среды SQL Server Management Studio

Использование Transact-SQL

SQL Server

Включение сжатия таблицы

Включение сжатия индекса

В базе данных SQL Azure

Включение сжатия таблицы

Включение сжатия индекса

См. также

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

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

Как создать сжатую таблицу

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

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

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

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

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

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

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

Заключение

Сжатие данных

Усовершенствованное сжатие (Advanced Compression)

Механизм сжатия

Гибридное столбцовое сжатие (Hybrid Columnar Compression) Только Release 2 в Oracle Exadata

SQL Server 2008: обзор нововведений

Добавить комментарий Отменить ответ

Гибридное столбцовое сжатие (Hybrid Columnar Compression)
Только Release 2 в Oracle Exadata