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

Откуда:
Сообщений: 4
Добрый день.
Есть задача. Есть моя база данных (Oracle 11.2) и удаленная БД (Oracle 11.2), к которой я подключаюсь из своей базы через DBLink.
В своей БД я написал некую функцию, которая берет список ID-шников из своей локальной таблицы, и по этому списку ID-шников лезет в удаленную БД и извлекает данные из большой таблицы. Сначала сделал через коллекцию так (пишу схематически, чтобы не загружать тему реальным кодом):

--создаю типы
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, а к удаленной базе у меня нет админского доступа.
Когда я делаю вот так:
  SELECT R.field1, R.field2
  BULK COLLECT INTO RES_C
  FROM remote_table@MY_DBLINK R
  WHERE R.id IN (1,2,3)

т.е. пишу ид-шники напрямую, запрос выполняется мгновенно.
Сделал временно так:
  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 как параметр:
EXECUTE IMMEDIATE 'SELECT... WHERE R.ID IN(:Param)'
BULK COLLECT INTO RES_C
USING IN id_list_str;

- не получилось. Видимо, оракл не может объявить параметр типа список, только конкретные значения, а у меня постоянно меняется их количество.
У меня остался еще один резервный вариант: в цикле по коллекции выполнять селект к удаленной БД, извлекая по одной записи через параметр, но это решение не очень эффективное, т.к. постоянно будет идти переключение контекста между PL/SQL и SQL, что тоже не очень хорошо. Хотелось бы все-таки обойтись одним запросом к удаленной базе с передачей списка ид-шников, но чтобы при этом не забить library cache этой удаленной базы.
Заранее спасибо за помощь.
30 сен 17, 10:17    [20833108]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать параметр в секции IN() запроса  [new]
SY
Member

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

SELECT R.field1, R.field2
BULK COLLECT INTO RES_C
FROM remote_table@MY_DBLINK R
WHERE R.id IN (1,2,3)


это remote SQL ибо у нас одна удаленная таблицa. A вот:

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;


это distributed SQL ибо имеем локальную и удаленную таблицы. Смотри план. Скорее всего driving site локальная база. Посему ВСЕ данные удаленной таблицы закачиваются в локальную базу и только потом фильтруются. Помоги оптимазеру вставь хинт /*+ DRIVING_SITE(R) */. Может уговоришь .

SY.
30 сен 17, 13:59    [20833361]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать параметр в секции IN() запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 4494
cybert
SELECT R.field1, R.field2
  BULK COLLECT INTO RES_C
  FROM remote_table@MY_DBLINK R
  WHERE R.id IN (SELECT/*+ cardinality(T 3) */ id FROM TABLE(C) T);
END;

Сделай так и все будет ок.
30 сен 17, 14:21    [20833378]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать параметр в секции IN() запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 4494
cybert
Посмотреть план запроса к удаленной БД не могу, т.к. дблинк - это отдельная транзакция и в плане запроса я увижу только REMOTE
"увижу" или "вижу"? Для начала смотреть надо на локальной базе, если плане только REMOTE, то все ок и хинт cardinality должен помочь
30 сен 17, 14:26    [20833383]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать параметр в секции IN() запроса  [new]
cybert
Member

Откуда:
Сообщений: 4
Спасибо Вам большое, DRIVING_SITE(R) помог) Правда, он отлично работает в SQL, когда локальная таблица - физическая. Как только ставлю этот хинт в середину кода на PL/SQL, где в качестве локальной таблицы используется коллекция - этот хинт игнорируется. Ну ничего, я это обойду через Temporary table вместо коллекций.
Спасибо еще раз, удачи Вам!
2 окт 17, 18:57    [20837213]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать параметр в секции IN() запроса  [new]
Fogel
Member

Откуда:
Сообщений: 223
cybert
  --лезу в удаленную базу, извлекаю из таблицы записи с айдишниками из моего списка:

  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));



решается без хинтов, заменой этого блока на:

