On what basis should someone consider developing an index for an Oracle database?
The following information comes from the "Oracle application developer's guide -- Fundamentals" documentation (chapter 5):
Use the following guidelines for determining when to create an index:
Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing:
WHERE COL_X > -9.99 *power(10,125)
is preferable to
WHERE COL_X IS NOT NULL
This is because the first uses an index on COL_X (assuming that COL_X is a numeric column).
Columns with the following characteristics are less suitable for indexing:
The size of a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block. Consult with the database administrator for assistance in determining the space required by an index.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.