Creating Tablespace guidelines please 1

[amolso]

Hello Everybody

I am in the process of creation of tablespaces for different
tables. They Broadly include
1) Masters with not more than 15 t0 20 records,no of column from 2 to 6
2) Masters including some hundred records colmns no of 15 to 30
3) Transactions tables having thousands of records no of column 5 to 30.

IF I have 10 GB Hard Disk (5 & 5 ) Partitions,
How many Tablespaces I should Create for tables as well as
Indexes ?
How the tables should be allocated for each Tablespace?
Should Index be in seperate tablespace ?
Should big tables and small tables be in separate tablespace?
Should Continuous accessing Tables be in Single Tablaespaces?
Should Related tables be in Single tablespace ?
How initiala storage to be decided ?
Please provide me above based as well as additional information about tablespaces?

Thanks in advance
Amol [sig][/sig]

 

[MikeLacey]

Amolso,

With respect to Harjinder, who sounds like he knows what he's doing to me, I'd like to add a couple of tricks to his advice that are appropriate to larger databases.

Your split of tables into types 1, 2 and 3 seems sensible to me.

Tables of type 1 (small master tables that will not grow much) could go into a tablespace of their own (STATIC). Create each table (using its initial extent) within that tablespace to be about twice as big as you think it will ever need to be. They will never get fragmented then. Set PCT_INCREASE to zero and the next extent size to 128k. Indexes on these tables would then go into their own tablespace (STATIC_IDX)

Tables of type 2 (larger master tables that, I assume, grow slowly) could also go into a tablespace of their own (MASTER). Again - create each table (using its initial extent) within that tablespace to be about twice as big as you think it will ever need to be. You will not, however, be able to avoid fragmentation in this tablespace. Set PCT_INCREASE to zero and the next extent size to 512k. Indexes on these tables would then go into their own tablespace (MASTER_IDX)

Tables of type 3 (transaction tables that grow like the clappers) *should* go into a tablespace of their own (TRANS). Create each table to allow for growth for quite a while (six months maybe) You will not be able to completely avoid fragmentation in this tablespace either, but you can get close. Set PCT_INCREASE to zero and the next extent size to 1024k. Indexes on these tables would then go into their own tablespace (TRANS_IDX)

Tablespaces have to go into their own data files. Set the data files to grow automatically in *exactly* the same steps as the tablespace they contain.

There is no performance advantage in having separate tablespaces on the same physical disk. It is easier, however, to split them out at this stage and move the data-files to other disks later on when you need to to improve performance. The same advice applies to Oracle's striping of the tablespaces as well I guess (I've never done that, always used striping at the operating system level)

Related tables should not, in my opinion, generally be in the same tablespace. These tables will *tend* to get updated at the same time as one another -- best to separate them out if you want to optimize for performance.

Group tables together based upon the way they grow initially. If you find that this will give you a "hot-spot" - a tablespace that will be continually updated - split it into two or more separate tablespaces so that the load can be spread.
[sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>Making mistakes, so you don't have to. &lt;grin&gt;[/sig]

 

[carp]

And for MY two cents, when you are putting objects in different tablespaces, you should also try to get the tablespace files on separate physical devices (particularly tables and their indexes or tables that tend to get queried and/or DML'ed in conjunction with one another).

Mike - forgive me but (1) what is a clapper and (2) how DO they grow? I assume they grow quickly, but is that vertically or horizontally? [sig][/sig]

 

[MikeLacey]

Good grief -- Americans!!!

A clapper is the the thing that goes &quot;ding&quot; inside a bell.

They don't *grow* so much as never stop going..... all they do is swing from side to side making a dreadful noise.

The expression is *really* &quot;Go like the clappers.&quot; but there you go....

Hope that's clearer.... <grin> [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>Making mistakes, so you don't have to. &lt;grin&gt;[/sig]

 

[amolso]

Thanks friends
your tips of creating tablespaces has really helped me a lot

If you can answer one additional query I will be very glad

-- When I am going to move my database from pc server to internet server and if while creating tablespaces and tables over there if
I change some of tablespace (initial next etc.) specification in table in order to improve performance which I found afterwards and
then load data , will it work ?
what other factors I will have to consider.

Amol [sig][/sig]

 

[karluk]

My understanding is that it is fairly difficult to change table parameters like initial and next extent on an existing system. The new parameters only affect how extents are allocated in the future. Existing tables already have extents allocated that follow the old parameters, so it's necessary to drop and recreate the tables in order to get space allocated the way you want.

This issue came up recently on our system. I was reading about the virtues of fixed size extents and did some investigation about how to implement them on our database. I may have missed something like a new feature of 8i, but it looked as if a mass drop and recreate would be needed. [sig][/sig]

 

[amolso]

Thank you Karluk

Your infn. is really valuable.
Can I recreate the tables on the server and allocate initial and next memory space and then load data from sqlloader in this case ?

Amol [sig][/sig]

 

[MikeLacey]

It's fairly easy to export and then import the entire database so that all objects fit into their first extent. I use this method to move databases between machines all of the time. [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>Making mistakes, so you don't have to. &lt;grin&gt;[/sig]

 

[karluk]

I don't have any experience with the sqlloader, but there is no obvious reason why it shouldn't work if you get the tables recreated with the desired parameters.

Doing an export, dropping tables, and then importing, as Mike suggests, is an easy way to get all the data in a table into one extent. However that is not the recommendation of the article I was reading (&quot;How to Stop Defragmenting and Start Living&quot; by Himatsingka and Loaiza). They suggest to make all data extent sizes 160k, 5120k or 160m, depending on the size of the tables. Don't go to the next higher extent size unless the table contains more than 1024 extents. They say that Oracle gets very little advantage from using fewer extents, but a big advantage in eliminating disk fragmentation by having all the extents in a tablespace be the same size.

That means a simple export followed by an import isn't exactly what is needed. I would need to generate accurate table creation scripts and then manually modify the space allocation parameters. (Better yet eliminate the space allocation parameters on the table create statements and use the tablespace defaults.) Then I could export the table data, drop the tables, recreate them from script, and do an import using &quot;ignore=y&quot;. Your idea of using sqlloader would probably also work.

One way of generating table creation scripts is to use import with the indexfile='filename' option. [sig][/sig]

 

[matrixcd]

This conversation sound like you are try use SQL server
theory on and Oracle database. One thing you should consider
while you attempting to strip your tablespace for performance. Is to ask your self if your are use RAID and if so it dose matter how you display your table because they will still cause contention... [sig][/sig]

 

[amolso]

Thanks matrixcd
Can you be specific on RAID and cases where the tables will cause contention as I am not aware of certain theoretical things. ALso if you could possibly refer to my recent question subjected 'Tablespaces again please'
All of you are requested to reply
Amol [sig][/sig]