Re: question: chaining and space

jrl1_at_kepler.unh.edu (John R Labonte) writes:

>
>
> Does anyone know if chaining results in wasted space. I am wondering
> if an export/drop/import of a large-old-frequently-changed table would
> result in any real space saving. I would suspect that there is a fair
> amount of chaining in it.
>
> John

Chained rows not only take up extra space, but REALLY slow response time. Your INDEX still points to the original location of the beginning of the row (which means the database goes to the original block first and then follows a pointer to the new location of the row or to the rest of the row's columns). Chained rows also defeats the 'built-in cache' or readahead  feature of ORACLE where multiple blocks are read at a time. Your chained row could be in another extent at the other 'end' of your tablespace. Chaining cannot be easily detected. I've heard of queries that use a complicated formula based on averages and block format ASSumptions, but are not acurate and use as much resource as just exporting and importing! I suggest you let response-time and multiple extents trigger your defragmentation schedule. You should do the following:

1. export the table (or all of the tables in the same tablespace)
2. drop the tables
3. pre-create tables (or export with compress extents) to fit entire table into a single extent (if it will fit inside a single datafile)
4. import the tables

Something else to consider are the PCTFREE and PCTUSED parameters for your table. PCTFREE determines how much space to reserve in each block for a row to 'grow' into when updates cause columns to grow in length.

Sometimes just dropping and re-creating an index on a heavily chained table can improve response time.

>>>>>>>>>>>>>>>>>  UNIX is only a technical term, <<<<<<<<<<<<<<<<<<<<<<<
>>>>>>>>>>>>>>>>>  without almost any vowels.     <<<<<<<<<<<<<<<<<<<<<<<