Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Удалить записи с сохранением партиций  [new]
Landgraf
Member

Откуда:
Сообщений: 274
Ребята всем привет!

Нужен ваш совет.
Есть таблица размером в 800 Гб. Она партицирована по полю dDate (тип дата). Нужно удалить все записи у которых dDate < 01.01.17.

Учитывая размер таблицы, оператор delete будет работать очень долго.
Думал над таким вариантом:

Create table MyTable_tmp as
select * from MyTable where dDate >= 01.01.17;

Truncate table MyTable;

Insert into mytable
Select * from mytable_p


Но при таком варианте пропадает партицирование.
Как бы сделать так, чтобы партицирование не слетели по оставшимся записям?
7 фев 19, 20:07    [21803836]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
-2-
Member

Откуда:
Сообщений: 14350
Landgraf
Она партицирована по полю dDate (тип дата).
С какой целью партиционировали таблицу?
7 фев 19, 20:10    [21803838]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
Landgraf
Member

Откуда:
Сообщений: 274
-2-
Landgraf
Она партицирована по полю dDate (тип дата).
С какой целью партиционировали таблицу?


На сколько мне известно, на основании этого поля строятся отчёты. Поле хранит дату загрузки данных. Т.е. за каждый день присутствует N записей.
Таблица была сделана до меня, поэтому почему ее партиционировали.... Для скорости наверное :)
7 фев 19, 20:17    [21803842]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
jan2ary
Member

Откуда: Киев
Сообщений: 1715
Landgraf,

Транкейтить партиции?
7 фев 19, 21:23    [21803881]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17095
Помолясь и запасясь бэкапом...

+
create or replace function EVALUATE( s in varchar ) return date as
  res date;
begin
  execute immediate 'begin :x := '||s||'; end;' using out res;
  return res;
end;
/

Function created

create table dropme_t(id, val, dDate)
partition by range(dDate)
interval (interval '11' day)
(partition p1 values less than (date'2000-01-01') segment creation deferred)
as select rownum, 'val'||rownum, date'2016-12-01'+rownum from dual connect by level < 200
;

Table created

select count(*) "ВСЕГО"
     , count(case when ddate <  date'2017-01-01' then 1 end) "Удалить"
     , count(case when ddate >= date'2017-01-01' then 1 end) "Сохранить"
from dropme_t;

     ВСЕГО    Удалить  Сохранить
---------- ---------- ----------
       199         30        169

-- Пограничный раздел
select * from dropme_t partition for (date'2017-01-01');

        ID VAL                                         DDATE
---------- ------------------------------------------- -----------
        25 val25                                       26.12.2016
        26 val26                                       27.12.2016
        27 val27                                       28.12.2016
        28 val28                                       29.12.2016
        29 val29                                       30.12.2016
        30 val30                                       31.12.2016
        31 val31                                       01.01.2017
        32 val32                                       02.01.2017
        33 val33                                       03.01.2017
        34 val34                                       04.01.2017
        35 val35                                       05.01.2017
11 rows selected

-- Поехали...
declare
  l_owner varchar2(100) := USER;
  l_name varchar2(100) := 'DROPME_T';
  l_keep_after date := date'2017-01-01';
  l_partkeyexpression varchar2(2000) := to_char(l_keep_after,q'{"TO_DATE('"yyyy-mm-dd hh24:mi:ss"','yyyy-mm-dd hh24:mi:ss')"}');
  l_meta xmltype;
begin
  l_meta := xmltype(dbms_metadata.get_xml('TABLE',l_name,l_owner));
  for i in (
    select interval_str
    from xmltable('/ROWSET/ROW/TABLE_T/PART_OBJ/PARTOBJ'
           passing l_meta
           columns interval_str path 'INTERVAL_STR'
         )
  )loop
    if i.interval_str is not null then
      execute immediate 'alter table '||l_owner||'.'||l_name||' set interval()';
      execute immediate 'alter table '||l_owner||'.'||l_name||' set interval('||i.interval_str||')';
    end if;
  end loop;
  for i in (
    select 'alter table DROPME_T drop partition '||part_name||' update indexes' stmnt
      from xmltable('/ROWSET/ROW/TABLE_T/PART_OBJ/PART_LIST/PART_LIST_ITEM'
      passing l_meta
      columns part_name path 'SCHEMA_OBJ/SUBNAME'
            , HIBOUNDVAL path 'HIBOUNDVAL'
      ) x
      where evaluate(hiboundval) <= l_keep_after
    ) loop
      execute immediate ''||i.stmnt;
    end loop;
    execute immediate 'alter table '||l_owner||'.'||l_name||' split partition for('||l_partkeyexpression||') at ('||l_partkeyexpression||') update indexes';
    execute immediate 'alter table '||l_owner||'.'||l_name||q'{ drop partition for(date'1900-01-01') update indexes}';
