Optimization of SQL join (between 40M and 240M rows) and case statements query

Question

I am using Oracle SQL Developer and have this query that takes 5 input tables:

• hit4 table that has 40M rows (used in join statement)
• trx table that has 240M rows (used in join statement)
• avg_hits_pk table with 1 float value (used in case statement)
• avg_hits_npk table with 1 float value (used in case statement)
• params that is 10 rows table with parameters (used is select as statement)

and it takes an endless amount of time. Is there anything that I can do to optimize this query?

SELECT DISTINCT
  trx.trx_id,
  hit4.customer_id,
  1 as value_pattern,
  hit4.trx_date,
  trx.trx_amount,
  role,
  tiv,
  tov,
  ratio,
  number_hits,
  CASE
      WHEN segment = 'PK' THEN round((SELECT avg_hits FROM avg_hits_pk))
      ELSE round((SELECT avg_hits FROM avg_hits_npk))
  END AS avg_hits,
  (SELECT param_value FROM params WHERE param_name = 'CSTR') as CSTR,
  trx.trx_type
FROM hit4
LEFT JOIN trx
  ON hit4.customer_id = trx.customer_id AND hit4.trx_date = trx.trx_date

SOLVED

I added indexes to trx.trx_date and trx.customer_id, removed distinct from statement and filtered out hit4 table. Execution takes ~7 min. Thanks for help!

Answer 1

Further to the suggestion already given remove the SUBSELECT and see how is the performance.

    SELECT 
      trx.trx_id,
      hit4.customer_id,
      1 as value_pattern,
      hit4.trx_date,
      trx.trx_amount,
      role,
      tiv,
      tov,
      ratio,
      number_hits,
      CASE
          WHEN segment = 'PK' THEN round(avg_hits_pk.avg_hits)
          ELSE round(avg_hits_npk.avg_hits)
      END AS avg_hits,
      params.param_value as CSTR,
      trx.trx_type
    FROM hit4,avg_hits_pk,avg_hits_npk,params
    LEFT JOIN trx
      ON hit4.customer_id = trx.customer_id AND hit4.trx_date = trx.trx_date
    where params.param_name='CSTR';