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

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!

CodePudding user response：

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';