Re: Is this a good use for an IOT type table or are there better solutions.

To be honest, I havent' used IOT's that much. Where I have implemented them, they have been lightning fast for range scans compared to a heap table.

Johnathan Lewis or one of the other super Oracle experts could probably throw in some more info. (There's another guy here "srivenu" that always seems to have the inside scoop too.) Also, I think there are few good articles on when to use IOT on asktom.oracle.com.

Once of the things, I'd be interested in hearing is if the IOT will save you space with a really large table like your's. The IOT's that I've used, ended up taking about the same amount of space as a heap table + primary key. I was a little surprised at that, but I was thinking that maybe it was because I was using them on a fairly small tables.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

colin_lyse_at_yahoo.com (colin_lyse) wrote in message news:<4061d8d8$0$16321$45beb828_at_newscene.com>...
> In article <5366fb41.0403240841.7d85d038_at_posting.google.com>, simmons_mark_at_yahoo.com (Mark) wrote:
> >Colin,
>
> >/* works good for iot */
> >SELECT * FROM TABLE ID=100 AND DATESTAMP BETWEEN SYSDATE AND
> >SYSDATE+30;
> >
> >/* works good for heap */
> >SELECT * FROM TABLE ID=100 AND DATESTAMP=SYSDATE AND HOUR=4;
> >
> >Testing always reveals the truth.
> >
> >Others here may have diffent input, but that's what I've seen.
> >
> >Again, I'd check with your DBA before pushing for this because he/she
> >may have information as to why an IOT will not work well in your
> >scenario.
> >
> found out that he has never used IOT and doesn't know how or why we would use
> them. He has used a heap/index format before and they works fine.
Received on Thu Mar 25 2004 - 11:32:54 CST