Large vs. Small Tablespaces?

Hi,

Right now we have 4 large tablespaces. 2 of those tablespaces are index tablespaces. All of the table data is stored in the other 2 tablespaces. The largest 'data' tablespace contains 50 ~500 MB datafiles and the largest index tablespace contains 20 ~500 MB datafiles.

I'm in the midst of implementing a hot backup script and due to the fact that I need to put the tablespaces into backup mode, backup the datafiles and then reset the state of the tablespace to normal... I am wondering if it would be beneficial if I segmented my tables across more smaller tablespaces (even though they would be on the same physical disks that they are on now), in order to minimize the amount of time each tablespace has to be in backup mode since there would be fewer files to backup for each tablespace.

Is this a sound idea? Will there by any issues? For example if scenario 1 has Table A and Table B are in tablespace 'USERS' and scenario 2 has Table A in tablespace 'USERSA' and Table B is in tablespace 'USERSB'... and both scenarios have the datafiles for all tablespaces in question on the same physical disk. Assuming there are queries that join table A to Table B, will one of the above scenarios perform better than the other? Are there any other issues with using this approach that I might be concerned about?

Thanks,
Gavin Received on Wed Jan 09 2002 - 09:17:05 CST