Re: How to query partitioned tables?

Hello RK,

  You can do it this way:

select a.cnt + b.cnt + c.cnt
from
( select count(*) from tab1 partition(yesterday_name) p1 where p1.f2 = yesterday ) a
,( select count(*) from tab1 partition(today_name) p2 where p2.f2 = today ) b
,( select count(*) from tab1 partition(tomorrow_name) p3 where p3.f2 => tomorrow ) b

As Sybrant mentioned, if you have only three partitions in a table you can run just one query to get count.

Regards,

  Ron
  DBA Infopower
  http://www.dbainfopower.com
  Standard disclaimer:
http://www.dbainfopower.com/dbaip_advice_disclaimer.html

"RK" <rekaben_at_yahoo.com> wrote in message news:239f1935.0402130943.6c056959_at_posting.google.com...
> Please help me.
>
> I have two tables tab1 and tab2, the same definition and structure.
> Two date fields, f1 and f2 are in each of them. The (range only)
> partitions are built on f1 daily. However f2 values may fall in
> yesterday, today and tomorrow's f1 partitions.
>
> Now I know I can do something like:
>
> select count(*) from tab1 partition(yesterday_name) p1 where p1.f2 =
> yesterday;
> select count(*) from tab1 partition(today_name) p2 where p2.f2 =
> today;
> select count(*) from tab1 partition(tomorrow_name) p3 where p3.f2 =
> tomorrow;
>
> Then I add the three results together to find the final count. Can I
> do something easier than this? Conditions are: I do not have the right
> to create a view. I can not create new stored procedures. The data
> volumes are big, only the partitioned field f1 is indexed.
>
> There is also a view v1 for all tab1 and tab2 content created by the
> DBA. Different data are in tab1 and tab2. Eventually I have to sum-up
> the results for the above partitions from two tables all together. Is
> there an easy way to do all these on v1?
>
> Thanks.
>
> RK
Received on Fri Feb 13 2004 - 17:43:22 CST