RDBPROD: Cardinality Drift and Optimizer Strategy
(Doc ID 1620148.1)
Last updated on FEBRUARY 05, 2020
Applies to:Oracle Rdb Server on OpenVMS - Version 7.0 and later
HP OpenVMS Alpha
HP OpenVMS Itanium
HP OpenVMS VAX
Several cardinality values are stored in the database system tables, giving the estimated number of rows in a table, the estimated number of unique rows in an index, and the estimated number of unique values for each segment of an index. The algorithm to update these estimates changed in Oracle Rdb 7.0.
The Oracle Rdb optimizer uses these stored, estimated cardinalities when choosing the retrieval strategy, for example Index A or Index B, sequential or indexed. Inaccurate cardinalities may lead to a less optimal strategy being chosen. Often there are reported inconsistencies in the performance of queries, updates or deletes against an Oracle Rdb database. In some cases the queries will take a few seconds to execute. In other cases the same queries will take many minutes to execute, sometimes a half an hour or more.
This article discusses the circumstances under which the estimated cardinality can become inaccurate, and recommends some regular preventative maintenance.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document