Добрый день.
Есть задача. Есть моя база данных (Oracle 11.2) и удаленная БД (Oracle 11.2), к которой я подключаюсь из своей базы через DBLink.
В своей БД я написал некую функцию, которая берет список ID-шников из своей локальной таблицы, и по этому списку ID-шников лезет в удаленную БД и извлекает данные из большой таблицы. Сначала сделал через коллекцию так (пишу схематически, чтобы не загружать тему реальным кодом):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
--создаю типы
CREATE TYPE T_OBJ AS OBJECT(id NUMBER, Name VARCHAR2(200));
CREATE TYPE T_OBJ_DATASET AS TABLE OF T_OBJ;
--тело функции
TYPE T_RESULT_REC IS RECORD(field1 NUMBER, field2 VARCHAR2(200));
TYPE T_RESULT_DATASET IS TABLE OF RESULT_REC;
C T_OBJ_DATASET;
RES_C T_RESULT_DATASET;
BEGIN
--заполняю коллекцию из локальной таблицы
SELECT T_OBJ(T.id, T.name)
BULK COLLECT INTO C
FROM my_local_table T
ORDER BY T.id;
--лезу в удаленную базу, извлекаю из таблицы записи с айдишниками из моего списка:
SELECT R.field1, R.field2
BULK COLLECT INTO RES_C
FROM remote_table@MY_DBLINK R
WHERE R.id IN (SELECT id FROM TABLE(C));
END;
И вот тут начинаются приколы. В моей локальной таблице всего 3 записи, в удаленной таблице записей много, но по полю ID есть индекс, он VALID-ный, я проверял. Казалось бы, оракл должен был вытянуть 3 ид-шника из коллекции, передать список из 3-х ид-шников в запрос к удаленной таблице, и поскольку то поле проиндексировано, то запрос должен был выполниться очень быстро.
Но в реале запрос выполняется больше минуты. Посмотреть план запроса к удаленной БД не могу, т.к. дблинк - это отдельная транзакция и в плане запроса я увижу только REMOTE, а к удаленной базе у меня нет админского доступа.
Когда я делаю вот так:
1.
2.
3.
4.
SELECT R.field1, R.field2
BULK COLLECT INTO RES_C
FROM remote_table@MY_DBLINK R
WHERE R.id IN (1,2,3)
т.е. пишу ид-шники напрямую, запрос выполняется мгновенно.
Сделал временно так:
1.
2.
3.
4.
5.
6.
7.
8.
id_list_str VARCHAR2(4000);
--заполняю переменную id_list_str из коллекции;
EXECUTE IMMEDIATE 'SELECT R.field1, R.field2
FROM remote_table@MY_DBLINK R
WHERE R.id IN ('||id_list_str||')'
BULK COLLECT INTO RES_C;
Казалось бы все хорошо, но на удаленной базе в кеше запросов будет куча моих однотипных запросов с разными вариациями IN(...), что есть очень плохо. Пытался сделать через параметризированный запрос, определив мой id_list_str как параметр:
1.
2.
3.
EXECUTE IMMEDIATE 'SELECT... WHERE R.ID IN(:Param)'
BULK COLLECT INTO RES_C
USING IN id_list_str;
- не получилось. Видимо, оракл не может объявить параметр типа список, только конкретные значения, а у меня постоянно меняется их количество.
У меня остался еще один резервный вариант: в цикле по коллекции выполнять селект к удаленной БД, извлекая по одной записи через параметр, но это решение не очень эффективное, т.к. постоянно будет идти переключение контекста между PL/SQL и SQL, что тоже не очень хорошо. Хотелось бы все-таки обойтись одним запросом к удаленной базе с передачей списка ид-шников, но чтобы при этом не забить library cache этой удаленной базы.
Заранее спасибо за помощь.
