Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: partitioning <-> partioned views

Re: partitioning <-> partioned views

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 7 Feb 2000 18:52:57 -0000
Message-ID: <949950508.928.1.nnrp-07.9e984b29@news.demon.co.uk>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US