Generates a formatted display of the actual and stored
cardinality values for specified tables and indexes. Also, if
the stored cardinality values are different from the actual
cardinality values, the RMU Analyze Cardinality command allows
you to update the stored cardinality values.
NOTE
Beginning in Oracle Rdb Version 7.0, the RMU Analyze
Cardinality command has been deprecated and might be removed
in future versions of Oracle Rdb. The features available
through this command are now available through the RMU
Collect Optimizer_Statistics command and the RMU Show
Optimizer_Statistics command.
In addition, updating cardinality information for indexes
using the RMU Analyze Cardinality command may cause poor
performance because the prefix cardinality information is
not collected.
Therefore, Oracle Corporation recommends that you use the
RMU Collect Optimizer_Statistics and RMU Show Optimizer_
Statistics commands instead of the RMU Analyze Cardinality
command.
See Collect_Optimizer_Statistics and Show Optimizer_
Statistics for information on the RMU Collect Optimizer_
Statistics and the RMU Show Optimizer_Statistics commands.
1 – Description
The actual cardinality values for tables and indexes can be
different from the stored cardinality values in your database's
RDB$SYSTEM storage area if RDB$SYSTEM has been set to read-
only access. When rows are added to or deleted from tables and
indexes after the RDB$SYSTEM storage area has been set to read-
only access, the cardinality values for these tables and indexes
are not updated.
For indexes, the cardinality value is the number of unique
entries for an index that allows duplicates. If the index is
unique, Oracle Rdb stores zero for the cardinality, and uses the
table cardinality instead. For tables, the cardinality value is
the number of rows in the table. Oracle Rdb uses the cardinality
values of indexes and tables to influence decisions made by the
optimizer. If the actual cardinality values of tables and indexes
are different from the stored cardinality values, the optimizer's
performance can be adversely affected.
When you use the SQL ALTER DATABASE statement to set the
RDB$SYSTEM storage area to read-only access for your database,
the Oracle Rdb system tables in the RDB$SYSTEM storage area are
also set to read-only access. When the Oracle Rdb system tables
are set to read-only access:
o Automatic updates to table and index cardinality are disabled.
o Manual changes made to the cardinalities to influence the
optimizer are not allowed.
o The I/O associated with the cardinality update is eliminated.
With the RMU Analyze Cardinality command, you can:
o Display the stored and actual cardinality values for the
specified tables and indexes.
o Update the stored cardinality value for a specified table
or index with either the actual value or an alternative
value of your own choosing. Oracle Corporation recommends
that you update the stored cardinality value with the actual
cardinality value. Specifying a value other than the actual
cardinality value can result in poor database performance.
2 – Format
(B)0[mRMU Analyze/Cardinality root-file-spec [table-or-index-name[,...]]
[4mCommand[m [4mQualifiers[m x [4mDefaults[m
x
/[No]Confirm x /Noconfirm
/Output = file-name x /Output = SYS$OUTPUT
/Transaction_Type=option x /Transaction_Type=Automatic
/[No]Update x /Noupdate
3 – Parameters
3.1 – root-file-spec
The name of the database root file for which you want
information. The default file extension is .rdb. This parameter
is required.
3.2 – table-or-index-name
table-or-index-name[,...]
The name of the table or index for which you want information
about cardinality. The default is all tables and all enabled
indexes. If you want information about a disabled index, you must
specify it by name.
If you do not accept the default and instead specify a table
name, the RMU Analyze Cardinality command and any qualifiers
you specify will affect only the named table; the command will
not result in a display or update (if the Update qualifier is
specified) of the indexes associated with the table.
This parameter is optional. An indirect file reference can
be used. See the Indirect-Command-Files help entry for more
information.
4 – Command Qualifiers
4.1 – Confirm
Confirm
Noconfirm
Specify the Confirm qualifier with the Update qualifier to
gain more control over the update function. When you specify
the Confirm qualifier, you are asked whether the update should
be performed for each selected table or index whose stored
cardinality value is different from its actual cardinality value.
You can respond with YES, NO, QUIT, or an alternative value for
the stored cardinality.
Specifying YES means that you want to update the stored
cardinality with the actual cardinality value. Specifying NO
means that you do not want to update the stored cardinality
value. Specifying QUIT aborts the RMU Analyze Cardinality
command, rolls back any changes you made to stored cardinalities,
and returns you to the operating system prompt. Specifying an
alternative value updates the stored cardinality value with the
alternative value.
When you specify the Noconfirm qualifier, you are not given the
option of updating stored cardinality values with an alternative
value of your own choosing. Instead, the stored cardinality
values that differ from the actual cardinality values are
automatically updated with the actual cardinality values.
The default is the Noconfirm qualifier.
The Confirm and Noconfirm qualifiers are meaningless and are
ignored if they are specified without the Update qualifier.
4.2 – Output
Output=file-name
Specifies the name of the file where output will be sent. The
default is SYS$OUTPUT. The default output file type is .lis, if
you specify a file name.
4.3 – Transaction Type
Transaction_Type=option
Allows you to specify the transaction mode for the transactions
used to perform the analyze operation. Valid options are:
o Automatic
o Read_Only
o Noread_Only
You must specify an option if you use this qualifier.
If you do not specify any form of this qualifier, the
Transaction_Type=Automatic qualifier is the default. This
qualifier specifies that Oracle RMU is to determine the
transaction mode used for the analyze operation. If any storage
area in the database (including those not accessed for the
analyze operation) has snapshots disabled, the transactions used
for the analyze operation are set to read/write mode. Otherwise,
the transactions are set to read-only mode.
The Transaction_Type=Read_Only qualifier specifies the
transactions used to perform the analyze operation be set to
read-only mode. When you explicitly set the transaction type to
read-only, snapshots need not be enabled for all storage areas
in the database, but must be enabled for those storage areas that
are analyzed. Otherwise, you receive an error and the analyze
operation fails.
You might select this option if not all storage areas have
snapshots enabled and you are analyzing objects that are stored
only in storage areas with snapshots enabled. In this case, using
the Transaction_Type=Read_Only qualifier allows you to perform
the analyze operation and impose minimal locking on other users
of the database.
The Transaction_Type=Noread_Only qualifier specifies that
the transactions used to for the analyze operation be set to
read/write mode. You might select this option if you want to
eradicate the growth of snapshot files that occurs during a read-
only transaction and are willing to incur the cost of increased
locking that occurs during a read/write transaction.
4.4 – Update
Update
Noupdate
Specify the Update qualifier to update the stored cardinality
values of tables and indexes. You can perform an update only when
the stored cardinality values differ from the actual cardinality
values. When updating cardinality values, Oracle Corporation
recommends that you update the stored cardinality values with
the actual cardinality values, not with an alternative value
of your own choosing. Specifying a value other than the actual
cardinality value can result in poor database performance. The
default is the Noupdate qualifier.
Using the Update qualifier allows you to update the stored
cardinality values of the specified tables and indexes even when
the RDB$SYSTEM storage area is designated for read-only access.
If you have set the RDB$SYSTEM storage area to read-only access,
Oracle RMU sets it to read/write during execution of the RMU
Analyze Cardinality command with the Update qualifier. Oracle RMU
resets the area to read-only when the operation completes.
If you are updating the stored cardinality for a table or index,
and a system failure occurs before the RDB$SYSTEM storage area is
changed back to read-only access, use the SQL ALTER DATABASE
statement to manually change the database back to read-only
access.
However, note that if you have set the area to read-only, the
update operation specified with the Update qualifier commences
only if the database is off line or the database is quiescent.
If you specify a table name parameter with an RMU Analyze
Cardinality command that includes the Update qualifier, the
associated indexes are not updated; you must specify each table
and index you want to be updated or accept the default (by not
specifying any table or index names) and have all items updated.
Oracle Corporation recommends that you use the Update qualifier
during offline operations or during a period of low update
activity. If you update a cardinality while it is changing
(as a result of current database activity), the end result is
unpredictable.
Specify the Noupdate qualifier when you want to display the
stored and actual cardinality values only for the specified
tables and indexes.
5 – Usage Notes
o To use the RMU Analyze Cardinality command for a database, you
must have the RMU$ANALYZE privilege in the root file ACL for
the database or the OpenVMS SYSPRV or BYPASS privilege.
o You must have the SQL ALTER privilege for the database to
update a read-only RDB$SYSTEM storage area.
o If you specify a name for the table-or-index-name parameter
that is both an index name and a table name, the RMU Analyze
Cardinality command performs the requested operation for both
the table and index.
o Although you can alter the cardinality of a unique index
using the RMU Analyze Cardinality command, it has no effect.
(A unique index has only unique keys and does not have any
duplicate keys.) Because the cardinality of a unique index and
the table it indexes are the same, Oracle Rdb uses the table
cardinality value when performing operations that involve
the cardinality of a unique index. Oracle Rdb does not use
the cardinality value stored for a unique index, nor does it
attempt to update this value as rows are stored or deleted.
o When the RMU Analyze Cardinality command is issued for a
closed database, the command executes without other users
being able to attach to the database.
6 – Examples
Example 1
The following command provides information on the cardinality for
all indexes and tables in the sample mf_personnel database:
$ RMU/ANALYZE/CARDINALITY/NOUPDATE MF_PERSONNEL.RDB /OUTPUT=CARD.LIS
Example 2
The following command provides information on the cardinality for
the EMPLOYEES table in the mf_personnel database:
$ RMU/ANALYZE/CARDINALITY/NOUPDATE MF_PERSONNEL.RDB EMPLOYEES -
_$ /OUTPUT=EMP.LIS