Re: Is there fast way to group by MMYY ( date type)

From: Rod Corderey <RodCorderey_at_Lane-Associates.com>
Date: Sat, 18 Nov 2000 14:46:35 +0000
Message-Id: <10684.122442@fatcity.com>

Couple of small points.

Your query will always do a full scan of something because it has no where clause. But at the moment it will do a full table scan, which could be much slower than a full index scan where the index is on sub_date.

To encourage the use of the index either use a hint and experiment with the effect of changing the scan direction, or add a where clause of where sub_date < sysdate + 10000 or something like.

Your order by can be on to_date(to_char(sub_date,'YYMM'),'YYMM') desc the day number will default to 01. the result will be a true date order. Alternatively convert to a julian in the order by.

If this query is required often and the table is very large, it might be worth denormalising the month start to the table with an extra column, populated via a database trigger to the first day of the month. Then your query becomes

  select to_char(sub_month_start, 'MM/YY') date_ent,

	 to_char(sub_month_start,'YYMM')   revdate,
         count(id) count

  from subs
  where sub_month_start < sysdate +10000   group by sub_month_start
  order by sub_month_start desc

with an index on sub_month_start

hope it helps

Rod

-- 
Rod Corderey

Lane Associates
RodCorderey_at_Lane-Associates.com
http://www.Lane-Associates.com




> Eunhee lee wrote:

> 

> I'm having a hard time to group by month.

> subs table has id, and sub_date (date type) and it's huge table.

> When I group by month as follow, it's really slow. and I cannot order by month

> correctly.

> 

> select to_char(sub_date, 'MM/YY') as date_ent, to_char(sub_date,'YYMM') as revdate,

> count(id) as count

> from subs

> group by to_char(sub_date,'YYMM'), to_char sub_date,'MM/YY')

> order by to_char(sub_date,'YYMM') desc

> 

> Is there any fast way to do this without usnding to_char?

> 

> Thank you.


-- 
Rod Corderey

Lane Associates
RodCorderey_at_Lane-Associates.com

Received on Sat Nov 18 2000 - 08:46:35 CST