end;
/

PL/SQL procedure successfully completed

-- Итог: 
select count(*) "ВСЕГО"
     , count(case when ddate <  date'2017-01-01' then 1 end) "Удалить"
     , count(case when ddate >= date'2017-01-01' then 1 end) "Сохранить"
from dropme_t;

     ВСЕГО    Удалить  Сохранить
---------- ---------- ----------
       169          0        169

select * from dropme_t partition for (date'2017-01-01');
        ID VAL                                         DDATE
---------- ------------------------------------------- -----------
        31 val31                                       01.01.2017
        32 val32                                       02.01.2017
        33 val33                                       03.01.2017
        34 val34                                       04.01.2017
        35 val35                                       05.01.2017

SQL> 
8 фев 19, 00:32    [21803935]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
Landgraf
Member

Откуда:
Сообщений: 274
Ребята всем спасибо за ответы.
В итоге сделал так: Отобрал все необходимые партиций, сделал altet table truncate partition, а потом alter table drop partition.

После того, как удалил данные, теперь нужно уменьшить таблицу, скорректировать уровень HWM.
Я так понимаю можно сделать либо:

alter table shrink space

Либо
alter table move


Во втором варианте нужно будет ещё индексы перестроить и пересобрать статистику.

Какой вариант предпочтительней? Таблица весит около 400Гб, партицирована.

В оракловой доке написано, что shrink ужимает неиспользуемое пространство с низу и с верху HWM.
11 фев 19, 16:33    [21806622]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 28679
Landgraf
нужно уменьшить таблицу, скорректировать уровень HWM
HWM - это свойство сегмента, а не объекта.
11 фев 19, 16:48    [21806639]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
Landgraf
Member

Откуда:
Сообщений: 274
Elic
Landgraf
нужно уменьшить таблицу, скорректировать уровень HWM
HWM - это свойство сегмента, а не объекта.


Да, есть такое.
11 фев 19, 16:53    [21806648]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9340
andrey_anonymous,

drop partition update indexes не уменьшит число листьев индекса да и увеличит время выборки по глобальным индексам. UPDATE != REBUILD.

SY.
11 фев 19, 17:15    [21806677]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
кит северных морей
Member

Откуда: Красноярск
Сообщений: 533
Landgraf
Ребята всем спасибо за ответы.
В итоге сделал так: Отобрал все необходимые партиций, сделал altet table truncate partition, а потом alter table drop partition.

После того, как удалил данные, теперь нужно уменьшить таблицу, скорректировать уровень HWM.
Я так понимаю можно сделать либо:

alter table shrink space

Либо
alter table move


Во втором варианте нужно будет ещё индексы перестроить и пересобрать статистику.

Какой вариант предпочтительней? Таблица весит около 400Гб, партицирована.

В оракловой доке написано, что shrink ужимает неиспользуемое пространство с низу и с верху HWM.
пересоберите global statistics и перестройте global indexes, если есть. больше ничего делать не нужно. truncate partition был лишним.
11 фев 19, 17:38    [21806711]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17095
SY
drop partition update indexes не уменьшит число листьев индекса

Не уменьшит - в сравнении с ЧЕМ?
11 фев 19, 18:14    [21806764]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9340
andrey_anonymous
Не уменьшит - в сравнении с ЧЕМ?


C rebuild.

SY.
11 фев 19, 18:16    [21806768]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17095
SY
C rebuild.

В сравнении с rebuild глобального индекса по крупной таблице некоторое падение производительности и последующая подчистка orphanned ночным джобиком - сущая ерунда с точки зрения доступности системы.
11 фев 19, 19:05    [21806812]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9340
andrey_anonymous
последующая подчистка orphanned ночным джобиком


