RE: IOT or Cluster or neither

Adi

> * Large number of records >200million rows
> * Tables have a unique "id" for each row (generated using sequence).
> * The rows can be logically grouped per "user_id"
> * The rows are primarily accessed per "user_id"
> * The distribution of the rows is not even among "user_id" i.e. Most
> users
> have <50 rows but there are a few who have >10000 rows.
> * The tables need to have some secondary indexes.
> * The tables have "minimal" updates and "moderate" inserts.
> 
> In order to increase the performance I have the following options.
> A) IOT with primary key similar to "userid,id"

IOT makes only sense if most accesses are based on the PK. Since in your
case most accesses are performed through "user_id", it makes no sense to
use an IOT.

> B) Cluster the table on user_id (index)
> C) Cluster the table on user_id(hash)

Clusters don't support partitioning. With that amount of data, you need
partitioning.


Therefore, IMHO, your only choice is finding the right partitioning
schema. Probably the most important matter to consider are the
definition of the PK and the number of distinct values and definition
(ranges?) for "user_id"


HTH
Chris
--
http://www.freelists.org/webpage/oracle-l

Other related posts: