How to query partitioned tables?

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 - 11:43:55 CST