Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

we have a fairly small table (~25k rows) that is being heavily written to and most of the rows go away quickly (it's part of a scheduling application which stores information about the jobs in that table).

It's a third party application which does a huge amount of small transactions which result in a high contention on that table.

This is all running on a two node RAC cluser using Oracle 11gR1

We can see in the AWR reports that most of the time is spent waiting for the cluster synchronization.

We would like to (hash) partition that table in order to distribute the contention on the blocks being modified.

However the table also contains a BLOB column which is used by the application to store the state of the job. We also see a high volume of reads and writes to the LOB segment for that column.

My question is: when we partition the table, is there a way to create multiple LOB segments as well? So that there is a one-to-one relation between table partitions (i.e. segments) and LOB segments?

I didn't find anything in the manual, but as the documentation for CREATE TABLE is sooo huge I wouldn't be surprised if I overlooked something.

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

Yes, it seems you overlooked it indeed:

PARTITION BY HASH(X)
(PARTITION P1
    TABLESPACE T1
    LOB (my_lob_column) STORE AS (TABLESPACE T1_LOB  /* here */ ),
 PARTITION P2
    TABLESPACE ts2
    LOB (my_lob_column) STORE AS (TABLESPACE T2_LOB))
)
share|improve this answer
    
Great, thanks for the answer –  a_horse_with_no_name Jan 1 '12 at 14:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.