Oracle 8 Tips
by Burleson Consulting
The Data Warehouse Development Life Cycle
Parallelism And Oracle Data Warehousing
CREATE TABLE ORDER (
Freelists And Oracle Parallel Server Contention
Listing 7.1 Table definition--Example 1.
STORAGE ( PCTFREE 10 PCTUSED 40 FREELISTS 3);
Here, we can infer that the table has very few updates that cause
the row length to increase because PCTFREE is only 10 percent. We
can also infer that this table will have a great deal of delete
activity, because PCTUSED is at 40 percent, thereby preventing
immediate reuse of database blocks as rows are deleted. This table
must also have a lot of insert activity, because FREELISTS is set to
three, indicating that up to three concurrent processes will be
inserting into the table.
Listing 7.2 Table definition--Example 2.
CREATE TABLE ITEM (
current_item_status varchar(200) )
STORAGE ( PCTFREE 10 PCTUSED 90 FREELISTS 1);
Here, we can infer that update operations are frequent and will
probably increase the size of the varchar columns, because PCTFREE
is set to reserve 10 percent of each block for row expansion. We can
also infer that this table has few deletes, because PCTUSED is set
to 90, making efficient use of the database blocks. Assuming that
there will not be very many deletes, these blocks would become
constantly re-added to the freelist.