Re: How to query partitioned tables?

On 13 Feb 2004 09:43:55 -0800, rekaben_at_yahoo.com (RK) wrote:

>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.
>

The whole thing about partitions is that it is transparent: you don't need to know where the data is located. Also partitions don't overlap. So what are you up to, other than creating a mess by explicitly naming partitions all the time.

>RK

--
Sybrand Bakker, Senior Oracle DBA