FBI Limitation

Latest update: Dec 2020

A recent question on the ODC (OTN) database forum prompted me to point out that the optimizer doesn’t consider function-based indexes on remote tables in distributed joins. I then spent 20 minutes trying to find the blog note where I had demonstrated this effect, or an entry in the manuals reporting the limitation – but I couldn’t find anything, so I’ve written a quick demo which I’ve run on 12.2.0.1 to show the effect. First, the SQL to create a couple of tables and a couple of indexes:

rem
rem     Script:         fbi_limitation.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2018
rem

-- create public database link orcl@loopback using 'orcl'; 
define m_target = orcl@loopback

create table t1
segment creation immediate
nologging
as
with generator as (
        select
                rownum id
        from dual
        connect by
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        rownum                          id,
        rownum                          n1,
        lpad(rownum,10,'0')             v1,
        lpad('x',100,'x')               padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e6 -- > comment to avoid WordPress format issue
;

create table t2
nologging
as
select * from t1
;

alter table t1 add constraint t1_pk primary key(id);
alter table t2 add constraint t2_pk primary key(id);
create unique index t2_f1 on t2(id+1);

begin
        dbms_stats.gather_table_stats(
                ownname     => user,
                tabname     => 'T1',
                cascade     => true,
                method_opt  => 'for all columns size 1'
        );

        dbms_stats.gather_table_stats(
                ownname     => user,
                tabname     => 'T2',
                cascade     => true,
                method_opt  => 'for all columns size 1'
        );
end;
/

The code is very simple, it creates a couple of identical tables with an id column that will produce an index with a very good clustering_factor. You’ll notice that I’ve (previously) created a public database link that is (in my case) a loopback to the current database and the code defines a variable that I can use as a substitution variable later on. If you want to do further tests with this model you’ll need to make some changes in these two lines.

So now I’m going to execute a query that should result in the optimizer choosing a nested loop between the tables – but I have two versions of the query, one which treats t2 as the local table it really is, and one that pretends (through the loopback) that t2 is remote.

set serveroutput off

select
        t1.v1, t2.v1
from
        t1,
        t2
--      t2@orcl@loopback
where
        t2.id+1 = t1.id
and     t1.n1 between 101 and 110
;


select * from table(dbms_xplan.display_cursor);

select
        t1.v1, t2.v1
from
        t1,
--      t2
        t2@orcl@loopback
where
        t2.id+1 = t1.id
and     t1.n1 between 101 and 110
;

select * from table(dbms_xplan.display_cursor);

Here are the two execution plans, pulled from memory – including the “remote” section in the distributed case:

SQL_ID  fthq1tqthq8js, child number 0
-------------------------------------
select  t1.v1, t2.v1 from  t1,  t2 -- t2@orcl@loopback where  t2.id+1 =
t1.id and t1.n1 between 101 and 110

Plan hash value: 1798294492

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |  2347 (100)|          |
|   1 |  NESTED LOOPS                |       |    11 |   407 |  2347   (3)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | T1    |    11 |   231 |  2325   (4)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |    16 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | T2_F1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - filter(("T1"."N1"<=110 AND "T1"."N1">=101))
   4 - access("T2"."SYS_NC00005$"="T1"."ID")

Note
-----
   - this is an adaptive plan




SQL_ID  ftnmywddff1bb, child number 0
-------------------------------------
select  t1.v1, t2.v1 from  t1, -- t2  t2@orcl@loopback where  t2.id+1 =
t1.id and t1.n1 between 101 and 110

Plan hash value: 1770389500

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4663 (100)|          |        |      |
|*  1 |  HASH JOIN         |      |    11 |   616 |  4663   (4)| 00:00:01 |        |      |
|*  2 |   TABLE ACCESS FULL| T1   |    11 |   231 |  2325   (4)| 00:00:01 |        |      |
|   3 |   REMOTE           | T2   |  1000K|    33M|  2319   (3)| 00:00:01 | ORCL@~ | R->S |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID"+1)
   2 - filter(("T1"."N1"<=110 AND "T1"."N1">=101))

Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT "ID","V1" FROM "T2" "T2" (accessing 'ORCL@LOOPBACK' )

Both plans show that the optimizer has estimated the number of rows that would be retrieved from t1 correctly (very nearly); but while the fully local query does a nested loop join using the high-precision, very efficient function-based index (reporting the internal supporting column referenced in the predicate section) the distributed query seems to have no idea about the remote function-based index and select all the required rows from the remote table and does a hash join.

Footnote:

Another reason for changes in execution plan when you test fully local and then run distributed is due to the optimizer ignoring remote histograms, as demonstrated in a much older blog note (though still true in 12.2.0.1).

Addendum

After finishing this note, I discovered that I had written a similar note about reverse key indexes nearly five years ago. Arguably a reverse key is just a special case of a function-based index – except it’s not labelled as such in user_tab_cols, and doesn’t depend on a system-generated hidden column. I still haven’t been able to find any references in any manual since 8i stating the reverse key index limitation, and have yet to find an official comment about ignoring function-based indexes.

 
 

Update (Dec 2020)

In a recent thread on the Oracle-L mailing list someone pointed out that with the appropriate hints Oracle would use the function-based index in this example, suggesting that this was evidence that the problem was not a limitation but more of a plan selection issue.

This highlighted an important principle: when the local optimizer tries to generate a plan and decomposes the query to produce remote components it thinks it knows what will happen when those component SQL statements reach the remote optimizer, but the remote optimizer may do something different because it knows more about its own data than the local optimizer does.

Here’s the test query with “appropriate” hints added, and the resulting execution plan pulled from memory on an instance of 19.3.0.0:

select
        /*+ leading(t1 t2) use_nl(t2) index(t2_f1) gather_plan_statistics */
        t1.v1, t2.v1
from
        t1,
        t2@&m_target
where
        t2.id+1 = t1.id
and     t1.n1 between 101 and 110
;

select * from table(dbms_xplan.display_cursor(format=>'cost allstats last hint_report remote outline'));


------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| Inst   |IN-OUT| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        | 29040 (100)|        |      |     10 |00:00:00.01 |   17864 |
|   1 |  NESTED LOOPS      |      |      1 |     11 | 29040   (7)|        |      |     10 |00:00:00.01 |   17864 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |     11 |  2394   (6)|        |      |     10 |00:00:00.01 |   17864 |
|   3 |   REMOTE           | T2   |     10 |      1 |  2422   (7)| ORCLP~ | R->S |     10 |00:00:00.01 |       0 |
------------------------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
      USE_NL(@"SEL$1" "T2"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("T1"."N1"<=110 AND "T1"."N1">=101))

Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT /*+ GATHER_PLAN_STATISTICS INDEX ("T2" "T2_F1") USE_NL ("T2") */ "ID","V1" FROM "T2" "T2"
       WHERE :1="ID"+1 (accessing 'ORCLPDB.LOCALDOMAIN@LOOPBACK' )

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------
   1 -  SEL$1
           -  leading(t1 t2)

   3 -  SEL$1 / T2@SEL$1
         U -  index(t2 t2_f1) / index specified in the hint doesn't exist
           -  use_nl(t2)

The hint set I supplied was one that said:

• consider only the join order t1 -> t2
• use a nested loop to get to t2
• use the function-based index t2_f1 to access t2

There are several details in the resulting output that tell us that the local optimizer doesn’t know anything about the remote function-based index.

From the top down:

• the cost of the access to t2 is clearly a tablescan cost – the local optimizer thinks it has to do a tablescan
• the outline information actually includes the hint FULL(@”SEL$1″ “T2″@”SEL$1”) – the local optimizer thinks it has to do a tablescan
• the hint report tells us that the hint index(t2 t2_f1) is unused because the index doesn’t exist.

Despite this, the run-time activity was a nested loop join using the t2_f1 index to reach the t2 table. At first sight it would be easy to think that this was because the /*+ index(t2 t2_f1) */ hint was forwarded to the remote database – as shown in the Remote SQL Information section of the plan – but that’s not the case. Because the optimizer has been hinted to do a nested loop from t1 to t2 the join predicate WHERE :1=”ID”+1 was included in the SQL sent to the remote database. Even if the index hint had not been present the remote optimizer would have recognised that the arriving query could best be satisfied by a unique index scan on t2_f1.

This is not a case of Oracle being able to use the correct plan when hinted, it’s a case of being a bit lucky that a set of highly suggestive hints produced a lucky result.  Of course in a very simple query like this it’s fairly easy to get lucky, but in a more complex case (multiple remote tables with multiple candidate indexes) the fact that the optimizer is costing for a tablescan when it should be costing for an index is likely to make it much harder to get lucky.

In this case a search through the shared pool showed that the remote instance had actually used the index unique scan rather than doing the full tablescan the local instance was assuming would happen. It’s far more common, though, for this type of local/remote mismatch  to result in the remote instance doing a full tablescan when the local instance was expecting it to do a high precision indexed access, and this is usually because the remote histogram information isn’t available to the local instance.

To be checked

As time (and versions) pass, limitations in the optimizer are removed, so it’s good to have to revisit old notes and re-run tests. Another effect of coming back to an old test is that you think of new details that might be worth checking. I’ve said in the past that “there’s no such thing as a function-based index”. If the remote index is a multi-column index that starts with one or more base columns, and only introduces the function-based bit later in the index will the local  optimizer still fail to pick it up?