with t as (SELECT id FROM TABLE(C))
SELECT R.field1, R.field2
  BULK COLLECT INTO RES_C
  FROM remote_table@MY_DBLINK R, t
  WHERE R.id = t.ID;

не будет тащить всю удалённую таблицу, а сразу извлечёт то, что нужно
4 окт 17, 12:04    [20841581]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать параметр в секции IN() запроса  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8623
Fogel
а сразу извлечёт то, что нужно


А возможно и больше чем нужно

with t as (SELECT DISTINCT id FROM TABLE(C))
SELECT R.field1, R.field2
  BULK COLLECT INTO RES_C
  FROM remote_table@MY_DBLINK R, t
  WHERE R.id = t.ID;


И не факт что "не будет тащить всю удалённую таблицу". Оптимайзер понятия не имеет о размере T, так-что совершенно не факт что WITH сделает R as driving site.

SY.
4 окт 17, 13:36    [20841998]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать параметр в секции IN() запроса  [new]
Fogel
Member

Откуда:
Сообщений: 223
SY
А возможно и больше чем нужно

про distinct согласен

SY
И не факт что "не будет тащить всю удалённую таблицу". Оптимайзер понятия не имеет о размере T, так-что совершенно не факт что WITH сделает R as driving site.
SY.

с этим не согласен
with для того и придумали, чтобы (в том числе) пошагово наборы данных формировать, поочередно их соединяя, поэтому к обращению к дблинку набор из with уже известен и тип соединения оптимизатор в любом случае выберет, не вытаскивая всю таблицу с удалённой базы.
4 окт 17, 17:18    [20843031]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать параметр в секции IN() запроса  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8623
Fogel
with для того и придумали, чтобы (в том числе) пошагово наборы данных формировать, поочередно их соединяя


И на каком заборе это написано? Это отимайзер решает материализовать WITH (вернее каждое CTE по отдельности) или нет. Но даже если и материализует то что? Получим соединение temporary table с удаленной таблицей что ничем это отличается от исходной ситуации. Оптимайзер понятия не имеет о размере temporary table и считает cardinality (если не ошибаюсь) по рaзмеру блока БД а посему как ни крути а либо CARDINALITY либо DRIVING_SITE.

SY.
4 окт 17, 20:48    [20843557]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать параметр в секции IN() запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 4494
cybert,

c коллекцией тоже можно, только я забыл что тут еще одну хитрость надо сделать:
select/*+ driving_site(l)  */ * 
from  (select/*+ no_merge cardinality(4) */ * 
       from table(cast (:t as sys.ku$_objnumset)) t) v
     ,xtest@remote l
where l.a = v.column_value


ps. Джонатан как всегда спасает
4 окт 17, 21:49    [20843724]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать параметр в секции IN() запроса  [new]
Fogel
Member

Откуда:
Сообщений: 223
SY
Fogel
with для того и придумали, чтобы (в том числе) пошагово наборы данных формировать, поочередно их соединяя


И на каком заборе это написано? Это отимайзер решает материализовать WITH (вернее каждое CTE по отдельности) или нет. Но даже если и материализует то что? Получим соединение temporary table с удаленной таблицей что ничем это отличается от исходной ситуации. Оптимайзер понятия не имеет о размере temporary table и считает cardinality (если не ошибаюсь) по рaзмеру блока БД а посему как ни крути а либо CARDINALITY либо DRIVING_SITE.

SY.


на заборе моего опыта.
возможно, мне не попадались иные кейсы.
но когда дблинк, материализация сте на локале идёт по умолчанию и получается "выдёргивание" по индексируемому полю нужных значений.
4 окт 17, 21:59    [20843753]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать параметр в секции IN() запроса  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8623
Fogel
но когда дблинк, материализация сте на локале идёт по умолчанию и получается "выдёргивание" по индексируемому полю нужных значений.


Да ну?

SQL> explain plan for
  2  with t as (select column_value empno from table(NumberList(1,2,3)))
  3  select  e.*
  4    from  emp@pdb1sol12 e,
  5          t
  6    where e.empno = t.empno
  7  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /
