Re: IOT or Cluster or neither
- From: Aditya Alurkar <aalurkar@xxxxxxxxxxxx>
- To: ora-list <Oracle-L@xxxxxxxxxxxxx>
- Date: Fri, 18 Aug 2006 16:41:35 -0700
On 8/18/06 4:32 PM, "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
wrote:
Thank you for your comments..
> 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.
>
If my PK is user_id,id wont the index be used for queries of type "where
user_id=<value>" ? My understanding was that since the PK starts with
user_id that it would use the PK index.
>> 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"
>
I could partition using hash of user_id but this does not solve my problem,
i.e. Wanting to "group" rows for a user together to reduce IO. With
partitioning for users who have larger number of rows the partition would
not only be large but within the partition the rows for a user would not
necessarily live together on disk. Also if I was to partition using hash on
user_id I would not be able to dictate that the users with more rows should
not end up on the same partition. And partitioning on "key" (user_id) where
the distribution of the data over the entire range of the key is likely to
change may be a tough one to plan for.
>
> HTH
> Chris
--
Adi Alurkar
aalurkar@xxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
