RE: Index-Organized Table experiences
- From: rstevenson@xxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 28 Apr 2004 10:03:13 -0500
Your gut instinct is spot on. IOT's are an excellent enhancement - one we have
made considerable use of - both in OLTP and DW environments. The scenario you
pose is the ideal situation in which to use an IOT - it negates the overhead of
scanning both table and index to get the required rows - with consequent
performance improvements.
In addition to usage as per your scenario, we also use them extensively
throughout our metadata repositories, especially for simple "lookup" data.
The one thing to watch out for is "overflow" - which maybe a real DBA can
explain better in response than I can (I'm an architect that knows enough DBA
stuff to be dangerous and that's as far as it goes!).
We're using 9i2 primarily, which allows us to place additional indices on IOTs
- mostly for referential integrity. However, we don't tend to add additional
indices unless we really need to - I figure it's a performance hit ???
Note also that they do require the use of a primary key... I think this is
still true - it's been a while ....
Regards,
Richard J Stevenson
CobbleSoft International Ltd.
"Helpdesk and Service Management Tools for a Real Database"
www.cobblesoft.com
US/Can Toll-Free: 1-866-380-6716
International: +1 315 548 5810
------------------------------------------------
On Wed, 28 Apr 2004 08:14:50 -0600, Daniel Fink <Daniel.Fink@xxxxxxx> 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
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
----------------------------------------------------------------
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:
