0

I have a table with primary key column on a remote database:

create table tab (
  id primary key,
  pad
) nologging as
select level id, lpad('x', 800) pad
from dual
connect by level <= 100000;

If I query a few rows from remote table by id, over db link:

create type id_list is table of number;

declare
    type tab_list is table of u.tab@dblink%rowtype;

    v_result tab_list;
    v_id_list id_list := id_list(10003, 10004, 10005);
begin
    select *
    bulk collect into v_result
    from u.tab@dblink
    where id in (select value(i) from table(v_id_list) i);
end;

This simple query takes around 3 seconds to complete. This time is linearly dependent on the number of values in v_id_list collection. Each additional id increases query time by about ~1s.

Query plan on local database is:

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |      1 |        |      3 |00:00:01.68 |       |       |          |
|   1 |  NESTED LOOPS                       |      |      1 |      1 |      3 |00:00:01.68 |       |       |          |
|   2 |   SORT UNIQUE                       |      |      1 |      3 |      3 |00:00:00.01 |  2048 |  2048 | 2048  (0)|
|   3 |    COLLECTION ITERATOR PICKLER FETCH|      |      1 |      3 |      3 |00:00:00.01 |       |       |          |
|*  4 |   FILTER                            |      |      3 |      1 |      3 |00:00:02.69 |       |       |          |
|   5 |    REMOTE                           | TAB  |      3 |        |    300K|00:00:03.45 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

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

"   4 - filter(""ID""=VALUE(KOKBF$))"

The remote query was executed 3 times for a total of 300k rows (3 full table scans). Similarly, querying v$sql on remote database reveals that the following query was executed 3 times:

SELECT /*+ */ "ID","PAD" FROM "U"."TAB" "TAB"

Why is the index not used in this case?

I verified, that the index is used correctly when values are supplied in pure SQL, even from another table:

create table ids nologging as
select 10003 id from dual union all
select 10004 from dual union all
select 10005 from dual;

select *
from u.tab@dblink
where id in (select id from ids);

I also checked that:

  • statistics were refreshed,
  • driving_site hint does not help.

I managed to get Oracle to use index eventually by extracting collection to subquery and adding materialize hint:

with ids AS (
  select /*+ materialize */ value(i)
  from table(v_id_list) i
)
select *
bulk collect into v_result
from u.tab@dblink
where id in (select i.id from ids i);

Is there a better way to tell optimizer to use index, without using explicit hints?

Tested on Oracle 12.1.0.2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.