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

The docs say (8.1.7)

"Use index organized tables (IOTs) on tables with large, non-key columns to speed data retrieval. Because IOTs can store key column values in the indexes and non-key values in the lower leaves of the tree, applications such as those retrieving large text files, coded with a short key value, like an ISBN, might make use of the IOT feature. "

So I don't think the meaningless sequence number necessarily makes for a poor IOT candidate (though it might be an idea to use a reversible index if that is possible I haven't tried that).

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************
"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

> > >

> > >

> > >

> >

> >

>

>