Database Reference
In-Depth Information
Specialized Oracle Database Objects . Clustering, presorting, and
physical preconstruction of data. The objective is to avoid repeating
the same tasks, targeting and tuning hard-hitting SQL code.
Clusters . Duplicate the most commonly used indexing plus part
of data column values together in the same place physically, in the
desired order. Retrieving from a cluster avoids costly joins and
conflict with highly concurrent source tables.
Index-Organized Tables . A table is constructed including both
index and data columns in the same physical space. The table
becomes both the index and the data because the table is con-
structed as a sorted binary tree, rather than just a heap or pile of
unorganized bits and pieces.
Materialized Views . Duplicates, preconstructs, and stores the results
of grouping SQL statements avoiding repetitive SQL. Materialized
views “materialize” or precreate reusable data buckets by storing data
physically separated from source tables.
Note: Views are overlays and not duplications of data and will interfere
with underlying source tables. Views often cause far more in the way of per-
formance problems than the application design issues they ease.
Copy Columns between Tables . Make copies of columns between
tables not directly related to each other. This can help avoid multiple
table joins between two tables where other tables must be passed
through in order to join the two desired tables.
Place Summary Columns in Parent Tables . This can help avoid
costly grouping joins, but real-time updates can cause serious prob-
lems with hot blocks.
Note: A hot block is a very busy part of the database accessed much too
often by many different sessions.
Separate Inactive from Active Data . Physical separation of historical
and perhaps seldom used or completely unnecessary data is often
ignored by data model designs. Avoid searching through data that is
no longer used in order to reduce the amount of physical space
searched through. Historical data can often be destroyed or trans-
ferred to a data warehouse or backups.
Search WWH ::




Custom Search