Oracle ignores hint for index with synonym and 2 views

Question

This is the query i am running:

select /*+ index(V_AMV_PLG_ORDER_HISTORY_200_MS.orders.T0 IDX_ORDER_VERSION_3) */ * 
from V_AMV_PLG_ORDER_HISTORY_200_MS 
where EXCHANGE_SK = 32 and PRODUCT_SK = 1000169

And it uses a different index than the one i am ordering it to.

As you can see, I am querying from the view V_AMV_PLG_ORDER_HISTORY_200_MS, you can see its sql query here:

V_AMV_PLG_ORDER_HISTORY_200_MS view SQL Query:


SELECT AMV_PERF_PROFILES_FRONTEND.AMV_PLG_GET_SEGMENT(200, orders.ORDER_GLOBAL_DATE_TIME) AS ORDER_DATE_TIME,
SUM(orders.BASE_VOLUME) AS VOLUME,
SUM(orders.BASE_CURR_LIMIT_PRICE*orders.BASE_VOLUME)/SUM(orders.BASE_VOLUME) AS PRICE,
orders.PRODUCT_SK AS PRODUCT_SK,
orders.EXCHANGE_SK AS EXCHANGE_SK,
orders.DIRECTION_CD AS DIRECTION_CD,
orders.AGG_UNIT_CD  AS AGG_UNIT_CD,
orders.TRADER_KEY   AS EXECUTING_REPRESENTATIVE_KEY,
orders.ACCOUNT_KEY  AS ACCOUNT_KEY,
a.BUSINESS_UNIT_CD AS BUSINESS_UNIT_CD
FROM AMV_PERF_PROFILES_FRONTEND.S_AMV_ORDER_VERSION_NEW orders
INNER JOIN AMV_PERF_PROFILES_FRONTEND.S_AMV_ACCOUNT a
ON a.ACCOUNT_KEY  = orders.ACCOUNT_KEY
WHERE BASE_VOLUME > 0
GROUP BY AMV_PERF_PROFILES_FRONTEND.AMV_PLG_GET_SEGMENT(200, orders.ORDER_GLOBAL_DATE_TIME),
  orders.PRODUCT_SK,
  orders.EXCHANGE_SK,
  orders.ACCOUNT_KEY,
  a.BUSINESS_UNIT_CD,
  orders.AGG_UNIT_CD,
  orders.TRADER_KEY,
  orders.DIRECTION_CD;

He is getting the data using the Synonym S_AMV_ORDER_VERSION_NEW, Which directs to another Scheme, to a view called V_AMV_ORDER_VERSION and refering to it as orders, its sql query here:

V_AMV_ORDER_VERSION view Sql query:


  SELECT T1.ENTITY_KEY ,
    T2.AGG_UNIT_CD ,
    T0.BASE_CURR_LIMIT_PRICE ,
    T7.DIRECTION_CD ,
    T0.EXCHANGE_SK,
    T0.ORDER_LOCAL_DATE_TIME ,
    T0.PRODUCT_SK,
    T18.ENTITY_KEY ,
    T19.ENTITY_KEY ,
    T0.NOTIONAL_VALUE2 ,
    T0.NOTIONAL_VALUE ,
    T0.ORDER_GLOBAL_DATE_TIME ,
    T0.BASE_VOLUME ,
    T31.TRANSACTION_STATUS_CD ,
    T0.ORDER_VERSION_KEY
  FROM ETS_UDM_CDS_NEW.ORDER_VERSION T0
  LEFT OUTER JOIN ETS_UDM_CDS_NEW.ENTITY T1
  ON T0.ACCOUNT_SK = T1.ENTITY_SK
  LEFT OUTER JOIN ETS_UDM_CDS_NEW.AGG_UNIT T2
  ON T0.AGG_UNIT_SK = T2.ENTITY_SK
  LEFT OUTER JOIN ETS_UDM_CDS_NEW.DIRECTION T7
  ON T0.DIRECTION_SK = T7.ENTITY_SK
  LEFT OUTER JOIN ETS_UDM_CDS_NEW.ENTITY T18
  ON T0.LOCAL_TIME_ZONE_SK = T18.ENTITY_SK
  LEFT OUTER JOIN ETS_UDM_CDS_NEW.ENTITY T19
  ON T0.TRADER_SK = T19.ENTITY_SK
  LEFT OUTER JOIN ETS_UDM_CDS_NEW.TRANSACTION_STATUS T31
  ON T0.TRANSACTION_STATUS_SK = T31.ENTITY_SK;

Which takes its data from a table called ORDER_VERSION and refers to it as T0 this table has an index called IDX_ORDER_VERSION

The problem is that oracle ignores my hint, And uses a different index, Now, I have managed to use a hint to make oracle use an index i wanted when i was querying a view that gets data from a table, But this time I am querying a view which gets his data from another view which gets his data from a table. And also, The second view in the line is on a different Scheme and i am using a synonym, So perhaps that is why i am missing something Cuz i tried many combinations of possible solutions i found on google but nothing seems to be working...

I would say that if i go one step forward and query directly from V_AMV_ORDER_VERSION (Without the synonym) IT works and i can make oracle work with any index i want, so this query works perfect:

select /*+ index(orders.T0 IDX_ORDER_VERSION_5) */ * from V_AMV_ORDER_VERSION orders
where EXCHANGE_SK =32 and PRODUCT_SK = 1000169

Answer 1

Well me and our company's DBA looked at it for a while, it seems like an Oracle bug in the Global Hint manifestation, We have created the view V_AMV_PLG_ORDER_HISTORY_200_MS using a regular join rather than an ANSI join, and now it works properly:

V_AMV_PLG_ORDER_HISTORY_200_MS view SQL Query:

SELECT AMV_PERF_PROFILES_FRONTEND.AMV_PLG_GET_SEGMENT(200, orders.ORDER_GLOBAL_DATE_TIME) AS ORDER_DATE_TIME,
SUM(orders.BASE_VOLUME) AS VOLUME,
SUM(orders.BASE_CURR_LIMIT_PRICE*orders.BASE_VOLUME)/SUM(orders.BASE_VOLUME) AS PRICE,
orders.PRODUCT_SK AS PRODUCT_SK,
orders.EXCHANGE_SK AS EXCHANGE_SK,
orders.DIRECTION_CD AS DIRECTION_CD,
orders.AGG_UNIT_CD  AS AGG_UNIT_CD,
orders.TRADER_KEY   AS EXECUTING_REPRESENTATIVE_KEY,
orders.ACCOUNT_KEY  AS ACCOUNT_KEY,
a.BUSINESS_UNIT_CD AS BUSINESS_UNIT_CD

FROM AMV_PERF_PROFILES_FRONTEND.S_AMV_ORDER_VERSION_NEW orders,
AMV_PERF_PROFILES_FRONTEND.S_AMV_ACCOUNT a
WHERE BASE_VOLUME > 0 AND a.ACCOUNT_KEY = orders.ACCOUNT_KEY

GROUP BY AMV_PERF_PROFILES_FRONTEND.AMV_PLG_GET_SEGMENT(200, orders.ORDER_GLOBAL_DATE_TIME),
orders.PRODUCT_SK,
orders.EXCHANGE_SK,
orders.ACCOUNT_KEY,
a.BUSINESS_UNIT_CD,
orders.AGG_UNIT_CD,
orders.TRADER_KEY,
orders.DIRECTION_CD;