The principle of Oracle composite Index design--prefix and optional

Prefix nature:

The prefix of a composite index is that the index is used only if the first field of the composite index appears in the predicate condition of the SQL statement.
If the composite index is (ename,job,mgr), you can use a composite index as long as the first field ename appears in the predicate condition, otherwise it will not be used.
The only exception is the skip Scan index, which is automatically split into two composite indexes if Oracle discovers that the first field has a very small value. such as a composite index (GENDER,ENAME,JOB,MGR), because the first field gender has only two values: male and female, so Oracle splits the index into (' Male ', ename,job,mgr) and (' Female ', ename,job,mgr) Two composite indexes, so that the composite index can be used even if gender does not appear in a predicate condition.

Optional:

Oracle recommends that composite indexes be arranged in terms of field availability (that is, the number of values), because the more field values, the more optional, the fewer records are positioned, and the higher the query efficiency.

The principle of Oracle composite Index design--prefix and optional