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

could a function based index help?

oli

On Saturday 18 November 2000 16:10, RodCorderey_at_Lane-Associates.com wrote:
> 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

-- 
---

Oliver Artelt, System- und Datenbankadministration
---------------------------------------------------------------
  cubeoffice GmbH & Co.KG # jordanstrasse 7 # 39112 magdeburg
telefon: +49 (0)391 6 11 28 10 # telefax: +49 (0)391 6 11 28 19
   email: oli@cubeoffice.de # web: http://www.cubeoffice.de
---------------------------------------------------------------