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