Sometimes you need full control over the planning process for a specific query. You can achieve that by setting the optimizer_mode session parameter to user_guided. In this mode, the optimizer will exclude all cost-based optimization rules. This ensures that the relative order of joins and aggregations in the resulting execution plan will follow the sytnatic order defined in the SELECT blocks of your SQL query. Specifically, the following optimizations are disabled in user-guided mode:
  • Join ordering
  • Aggregate push-down and placement
  • Subquery decorrelation
  • Common sub-plan discovery
  • Redundant join removal
  • Transforming join conditions to match the best execution strategy
Consider the following example which executes a three-way join between a fact table (store_sales) and two dimension tables (item and date_dim). Both dimension tables have local predicates (item.i_manufact_id = 128 and date_dim.d_year). When the optimizer_mode is automatic, the join order depends on the estimated number of rows in the two dimension tables: since the Filter on top item produces a smaller intermediate result, the optimizer decides to join it with store_sales first, and then join this intermediate result is then joined with date_dim. However, if you believe that the better join order is to first join with date_dim and then with item, you can: (1) set optimizer_mode = user_guided, and (2) ensure that the join date_dim on (...) happens without join item on (...). 
explain(logical)
select
  date_dim.d_year,
  item.i_brand_id brand_id,
  item.i_brand brand,
  sum(ss_ext_sales_price) sum_agg
from
  store_sales
  join date_dim on (store_sales.ss_sold_date_sk = date_dim.d_date_sk)
  join item on (store_sales.ss_item_sk = item.i_item_sk)
where
  item.i_manufact_id = 128 and
  date_dim.d_year = 2000
group by
  date_dim.d_year, item.i_brand, item.i_brand_id
order by
  date_dim.d_year, sum_agg desc, brand_id
limit
  10