Re: Grouping and counting records from multiple tables

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 05 Jan 2000 14:17:42 -0500
Message-ID: <jt577sogj4ksv0ljla26v2s3jt2ordb8p0@4ax.com>

A copy of this was sent to tdry_at_yet2.com (if that email address didn't require changing) On Wed, 05 Jan 2000 18:39:19 GMT, you wrote:

> I have two different tables which are holding different events using
>the date of the event. I would like to get the number of events that
>happened in each table grouped by the date. The following query works
>in most cases, but fails if only one table has an event for a given date
>and is also very expensive:
>
>select trunc(a.create_date),
> count(distinct a.pk),
> count(distinct b.pk)
> from events_a a, events_b b
> where trunc(a.create_date) = trunc(b.create_date)
> and a.create_date > sysdate-45
> group by trunc(a.create_date)
>
> Is there a better way to write this type of query?
>

It depends on the amounts of data, you can try:

select a.create_date, a.cnt, b.cnt
  from ( select trunc(create_date) create_date, count(*) cnt

           from events_a 
          where create_date > sysdate-45
          group by create_date ) a,
       ( select trunc(create_date) create_date, count(*) cnt
           from events_b
          where create_date > sysdate-45
          group by create_date ) b

  where a.create_date = b.create_date
/

that'll create the aggregates and probably use an index on CREATE_DATE on both A and B if you have one (and if it should be used) and then join these 2 small 45 row subsets.

yours is probably index scanning A and then full scanning B for each row in A (trunc(b.create_date) would remove the index usage on B) and then aggregate and count...  

> -Tim
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jan 05 2000 - 13:17:42 CST