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

Откуда: Kraków
Сообщений: 103
Всем привет, есть запрос:

DELETE FROM runiqkeys r1
  WHERE EXISTS (SELECT * FROM rdeleted_mesg_id_temp r2
					WHERE r1.tpid=r2.tpid AND (
						  (r1.tpid IN (2,22)                and r1.MESG_OPTIONALUSERKEY=r2.mesg_hash)
							or (r1.tpid NOT IN (2,22)   and r1.MESG_OPTIONALUSERKEY=r2.MESG_OPTIONALUSERKEY)));

runiqkeys содержит 5 миллионов записей.
rdeleted_mesg_id_temp содержит 10 тысяч.


Выполняется данное удаление около 12 часов, никак не могу разобраться почему, подскажите, пожалуйста, в чем может быть дело?
30 ноя 11, 11:18    [11681906]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6696
Владимир1984,

план покажи
30 ноя 11, 11:23    [11681952]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
Добрый Э - Эх
Guest
Ну, для начала посмотреть бы план запроса.
А вообще, информации недостаточно...
Индексы по таблице есть? Триггеры на удаление висят? А может там констрейнты внешние на таблицу ссылаются и при этом у них установлена флаг ON DELETE CASCADE?
30 ноя 11, 11:23    [11681954]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
Добрый Э - Эх
Guest
Ну и какой процент строк попадает под удаление...
30 ноя 11, 11:24    [11681958]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5928
Попробуй удалять записи оператором MERGE.
30 ноя 11, 11:30    [11682004]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
Владимир1984
Member

Откуда: Kraków
Сообщений: 103
Добрый Э - Эх
Ну, для начала посмотреть бы план запроса.
А вообще, информации недостаточно...
Индексы по таблице есть? Триггеры на удаление висят? А может там констрейнты внешние на таблицу ссылаются и при этом у них установлена флаг ON DELETE CASCADE?


Триггеров нет.
DDL таблиц:
CREATE TABLE rdeleted_mesg_id_temp
    (mesg_id                        NUMBER(10,0),
     mesg_optionaluserkey           VARCHAR2(40 BYTE),
     mesg_hash                      VARCHAR2(40 BYTE),
     tpid                           NUMBER(10,0) NOT NULL);
/
-- Indexes for RDELETED_MESG_ID_TEMP

CREATE UNIQUE INDEX pk1_rdeleted_mesg_id_temp ON rdeleted_mesg_id_temp
  (mesg_id                         ASC)
/
CREATE INDEX pk2_rdeleted_mesg_id_temp ON rdeleted_mesg_id_temp
  (tpid                            ASC,
   mesg_hash                       ASC)
/
CREATE INDEX pk3_rdeleted_mesg_id_temp ON rdeleted_mesg_id_temp
  (tpid                            ASC,
   mesg_optionaluserkey            ASC)
/
ALTER TABLE rdeleted_mesg_id_temp
ADD CHECK ("MESG_ID" IS NOT NULL)
/
ALTER TABLE rdeleted_mesg_id_temp
ADD CHECK ("MESG_ID" IS NOT NULL)
/
COMMIT;
/
CREATE TABLE runiqkeys
    (tpid                           NUMBER(10,0) NOT NULL,
    mesg_optionaluserkey           VARCHAR2(40 CHAR) NOT NULL,
    mesg_original_id               NUMBER(10,0) DEFAULT NULL)
/
ALTER TABLE runiqkeys
ADD CONSTRAINT pk_runiqkeys PRIMARY KEY (tpid, mesg_optionaluserkey)
USING INDEX
/

Обычно 10к строк из runiqkeys удаляются.

План запроса прилагаю в файл:

К сообщению приложен файл. Размер - 50Kb
30 ноя 11, 12:01    [11682269]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
Wool
Guest
1. метод тыка :)

т.к. R1.TPID = R2.TPID

значит можно поменять R1.TPID IN (2, 22) на R2.TPID IN (2, 22) и/или
R1.TPID NOT IN (2, 22) на R2.TPID NOT IN (2, 22)

2. попробовать сделать 2 DELETE, избавившись от OR и посмотреть какой из двух дольше работает (скорее всего с not in)

3. попробовать сделать не через EXISTS, а через IN (
SELECT PK(R1)
FROM RDELETED_MESG_ID_TEMP R2, RUNIQKEYS R1
....
)
и оттюнить этот select. также можно разбить на 2
30 ноя 11, 12:36    [11682555]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6696
Владимир1984,

статистику актуализируй
30 ноя 11, 12:52    [11682706]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
kinky cat
Member

