You want to create a sql to list data group in year (datetime datatype). Example as below :
The table is smp.SC03F_TEMP with over 10 million rows. Usually, if there something deals with date we will use to_char function. As an alternative, you can use extract function to extract certain part of the date. In belows example, we extract the year of a column. As we can see the time taken is almost half for 10 million rows. Happy trying.
select count(*) from smp.SC03F_TEMP;
-- 10,866,921 records
select count(*), to_char(A.SC03TARIKH, 'YYYY')
from smp.SC03F_TEMP a
group by to_char(A.SC03TARIKH, 'YYYY');
-- Time taken 54sec
select count(*), extract(year from A.SC03TARIKH)
from smp.SC03F_TEMP a
group by extract(year from A.SC03TARIKH);
-- Time taken 27sec
ref : http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions050.htm
The table is smp.SC03F_TEMP with over 10 million rows. Usually, if there something deals with date we will use to_char function. As an alternative, you can use extract function to extract certain part of the date. In belows example, we extract the year of a column. As we can see the time taken is almost half for 10 million rows. Happy trying.
select count(*) from smp.SC03F_TEMP;
-- 10,866,921 records
select count(*), to_char(A.SC03TARIKH, 'YYYY')
from smp.SC03F_TEMP a
group by to_char(A.SC03TARIKH, 'YYYY');
-- Time taken 54sec
select count(*), extract(year from A.SC03TARIKH)
from smp.SC03F_TEMP a
group by extract(year from A.SC03TARIKH);
-- Time taken 27sec
ref : http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions050.htm
Comments