1 2 Previous Next 18 Replies Latest reply on Jan 13, 2018 4:28 PM by 1503346

    Join with user collection type does not pass predicates to dblink.

    1503346

      Hi all.

      Oracle 12.1, two databases.

       

      At first database, we have table "td" with key td_id.

       

      At second database, we have:
        dblink to first (name 'firstdb'),

      collection type
      create OR REPLACE TYPE T_NUMBER_TBL as table of NUMBER;

       

      and example query:

      select /*+ use_nl(t se) ordered*/
      t.column_value,
      SE.NOTE DONOR_TD_ID,
      'D' CHARGING_FLAG
      FROM TABLE(CAST(:B1 AS T_NUMBER_TBL)) T
      JOIN td@firstdb SE
      ON SE.td_ID = t.column_value

       

      This query is slow.


      Explain shows this:

      Plan hash value: 3896059973

      ----------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
      ----------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                   |                 | 29171 |  4187K|  3550   (8)| 00:00:01 |        |      |
      |   1 |  NESTED LOOPS                      |                 | 29171 |  4187K|  3550   (8)| 00:00:01 |        |      |
      |   2 |   COLLECTION ITERATOR PICKLER FETCH|                 |  8168 | 16336 |    24   (0)| 00:00:01 |        |      |
      |*  3 |   FILTER                           |                 |     4 |   580 |     1   (0)| 00:00:01 |        |      |
      |   4 |    REMOTE                          | td              |       |       |            |          |firstdb | R->S |
      ----------------------------------------------------------------------------------------------------------------------

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

         3 - filter("SE"."td_ID"=VALUE(KOKBF$))

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

         4 - SELECT /*+ USE_NL ("SE") */ "td_ID","NOTE" FROM "td" "SE" (accessing 'FIRSTDB' )

       

      So, NO predicate passed to remote db, thus no key used

       

      Add artifical predicate to query:

      select /*+ use_nl(t se) ordered*/
      t.column_value,
      SE.note DONOR_TD_ID,
      'D' CHARGING_FLAG
      FROM TABLE(CAST(:B1 AS T_NUMBER_TBL)) T
      JOIN td@firstdb SE
      ON SE.td_ID = t.column_value
      and se.td_id in (:b2)

       

      Happy plan:

      Plan hash value: 3896059973

      ----------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
      ----------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                   |                 |   292 | 42924 |    60   (5)| 00:00:01 |        |      |
      |   1 |  NESTED LOOPS                      |                 |   292 | 42924 |    60   (5)| 00:00:01 |        |      |
      |*  2 |   COLLECTION ITERATOR PICKLER FETCH|                 |    82 |   164 |    24   (0)| 00:00:01 |        |      |
      |*  3 |   FILTER                           |                 |     4 |   580 |     1   (0)| 00:00:01 |        |      |
      |   4 |    REMOTE                          | td              |       |       |            |          |firstdb | R->S |
      ----------------------------------------------------------------------------------------------------------------------

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

         2 - filter(VALUE(KOKBF$)=TO_NUMBER(:B2))
         3 - filter("SE"."td_ID"=VALUE(KOKBF$))

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

         4 - SELECT /*+ USE_NL ("SE") */ "td_ID","NOTE" FROM "td" "SE" WHERE
             "td_ID"=:1 (accessing 'firstdb' )

      Artifical predicate IS passed.

      Modifying this query to work with some local table we have normal predicate work, it is taken from collection and to local table access.

       

      Tried hinting (especially no_query_transformation), wrapping B1 to pipelined function - no luck, predicate is not passed to remote query.

      How can we make this predicate work?

       

       

       

        • 1. Re: Join with user collection type does not pass predicates to dblink.
          Cookiemonster76

          To make this work I rather suspect you'll need to create the type on the remote DB and use the type there.

          • 2. Re: Join with user collection type does not pass predicates to dblink.
            Mustafa KALAYCI

            use DRIVING_SITE and run query at the remote db?

            • 3. Re: Join with user collection type does not pass predicates to dblink.
              Paulzip

              Try this and post explain plan back

               

              select /*+ driving_site(se) */ se.td_id, 'D' charging_flag

              from td@firstdb se

              where se.td_id in (select /*+ dynamic_sampling(t 2) */ t.column_value

                                 from   table(cast(:b1 as t_number_tbl)) t)

              • 4. Re: Join with user collection type does not pass predicates to dblink.
                1503346

                Plan hash value: 1718030872

                -----------------------------------------------------------------------------------------------------------------------
                | Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
                -----------------------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT                    |                 |   912 | 13680 |    62   (4)| 00:00:01 |        |      |
                |   1 |  NESTED LOOPS                       |                 |   912 | 13680 |    62   (4)| 00:00:01 |        |      |
                |   2 |   SORT UNIQUE                       |                 |  8168 | 16336 |     9   (0)| 00:00:01 |        |      |
                |   3 |    COLLECTION ITERATOR PICKLER FETCH|                 |  8168 | 16336 |     9   (0)| 00:00:01 |        |      |
                |*  4 |   FILTER                            |                 |     4 |    52 |     1   (0)| 00:00:01 |        |      |
                |   5 |    REMOTE                           | td |       |       |            |          |   CRMC | R->S |
                -----------------------------------------------------------------------------------------------------------------------

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

                   4 - filter("SE"."td_ID"=VALUE(KOKBF$))

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

                   5 - SELECT /*+ */ "td_ID" FROM "td" "SE" (accessing 'CRMC' )

                 

                 

                No effect, hint just ignored.

                So, example query is very simplified part of real query, which joins more tables by different dblinks.

                Only perspective idea i see is to create database table instead of collection. In this case predicate is used very well.

                • 5. Re: Join with user collection type does not pass predicates to dblink.
                  1503346

                  Alas, second db is a kind of central point in problematic query. Real query (from part of which i have made example query) joins more than one remote tables with collection.

                  Collection must be filled at second db, we have no other way to fill and use it.

                  • 6. Re: Join with user collection type does not pass predicates to dblink.
                    Mustafa KALAYCI

                    I tried your code. first I thought it could be the type you used (T_NUMBER_TBL) I created it in my remote db but result was same, then I tried to use it from remotedb, in your example like this:

                     

                    select /*+driving_site(p) */ p.id

                    from  my_dummy_table@remotedb p

                              join table(T_NUMBER_TBL@remotedb(10,11)) r on p.id = r.column_value;

                     

                    but result is the same. I believe problem is TABLE function/operator here. it can not be called from remote db, at least I couldn't achieve to call it. probably TABLE is calling everything to local from remote db.

                     

                    edit: also I tried built in type sys.ODCINUMBERLIST instead of T_NUMBER_TBL but result is same.

                     

                    edit2: also tried creating TYPES with same OID...

                    • 7. Re: Join with user collection type does not pass predicates to dblink.
                      Paulzip

                      From my experience, collections and types in general are notoriously "DBLink unfriendly", and PLSQL tables are often also explain plan unfriendly.  I had a similar situation a year ago, where a view joined to to PLSQL table and regardless of what I did, regardless of hints, the presence of the PLSQL table just caused horrific explain plans.

                       

                      You could try to create the same collection on the DBLink end and recreate the local one but include an OID - this may allow the two sites to be compatible.

                       

                      Generate one using SYS_OP_GUID().

                       

                      select sys_op_guid() from dual

                      /

                       

                      SYS_OP_GUID()

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

                      89ADCEF5724D4394B0563F9047478264

                       

                      -- Run the following on both local and remote site

                      drop type t_number_tbl

                      /                

                      create type t_number_tbl oid '89ADCEF5724D4394B0563F9047478264' is table of number

                       

                      Then try working with that.

                      • 8. Re: Join with user collection type does not pass predicates to dblink.
                        Mustafa KALAYCI

                        I tried and still no result. I am working on passing this type as parameter to a pipelined function but I couldn't achieve passing collection on a remote db proc.

                        • 9. Re: Join with user collection type does not pass predicates to dblink.
                          1503346

                          I did try pipelined function.

                          Pretty stupid function, just taking a collection as a parameter and pipelining it row by row to result of the same type.

                          It works, but predicate still not used.

                          • 10. Re: Join with user collection type does not pass predicates to dblink.
                            Mustafa KALAYCI

                            it is not what I did, I created pipelined function on remote db, "tried to" pass collection and do join in pipelined function, return rows from it. I will keep trying. I am curious about it.

                            • 11. Re: Join with user collection type does not pass predicates to dblink.
                              rp0428

                              Alas, second db is a kind of central point in problematic query. Real query (from part of which i have made example query) joins more than one remote tables with collection.

                              Collection must be filled at second db, we have no other way to fill and use it.

                              Queries only execute on ONE SERVER.

                               

                              When a query involves multiple DBs Oracle chooses ONE OF THEM to execute the entire query on.

                               

                              Which means that anything Oracle needs in order to execute the query MUST BE on the DB Oracle chooses for execution.

                               

                              Any data on one DB that is needed on the other db needs to be first sent to the other db. Any object types need to exist on the other db.

                               

                              I suggest use the 'solution' you already know works and put your data into a table.

                              • 12. Re: Join with user collection type does not pass predicates to dblink.
                                gaverill

                                The best I was able to do was create a package on the remote DB that could be called to set a context attribute containing a comma-separated string of ID's. Then, again on the remote DB, create a view that joins TD with either a pipelined function or an inline view that "unnests" the context attribute value into rows... e.g.

                                 

                                create or replace view V_TD
                                as
                                select  td.*
                                from    td
                                where   td.t_id in (
                                            select  to_number(regexp_substr(sys_context('TESTING_CONTEXT','COMMA_SEPARATED_NUMBERS'), '[^,]+', 1, level))
                                            from    DUAL
                                            connect by
                                                    regexp_substr(sys_context('TESTING_CONTEXT','COMMA_SEPARATED_NUMBERS'), '[^,]+', 1, level) is not null
                                        );
                                

                                 

                                Gerard

                                • 13. Re: Join with user collection type does not pass predicates to dblink.
                                  Mustafa KALAYCI

                                  yep, I thought about this too. this way you don't use TABLE operator either but still stuck on passing collection as parameter over dblink.

                                  • 14. Re: Join with user collection type does not pass predicates to dblink.
                                    rp0428

                                    but still stuck on passing collection as parameter over dblink.

                                    May I suggest you use your time for something more productive?

                                    https://docs.oracle.com/database/121/ADOBJ/adobjbas.htm#ADOBJ7083

                                    Restriction on Using User-Defined Types with a Remote Database

                                     

                                    Objects or user-defined types (specifically, types declared with a SQL CREATE TYPE statement, as opposed to types declared within a PL/SQL package) are currently useful only within a single database. Oracle Database restricts use of a database link as follows:

                                     

                                        You cannot connect to a remote database to select, insert, or update a user-defined type or an object REF on a remote table.

                                     

                                        You can use the CREATE TYPE statement with the optional keyword OID to create a user-specified object identifier (OID) that allows an object type to be used in multiple databases. See the discussion on assigning an OID to an object type in the Oracle Database Data Cartridge Developer's Guide.

                                     

                                        You cannot use database links within PL/SQL code to declare a local variable of a remote user-defined type.

                                     

                                        You cannot convey a user-defined type argument or return value in a PL/SQL remote procedure call.

                                    That restriction has been around forever.

                                    1 person found this helpful
                                    1 2 Previous Next