Get record based on year in oracle

Question

I am creating a query to give number of days between two days based on year. Actually I have below type of date range

From Date: TO_DATE('01-Jun-2011','dd-MM-yyyy') 
To Date:   TO_DATE('31-Dec-2013','dd-MM-yyyy')

My Result should be:

   Year         Number of day
   ------------------------------
   2011           XXX
   2012           XXX
   2013           XXX

I've tried below query

WITH all_dates AS
  (SELECT start_date + LEVEL - 1 AS a_date
  FROM
    (SELECT TO_DATE ('21/03/2011', 'DD/MM/YYYY') AS start_date ,
      TO_DATE ('25/06/2013', 'DD/MM/YYYY')       AS end_date
    FROM dual
    )
    CONNECT BY LEVEL <= end_date + 1 - start_date
  )
SELECT TO_CHAR ( TRUNC (a_date, 'YEAR') , 'YYYY' ) AS YEAR,
  COUNT (*) AS num_days
FROM all_dates
WHERE a_date - TRUNC (a_date, 'IW') < 7
GROUP BY TRUNC (a_date, 'YEAR')
ORDER BY TRUNC (a_date, 'YEAR') ;

I got exact output

   Year         Number of day
   ------------------------------
   2011           286
   2012           366
   2013           176

My question is if i use connect by then query execution takes long time as i have millions of records in table and hence i don't want to use connect by clause connect by clause is creating virtual rows against the particular record.

Any help or suggestion would be greatly appreciated.

Answer 1

you can use the below function to reduce the number of virtual rows by considering only the years in between.You can check the SQLFIDDLE to check the performance.

1. First consider only the number of days between start date and the year end of that year or End date if it is in same year

2. Then consider the years in between from next year of start date to the year before the end date year

3. Finally consider the number of days from start of end date year to end date

   Hence instead of iterating for all the days between start date and end date we need to iterate only the years

   WITH all_dates AS
   (SELECT (TO_CHAR(START_DATE,'yyyy') + LEVEL - 1) YEARS_BETWEEN,start_date,end_date 
   FROM
   (SELECT TO_DATE ('21/03/2011', 'DD/MM/YYYY') AS start_date ,
   TO_DATE ('25/06/2013', 'DD/MM/YYYY')       AS end_date
   FROM dual
   )  
   CONNECT BY LEVEL <= (TO_CHAR(end_date,'yyyy')) - (TO_CHAR(start_date,'yyyy')-1)
   ) 
   SELECT DECODE(TO_CHAR(END_DATE,'yyyy'),YEARS_BETWEEN,END_DATE
   ,to_date('31-12-'||years_between,'dd-mm-yyyy'))
   - DECODE(TO_CHAR(START_DATE,'yyyy'),YEARS_BETWEEN,START_DATE
   ,to_date('01-01-'||years_between,'dd-mm-yyyy'))+1,years_between
   FROM ALL_DATES;