Tablespaces and Indexes 1

[jetec]

Is there a rule of thumb on how to create indexes? Should indexes have their own tablespace?

 

[SantaMufasa]

Is there a rule of thumb on how to create indexes?

Are you asking:

Q1) Is there special syntax?
A1) Yes. Does the syntax not appear in your Oracle reference manuals?

Q2) Are there particular situations for which one should create indexes?
A2) Yes:
a) If you declare either a Primary Key or Unique constraint, Oracle automatically creates indexes.
b) You should explicitly create indexes for Foreign Keys
c) You should seriously consider creating indexes for columns that appear as operands in WHERE clauses.

Should indexes have their own tablespace?

In days before disk striping, placing indexes in a tablespace separate from table data could yield performance savings by avoiding disk-head contention while simultaneously accessing index blocks and table blocks.

Striping, however, typically eliminates the need for positioning indexes in a separate tablespace from table data. I, personally, place table data and indexes in the same tablespace, grouped by application, since striping avoids disk-head contention.

Let us konw if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[jetec]

If I do not have striping, should I create a tablespace for INDEX and DATA each? and ensure that these get put on different disks? This is for an OLTP system. Should an INDEX and DATA tablesapce be created for each table?

 

[SantaMufasa]

If you have no system-level striping, then, yes, creating difference tablespaces whose files reside on different volumes (on different controlers) would provide some performance advantages.

There is certainly no need to create different tablespaces for each table and each index. Think of a tablespace as a "neighborhood". There would certainly not be a compelling reason to create a separate neighborhood for each home and yet another neighborhood for each home's garage.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[jetec]

Thanks Mufasa!!!