Откуда: с дивана23
Сообщений: 1238
вроде тут получится
delete from ( key preserved вью )
либо merge
ну и индексов возможно каких то не хвататет
30 ноя 11, 12:57    [11682757]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
Интересно, а это не эквивалентно
DELETE FROM runiqkeys
  WHERE (tpid,MESG_OPTIONALUSERKEY) in
   (SELECT tpid,decode(tpid,2,mesg_hash,22,mesg_hash,MESG_OPTIONALUSERKEY)
      FROM rdeleted_mesg_id_temp);
30 ноя 11, 13:06    [11682829]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
Vicont_rtf
Member

Откуда:
Сообщений: 64
Попробуй так

DELETE r1 FROM runiqkeys r1, rdeleted_mesg_id_temp r2
WHERE r1.tpid=r2.tpid AND (
(r1.tpid IN (2,22) and r1.MESG_OPTIONALUSERKEY=r2.mesg_hash)
or (r1.tpid NOT IN (2,22) and r1.MESG_OPTIONALUSERKEY=r2.MESG_OPTIONALUSERKEY));
30 ноя 11, 14:13    [11683506]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
Владимир1984
Member

Откуда: Kraków
Сообщений: 103
Vicont_rtf
Попробуй так

DELETE r1 FROM runiqkeys r1, rdeleted_mesg_id_temp r2
WHERE r1.tpid=r2.tpid AND (
(r1.tpid IN (2,22) and r1.MESG_OPTIONALUSERKEY=r2.mesg_hash)
or (r1.tpid NOT IN (2,22) and r1.MESG_OPTIONALUSERKEY=r2.MESG_OPTIONALUSERKEY));

такой синтаксис в Oracle не работает.

Короче, тот план который я выложил был сделан не на боевой системе, а вот теперь выкладываю с боевой системы:
Запрос (Исходный)
DELETE FROM runiqkeys r1
  WHERE EXISTS (SELECT * FROM rdeleted_mesg_id_temp r2
					WHERE r1.tpid=r2.tpid AND (
						  (r1.tpid IN (2,22)            and r1.MESG_OPTIONALUSERKEY=r2.mesg_hash)
							or (r1.tpid NOT IN (2,22)   and r1.MESG_OPTIONALUSERKEY=r2.MESG_OPTIONALUSERKEY)));
План запроса:

К сообщению приложен файл. Размер - 51Kb
30 ноя 11, 17:42    [11685347]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
Владимир1984
Member

Откуда: Kraków
Сообщений: 103
Запросы (разделены на 3 делита):
DELETE FROM runiqkeys r1
  WHERE EXISTS (SELECT * FROM rdeleted_mesg_id_temp r2
					WHERE r1.tpid=r2.tpid AND
						  r1.tpid=2       AND
						  r1.MESG_OPTIONALUSERKEY=r2.mesg_hash);

DELETE FROM runiqkeys r1
  WHERE EXISTS (SELECT * FROM rdeleted_mesg_id_temp r2
					WHERE r1.tpid=r2.tpid AND
						  r1.tpid=22      AND
						  r1.MESG_OPTIONALUSERKEY=r2.mesg_hash);

DELETE FROM runiqkeys r1
  WHERE EXISTS (SELECT * FROM rdeleted_mesg_id_temp r2
					WHERE r1.tpid=r2.tpid AND
						  r1.tpid<>2      AND
						  r1.tpid<>22     AND
						  r1.MESG_OPTIONALUSERKEY=r2.MESG_OPTIONALUSERKEY);
Планы запросов:

К сообщению приложен файл. Размер - 139Kb
30 ноя 11, 17:43    [11685355]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Владимир1984
..
Выполняется данное удаление около 12 часов, никак не могу разобраться почему, подскажите, пожалуйста, в чем может быть дело?

1) блокировки?
2) dml на_селекте начинайте отлаживать с селекта
30 ноя 11, 18:04    [11685497]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
Владимир1984
Member

Откуда: Kraków
Сообщений: 103
в исходном запросе Cost=5000010. Это нормально?
30 ноя 11, 18:47    [11685654]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
Владимир1984
Member

Откуда: Kraków
Сообщений: 103
удивительно, разделение на три делита ускорило процесс удаления в 100 раз, проблема, по ходу, решена. Вот только почему так...
1 дек 11, 10:17    [11687642]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
Владимир1984,

планы запросов. В трех запросах он понимал, что удалить меньшее количество строк выгоднее.
1 дек 11, 10:18    [11687650]     Ответить | Цитировать Сообщить модератору
 Re: Почему долго выполняется DELETE WHERE EXISTS или как оптимизировать запрос?  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Быстрее потому что раньше был неэффективный FILTER из-за OR.
1 дек 11, 10:28    [11687705]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить