Как очистить таблицу в oracle
Лучший способ удалить очень большой набор записей в Oracle
Я управляю приложением, которое имеет очень большой (почти 1 ТБ данных с более чем 500 миллионами строк в одной таблице) серверной части базы данных Oracle. База данных на самом деле ничего не делает (ни SProcs, ни триггеры, ни что-либо еще), это просто хранилище данных.
Каждый месяц мы обязаны удалять записи из двух основных таблиц. Критерии очистки различаются и представляют собой комбинацию возраста строки и пары полей состояния. Обычно мы чистим от 10 до 50 миллионов строк в месяц (добавляем около 3-5 миллионов строк в неделю за счет импорта).
В настоящее время мы должны сделать это в пакетах по 50000 строк (т.е. удалить 50000, comit, удалить 50000, зафиксировать, повторить). Попытка удалить весь пакет за один раз приводит к тому, что база данных перестает отвечать на запросы примерно на час (в зависимости от количества строк). Такое удаление строк в пакетах очень сложно для системы, и мы обычно должны делать это «как позволяет время» в течение недели; постоянное выполнение сценария может привести к снижению производительности, что неприемлемо для пользователя.
Я считаю, что этот вид пакетного удаления также снижает производительность индекса и оказывает другие воздействия, которые в конечном итоге приводят к снижению производительности базы данных. В одной таблице 34 индекса, и размер данных индекса фактически больше, чем сами данные.
Вот сценарий, который один из наших ИТ-специалистов использует для этой очистки:
Эта база данных должна быть на 99.99999%, и у нас есть только 2-дневный интервал обслуживания один раз в год.
Я ищу лучший способ удаления этих записей, но я еще не нашел ни одного. Какие-либо предложения?
Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite
Базы данных
DELETE ОПЕРАТОР
В этом учебном материале вы узнаете, как использовать Oracle оператор DELETE с синтаксисом, примерами и практическими упражнениями.
Описание
Oracle оператор DELETE используется для удаления одной или нескольких записей из таблицы в Oracle.
Синтаксис
Синтаксис оператора DELETE в Oracle/PLSQL:
Параметры или аргументы
Таблица из которой вы хотите удалить записи.
Необязательный. Условия, которые должны быть выполнены для удаляемых записей. Если не предусмотрено никаких условий, то все записи из таблицы будут удалены.
Примечание
Вам не нужно перечислять поля в Oracle DELETE, поскольку вы удалите всю строку из таблицы.
Пример использования с одним условием
Рассмотрим простой Oracle пример DELETE, где в запросе DELETE есть только одно условие.
Вы можете проверить количество строк, которые будут удалены. Вы можете определить количество строк, которые будут удалены, выполнив следующее Oracle предложение SELECT, перед выполнением удаления.
Пример использования двух условий
Рассмотрим пример, когда в DELETE просто два условия.
Вы можете проверить количество строк, которые будут удалены. Вы можете определить количество строк, которые будут удалены, выполнив следующее предложение SELECT, перед выполнением удаления.
Пример использования условия EXISTS
Вы можете, также выполнять более сложные операции удаления.
Вы можете удалять записи в одной таблице на основе значений в другой таблице. Так как, при удалении, вы не можете перечислить более одной таблицы в FROM, вы можете использовать Oracle условие EXISTS.
Если вы хотите определить количество строк, которые будут удалены, вы можете запустить, перед выполнением удаления, следующее Oracle предложение SELECT.
Как очистить таблицу в oracle
You cannot roll back a TRUNCATE TABLE statement, nor can you use a FLASHBACK TABLE statement to retrieve the contents of a table that has been truncated.
Use the TRUNCATE TABLE statement to remove all rows from a table. By default, Oracle Database also performs the following tasks:
Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter
Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process
Grant object privileges on the table
Create the indexes, integrity constraints, and triggers on the table
Specify the storage parameters of the table
Truncating has none of these effects.
Removing rows with the TRUNCATE TABLE statement can be faster than removing all rows with the DELETE statement, especially if the table has numerous triggers, indexes, and other dependencies.
DELETE and DROP TABLE for information on other ways of removing data from a table
TRUNCATE CLUSTER for information on truncating a cluster
To truncate a table, the table must be in your schema or you must have the DROP ANY TABLE system privilege.
To specify the CASCADE clause, all affected child tables must be in your schema or you must have the DROP ANY TABLE system privilege.
You can truncate index-organized tables and temporary tables. When you truncate a temporary table, only the rows created during the current session are removed.
Oracle Database changes the NEXT storage parameter of table to be the size of the last extent deleted from the segment in the process of truncation.
Oracle Database also automatically truncates and resets any existing UNUSABLE indicators for the following indexes on table : range and hash partitions of local indexes and subpartitions of local indexes.
For a domain index, this statement invokes the appropriate truncate routine to truncate the domain index data.
If a regular or index-organized table contains LOB columns, then all LOB data and LOB index segments are truncated.
If table is partitioned, then all partitions or subpartitions, as well as the LOB data and LOB index segments for each partition or subpartition, are truncated.
When you truncate a table, Oracle Database automatically removes all data in the table’s indexes and any materialized view direct-path INSERT information held in association with the table. This information is independent of any materialized view log. If this direct-path INSERT information is removed, then an incremental refresh of the materialized view may lose data.
All cursors are invalidated.
Restrictions on Truncating Tables
This statement is subject to the following restrictions:
You cannot roll back a TRUNCATE TABLE statement.
You cannot flash back to the state of the table before the truncate operation.
You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and re-create the table.
You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table. An exception is that you can truncate the table if the integrity constraint is self-referential.
You cannot truncate the parent table of a reference-partitioned table. You must first drop the reference-partitioned child table.
You cannot truncate a duplicated table.
MATERIALIZED VIEW LOG Clause
The MATERIALIZED VIEW LOG clause lets you specify whether a materialized view log defined on the table is to be preserved or purged when the table is truncated. This clause permits materialized view master tables to be reorganized through export or import without affecting the ability of primary key materialized views defined on the master to be fast refreshed. To support continued fast refresh of primary key materialized views, the materialized view log must record primary key information.
The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.
Specify PRESERVE if any materialized view log should be preserved when the master table is truncated. This is the default.
Specify PURGE if any materialized view log should be purged when the master table is truncated.
Oracle Database Administrator’s Guide for more information about materialized view logs and the TRUNCATE statement
The STORAGE clauses let you determine what happens to the space freed by the truncated rows. The DROP STORAGE clause, DROP ALL STORAGE clause, and REUSE STORAGE clause also apply to the space freed by the data deleted from associated indexes.
Specify DROP STORAGE to deallocate all space from the deleted rows from the table except the space allocated by the MINEXTENTS parameter of the table. This space can subsequently be used by other objects in the tablespace. Oracle Database also sets the NEXT storage parameter to the size of the last extent removed from the segment in the truncation process. This setting, which is the default, is useful for small and medium-sized objects. The extent management in locally managed tablespace is very fast in these cases, so there is no need to reserve space.
Specify DROP ALL STORAGE to deallocate all space from the deleted rows from the table, including the space allocated by the MINEXTENTS parameter. All segments for the table, as well as all segments for its dependent objects, will be deallocated.
Restrictions on DROP ALL STORAGE
Specify REUSE STORAGE to retain the space from the deleted rows allocated to the table. Storage values are not reset to the values when the table was created. This space can subsequently be used only by new data in the table resulting from insert or update operations. This clause leaves storage parameters at their current settings.
This setting is useful as an alternative to deleting all rows of a very large table—when the number of rows is very large, the table entails many thousands of extents, and when data is to be reinserted in the future.
This clause is not valid for temporary tables. A session becomes unbound from the temporary table when the table is truncated, so the storage is automatically dropped.
If you have specified more than one free list for the object you are truncating, then the REUSE STORAGE clause also removes any mapping of free lists to instances and resets the high-water mark to the beginning of the first extent.
Truncating a Table: Example
The following statement removes all rows from a hypothetical copy of the sample table hr.employees and returns the freed space to the tablespace containing employees :
The preceding statement also removes all data from all indexes on employees and returns the freed space to the tablespaces containing them.
Preserving Materialized View Logs After Truncate: Example
The following statements are examples of TRUNCATE statements that preserve materialized view logs:
Лучший способ удалить очень большой набор записей в Oracle
Я управляю приложением, которое имеет очень большое (почти 1 Тбайт данных с более чем 500 миллионами строк в одной таблице). База данных на самом деле ничего не делает (без SProcs, триггеров или чего-то еще), это просто хранилище данных.
Каждый месяц мы должны очищать записи из двух основных таблиц. Критерий для чистки варьируется и представляет собой комбинацию возраста строки и пары статусных полей. Обычно мы заканчиваем очистку от 10 до 50 миллионов строк в месяц (мы добавляем около 3-5 миллионов строк в неделю через импорт).
В настоящее время мы должны сделать это удаление партиями из примерно 50000 строк (т. е. удалить 50000, comit, delete 50000, commit, repeat). Попытка удалить всю партию за один раз делает базу данных неактивной в течение часа (в зависимости от количества строк). Удаление строк в партиях, как это, очень грубо в системе, и мы, как правило, должны делать это «как позволяет время» в течение недели; позволяя сценарию работать непрерывно, может привести к снижению производительности, которое неприемлемо для пользователя.
Я считаю, что такое удаление пакетов также ухудшает производительность индекса и имеет другие последствия, которые в конечном итоге приводят к ухудшению производительности базы данных. На одной таблице есть 34 индекса, а размер данных индекса на самом деле больше, чем сами данные.
Вот сценарий, который использует один из наших ИТ-специалистов для этой очистки:
Эта база данных должна быть 99.99999%, и у нас есть только 2-дневное окно обслуживания один раз в год.
Я ищу лучший метод для удаления этих записей, но я еще не нашел их. Любые предложения?
DROP TABLE
Use the DROP TABLE statement to move a table or object table to the recycle bin or to remove the table and all its data from the database entirely.
Unless you specify the PURGE clause, the DROP TABLE statement does not result in space being released back to the tablespace for use by other objects, and the space continues to count toward the user’s space quota.
For an external table, this statement removes only the table metadata in the database. It has no affect on the actual data, which resides outside of the database.
When you drop a table that is part of a cluster, the table is moved to the recycle bin. However, if you subsequently drop the cluster, the table is purged from the recycle bin and can no longer be recovered with a FLASHBACK TABLE operation.
Dropping a table invalidates dependent objects and removes object privileges on the table. If you want to re-create the table, then you must regrant object privileges on the table, re-create the indexes, integrity constraints, and triggers for the table, and respecify its storage parameters. Truncating has none of these effects. Therefore, removing rows with the TRUNCATE statement can be more efficient than dropping and re-creating a table.
CREATE TABLE and ALTER TABLE for information on creating and modifying tables
TRUNCATE and DELETE for information on removing data from a table
FLASHBACK TABLE for information on retrieving a dropped table from the recycle bin
The table must be in your own schema or you must have the DROP ANY TABLE system privilege.
Description of the illustration drop_table.gif
Specify the name of the table to be dropped. Oracle Database automatically performs the following operations:
All rows from the table are dropped.
All table indexes and domain indexes are dropped, as well as any triggers defined on the table, regardless of who created them or whose schema contains them. If table is partitioned, then any corresponding local index partitions are also dropped.
All the storage tables of nested tables and LOBs of table are dropped.
When you drop a range-, hash-, or list-partitioned table, then the database drops all the table partitions. If you drop a composite-partitioned table, then all the partitions and subpartitions are also dropped.
Oracle Database Administrator’s Guide for more information on dropping partitioned tables.
For an index-organized table, any mapping tables defined on the index-organized table are dropped.
For a domain index, the appropriate drop routines are invoked. Please refer to Oracle Data Cartridge Developer’s Guide for more information on these routines.
If any statistic types are associated with the table, then the database disassociates the statistics types with the FORCE clause and removes any user-defined statistics collected with the statistics type.
ASSOCIATE STATISTICS and DISASSOCIATE STATISTICS for more information on statistics type associations
If the table is not part of a cluster, then the database returns all data blocks allocated to the table and its indexes to the tablespaces containing the table and its indexes.
To drop a cluster and all its the tables, use the DROP CLUSTER statement with the INCLUDING TABLES clause to avoid dropping each table individually. See DROP CLUSTER.
If the table is a base table for a view, a container or master table of a materialized view, or if it is referenced in a stored procedure, function, or package, then the database invalidates these dependent objects but does not drop them. You cannot use these objects unless you re-create the table or drop and re-create the objects so that they no longer depend on the table.
If you choose to re-create the table, then it must contain all the columns selected by the subqueries originally used to define the materialized views and all the columns referenced in the stored procedures, functions, or packages. Any users previously granted object privileges on the views, stored procedures, functions, or packages need not be regranted these privileges.
If the table is a master table for a materialized view, then the materialized view can still be queried, but it cannot be refreshed unless the table is re-created so that it contains all the columns selected by the defining query of the materialized view.
If the table has a materialized view log, then the database drops this log and any other direct-path INSERT refresh information associated with the table.
Specify CASCADE CONSTRAINTS to drop all referential integrity constraints that refer to primary and unique keys in the dropped table. If you omit this clause, and such referential integrity constraints exist, then the database returns an error and does not drop the table.
You cannot roll back a DROP TABLE statement with the PURGE clause, nor can you recover the table if you have dropped it with the PURGE clause.
Using this clause is equivalent to first dropping the table and then purging it from the recycle bin. This clause lets you save one step in the process. It also provides enhanced security if you want to prevent sensitive material from appearing in the recycle bin.
Oracle Database Administrator’s Guide for information on the recycle bin and naming conventions for objects in the recycle bin