1

Topic: Whether it is possible to use parameter in section IN () request

Good afternoon.
There is a task. There is my database (Oracle 11.2) and a remote DB (Oracle 11.2) to which I am connected from the basis through DBLink.
In the DB I wrote a certain function which takes list ID-shnikov from the local table, and under this list ID-shnikov climbs in a remote DB and derives the data from the big table. At first made through a collection so (I write schematically not to load a subject the real code):

--I create types
CREATE TYPE T_OBJ AS OBJECT (id NUMBER, Name VARCHAR2 (200));
CREATE TYPE T_OBJ_DATASET AS TABLE OF T_OBJ;
--Function body
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
--I fill a collection from the local table
SELECT T_OBJ (T.id, T.name)
BULK COLLECT INTO A C
FROM my_local_table T
ORDER BY T.id;
--I climb in remote base, I derive from the table of record with  from my list:
SELECT R.field1, R.field2
BULK COLLECT INTO RES_C
FROM remote_table@MY_DBLINK R
WHERE R.id IN (SELECT id FROM TABLE (A C));
END;

And here tricks begin. In my local table of only 3 records, in the remote table of records it is a lot of, but across the field ID there is an index, it VALID th, I checked. It would Seem,  should draw out 3 id-shnika from a collection, transfer the list from 3 id-shnikov in request to the remote table and as that field is indexed the request should be fulfilled very quickly.
But in real the request is fulfilled more minutes. To look at the request plan to a remote DB I can not, since  is a separate transaction and in respect of request I will see only REMOTE, and to remote base at me was not present  access.
When I do here so:

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

I.e. I write id-shniki directly, the request is fulfilled instantly.
Made temporarily so:

id_list_str VARCHAR2 (4000);
--I fill a variable id_list_str from a collection;
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;

All would seem well, but on remote base in  requests there will be a heap of my same requests with different variations IN (...) what to eat very badly. Tried to make through parameterized query, defining mine id_list_str as parameter:

EXECUTE IMMEDIATE ' SELECT... WHERE R.ID IN (:Param)'
BULK COLLECT INTO RES_C
USING IN id_list_str;

- It did not turn out. Probably,  cannot declare parameter of type the list, only specific values, and at me their amount permanently changes.
I still had one more reserve variant: in a cycle on a collection to fulfill  to a remote DB, deriving on one record through parameter, but this decision not so effective since there will be permanently a context switching between PL/SQL and SQL that too it is not so good. It would be desirable to manage all the same one request to remote base with list transmission id-shnikov but thus not to hammer library cache in this remote base.
In advance thanks for the help.

2

Re: Whether it is possible to use parameter in section IN () request

SQL:

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

It remote SQL for at us one remote a. A here:

SELECT R.field1, R.field2
BULK COLLECT INTO RES_C
FROM remote_table@MY_DBLINK R
WHERE R.id IN (SELECT id FROM TABLE (A C));
END;

It distributed SQL for it is had the local and remote tables. Look the plan. Most likely driving site local basis. Therefore ALL data of the remote table is downloaded in local basis and only is then filtered. Help  interpose  / * + DRIVING_SITE (R) */. Can you will persuade smile)).
SY.

3

Re: Whether it is possible to use parameter in section IN () request

cybert wrote:

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 (A C)>>> T <<<);
END;

Make and all will be apprx.

4

Re: Whether it is possible to use parameter in section IN () request

cybert wrote:

to Look at the request plan to a remote DB I can not, since  is a separate transaction and in respect of request I will see only REMOTE

I "will see" or I "see"? To begin with it is necessary to look on local basis, if the plan only REMOTE all  and  cardinality should help

5

Re: Whether it is possible to use parameter in section IN () request

Thanks to you big, DRIVING_SITE (R) helped) However, it perfectly works in SQL, when the local table - physical. As soon as I put this  in the code middle on PL/SQL where as the local table the collection is used - this  is ignored. Well anything, I will bypass it through Temporary table instead of collections.
Thanks once again, good luck to you!

6

Re: Whether it is possible to use parameter in section IN () request

cybert wrote:

--I climb in remote base, I derive from the table of record with  from my list:
SELECT R.field1, R.field2
BULK COLLECT INTO RES_C
FROM remote_table@MY_DBLINK R
WHERE R.id IN (SELECT id FROM TABLE (A C));

Dares without , changeover of this unit on:

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

Will not drag all remote table, and at once derives that is necessary

7

Re: Whether it is possible to use parameter in section IN () request

Fogel wrote:

and at once derives that is necessary

And probably and more than it is necessary smile))

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

And not the fact that "will not drag all remote table".  no concept has about the size T so-that at all that WITH makes the fact R as driving site.
SY.

8

Re: Whether it is possible to use parameter in section IN () request

SY wrote:

And probably and more than it is necessary smile))

About distinct it agree

SY wrote:

And not the fact that "will not drag all remote table".  no concept has about the size T so-that at all that WITH makes the fact R as driving site.
SY.

With it it do not agree
with for this purpose also invented, that (including)  data sets to form, in turn them connecting, therefore to reversal to  the dial-up from with is already known also type of connection the optimizer anyway selects, without pulling out all table from remote base.

9

Re: Whether it is possible to use parameter in section IN () request

Fogel wrote:

