Re: How to query partitioned tables?

Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> wrote in message news:<vqaq20dd0jusjqfmkahn418sdg2775c75j_at_4ax.com>...
> 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.
>
>
>

I doubt it is really transparent to the users. For example,

If I do

select count(*) from tab1 partition(XXX_name) p2 where p2.f2 = today;

it takes me only a few minutes to get the count results each.

If I do

select count(*) from tab1 where p2.f2 = today;

it takes me several hours to get the results.

So how do you comment on this performance difference? Remember only f1 is indexed in my case, f2 is not.

Anyway thanks for your input. Received on Fri Feb 13 2004 - 21:13:48 CST