Plan hash value: 2694815159

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |      |  8168 |   319K|    32   (0)| 00:00:01 |        |      |
|*  1 |  HASH JOIN                             |      |  8168 |   319K|    32   (0)| 00:00:01 |        |      |
|   2 |   REMOTE                               | EMP  |    14 |   532 |     3   (0)| 00:00:01 | PDB1S~ | R->S |
|   3 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|      |  8168 | 16336 |    29   (0)| 00:00:01 |        |      |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."EMPNO"=VALUE(KOKBF$))

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "EMP" "E" (accessing
       'PDB1SOL12.AD1.PROD' )


22 rows selected.

SQL> 


Ну где " материализация сте на локале идёт по умолчанию и получается "выдёргивание" по индексируемому полю нужных значений"?

SY.
4 окт 17, 22:45    [20843892]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать параметр в секции IN() запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 4494
Материализация вообще тут не работает, т.е. с коллекцией и remote:
+ +materialize
SQL> with v(x) as (select/*+ materialize */ * from table(ku$_objnumset(1,2,4)))
  2  select *
  3  from  v
  4       ,xtest@loopback l
  5  where l.a = v.x;

         X          A          B          C
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          2
         4          4          4          4

3 rows selected.

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Plan hash value: 16270364

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |       |       |       |    36 (100)|          |        |      |
|*  1 |  HASH JOIN                             |       |  8168 |   111K|    36   (0)| 00:00:01 |        |      |
|   2 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|       |  8168 | 16336 |    29   (0)| 00:00:01 |        |      |
|   3 |   REMOTE                               | XTEST | 10000 |   117K|     7   (0)| 00:00:01 | LOOPB~ | R->S |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("L"."A"=VALUE(KOKBF$))

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "A","B","C" FROM "XTEST" "L" (accessing 'LOOPBACK' )

+ materialize+driving_site+cardinality
SQL> with v(x) as (select/*+ materialize */ * from table(ku$_objnumset(1,2,4)))
  2  select/*+ driving_site(l) cardinality(v 4) */ *
  3  from  v
  4       ,xtest@loopback l
  5  where l.a = v.x;

         X          A          B          C
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          2
         4          4          4          4

3 rows selected.

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Plan hash value: 1172462887

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |       |       |       |    33 (100)|          |        |      |
|   1 |  NESTED LOOPS                          |       |     4 |    56 |    33   (0)| 00:00:01 |        |      |
|   2 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|       |     4 |     8 |    29   (0)| 00:00:01 |        |      |
|*  3 |   FILTER                               |       |     1 |    12 |     1   (0)| 00:00:01 |        |      |
|   4 |    REMOTE                              | XTEST |       |       |            |          | LOOPB~ | R->S |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("L"."A"=VALUE(KOKBF$))

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT /*+ */ "A","B","C" FROM "XTEST" "L" (accessing 'LOOPBACK' )

+ c workaround от Jonathan Lewis
SQL> with v(x) as (select/*+ no_merge cardinality(t 4) */ * from table(ku$_objnumset(1,2,4)) t)
  2  select *
  3  from  v
  4       ,xtest@loopback l
  5  where l.a = v.x;

         X          A          B          C
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          2
         4          4          4          4

3 rows selected.

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Plan hash value: 3879124937

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |       |       |       |    33 (100)|          |        |      |
|   1 |  NESTED LOOPS                           |       |     4 |   100 |    33   (0)| 00:00:01 |        |      |
|   2 |   VIEW                                  |       |     4 |    52 |    29   (0)| 00:00:01 |        |      |
|   3 |    COLLECTION ITERATOR CONSTRUCTOR FETCH|       |     4 |     8 |    29   (0)| 00:00:01 |        |      |
|   4 |   REMOTE                                | XTEST |     1 |    12 |     1   (0)| 00:00:01 | LOOPB~ | R->S |
-----------------------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT "A","B","C" FROM "XTEST" "L" WHERE "A"=:1 (accessing 'LOOPBACK' )

