Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: IOT or Cluster or neither

RE: IOT or Cluster or neither

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Sat, 19 Aug 2006 01:53:38 +0200
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAF6153EF@MSXVS04.trivadis.com>


Adi

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

You mentioned that "id" is generated through a sequence. I would never add to a primary key another attribute just because I would like to store that table in an IOT. IMHO it's just bad design.

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

I didn't mention hash partitioning ;-) Without more information about data distribution and the kind of queries you would like to optimize on it, it's simply impossible to give an advice. That said, as I wrote, IMHO only partitioning can save you.

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

Mhmm... you wrote ">10000". IMHO this is not a huge amount compared to the total number of rows stored in the table.

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Aug 18 2006 - 18:53:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US