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.