Re: How to partition only LOB column but not data/index?

In article <a5ae1554.0107171505.4e036b35_at_posting.google.com>, ewong_at_mail.com says...
>
>I am trying to have a 100GB table partitioned. I want to have the
>regular column and index on tablespaces user_data and user_index
>tablespace respectively. With the clob column, I want to create hash
>partition on tablespaces user_lob_p1, user_lob_p2, user_lob_p3 and
>user_lob_p4. But I notice that the index goes to the index
>tablespace, but the table as well as clob goes into the partition
>tablespaces. I would like to know how to separate table and it's
>clob. This is what I do -
>

you cannot, from the server concepts:

Partitioning of Tables with LOB Columns

Tables that contain LOB columns can be partitioned. However, a partitioning key cannot contain a LOB column. The LOB data and LOB index segments of a LOB column are equipartitioned with the base table.

http://technet.oracle.com/doc/oracle8i_816/server.816/a76965/c09parti.htm#441333

LOB data is always equi-partitioned with the base table itself.

You must hash partition the base table as well.

>Create table test
>(id number(10),
> name varchar2(10),
> output CLOB,
> constraint pk_test primary key (id) using index tablespace user_index
>storage (...)
>)
>tablespace user_data storage (...)
>LOB (output) store as lob_test
>(storage (...))
>partition by HASH(id)
>(partition test_lob_p1 tablespace user_lob_p1,
> partition test_lob_p2 tablespace user_lob_p2,
> partition test_lob_p3 tablespace user_lob_p3,
> partition test_lob_p4 tablespace user_lob_p4
>);
>
>Thanks.

--
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