i have a select statement that contains hundred thousands if data, however the execution time is very slow which take longer than 15 minutes. Is the any way that i can improve the execution time for this select statement.
(select nvl(SUM(amount),0) from ca_glopen where code = a.code and acc_mth = '2016' ) ocf,
(select nvl(SUM(amount),0) from ca_glmaintrx where code = a.code and to_char(doc_date,'yyyy') = '2016' and to_char(doc_date,'yyyymm') < '201601') bcf,
(select nvl(SUM(amount),0) from ca_glmaintrx where jum_amaun > 0 and code = a.code and to_char(doc_date,'yyyymm') = '201601' ) debit,
(select nvl(SUM(amount),0) from ca_glmaintrx where jum_amaun < 0 and code = a.code and to_char(doc_date,'yyyymm') = '201601' ) credit
from ca_chartAcc a
where a.code is not null
order by to_number(a.code), to_number(levelP)
Your primary problem is that most of your subqueries use functions on your search criteria, including some awkward ones on your dates. It's much better to flip that around and explicitly qualify the expected range, by supplying actual dates (a one month range is usually a small percentage of total rows, so this is very likely to hit an index).
SELECT Chart.levelP, Chart.code, Chart.descP, COALESCE(GL_SUM.ocf, 0), COALESCE(Transactions.bcf, 0), COALESCE(Transactions.debit, 0), COALESCE(Transactions.credit, 0), FROM ca_ChartAcc Chart LEFT JOIN (SELECT code, SUM(amount) AS ocf FROM ca_GLOpen WHERE acc_mth = '2016') GL_Sum ON GL_Sum.code = Chart.code LEFT JOIN (SELECT code, SUM(amount) AS bcf, SUM(CASE WHEN amount > 0 THEN amount) AS debit, SUM(CASE WHEN amount < 0 THEN amount) AS credit, FROM ca_GLMainTrx WHERE doc_date >= TO_DATE('2016-01-01') AND doc_date < TO_DATE('2016-02-01')) Transactions ON Transactions.code = Chart.code WHERE Chart.code IS NOT NULL ORDER BY TO_NUMBER(Chart.code), TO_NUMBER(Chart.levelP)
If you only need a few codes, it may yield better results to push those values into the subqueries as well (although note that the optimizer is likely to perform this for you).
It may be possible to remove the calls to
TO_NUMBER(...) from the
ORDER BY clause; however, this depends on the format of the values, since how they were encoded may change the ordering of results.