Guys i have the following oracle sql query that gives me the monthwise report between the dates. Basically for nov month i want sum of values between the dates 01nov to 30 nov.
The table tha is being queried is residing in another database and accesssed using dblink. The DT columns is of NUMBER type (for ex 20101201) .The execution of the query is taking a day long and not completed. kindly suggest me , if their is any optimisation that can be suggested to my DBA on the dblink, or any tuning that can be done on the query , or rewriting the same.
SELECT /*+ PARALLEL (A 8) */
TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM')- 1,'MM'),'MONYYYY') "MONTH",
TYPE AS "TYPE", COLUMN, COUNT (DISTINCT A) AS "A_COUNT",
COUNT (COLUMN) AS NO_OF_COLS, SUM (DURATION) AS "SUM_DURATION",
SUM (COST) AS "COST" FROM **A@LN_PROD A**
WHERE DT >=TO_NUMBER(TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM')-1,'MM'),'YYYYMMDD'))
AND DT < TO_NUMBER(TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM'),'MM'),'YYYYMMDD'))
GROUP BY TYPE, COLUMN
