I have a doubt about the use of oracle tablespaces. I've decided to define for each schema of the datababase two different tablespaces (one for data, and another one for indexes).
The problem is that some time has passed, and my developers had mixed the tablespaces creating indexes in the data tablespace and creating tables and partitions in the index tablespace.
I would like to know how this could impact on performance.
Thanks in advance
Best Answer
In reality, on the modern devices the impact on performance is likely to be minimal.
The old "tablespace for index, tablespace for data" mantra comes from a time when storage tended to be made up of sets of single devices. I.E. if you said that you wanted to put a tablespace on a certain disk, it went onto a particular physical disk.
Having both indexes and data on the same disk increases disk contention as Oracle can't read both index and data at the same time.
However, in most reasonably specc'd servers these days a 'disk' as far as Oracle is concerned is not necessarily a single disk. Often they are striped disk arrays. In this case there often isn't an increase in disk contention as there are multiple physical disks and IO cards in the server and the indexes and data don't always clash anyway.
So, in short - it depends on your hardware set-up.
Though, to be honest, unless you're really on top of monitoring, responding to your "top SQL" reports, have good data model designers and are building the right indexes you are much more likely to find performance improvements by learning about those things first. I'm not saying you don't do those things, but if you don't you should look to improve there first.
If you think the tablespaces issue is a problem, then (as others have said) you can just move the tables and indexes around to put them back in the right place.
Once done, a good plan would be to implement some kind of standard for how you implement table and index creates, probably using substitution variables for the names of tablespaces, most certainly supported by a scripted test that runs as part of your commit / build process to check that people aren't putting things in the wrong place.
You can then follow that up with a regular process on your demo / test server that checks for things in the wrong place and sends an e-mail to the team when it discovers something. That way you stand a chance of discovering problems before they hit the live environment.
I'd use this approach for any standard that is broken more than once or twice.
As for non-performance related implications, it can have an impact on the maintainability of your tablespaces and ensuring that the datafiles are properly configured. Even that is probably negligible on modern servers.
Is should say though - I'm not a DBA, I've just worked as a developer with Oracle a LONG time - so when it comes to maintainability, I'm no expert...