0

I came across two code snippets :

one...

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
DROP INDEX index_name;
alter TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column, column)
USING INDEX TABLESPACE tablespace_name;

Two ...

CREATE INDEX index_name ON table_name (column_name)
TABLESPACE tablespace_name;

Now I can understand the statements that are not in bold but the statements in bold are quite difficult to understand. Why are we using tablespaces, especially in this context? What is the meaning of these two statements? Can someone give me a detailed answer with examples?
Thank you!

APC
  • 144,005
  • 19
  • 170
  • 281
Nisha Rajan
  • 9
  • 1
  • 1
  • 3
  • The index [segments](https://docs.oracle.com/database/121/CNCPT/logical.htm#CNCPT89026) have to be stored in a tablespace; it isn't that strange to want to specify which one, surely? – Alex Poole Mar 01 '17 at 14:42
  • Its a basic part of the way Oracle structures the DB. Take a look at https://docs.oracle.com/cd/B19306_01/server.102/b14220/physical.htm#i5919 – BriteSponge Mar 01 '17 at 14:47

1 Answers1

4

A tablespace is a logical storage unit. Actual OS storage is defined in datafiles, and datafiles are linked to a tablespace. This means that we can deploy database objects on different servers, different OS even, without needing to know the underlying directory structure.

As for index tablespaces, indexes are physical objects and need to be stored somewhere. These days it is not as common to distinguish between Index tablespaces and table tablespaces, because modern servers are raided, striped, etc so nobody need worry about disk heads.

In fact, Oracle's built-in tablespace management is so good that we can largely forget about tablespace planning altogether. There is still a case for having separate tablespaces for e.g. read-only data, transportable tablespaces, partitioning, etc.

APC
  • 144,005
  • 19
  • 170
  • 281