pctused with index

nobleit used Ask the Experts™
Is there any reason Oracle do not permit the use of PCTUSED with indexes

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Aaron ShiloChief Database Architect


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.
Top Expert 2009
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial