Cardinality is how many rows should be returned after evaluating predicates. Selectivity is cardinality divided by the total number of input rows. DBMS uses selectivity for choosing the right index.

Density is characteristic of data in a column. It is equal to 1 divided by the number of distinct values.

Let’s consider few examples of queries with different selectivity of predicate. We will restrict on one field so the selectivity of the predicate will be equal to the density.

Data

Create a table with five indexed fields. All fields have different number of unique values.

DROP TABLE t;
CREATE TABLE t (id INT, 
                group1 INT, 
                group2 INT, 
                group3 INT, 
                group4 INT);

BEGIN
  FOR id IN 1..1000000 LOOP
    INSERT INTO t VALUES (id, 
                          dbms_random.Value(1,100000), 
                          dbms_random.Value(1,1000), 
                          dbms_random.Value(1,100), 
                          dbms_random.Value(1,10));
  END LOOP;
END;
/

commit;

CREATE INDEX t_id ON t (id);
CREATE INDEX t_group1 ON t (group1);
CREATE INDEX t_group2 ON t (group2);
CREATE INDEX t_group3 ON t (group3);
CREATE INDEX t_group4 ON t (group4);

Example 1

Query with a restriction on id with unique values. Selectivity=1/1M (high)

SELECT * FROM t WHERE id = 1; 

Execution plan. Index used.

TABLE ACCESS (BY INDEX ROWID) of T #1 TABLE (Cost=4 Card=1 Bytes=65)
  INDEX (RANGE SCAN) of T_ID INDEX Optimizer=ANALYZED (Cost=3 Card=1 Bytes=)

Example 2

Query with a restriction on group1. Selectivity=1/100K (still high)

SELECT * FROM t WHERE group1 = 1;

Execution plan. Index used.

TABLE ACCESS (BY INDEX ROWID) of T #1 TABLE (Cost=6 Card=3 Bytes=195)
  INDEX (RANGE SCAN) of T_GROUP1 INDEX Optimizer=ANALYZED (Cost=6 Card=3 Bytes=)

Example 3

Query with a restriction on group2. Selectivity=1/1K

SELECT * FROM t WHERE group2 = 1;

Execution plan. Index used.

TABLE ACCESS (BY INDEX ROWID) of T #1 TABLE (Cost=348 Card=468 Bytes=30,420)
  INDEX (RANGE SCAN) of T_GROUP2 INDEX Optimizer=ANALYZED (Cost=3 Card=468 Bytes=)

Example 4

Query with a restriction on group3. Selectivity=1/100 (low)

SELECT * FROM t WHERE group3 = 1;

Execution plan. Index has NOT been used.

TABLE ACCESS (FULL) of T #1 TABLE (Cost=795 Card=6,425 Bytes=417,625)

Example 5 (hint)

Let’s compare with the plan of the same query but using hint forcing use of the corresponding index.

                       
SELECT /*+ INDEX(t t_group3) */ * FROM t WHERE group3 = 1;

The cost is 1,774 vs 795 i.e. 2.23 times larger.

TABLE ACCESS (BY INDEX ROWID) of T #1 TABLE (Cost=1,774 Card=6,425 Bytes=417,625)
  INDEX (RANGE SCAN) of T_GROUP3 INDEX Optimizer=ANALYZED (Cost=13 Card=6,425 Bytes=)

Example 6

Just to complete picture. Here is query without restriction:

SELECT * FROM t;

Execution plan:

TABLE ACCESS (FULL) of T #1 TABLE (Cost=792 Card=1,179,200 Bytes=76,648,000)