RE: Reorganizing tables

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Mon, 24 Mar 2003 14:05:14 -0700
Message-Id: <24765.322936@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C2F249.6149890E
Content-Type: text/plain

Re "I do not use the 'alter table ... move ...' command since it retains the old extent size for the very 1st extent in the new tablespace."

If you have LMT with uniform size and you move a table "up", each extent, including the first will be of the uniform size. There is no "retaining the old extent size". Eeven if you move "down", all extents, including the first, will be of the uniform size, you just get enough initially to cover whatever is requested for initial - which is why all my tables have an initial 2K, next 2K storage clause. That leaves it entirely to the LMT to allocated the necessary extents.

Alternately, you can specify a storage clause with a different, fitting initial extent in the move command.

At 11:34 AM 3/24/2003 -0800, you wrote:

>Hi Dennis,
>
>Let me try to answer part of question#1. We only deal with warehouse
>applications. So there are only inserts and updates.
>
>All tablespaces are LMTs and I use 3 extent sizes (128K, 4M and 32M).
>
>I do not mix the staging (insert and truncate) tables and the normal tables
>in the same tablespace.
>
>Once I month, I run a job for tables in 128K and 4M tablespaces to see
>whether I need to promote them to a higher extent size. If so, I export,
>drop, recreate and import in a new tablespace. I do not use the 'alter

table
>... move ...' command since it retains the old extent size for the very 1st >extent in the new tablespace.

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications.

Cette communication par courrier electronique est une communication privee a l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'etes pas le destinataire prevu, vous etes avise, par les presentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'etes pas specifiquement autorise a recevoir ce courriel ou si vous croyez l'avoir recu par erreur, veuillez en aviser l'expediteur original immediatement. Nous respectons les demandes similaires qui touchent la confidentialite des communications par courrier electronique.

------_=_NextPart_001_01C2F249.6149890E
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3DUS-ASCII">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2655.35">
<TITLE>RE: Reorganizing tables</TITLE>
</HEAD>
<BODY>

Re "I do not use the 'alter table ... move ...' =
command since it retains 
 the old extent size for the very 1st extent in the =
new tablespace."


If you have LMT with uniform size and you move a =
table "up", each extent, 
 including the first will be of the uniform size. =
There is no "retaining the 
 old extent size". Eeven if you move =
"down", all extents, including the 
 first, will be of the uniform size, you just get =
enough initially to cover 
 whatever is requested for initial - which is why all =
my tables have an 
 initial  2K, next 2K storage clause. That =
leaves it entirely to the LMT to 
 allocated the necessary extents.


Alternately, you can specify a storage clause with a =
different, fitting 
 initial extent in the move command.


At 11:34 AM 3/24/2003 -0800, you wrote:
 >Hi Dennis,
 >
 >Let me try to answer part of question#1. We only =
deal with warehouse
 >applications. So there are only inserts and =
updates.
 >
 >All tablespaces are LMTs and I use 3 extent =
sizes (128K, 4M and 32M).
 >
 >I do not mix the staging (insert and truncate) =
tables and the normal tables
 >in the same tablespace.
 >
 >Once I month, I run a job for tables in 128K and =
4M tablespaces to see
 >whether I need to promote them to a higher =
extent size. If so, I export,
 >drop, recreate and import in a new tablespace. I =
do not use the 'alter table
 >... move ...' command since it retains the old =
extent size for the very 1st
 >extent in the new tablespace.


Wolfgang Breitling
 Centrex Consulting Corporation
 <A HREF=3D"http://www.centrexcc.com" =
TARGET=3D"_blank">http://www.centrexcc.com</A>

 

          &nb= sp;           &nb=

sp;&nbsp; ********************</FONT>

This email communication is intended as a private =
communication for the sole use of the primary addressee and those =

individuals listed for copies in the original message. The information =
contained in this email is private and confidential and if you are not =
an intended recipient you are hereby notified that copying, forwarding =

or other dissemination or distribution of this communication by any = means is prohibited.  If you are not specifically authorized to = receive this email and if you believe that you received it in error = please notify the original sender immediately.  We honour similar = requests relating to the privacy of email communications.

Cette communication par courrier électronique =
est une communication privée à l'usage exclusif du = destinataire principal ainsi que des personnes dont les noms figurent = en copie.  Les renseignements contenus dans ce courriel sont = confidentiels et si vous n'êtes pas le destinataire prévu, = vous êtes avisé, par les présentes que toute = reproduction, tout transfert ou toute autre forme de diffusion de cette = communication par quelque moyen que ce soit est interdit.  Si vous = n'êtes pas spécifiquement autorisé à = recevoir ce courriel ou si vous croyez l'avoir reçu par erreur, = veuillez en aviser l'expéditeur original = immédiatement.  Nous respectons les demandes similaires qui = Received on Mon Mar 24 2003 - 15:05:14 CST