Тут нужно пояснить:
1. Во-первых, я надеялся на FULL REMOTE Statement, но из-за волшебных kokbf$, это не проходит (логично предположить, что просто не стали париться с проверкой соответствия типов коллекций)
2. Материализация не работает. Я уже говорил, что решение о материализации принимается не стоимостным алгоритмом, а захардкоденными правилами, и, скорее все, этот тип находится в исключениях, т.к. я пробовал стандартные известные мне правила - и добавить предикат, и обратиться к V несколько раз - не помогло...
3. DRIVING_SITE не работает для коллекций, т.к. нужен инициатор вызова remote statements, передающий бинды
4. Workaround от Льюиса, конечно, рабочий, и в большинстве случаев он будет хорошо подходить, но у него есть и минус - remote statement будет вызываться столько же раз сколько к нему лукапов будет, а это, естественно, будет медленнее при большом кол-ве обращений (и само кол-во выполнений, и кол-во сетевых раундтрипов...)
4 окт 17, 23:24    [20843998]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать параметр в секции IN() запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 4494
В целом, все это еще и зависит от версий - волшебные kokbf и их ограничения меняются от версии к версии...
Я тестировал на 12.2
4 окт 17, 23:28    [20844002]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать параметр в секции IN() запроса  [new]
Fogel
Member

Откуда:
Сообщений: 223
xtender, спасибо за подробные пояснения
5 окт 17, 09:08    [20844189]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать параметр в секции IN() запроса  [new]
cybert
Member

Откуда:
Сообщений: 4
Спасибо еще раз всем огромное)
Сделал, как советовал xtender, прочитав статью Джонатана (https://jonathanlewis.wordpress.com/2010/10/11/distributed-objects/).
Все получилось, финальный тестовый код работает отлично.

SY.: Workaround от Льюиса, конечно, рабочий, и в большинстве случаев он будет хорошо подходить, но у него есть и минус - remote statement будет вызываться столько же раз сколько к нему лукапов будет, а это, естественно, будет медленнее при большом кол-ве обращений (и само кол-во выполнений, и кол-во сетевых раундтрипов...)
- да, бесспорно, но это в 100 раз лучше, чем мое временное решение, где я в цикле PL/SQL вызывал Select, выдирая из ремоут таблицы по одной записи, т.е. еще и переключение контекста шло)

Еще раз спасибо всем участникам за помощь, удачи всем!

финальный тестовый код:

create or replace type T_ID_NAME_OBJ as object
(id NUMBER,
 NAME VARCHAR2(255)
);

create or replace type T_ID_NAME_DATASET AS TABLE OF T_ID_NAME_OBJ;

-------------------------------

declare

C T_ID_NAME_DATASET;
RES_C T_ID_NAME_DATASET;
BEGIN
  C:=T_ID_NAME_DATASET();
  C.EXTEND(10);
  C(1):=t_id_name_obj(1841,'');
  C(2):=t_id_name_obj(1912,'');
  C(3):=t_id_name_obj(1844,'');
  C(4):=t_id_name_obj(1482,'');
  C(5):=t_id_name_obj(1770,'');
  C(6):=t_id_name_obj(1774,'');
  C(7):=t_id_name_obj(1657,'');
  C(8):=t_id_name_obj(1587,'');
  C(9):=t_id_name_obj(1597,'');
  C(10):=t_id_name_obj(1608,'');
  
  select T_ID_NAME_OBJ(R.col_id, R.col_responsenumber)
  BULK COLLECT INTO RES_C
  from (select /*+ no_merge cardinality(L1 10) */ cast(id as number) as id
        from table(C) L1
       ) L,
       APPBASE_ENVERIM.TBL_RESPONSE@DBLINK_ERIM R
  where R.col_id=L.id;
    
  for i in RES_C.FIRST..RES_C.LAST
    loop
      dbms_output.put_line(res_c(i).id||' '||res_c(i).name);
    end loop;
END;
7 окт 17, 00:37    [20850126]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить