Cardinality means that an execution plan generated by the optimizer is optimal for a particular dataset and is stored in the plan cache. However, this plan may not be optimal for subsequent datasets. As a result, the execution duration of a subsequent SQL query increases and the throughput decreases.
This issue may arise in the following scenario:
A table stores order information and each record has two columns: user ID and order ID. Indexes are created on the user ID and order ID columns, respectively.
You can use the following methods to solve this issue:
Refresh the plan cache. This operation clears cached execution plans and triggers hard parsing to generate new ones. However, suboptimal execution plans may still be generated.
Bind a specific execution plan: You can bind a specific plan to the target SQL query by executing the
CREATE OUTLINEstatement.
For more information, see the How to resolve the "large account" issue in SQL queries section in SQL FAQ and the Inverted-pyramid data storage section in Typical scenarios and cases.