Re: Partitionned tables

Correct - the Oracle9i feature "list partitioning" is exactly what is needed here:

create table part_tab (

    type varchar2(20),
    ...
)
partition by list (type) (

    partition part_1 values ('VALUE1'),
    partition part_2 values ('VALUE2'),
    ...
);

/Gollum

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:983622490.27028.0.nnrp-09.9e984b29_at_news.demon.co.uk...
>
> I've complained about this defect a couple of
> times (see my web-site). However you can
> still use range partitioning in an artificial way:
>
> e.g. values less than ('VALUE1'||chr(0)')
>
> of
> partition BB values less than ('CC'),
> partiiton CC values less than ('PP')
> where (counterintuitiviely) the boundary of a
> partition is the name of, and value you want to
> appear in the next partition.
>
> It's ugly, but it does work. There is some hint
> about 'list partitioning' in Oracle 9, which sounds
> promising, but I haven't heard any details yet.
>
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
> Publishers: Addison-Wesley
>
> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
>
>
> laurent.citton_at_socgen.com wrote in message
> <3a9b7a0b.521975410_at_192.168.48.70>...
> >
> >Hi all,
> >
> >I was previously using partition views on Oracle 7.3 and I am
> >currently migrating to Oracle 8i.
> >
> >Oracle doc says that partition views should not be used anymore, but
> >partitionned tables should be used instead.
> >
> >However, my partitions were based on a single column, each distinct
> >value of the column giving a partition. I have a limited number of
> >values for this column and therefore a limited number of partitions.
> >Inserting a row into this table is not possible if the partition has
> >not been definied first.
> >
> >The problem is that partitionned tables are based on range of values,
> >not on single values.
> >
> >In O7 it looked like
> >
> >create table t1 (id number, type varchar2(10));
> >alter table t1 add constraint c1 check (type='VALUE1');
> >create table t2 (id number, type varchar2(10));
> >alter table t2 add constraint c2 check (type='VALUE2');
> >
> >create view v as
> >select id, type from t1
> >union all
> >select id, type from t2;
> >
> >How can I create a partition table whose ranges are limited to a
> >single value ? I do not want to use hash partitions, because I need to
> >have a direct access to partitions data.
> >
> >Thanks in advance for your answers.
> >
> >Laurent
> >
> >
>
>
Received on Sat Mar 03 2001 - 07:24:01 CST