When am trying to run the query, it is taking 20hrs to run the query.
we done the collect stats for all the tables by giving Diagnostic collect stats
help me to improve the query performance.
SEL Col1,Col2,Col3,.... Col200 From
Table18 Left Join
(Table1 Inner Join Table2
Inner Join Table3
Inner Join Table4
Inner Join Table5
Inner Join Table6
Inner Join Table7
Inner Join Table8
Inner Join Table9
Inner Join Table10
Inner Join Table11
Inner Join Table12
Inner Join Table13
Inner Join Table14
Inner Join Table15
Inner Join Table16
Inner Join Table17
AND table18.datecol between DT.datecol and Dt.datecol1
provide ur original Query.
you are joining table1field with all other tables which will create product join and its product degradation.
the recipe is to use Explain and look for highly expensive steps (estimates). And see what you can do with them.
And use DBQL after executing the query. Particularly, the DBQLsteps - to find the long-running step(s).
Without looking at the table DDLs and EXPLAIN plain, I don't think its possible to suggest something. Share the table definition and explain plan to have a look.