Topic: Whether it is possible to use parameter in section IN () request
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.