locally managed tablespaces

[naushi]

This is an interesting question. I would need an expert opinion as to when it is advisable to create locally managed tablespaces as opposed to dictionary managed.

I have history tables each 6 to 10 million rows and I am doing selects on these with different where clauses.

would it not be a good idea to have a real large initial extent for eah and a large next extent.

if it is better to do it locally managed please explain why.

Thanks in advance. Regards,
Naushi Hussain
Naushi.hussain@honeywell.com

 

[spenglerr]

sorry, but I do not completely understand what you mean with "historically".

If the data get not updated or deleted very often (historic data which is just kept and used for reporting) and you are only perfroming selects on them (not Inserts, Deletes, Updates) then your problems (if you have any) are not caused by dictionary tables but by missing, needed indexes.

Locally managed tablespaces are always a good choice since you won't have requests to the data dictionary when new extents are created or extents have to be requested. So the administrative overhead will be reduced and the system-tablespace-usage will decrease.

Actually in Oracle 9i locally managed tablespaces is the DEFAULT exactly due to this reasons.