Oracle Database Discussions

Hi,

Here is the problem with DB links

DB_TEST1: 9.2.0.5 -default Optimiaer_mode=RULE,
DB_TEST2: 11.0.1.7- Optimiaer_mode=ALL_ROWS

on DB_TEST2 database has two sysnonyms TB1,TB2 points to DB1_TEST1 tables TB1,TB2

CASE1:
======
Execute below query on DB2_TEST:
SELECT /*+ use_nl(TB1  TB2) */
     max(TB1.SDATE)
    FROM TB1  TB1,
    TB2  TB2
    where TB1.ID= 10000
  and TB1.ID=TB2.ID;

The above query execution on DB1_TEST as

SELECT /*+ USE_NL ("A1") USE_NL ("A2") */ MAX("A2"."SDATE") FROM "USER1"."TB1" "A2","USER1"."TB2" "A1" WHERE "A2"."ID"=10000 AND "A2"."ID"="A1"."ID"


CASE2;
====
Execute below query on DB2_TEST:
 SELECT /*+ ALL_ROWS */
     max(TB1.SDATE)
    FROM TB1  TB1,
    TB2  TB2
    where TB1.ID= 10000
  and TB1.ID=TB2.ID

The above query execution on DB1_TEST as

SELECT MAX("A2"."SDATE") FROM "USER1"."TB1" "A2","USER1"."TB2" "A1" WHERE "A2"."ID"=10000 AND "A2"."ID"="A1"."ID"      --> MISSING *ALL_ROWS*  hint

Please explain the reason why for hints of type "Approaches and Goals"(ALL_ROWS, FIRST_ROWS...etc) is not working from db link and other hints like USE_HASH or FULL working fine.

Thanks
Vijay