Databases Reference
In-Depth Information
datafile '/ora02/DWREP/reporting_index01.dbf'
size 500M
extent management local
uniform size 128K
segment space management auto
nologging;
We prefer to use uniform extent sizes because that ensures that all extents within the tablespace will
be of the same size, which reduces fragmentation as objects are created and dropped. The ASSM feature
allows Oracle to automatically manage many storage attributes, which previously had to manually
monitored and maintained by the DBA.
Inheriting Storage Parameters from the Tablespace
When creating a table or an index, there are a few tablespace-related technical details to be aware of. For
example, if you don't specify storage parameters when creating tables and indexes, then the table and
index inherit storage parameters from the tablespace. This is the desired behavior in most
circumstances. This saves you from having to manually specify these parameters. If you need to create
an object with different storage parameters from its tablespace, then you can do so within the
CREATE
TABLE/INDEX
statement.
Also, keep in mind if you don't explicitly specify a tablespace, by default tables and indexes are
created in the default tablespace for the user. This is acceptable for development and test environments.
For production environments, you should consider explicitly naming tablespaces in the
CREATE
TABLE/INDEX
statements.
Naming Standards
When you're creating and managing indexes, it's highly desirable to develop some standards regarding
naming. Consider the following motives:
•
Diagnosing issues is simplified when error messages contain information that
indicates the table, index type, and so on.
Reports that display index information are more easily grouped and therefore are
more readable and make it easier to spot patterns and issues.
Given those needs, here are some sample index-naming guidelines:
•
Primary-key index names should contain the table name and a suffix such as
_PK
.
•
Unique-key index names should contain the table name and a suffix such as
_UKN
,
where
N
is a number.
•
•
Indexes on foreign-key columns should contain the foreign-key table and a suffix
such as
_FKN
, where
N
is a number.
•
For indexes that aren't used for constraints, use the table name and a suffix such
as
_IDXN
, where
N
is a number.
•
Function-based index names should contain the table name and a suffix such as
_FCN
, where
N
is a number.
