Help for SQL tuning - ORACLE -

i have query take data 5 huge table, please me performance tuning of query :

select decode(sign((t1.amount - nvl(t2.amount, 0)) - 4.999), 1, nvl(t2.amount, 0), t1.amount) amount_1,        t1.element_id,        t1.start_date ,        t1.amount,        nvl(t5.abrev, null) criteria,        t1.case_id ,        nvl(t5.value, null) segment,        add_months(t1.start_date, -1) invoice_date,        nvl((select sum(b.amount)              table1 a, table3 b             a.element_id = b.element_id               , b.date_invoicing < a.start_date               , t1.element_id = a.element_id),            0) amount_2   table1 t1, table2 t2, table3 t3, table4 t4, table5 t5  t1.type = 'invoice'    , t2.case_id = t3.case_id    , t2.invoicing_id = t3.invoicing_id    , t2.date_unpaid null    , t1.element_id = t3.element_id(+)    , add_months(t1.start_date, -1) <        nvl(t4.dt_fin_dt(+), sysdate)    , add_months(t1.start_date, -1) >= t4.date_creation(+)    , t1.case_id = t4.case_id(+)    , t4.segment = t5.abrev(+)    , t5.type(+) = 'criteria_type'; 

is there wrong , replaced else?
help

as others have noted, it's hard tell without looking @ execution plan.

but... things i'd concerned with:

1. the outer join table3 in main query isn't complete @tonyandrews mentioned in comment above. see "incomplete join trail" example on common errors seen when using outer-join. this means query producing wrong results, without knowing full intent of query , schema, no 1 know sure.

   updating query use ansi-style inner/[left|right] outer syntax oracle-style tablename.columnname(+) make more apparent.

2. the scalar subquery run every row , may slow (assuming table3 large). extremely slow if there's not useful index on table3.element_id , table3.date_invoicing:

   nvl((select sum(b.amount)      table1 a, table3 b      a.element_id = b.element_id        , b.date_invoicing < a.start_date        , t1.element_id = a.element_id),     0) amount_2 

   as such, i'm not seeing need include table1 again in subquery. may better refactor into:

   nvl((select sum(b.amount)      table3 b      t1.element_id = b.element_id        , b.date_invoicing < t1.start_date,     0) amount_2 

   or, may better off refactoring use analytical function (so question, oracle documentation) if criteria summing b.amount values same including them in query in first place:

   sum(b.amount) on (partition b.element_id) amount_2 

   obviously, have different criteria summing b.amount since you're joining table3 differently in main query , subquery, i'd imagine that's more factor of "incomplete join trail" purposeful design (a guess on part, can't tell intent of query code itself).