Selectivity and cardinality statistics

The optimizer determines the number of rows that will be scanned from disk when deciding on an access path for a particular table (whether to use an index or to scan the table).

• For table scans, the optimizer knows exactly the number of rows that will be scanned from disk (see Determinations of rows scanned from disk for a table scan).
• For index scans, the optimizer must estimate the number of rows that will be scanned from disk (see Estimations of rows scanned from disk for an index scan).

  Derby might be able to use cardinality statistics to make a better estimate of the number of rows that will be scanned from disk, as described in this section.

• Determinations of rows scanned from disk for a table scan
  For table scans, the optimizer does not need to estimate the number of rows that will be scanned from disk during the scan; the number of rows that will be scanned from disk will be equal to the number of rows in the table.
• Estimations of rows scanned from disk for an index scan
  When an index is available, the optimizer has to estimate the number of rows that will be scanned from disk.
• Statistics-based versus hard-wired selectivity
  Derby determines the selectivity for a WHERE clause in one of two ways.
• What are cardinality statistics?
  When Derby creates statistics for a table's index, it calculates and stores the following in the system tables.
• Working with cardinality statistics
  Cardinality statistics are gathered on the keys of an index when the index is created.