Re: How to partition primary key in create table script?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 19 Jul 2001 07:47:10 -0700
Message-ID: <9j6rte02t5p@drn.newsguy.com>

In article <3b5665fa_at_usenet.per.paradox.net.au>, "Howard says...
>
>Given that an in-line 'create index' command during constraint definition is
>a new feature of Oracle 9i, I suspect that what you wish to happen simply
>can't be done in earlier versions.
>

sure it can:

CREATE TABLE test
(

        id                      NUMBER(10),
        name                    VARCHAR2(10),
        output                  CLOB,
        CONSTRAINT pk_test PRIMARY KEY (id) using index LOCAL
                                            ^^^^^^^^^^^^^^^^^

)
PARTITION BY HASH(id)

(PARTITION test_p1,
 PARTITION test_p2,
 PARTITION test_p3,
 PARTITION test_p4

);

>You'll have to (I suspect) create the table without a constraint, create
>your own manually-partitioned index on ID, and then alter the table to add a
>primary key constraint 'USING INDEX nameofindexcreatedearlier'.
>
>In 9i, for what it's worth, the syntax runs
>
>create table blah
>col1 number(2) constraint pk_blah_id primary key using index
>(create index pk_blah_idx etc etc etc),
>col2 char(3));
>
>And about time too, I suspect.
>
>Regards
>HJR
>
>
>
>"Ed Wong" <ewong_at_mail.com> wrote in message
>news:a5ae1554.0107181040.4ce9f29e_at_posting.google.com...
>> After I partition my table, I found that both lob column and data is
>> partitioned, but not primary key. I am wondering how to partition
>> primary key in my create table script? Below is my script. Thanks.
>>
>> CREATE TABLE test
>> (
>> id NUMBER(10),
>> name VARCHAR2(10)
>> output CLOB,
>> CONSTRAINT pk_test PRIMARY KEY (id)
>> PCTFREE 10 STORAGE (...)
>> )
>> STORAGE (...)
>> LOB (output) STORE AS lob_test_output
>> (
>> STORAGE (...) CHUNK 32K PCTVERSION 20 NOCACHE ENABLE STORAGE IN ROW
>> )
>> PARTITION BY HASH(id)
>> (PARTITION test_p1 TABLESPACE ts_test_p1,
>> PARTITION test_p2 TABLESPACE ts_test_p2,
>> PARTITION test_p3 TABLESPACE ts_test_p3,
>> PARTITION test_p4 TABLESPACE ts_test_p4
>> );
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Thu Jul 19 2001 - 09:47:10 CDT