"shsandeep" <sa**********@gmail.com> wrote in message
news:4c******************************@localhost.ta lkaboutdatabases.com...
Thanks Mark, that definitely solves my query.
My 2nd question would be as follows:
I am using a 'system managed' tablespace in which the tables are being
created.
No specific indexspace has been specified.
Where would the indexes be created in such a case?
And is it ok to have this?
Cheers,
San.
With SMS, the index will be placed in the same tablespaces as the table.
Since a given tablespace can only use one bufferpool, then the data and
index(es) will share the same bufferpool.
There is nothing wrong with this, especially if you have an OLTP system and
the bufferpool is relatively large compared to size of the tables and
indexes (resulting in a high bufferpool hit ratio).
If you have a data warehouse, and the it is not possible to have a high
bufferpool hit ratio because the amount of data is so large, it is sometimes
advisable to use DMS to place the index in a separate tablespace that can
use a different bufferpool, so that at least the bufferpool hit ratio can be
relatively high for the index(es).