Re: use of tablespaces in oracle

>I have a question on the use of tablespaces in Oracle. We are developing
>some new applications in Oracle, and being new to the product have gone
>to Oracle training. Unfortunatly, we have gone at different times and
>recived contradictory advice from the instructors. In this case one
>instructor said to create a separate tablespace for each table in the
>database. The other said that tablespaces are logical areas and that the
>tables should be grouped in tablespaces based on how they are used. This
>instructor also said that indexes should be placed in a single tablespace
>to reduce the administration effort due to the high degree of
>fragmentation caused by indexes.
>
>I would appreciate any advice from experienced Oracle administrators.
>Thanks
>
>prompom_at_mcln.unisysgsg.com
>
>

The second bit of advice that u received was more correct. But the best way to set up tablespaces is very system dependant. Indexes are often put into a seperate tablespace for system performance. This way the tablespace files can be kept on a seperate disk, this way when you are readinf the table the assosciated indexes are NOT on the same drive. Less head movement meands better performance. usually tablespaces are used for common tables that belong to the same application. But you must be aware of which table grow or shrink the most. code tables and the like could be inthere own tablespace and they counld be created to just fit. While the others couuld be given room to expand ( see PCTINCREASE and INITIAL and NEXT) from my experience of late, most systems have a hand full of tables that are thecore tables. They could be in their own spaces ( maybe more than 1 per tablespace but u get the idea) and u should also set the INITIAL extent for these tables large enough to fit most of the table. Less fragmentation in the segments and extenets the btter the performance

tbest_at_frontier.canrem.com Received on Thu Aug 10 1995 - 00:00:00 CEST