Есть вот такой запрос
DELETE FROM T1 WHERE T1.ID1 IN (SELECT T2.ID FROM T2 WHERE T2.ID1 = :param1)
Индексы по T1.ID1 и T2.ID1 имеются, T2.ID – первичный ключ.
Кажется (мне, по крайней мере) естественным, что нужно выбрать все записи из TD, используя индекс по T2.ID1, а удалять записи из T1, используя индекс по T1.ID1. Но Firebird поступил странно: по T2 он использует первичный ключ, а с T1 вообще работает по натуралу, что приводит к жутким тормозам. Как задать свой план для такого запроса, я не врубился, планы – это моё слабое место :) Можете подсказать, как здесь лучше поступить.
Если что, вопрос не критичный. Это запрос из хранимой процедуры, поэтому я тупо заменил один запрос циклом по T2, в котором для каждой записи выполняется DELETE из T1. Но просто интересно, как можно правильно сделать одним запросом.
Уважаемые авторы вопросов! Большая просьба сообщить о результатах решения проблемы на этой странице. Иначе, следящие за обсуждением, возможно имеющие аналогичные проблемы, не получают ясного представления об их решении. А авторы ответов не получают обратной связи. Что можно расценивать, как проявление неуважения к отвечающим от автора вопроса.
11-03-2024 02:30 | Комментарий к предыдущим ответам
В запрос "вариант удаления с db_key", закралась очепятка:
>>>что запрос SELECT к одной таблице никак не сможет получить RDB$DB_KEY записи другой таблицы
Посмотрите мой первый пост, запрос 2. По-моему, у вас именно такое соединение таблиц. Если так, то вот пример:
Вариант удаления с ПК:
SET TERM ^ ;
create or alter procedure TD_TEST_DELETE
as
declare variable WID type of DB$DM_IDENT;
begin
for select t1.TEST1_ID
from TD_TEST1 t1
join TD_TEST2 t2 on t1.TEST2_ID = t2.TEST2_ID
into :wId
do
delete from TD_TEST1
where TEST1_ID = :wId;
end^
SET TERM ; ^
Вариант удаления с db_key:
SET TERM ^ ;
create or alter procedure TD_TEST_DELETE2
as
declare variable wKey char(8) character set OCTETS;
begin
for select t1.rdb$db_key
from TD_TEST1 t1
join TD_TEST2 t2 on t1.TEST2_ID = t2.TEST2_ID
into :wKey
do
delete from TD_TEST1
where TEST1_ID = :wKey;
end^
SET TERM ; ^
>>>вопрос: имеет ли смысл в моём случае (две разных таблицы) использовать rdb$db_key, будет ли это иметь преимущество перед работой через primary key?
Слово "смысл" обычно связано с вопросом "зачем?".
В моём представлении, программу сначала разрабатывают, чтобы она "работала", как первоначально задумывалось. А затем дорабатывают, чтобы она "хорошо работала", в соответствии с новыми котелками, появившимися после начала разработки.
То, что "делается сначала", разрабатывается исключительно штатными средствами, по причине предсказуемости, как вашего опыта работы с таковыми, так и затраченного времени. А то что потом "дорабатывается", уже может использовать всё до чего можно дотянуться. Причём делается оно, либо, если вдруг у вас останется свободное время, либо уже в новом, дополнительном проекте.
Поскольку rdb$db_key является: а) недокументированной возможностью FB и б) специфическим, техническим средством оптимизации запросов, а не универсальным инструментом, то использовать его при начальной разработке программы не рекомендуется.
Поэтому, по поводу заданного вопроса, мне трудно что-либо предположить.
По моим прикидкам, на "стандартном офисном компьютере" 15-летней давности с FB3 x84: для T1 с 1 000 000 записями, T2 со 100 000 записями и обновлением (или удалением) половины записей T1 (входящими в T2), вариант с ПК, должен отрабатывать порядка десятка секунд. Можно сказать, более чем приемлемо.
Попробуйте проверить и провести замеры сами. У вас же теперь есть отдельный запрос выборки (в составе неявного курсора). Посмотрите на сколько различается время его работы в том и другом случае.
1. Одним простым запросом эффективно удалить записи не получится.
2. Я интуитивно принял правильное решение (на самом деле, оно очевидно): использовал for select do delete. Благо, у меня этот запрос в хранимой процедуре, так что execute block писать не пришлось.
Если можно, ещё один дополнительный вопрос: имеет ли смысл в моём случае (две разных таблицы) использовать rdb$db_key, будет ли это иметь преимущество перед работой через primary key? Скорее всего, нет. Но разу уж пошла такая пьянка... то лучше уточнить.
>>>Есть вот такой запрос ... . Но просто интересно, как можно правильно сделать одним запросом.
В БД Firebird, для операций update и delete данными из других таблиц используют неявные курсоры for select ... do update ... и for select ... do delete ... .
Такой синтаксис выглядит значительно нагляднее и выразительнее, чем перечисление несколько таблиц в разделе where опереторов update или delete (как это делает oracle или ms).
В запросе, такой неявный курсор, помещается в "исполняемый блок" execute block.
Пример можно посмотреть там:
"UPDATE данными из других таблиц" http://www.ibase.ru/updsame/
>>>Как задать свой план для такого запроса, я не врубился, планы – это моё слабое место
Не надо трогать "план запроса".
Полагайте, что "план запроса" создан для того, чтобы пояснить вам насколько доходчиво вы объяснили серверу, что вы от него хотите. "План запроса", это такая подсказка сервера БД вам, что именно вы делаете неверно.
>>>Но Firebird поступил странно ... что приводит к жутким тормозам
Тормоза вполне предсказуемы. Поясню на примере.
Есть две таблицы: td_Test1 и td_Test2. В каждой есть первичный ключ: td_Test1.Test1_id и td_Test2.Test2_id. Таблица td_Test1 связана с таблицей td_Test2 как главная-подчинённая по ПК (читай по внешнему ключу): td_Test1.Test2_id -> td_Test2.Test2_id.
—1. простая выборка:
select *
from td_Test1
План:
PLAN (TD_TEST1 NATURAL)
Пояснение:
- всё выбирается как есть;
- очевидно, что индекс на ПК не используется (незачем).
—2. простая выборка с соединением:
select t1.*
from td_Test1 t1
join td_Test2 t2 on t1.Test2_id = t2.Test2_id
План:
PLAN JOIN (T2 NATURAL, T1 INDEX (TD_TEST1_TEST2_IDX1))
Расширенный план:
Select Expression
-> Nested Loop Join (inner)
-> Table "TD_TEST2" as "T2" Full Scan
-> Filter
-> Table "TD_TEST1" as "T1" Access By ID
-> Bitmap
-> Index "TD_TEST1_TEST2_IDX1" Range Scan (full match)
Пояснение:
- соединение происходит по индексу в таблице T1;
- всё что осталось, выбирается как есть.
—3. простая выборка с соединением по in:
select t1.*
from td_Test1 t1
where t1.Test2_id in (
select t2.Test2_id
from td_Test2 t2
)
План:
PLAN (T2 NATURAL)
PLAN (T1 NATURAL)
Пояснение:
-получаем неотсортированный список из T1
-для каждого эл-та из T2 ищем соответствие в полученном списке
Предсказуемо получаем тормоза в БД.
Потому что:
1. "В списочном же контексте (чаще всего - в IN (...)), подзапрос всегда вызывается на каждую итерацию внешнего запроса." ( http://www.ibase.ru/dpopov/ )
2. Борри Х. "Firebird руководство разработчика баз данных", Глава 21: "С точки зрения производительности предикат IN не будет полезен, когда подзапрос возвращает достаточно большое количество значений".
Если вы заметили орфографическую ошибку на этой странице, просто выделите ошибку мышью и нажмите Ctrl+Enter. Функция может не работать в некоторых версиях броузеров.
