indexing - Oracle ignores hint for index with synonym and 2 views -

indexing - Oracle ignores hint for index with synonym and 2 views -

this query running:

select /*+ index(v_amv_plg_order_history_200_ms.orders.t0 idx_order_version_3) */ * v_amv_plg_order_history_200_ms exchange_sk = 32 , product_sk = 1000169

and uses different index 1 ordering to.

as can see, querying view v_amv_plg_order_history_200_ms, can see 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) order_date_time, sum(orders.base_volume) volume, sum(orders.base_curr_limit_price*orders.base_volume)/sum(orders.base_volume) price, orders.product_sk product_sk, orders.exchange_sk exchange_sk, orders.direction_cd direction_cd, orders.agg_unit_cd agg_unit_cd, orders.trader_key executing_representative_key, orders.account_key account_key, a.business_unit_cd business_unit_cd amv_perf_profiles_frontend.s_amv_order_version_new orders inner bring together amv_perf_profiles_frontend.s_amv_account on a.account_key = orders.account_key base_volume > 0 grouping 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 getting info using synonym s_amv_order_version_new, directs scheme, view called v_amv_order_version , refering orders, 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 ets_udm_cds_new.order_version t0 left outer bring together ets_udm_cds_new.entity t1 on t0.account_sk = t1.entity_sk left outer bring together ets_udm_cds_new.agg_unit t2 on t0.agg_unit_sk = t2.entity_sk left outer bring together ets_udm_cds_new.direction t7 on t0.direction_sk = t7.entity_sk left outer bring together ets_udm_cds_new.entity t18 on t0.local_time_zone_sk = t18.entity_sk left outer bring together ets_udm_cds_new.entity t19 on t0.trader_sk = t19.entity_sk left outer bring together ets_udm_cds_new.transaction_status t31 on t0.transaction_status_sk = t31.entity_sk;

which takes info table called order_version , refers t0 table has index called idx_order_version

the problem oracle ignores hint, , uses different index, now, have managed utilize hint create oracle utilize index wanted when querying view gets info table, time querying view gets info view gets info table. , also, sec view in line on different scheme , using synonym, perhaps why missing cuz tried many combinations of possible solutions found on google nil seems working...

i if go 1 step forwards , query straight v_amv_order_version (without synonym) works , can create oracle work index want, query works perfect:

select /*+ index(orders.t0 idx_order_version_5) */ * v_amv_order_version orders exchange_sk =32 , product_sk = 1000169

well me , our company's dba looked @ while, seems oracle bug in global hint manifestation, have created view v_amv_plg_order_history_200_ms using regular bring together rather ansi join, , 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) order_date_time, sum(orders.base_volume) volume, sum(orders.base_curr_limit_price*orders.base_volume)/sum(orders.base_volume) price, orders.product_sk product_sk, orders.exchange_sk exchange_sk, orders.direction_cd direction_cd, orders.agg_unit_cd agg_unit_cd, orders.trader_key executing_representative_key, orders.account_key account_key, a.business_unit_cd business_unit_cd amv_perf_profiles_frontend.s_amv_order_version_new orders, amv_perf_profiles_frontend.s_amv_account base_volume > 0 , a.account_key = orders.account_key grouping 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;

oracle indexing views synonym hint