В смысле ночным index rebuild? Хорошо когда не 24x7 - у меня удаление soft deleted index keys получаетя только когда release. Проверить насколько дополнительный предикат проверки на orphaned влияет на производительность все руки не доходят.

SY.
11 фев 19, 20:08    [21806836]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17095
SY
andrey_anonymous
последующая подчистка orphanned ночным джобиком

В смысле ночным index rebuild?

DBMS_PART.CLEANUP_GIDX
12 фев 19, 14:35    [21807430]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
Fogel
Member

Откуда:
Сообщений: 334
Коллеги, вы сильно усложняете всё.

Landgraf
Ребята всем привет!

Нужен ваш совет.
Есть таблица размером в 800 Гб. Она партицирована по полю dDate (тип дата). Нужно удалить все записи у которых dDate < 01.01.17.

Учитывая размер таблицы, оператор delete будет работать очень долго.
Думал над таким вариантом:

Create table MyTable_tmp as
select * from MyTable where dDate >= 01.01.17;

Truncate table MyTable;

Insert into mytable
Select * from mytable_p


Но при таком варианте пропадает партицирование.
Как бы сделать так, чтобы партицирование не слетели по оставшимся записям?


Будьте проще.

1. смОтрите скрипт создания вашей оригинальной таблицы (со всеми индексами, констрэйнтами и т.д.).
Копируете его целиком. (заменяете названия индексов и констрэйнтов, дописывая 1 в конце)
2. Вместо
create table MyTable
(col1,
col2,
...
colN)
tablespace <какое-то tb>
PARTITION BY RANGE (dDate)
...
и там потом индексы и прочие констрэйнты с комментариями

--
пишете
create table MyTable_NEW -- выкидываете весь блок столбцов здесь
tablespace <какое-то tb>
    PARTITION BY RANGE (dDate)
INTERVAL(NUMTOYMINTERVAL(1, 'DAY'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-2017', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('01-02-2017', 'DD-MM-YYYY'))
)
ENABLE ROW MOVEMENT
as select 
перечисление всех колонок, а не звёздочка
from MyTable
where dDate >= date '2017-01-01';
...
и там потом индексы и прочие констрэйнты с комментариями

3. собираете статистику
begin
  dbms_stats.gather_table_stats(ownname     => <схема где таблица>,
                                tabname     => 'MYTABLE_NEW',
                                granularity => 'ALL',
                                force       => TRUE);
end;

4. Переименовываете таблички
alter table MYTABLE rename to MYTABLE_OLD;
alter table MYTABLE_NEW rename to MYTABLE;

4.1 можете сверить данные в табличках всякими интерсектами и минусами для спокойствия души.

5.Дропаете старую таблицу с высвобождением места
drop table MYTABLE_OLD purge;

6. переименовываете все те индексы и констрэйнты, которым добавили 1 в пункте 1, обратно в оригинальные названия.
ALTER INDEX index1 RENAME TO oldnameindex; 

как-то так.
без лишней головной боли, с сохранением партиций и прочей структуры таблицы, корректным высвобождением места (без "хвостов" от шринкования ) и т. д., и т. п. ...
13 фев 19, 01:01    [21807931]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
кит северных морей
Member

Откуда: Красноярск
Сообщений: 533
Fogel
Будьте проще.

как говорила моя не по годам мудрая соседка по парте классе в шестом, "просто даже мухи не еб-ся".

2e12
13 фев 19, 06:56    [21807968]     Ответить | Цитировать Сообщить модератору
 Re: Удалить записи с сохранением партиций  [new]
flexgen
Member

Откуда: Город на песке
Сообщений: 676
Fogel
Коллеги, вы сильно усложняете всё...


Ты не упомянул о следующих шагах:
1. Не забыть дать все необходимые права на созданные заново таблицы поскольку при переименовании объекта теряются и права на него.
2. Проверить не стали ли объекты, каким-либо образом использующие пересоздаваемые таблицы, инвалидными и, соответственно, перекомпилировать эти объекты.
3. Не забыть про foreign key constraints в других таблицах, завязанные на пересоздаваемые таблицы.
4. И главное - без downtime, пусть даже минимального, все равно не обойтись.
13 фев 19, 21:50    [21808962]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить