Oracle сжать таблицу после удаления записей

Обновлено: 08.07.2024

В прошлый раз я упустил один момент, давайте еще не надолго вернемся к дефрагментации. Это достаточно обширная тема, но думаю последнее, на что стоит обратить внимание это размер блока Oracle. Он содержатся в файле init.ora в секции db_block_size и имеет, как правило, оптимальное значение выбранное по умолчанию. Но эффект от увеличения размера блок просто поражает! В большинстве случаев используют блоки двух размеров 2 и 4 Кбт. (Хотя я почти всегда ставлю 8 Кбт!). Переход на больший размер блока может повысить производительность на 50%! И достигается это без значительных затрат! Учтите, что менять секцию db_block_size просто так нельзя! Для увеличения размера блока БД лучше пересоздать весь экземпляр заново с новым значением! Повышение производительности связано со способом работы сервера Oracle с заголовком блока. Как следствие для данных используется больше места, что улучшает возможность обращения к одному и тому же блоку данных, от нескольких пользователей. Удвоение размера блока Oracle практически не влияет на его заголовок. Это значит, что в процентном отношении для заголовка расходуется меньше места! Но учтите, что, например, удвоение размера блока Oracle так же будет влиять на кэш буфера данных и может вызвать проблемы с управлением памятью на сервере!

Теперь давайте рассмотрим момент, когда табличное пространство необходимо модифицировать в ту или иную сторону. Например, рассмотрим случай когда табличное пространство и связанный с ним файл данных необходимо усечь в размерах! Сделать это можно, например, с помощью команды ALTER DATABASE. Но учтите, что нельзя изменить размер файла данных, если пространство, которое вы пытаетесь освободить, в настоящий момент занято объектами БД. Например, если объекты БД занимают объем 200 Мб, а размер файла данных 300 Мб, то можно отсечь только 100 Мб у файла данных! Сама команда будет выглядеть вот так:

При этом учтите, если табличное пространство сильно дефрагментировано, то Oracle может выдать ошибку при попытке усечь табличное пространство! Далее давайте посмотрим как можно производить сокращение таблиц и индексов в БД. Но, для начала проделаем следующее. Создадим таблицу SPEED в схеме MILLER:

Теперь запишем вот такой блок для того, чтобы наполнить таблицу данными! Для примера:

Время, которое потратил Oracle в моем случае составило 2,5 Сек. (Это оценивает PL/SQL Developer). Когда Oracle записывает данные в сегмент, обновляется так называемая - верхняя отметка (high - water mark - высшая точка) сегмента. Верхняя отметка сегмента - это наибольший номер блока сегмента, в котором вы когда-либо хранили данные. Если вы добавили скажем 5000 строк верхняя отметка будет увеличиваться! Дайте к таблице SPEED вот такой запрос:

Время на исполнение у меня было 0.016 сек. Хорошо. Запрос прошел все блоки таблицы до верхней отметки. А теперь удалим записи:

Время на удаление чуть больше, уже 0.235 сек! А теперь повторите прошлый запрос:

Снова 0.016 сек! Но почему? А в следствии того, что при удалении записей из таблицы ее high - water mark не снижается и запрос прошел все блоки снова! Вот как! Если не считать удаление таблицы и ее воссоздание, верхняя отметка сегмента переустанавливается только после команды TRUNCATE TABLE (к ней мы еще вернемся!) Давайте проделаем следующее. Снова наполним таблицу:

А теперь дадим команду нашего запроса:

Время снова примерно 0.017 сек. Хорошо, даем вот такую команду:

Затраченное время 0 сек! Указатель high - water mark был перемещен! Что и требовалось доказать! Здесь так же кроется некий подводный камень, при работе с таблицами БД и особенно большими таблицами! Знание этого нюанса думаю в дальнейшем поможет вам справляться с распределением табличного пространства под объекты БД. Найти верхнюю отметку для таблицы CUSTOMERS для схемы MILLER нашей учебной БД поможет такой сценарий (для того, чтобы все получилось необходимо зайти в экземпляр пользователем SYS или SYSTEM!):

Здесь используется пакет SYS.dbms_space и его метод unused_space! Получаем:

Здесь верхняя отметка таблицы (в байтах) представляет собой разницу между значениями TOTAL_BYTES и UNUSED_BYTES. Значение UNUSED_BLOCKS соответствует числу блоков выше высшей точки. TOTAL_BLOCKS это общее количество блоков связанное с данной таблицей! Улавливаете! Если нужно сжать таблицу и значение UNUSED_BLOCKS не равно нулю, с помощью команды ALTER TABLE можно забрать пространство выше верхней отметки. Чтобы освободить занимаемое таблицей пространство можно дать команду:

И действительно зачем ей лишние 8 блоков! У меня это получается (16 * 8192) - (8 * 8192) = 65536! Вот так лишнее долой! Кстати, если не указать конструкцию keep, то значение параметров сохранения minextents и initial таблицы останутся прежними. Если использовать keep, то можно освобождать свободное пространство из любого экстента! Даже из initial, если в других экстентах данных нет! Так, что пользуйтесь возможностью борьбы с неиспользуемым свободным местом табличных пространств! Но, осторожно! Удачи!

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

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

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

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

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

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

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

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

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

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

