I had some issues when joining local tables with views over a database link [9iR2]. Eventually, using a 10053 event, I found that limited information comes from the remote database about its view. It has an estimated cardinality (based on the last table in the FROM clause of that view) and doesn't go to the level of detail of what tables make up the view, their indexes, stats etc.

As such, the optimizer MAY come up with sub-optimal plans and send poorly chosen queries to the remote db.

When the view was moved to the local database, the local optimizer knows exactly which remote tables are used, and gets cardinality,index information etc from the remote database. Then it is more likely to come up with a better query plan.

If you can't move the view/query the remote tables directly, flipping the driving site to the remote database will mean it can optimizer the plan using its knowledge of the view and its underlying tables plus whatever the local database tells it about the local tables.

[Personally, I feel that if your application is regularly accessing a database link, you should look at merging the databases or replicating the table(s).]


Example details :

On remote database :
create table temp_objects as select * from user_objects;
create index t_obj_i on temp_objects (object_name);
create view view_temp_obj as select * from temp_objects;

On local database:
create table local_obj as select * from user_objects;

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1'

explain plan set statement_id='123' into PLAN_TABLE For
select * from temp_objects@dblink r, local_obj l
where l.object_name = r.object_name;

explain plan set statement_id='456' into PLAN_TABLE For
select * from view_temp_obj@isadgms r, local_obj l
where l.object_name = r.object_name

The trace file shows that when querying the remote table directly, the index information is present.
When querying the view, the index is invisible.
Extract :

***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats Table: LOCAL_OBJ Alias: L
TOTAL :: CDN: 791 NBLKS: 13 AVG_ROW_LEN: 84
Column: OBJECT_NAM Col#: 1 Table: LOCAL_OBJ Alias: L
NO STATISTICS (using defaults)
NDV: 25 NULLS: 0 DENS: 4.0455e-02
NO HISTOGRAM: #BKT: 0 #VAL: 0
Column: OBJECT_NAM Col#: 1 Table: LOCAL_OBJ Alias: L
NO STATISTICS (using defaults)
NDV: 25 NULLS: 0 DENS: 4.0455e-02
NO HISTOGRAM: #BKT: 0 #VAL: 0
***********************
Table stats Table: TEMP_OBJECTS Alias: R
TOTAL :: (NOT ANALYZED) CDN: 327 NBLKS: 4 AVG_ROW_LEN: 100
Column: OBJECT_NAM Col#: 1 Table: TEMP_OBJECTS Alias: R
NO STATISTICS (using defaults)
NDV: 10 NULLS: 0 DENS: 9.7859e-02
NO HISTOGRAM: #BKT: 0 #VAL: 0
Column: OBJECT_NAM Col#: 1 Table: TEMP_OBJECTS Alias: R
NO STATISTICS (using defaults)
NDV: 10 NULLS: 0 DENS: 9.7859e-02
NO HISTOGRAM: #BKT: 0 #VAL: 0
-- Index stats
INDEX NAME: 0 COL#: 1
TOTAL :: LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 800
_OPTIMIZER_PERCENT_PARALLEL = 0

***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats Table: LOCAL_OBJ Alias: L
TOTAL :: CDN: 791 NBLKS: 13 AVG_ROW_LEN: 84
Column: OBJECT_NAM Col#: 1 Table: LOCAL_OBJ Alias: L
NO STATISTICS (using defaults)
NDV: 25 NULLS: 0 DENS: 4.0455e-02
NO HISTOGRAM: #BKT: 0 #VAL: 0
Column: OBJECT_NAM Col#: 1 Table: LOCAL_OBJ Alias: L
NO STATISTICS (using defaults)
NDV: 25 NULLS: 0 DENS: 4.0455e-02
NO HISTOGRAM: #BKT: 0 #VAL: 0
***********************
Table stats Table: VIEW_TEMP_OBJ Alias: R
TOTAL :: (NOT ANALYZED) CDN: 327 NBLKS: 4 AVG_ROW_LEN: 100
Column: OBJECT_NAM Col#: 1 Table: VIEW_TEMP_OBJ Alias: R
NO STATISTICS (using defaults)
NDV: 10 NULLS: 0 DENS: 9.7859e-02
NO HISTOGRAM: #BKT: 0 #VAL: 0
Column: OBJECT_NAM Col#: 1 Table: VIEW_TEMP_OBJ Alias: R
NO STATISTICS (using defaults)
NDV: 10 NULLS: 0 DENS: 9.7859e-02
NO HISTOGRAM: #BKT: 0 #VAL: 0
_OPTIMIZER_PERCENT_PARALLEL = 0

PS. Forgot to add, to generate the 10053 trace the local table needed to be analyzed.

Hi Tom,

For your answer in the original question, I do not know which plan shows the use of remote index. Could you tell me which one can use the remote index (is case 2)? Which statement in the plan show the remote index is being used?

Moreover, the original question mentioned that in rule-based database. However, I found that the optimizer mode in the plan is using choose. Does this mean that the remote index can't be used if the optimizer mode is rule?

Thanks
Brian

Hi Tom,

Sorry that I am still not fully understand your answer. So do you mean that both cases can use the remote index? i.e.

4 SERIAL_FROM_REMOTE SELECT "X","Y" FROM "T" "T2" WHERE :1="X"

2 SERIAL_FROM_REMOTE SELECT "X","Y" FROM "T" "T2" WHERE "X"=55

Thanks
Brian

Hi Tom,

Thanks for your examples and answers.

Brian

And one question...

Why the fonts of this question is BOLD? Is this very important ;) and how to do that?

Brian