As the question states, what are the benefits of doing this given the following information/constraints:
- I will not be using partitioning
- I will not be placing the tablespaces and its datafiles into different hard disk drives
- My schema has lots (maybe around 50 to 100) of small tables and there are only about 5 to 10 tables that will grow in terms of number of records
- Expected growth of these 5 to 10 tables can reach up to 100k rows but will not probably reach a million rows
I am planning to segregate these big tables into their own tablespaces instead of mixing it with the tablespace used for the rest of the tables and indexes. I am also planning to place all indexes on a separate tablespace, so the list of tablespace that my user will be using should look something like this:
Aside from being "neat and organized" with my tablespaces, are there any performance benefits that I can get from this given that I am not using partitioning and only using a single disk, in addition I am only working with a relatively small set of records?
Or should I just stick with placing everything on a single tablespace?