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?
Hi Jonathan,
Very interesting case, I tried to reproduce it with pg 13.1. I used the standard SQL/MED (Management of External Data) feature to mimic the loopback thing (good idea to quickly test distributed queries, thanks !)
I learnt important things with your case. Most important point to consider : I can choose if the planner (optimizer) estimates remote cost. Default is FALSE and I obtain the “hash join” plan. If I set the option “use_remote_estimate” to TRUE plan seems OK and very similar to the plan obtain by Oracle CBO in the last hinted query.
Hints are provided as an extension but in this case they don’t seem to be useful. It’s a good thing since we don’t want our developers to put hints in their queries. They can choose the statistics target and they can create indexes and extended statistics if needed. I set the statistics target to 10000 but the plans and executions times were in fact the same with the default value of 100.
Regards,
Phil Florent
Comment by Phil Florent — December 3, 2020 @ 7:12 pm GMT Dec 3,2020 |