Re: Index-Organized Table experiences
- From: Stephane Faroult <sfaroult@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 28 Apr 2004 20:58:45 +0200
Daniel Fink wrote:
>
> We are looking to implement IOTs for a couple of intersection
> entities in a 10g db. I would like to hear from those brave
> enough to actually use IOTs what is the good, the bad and the
> ugly.
>
> example:
>
> Employee (heap table)
> Project (heap table)
>
> There is a many-to-many relationship between the tables (1
> employee can be on many projects and 1 project can have many
> employees).
>
> The emp_project table is the intersection entity containing
> emp_id and project_id as the only columns. There are FK
> constraints on each of the columns. The combination of emp_id
> and project_id is unique.
>
> This situation *sounds* like the right one for an IOT,
> otherwise we would have 1 table and 2 indexes (1 on each
> column).
>
> My main concerns are:
> 1) Integrity/performance
> 2) Locking behavior (do I need to adhere to the traditional
> "index all foreign keys" rule to prevent excessive locking?)
> 3) Any especially nasty gotchas
>
> Thanks,
> Daniel
Dan,
I have mostly had disappointing experiences with IOTs. I especially
remember a case where one of the tables (around 3 million rows) looked
like the perfect case-study for IOTs. First tests were rather
encouraging, fewer LIOs, etc. However, the cruel reality of timing
contradicted the stats. The application was a little weird (investment
book valuation, biggest table involved 15 million rows, thankfully
partitioned, reports of death concurrently run by two scores of users).
What we noticed with IOTs is that the initially good throughput was
diminishing over time. An indiscreet peek at V$BH revealed that the IOT
was taking more and more place into the SGA, letting fewer and fewer
space to the rest of the data.
My feeling is that IOTs, being first and foremost indices, tend to be a
bit 'sticky' in memory and are bad memory-mates of tables (or
partitions) which are scanned.
--
Regards,
Stephane Faroult
Oriole Software
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
