Re: partitioning <-> partioned views

You can't do it (properly) in Oracle 8. Partition views allow multi-dimensional partitioning, partitioned table allow only a single dimension.

However, you can get lucky with composite partitions.

Create a composite partition which is

    partition by range on date
    subpartition by hash on country

You need 2^N partitions in general for best distribution of hash partitions, but with a small number of countries you will probably end up with an odd scattering of countries amongst partitions.

You could 'fix' things by using

    dbms_utility.get_hash_value(country, base, limit) (see dbmsutil.sql) to choose the way you name the countries very careful (i.e.
all CAPS, all lower, or InitCap).

There is an article on this on my web-site '2-dimensional partitioning in 8.1'

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Wim Derweduwe wrote in message <389CB07B.7A5DC393_at_yucom.be>...
>Hello,
>
>I've a question about oracle.
>
>We keep for all countries data for 4 months in our database. We now
>created for every month and country a new table that has an initial
>extend just big enough to hold all the data for that country and that
>period. We got this way a partitioned view and queries go relatively
>fast if you specify country and period.
>
>But now we are moving to oracle 8i and we want to use partitioned
>views. Is there anyone that could give us some tips on how we could do
>this same concept with partitioning?
>
>Regards,
>
>
>Wim
>
Received on Mon Feb 07 2000 - 12:52:57 CST