RE: Is this Needed?

Don

>I just checked and code tables are set to 10% while key application tables are set to 30%. I'm
>pretty sure these aren't going to be changed. I don't think there is any row chaining there.
>What are you getting at?

If you have tables with PCTFREE=30 it means that the application inserts rows and, later, updates them in a way that their size increases. Now, if you truncate that tables and re-insert the data, the result will be that you wasted 30% of the space in most of the blocks. In fact, I guess, most of the rows have already been updated by the application, i.e. they don't need free space in the blocks.

As a result the tables will be larger and therefore some execution plan may change, in fact: - full table scans will be more expensive (FTS cost is "proportional" to the number of data blocks) - index range scan will be more expensive (more data blocks lead to higher clustering factors)

Of course you could use a smaller PCTFREE. The only problem is that lot of chained or migrated rows will be generated...

Regards,
Chris

New Features Oracle Database 10g Release 2 seminars @ www.trivadis.com Italiano: Lugano (24-Nov)
Français: Genève (17-Nov)
Deutsch: Düsseldorf (23-Nov), Zürich (29-Nov), Stuttgart (13-Dec)

--
http://www.freelists.org/webpage/oracle-l