Database Reference
In-Depth Information
COLTYPE$
SUBCOLTYPE$
NTAB$
REFCON$
ICOLDEP$
C_OBJ#_INTCOL# HISTGRM$
C_RG# RGROUP$
RGCHILD$
C_TOID_VERSION# RESULT$
PARAMETER$
METHOD$
ATTRIBUTE$
COLLECTION$
TYPE$
C_TS# TS$
FET$
C_USER# TSQ$
USER$
SMON_SCN_TO_TIME_AUX SMON_SCN_TIME
37 rows selected.
As you can see, most of the object-related data is stored in a single cluster (the C_OBJ# cluster): 17 tables sharing
the same block. It is mostly column-related information stored there, so all of the information about the set of
columns of a table or index is stored physically on the same block. This makes sense, as when Oracle parses a query,
it wants to have access to the data for all of the columns in the referenced table. If this data were spread all over the
place, it would take a while to get it together. Here, it is on a single block typically and readily available.
When would you use a cluster? It is easier perhaps to describe when not to use one:
If you anticipate the tables in the cluster will be modified heavily : You must be aware that an
index cluster will have certain negative performance side effects on DML performance, INSERT
statements in particular. It takes more work to manage the data in a cluster. The data has to be
put away carefully, so it takes longer to put the data away (to insert it).
If you need to perform full scans of tables in clusters : Instead of just having to full scan the data
in your table, you have to full scan the data for (possibly) many tables. There is more data to
scan through, so full scans will take longer.
If you need to partition the tables: Tables in a cluster cannot be partitioned, nor can the cluster
be partitioned.
If you believe you will frequently need to TRUNCATE and load the table : Tables in clusters cannot
be truncated. That is obvious—since the cluster stores more than one table on a block, you
must delete the rows in a cluster table.
So, if you have data that is mostly read (that does not mean “never written”; it is perfectly OK to modify cluster
tables) and read via indexes, either the cluster key index or other indexes you put on the tables in the cluster, and join
this information together frequently, a cluster would be appropriate. Look for tables that are logically related and
always used together, like the people who designed the Oracle data dictionary when they clustered all column-related
information together.
 
Search WWH ::




Custom Search