with for this purpose also invented, that (including)  data sets to form, in turn them connecting

And on what fence it is written? It  decides to materialize WITH (more truly everyone CTE separately) or not. But even if materializes that that? We receive connection temporary table with the remote table that anything it differs from an initial situation.  no concept has about the size temporary table and considers cardinality (if I am not mistaken) on a the unit of a DB and therefore whatever one may do and either CARDINALITY or DRIVING_SITE.
SY.

10

Re: Whether it is possible to use parameter in section IN () request

cybert;
c a collection too it is possible, only I forgot that here still one cunning should be made:

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. Dzhonatan as always rescues smile))

11

Re: Whether it is possible to use parameter in section IN () request

SY wrote:

it is passed...
And on what fence it is written? It  decides to materialize WITH (more truly everyone CTE separately) or not. But even if materializes that that? We receive connection temporary table with the remote table that anything it differs from an initial situation.  no concept has about the size temporary table and considers cardinality (if I am not mistaken) on a the unit of a DB and therefore whatever one may do and either CARDINALITY or DRIVING_SITE.
SY.

On a fence of my experience.
Probably, other cases did not come across to me.
But when ,   on  goes by default and it turns out "" on an indexable field of the necessary values.

12

Re: Whether it is possible to use parameter in section IN () request

Fogel wrote:

but when ,   on  goes by default and it turns out "" on an indexable field of the necessary values.

Yes well?

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) | 0:00:01 AM | | |
|* 1 | HASH JOIN | | 8168 | 319K | 32 (0) | 0:00:01 AM | | |
| 2 | REMOTE | EMP | 14 | 532 | 3 (0) | 0:00:01 AM | PDB1S ~ | R-> S |
| 3 | COLLECTION ITERATOR CONSTRUCTOR FETCH | | 8168 | 16336 | 29 (0) | 0:00:01 AM | | |
---------------------------------------------------------------------------------------------------------------
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>

Well where "  on  goes by default and it turns out"  "on an indexable field of the necessary values"?
SY.

13

Re: Whether it is possible to use parameter in section IN () request

generally here does not work, i.e. with a collection and remote:
[spoiler +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 A 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) | 0:00:01 AM | | |
| 2 | COLLECTION ITERATOR CONSTRUCTOR FETCH | | 8168 | 16336 | 29 (0) | 0:00:01 AM | | |
| 3 | REMOTE | XTEST | 10000 | 117K | 7 (0) | 0:00:01 AM | 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 ')

[/spoiler]
[spoiler 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 A 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) | 0:00:01 AM | | |
| 2 | COLLECTION ITERATOR CONSTRUCTOR FETCH | | 4 | 8 | 29 (0) | 0:00:01 AM | | |
|* 3 | FILTER | | 1 | 12 | 1 (0) | 0:00:01 AM | | |
| 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 ')

[/spoiler]
[spoiler c workaround from 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 A 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) | 0:00:01 AM | | |
| 2 | VIEW | | 4 | 52 | 29 (0) | 0:00:01 AM | | |
| 3 | COLLECTION ITERATOR CONSTRUCTOR FETCH | | 4 | 8 | 29 (0) | 0:00:01 AM | | |
| 4 | REMOTE | XTEST | 1 | 12 | 1 (0) | 0:00:01 AM | LOOPB ~ | R-> S |
-----------------------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - SELECT "A", "B", "C" FROM "XTEST" "L" WHERE "A" =:1 (accessing ' LOOPBACK ')

[/spoiler]
Here it is necessary to explain:
1. At first, I hoped on FULL REMOTE Statement, but because of magic kokbf $, it does not transit (logically to assume, did not begin what to be soared simply with check of type compatibility of collections)
2.  does not work. I already said that the decision about  is accepted not by cost algorithm, and  rules, and, everything is faster, this type is in exceptions since I tried standard rules known to me - both to add a predicate, and to address to V some times - did not help...
3. DRIVING_SITE does not work for collections since the initiator of a call remote statements, transferring  is necessary
4. Workaround from Lewis, of course, the worker, and in most cases it it will be good to approach, but it has also a minus - remote statement time how many to it  will be caused as much will be, and it, naturally, will be more slowly at big kol-ve calls (and itself kol-in executions, and kol-in network ...)

14

Re: Whether it is possible to use parameter in section IN () request

As a whole, all it also depends on versions - magic kokbf and their restrictions change from the version to the version...
I tested on 12.2

15

Re: Whether it is possible to use parameter in section IN () request

xtender, thanks for detailed explanations

16

Re: Whether it is possible to use parameter in section IN () request

Thanks once again all huge)
Made, as advised xtender, reading article of Dzhonatana (https://jonathanlewis.wordpress.com/201 … d-objects/).
All turned out, the final test code works perfectly.
SY.: Workaround from Lewis, of course, the worker, and in most cases it it will be good to approach, but it has also a minus - remote statement time how many to it  will be caused as much will be, and it, naturally, will be more slowly at big kol-ve calls (and itself kol-in executions, and kol-in network ...)
- Yes, undoubtedly, but it in 100 times is better, than my makeshift where I in cycle PL/SQL caused Select, tearing out from  tables on one record i.e. also context switching went)
Once again thanks all participants for the help, good luck all!
Final test code:

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;