Атрибут COMPRESS также может быть задан на уровне табличного пространства, как в момент его создания (с помощью оператора CREATE TABLESPACE), так и в дальнейшем (с помощью оператора ALTER TABLESPACE). Атрибут COMPRESS наследуется аналогично параметрам хранения. При создании таблицы в табличном пространстве наследуется атрибут COMPRESS этого табличного пространства. Чтобы определить, задан ли для табличного пространства атрибут COMPRESS, выполните запрос к представлению DBA_TABLESPACES словаря данных и проверьте значение столбца DEF_TAB_COMPRESSION, как в следующем примере:

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

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

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

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

Непосредственная загрузка SQL*Loader
Последовательные вставки INSERT с подсказкой APPEND
Параллельный INSERT
CREATE TABLE . AS SELECT

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

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

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

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

Учтите, что при использовании параллельного INSERT надо сначала включить распараллеливание операторов DML в сеансе с помощью команды ALTER SESSION ENABLE PARALLEL DML.

Можно также использовать оператор CREATE TABLE . AS SELECT для создания сжатой таблицы и вставки в нее данных за один шаг. Вот пример:

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

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

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

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

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

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

Сжатие секционированной таблицы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, как показано ниже:

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

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

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

После Google не могу найти простой способ вернуть свободное место после удаления таблицы.

Я нашел много объяснений, рассказывающих, как файл данных становится фрагментированным, большой стек скучных запросов, которые вам нужно выполнить, чтобы переместить «пустое пространство» в конец файла данных (таблица за таблицей . даже когда вы есть 200 столов!?).

Затем вам нужно уменьшить размер файла данных, «угадав», насколько вы можете его уменьшить, или вы должны точно знать, каков ваш «размер блока» . И, наконец, вы не должны забывать «перестраивать индексы».

Существует ли простая процедура PL / SQL, которая, учитывая имя табличного пространства или имя файла данных, будет выполнять эту работу? Или любой подобный инструмент Oracle?

Краткий ответ - нет . К сожалению, способ сделать это в Oracle требует «большого количества скучных запросов». Статьи, на которые вы ссылаетесь, являются одними из лучших на эту тему. Файл данных действительно фрагментируется, поэтому даже если свободное пространство находится ниже самого высокого сегмента, Oracle не будет автоматически консолидировать его, когда RESIZE будет выполнено a .

Чтобы «дефрагментировать» табличное пространство, вам нужно переместить эти сегменты в начало файла данных, а не в конец. Для таблиц это автономный процесс, то есть таблица будет недоступна во время перемещения. Индексы можно перемещать либо в автономном режиме, либо с помощью Enterprise Edition их можно перемещать в режиме онлайн. Поскольку у вас есть окно отключения, я рекомендую вам выполнить следующие шаги.

A. Сжатие файлов данных со свободным пространством за отметкой максимальной воды. Это можно сделать следующим образом (запрос аналогичен процедуре Frosty Z):

Б. После того, как вы сузили положение выше верхней отметки, выясните, какие табличные пространства все еще выиграют от перемещения сегментов.

C. Для каждого из этих табличных пространств определите, какие сегменты необходимо переместить. (Замените USERS именем вашего табличного пространства или присоедините его к предыдущему запросу)

D. Переместите каждую таблицу и перестройте индексы и статистику.

E. повторите шаг A.

Я только что построил большинство этих запросов, так что вы захотите тщательно протестировать их перед использованием. Я полагаю, вы могли бы создать процедуру, которая использовалась бы EXECUTE IMMEDIATE для создания фактических операторов для динамического выполнения, но поскольку запросы будут получать ORA-08103: объект больше не существует, пока выполняется перемещение, я думаю, что лучше контролировать этот процесс вручную, даже если если это действительно означает немного больше времени / усилий.

ORACLE уменьшить размер табличного пространства (удалить файл данных, файл с данными или уменьшить размер файла данных)

Многие поисковые запросы в Интернете приводят к одновременному удалению файлов данных и табличных пространств. В ходе моего собственного исследования я выделил метод удаления одного файла данных в табличном пространстве, который используется только для справки. Лучше работать, когда в табличное пространство не записываются никакие данные.
**

Первый способ удаления файлов данных

**
Первая часть - создать табличное пространство с нулевым временем для хранения удаляемых объектов.

Вторая часть - это проверка того, какие объекты базы данных находятся в удаляемом файле данных, в основном таблицы.Индексы таблиц могут быть сначала удалены, а затем созданы заново.

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

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

Затем проверьте, есть ли в файле данных какие-либо объекты.

В настоящее время файл данных TRW02.dbf удален.

Четвертая часть, удалите файл данных

Вы видите, что файл данных был удален.

Пятая часть, переместите все объекты из временного табличного пространства в табличное пространство TRW.

Видно, что все объекты были сохранены в файл TRW01.bdf в табличном пространстве TRW.

Пятый и последний шаг - удалить созданное временное табличное пространство TRWN;

Табличное пространство TRWN удалено;

Второй способ уменьшить размер файла данных

Первый шаг - просмотреть большие бесплатные файлы данных.

Убедитесь, что размер файла на сервере действительно 51 МБ.

Видно, что использование файла данных TRW01.dbf очень мало;

Во второй части мы уменьшили TRW01.dbf с 50M до 20M,Уменьшенный размер должен быть больше размера данных объекта.

Размер файла уменьшен до 20 МБ.
Это конец области сокращения.

Читайте также: