Re: Index-Organized Tables (suck ???)

"JohnB" <jcj_becker_at_yahoo.com> wrote in message news:<QslG8.21559$DF2.7517463_at_twister.socal.rr.com>...
> Thanks HJR... You're right. I kept getting hung up on the fact that it was
> an 'index' segment showing up as hitting maxextents when it was really the
> underlying IOT..(I know it is a table organized as an index)
>
> CTAS is the fix action I took. I noticed that the Primary Key is generated
> by a sequence (through a trigger)... isn't this a bad candidate for an
> IOT????
>
> Thanks for your advice.
>
> John
>
>
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> news:ac48qf$1vr$1_at_lust.ihug.co.nz...
> > Why are you comparing apples and pears?
> >
> > It's pointless saying 'in Oracle 7 we used alter index rebuild' but we
> can't
> > do this in 8.0 because these are IOTs! An IOT is a table, not an index,
> > though it happens to be *structured* like an index. So you can't expect to
> > use commands specific to an index when working with a table. (Validate
> > structure is OK, because it's the b-tree-ness of the table you are
> > validating).
> >
> > It's also the case that IOTs were first introduced in 8.0, with many
> > restrictions (of which the one that is bugging you is a classic), to the
> > point where I wouldn't have touched them with a barge pole.
> >
> > With all that in mind, what would you have done in Oracle 7 to reorganise
> a
> > table? You could do a CTAS. You could try an export, truncate, import. But
> > that's about it.
> >
> > Regards
> > HJR
> >
> >
> > "JohnB" <jcj_becker_at_yahoo.com> wrote in message
> > news:UK%E8.14445$DF2.2703107_at_twister.socal.rr.com...
> > > Help please... we just took over database responsibilities for a newer
> > > version of Oracle databases (8.0.6 to be specific). We are used to
> 7.3.2...
> > > which is pretty pathetic itself.. old,old,old...
> > >
> > > In 8.0.6 database, we have a couple of index segments that repeately
> reach
> > > maxextents. Simple enough, I guess... We just alter index and increase
> > > maxextents which works for a couple of weeks.
> > >
> > > Anyways, yesterday I decided to 'analyze index <name> validate
> structure;'
> > > Queried index_stats and found out there are 81% deleted entries..
> > >
> > > In 7.3.2, we would just 'alter index <name> rebuild tablespace
> <ts_name>;
> > > Done..
> > >
> > > Well, it won't work in these particular 8.0.6 tables because they are
> > > index-organized tables.
> > >
> > > After much research the last two days, I found out in 9i databases (8i
> too,
> > > I think)... you can use 'alter table <iot_table name> move tablespace
> > > <ts_name>; to rebuild the IOT table.
> > >
> > > But that doesn't seem to work on the 8.0.6 database. Looking through the
> > > white Oracle reference guides, it looks like I might be able to 'alter
> > > table' with a partitioning_clause but I can't seem to make that work
> either.
> > >
> > > Any suggestions? Please help me, I am a beaten down little DBA that is
> tired
> > > of thumbing through all these darn books...
> > >
> > > thanks!
> > > John
> > >

John, I think the first step you should take is to determine if this table should be organized as an IOT or converted back to being a heap with a seoparate PK. IOT's were added to Oracle for meeting a specific application need such as indexing word occurrances within documents (based on ver 8 documentation), and with version 8 they are great for holding tables of relatively static data where almost all the columns are part of the PK.

But if you data is highly dynamic and the same key values are not reused then an IOT may not be the best organization to begin with.

• Mark D Powell --