pctused with index

Is there any reason Oracle do not permit the use of PCTUSED with indexes

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aaron ShiloChief Database ArchitectCommented:

this is basicaly becouse oracle indexes dont release space.

you can easily test this.

1. create a sample table and index.
2. delete some rows from the table.
3. run this aommand : analyze index YOURINDEX validate structure;
4. view the DEL_LF_ROWS in the index_stats view.

what you will find is that when you delete rows the arent realy deleted from the index but only marked as deleted.
Slightly related, you can, however, specify pctfree for indexes, and if you either have tables that never change (static data) then it helps make your indexes more dense (block wise) by specifying a smaller or zero pctfree. If you also rebuild your indexes constantly (I don't recommend it, but some compulsive DBAs do), a small pctfree may also interest you. Small pctfree crams more rows into a block, optimizing buffer cache. Oracle's default for indexes is 10, but you can use 0 in certain cases for minor improvements.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.