Composite Index
In a composite index, the selected columns should be chosen wisely as this impacts the index performance. The more the columns of a composite index are used in the WHERE clause , the faster would be the execution. The rule is most commonly used column should do first in the composite index.
---------
Cardinality
This is the measure of distinct values of a set. And in terms of database, cardinality is defined as the number of distinct rows returned by a query.
-----------
HINTS -
ALL_ROWS- It instructs the optimizer to fetch the last row of the result-set as fast as possible. non-interactive, batch mode type of application would benefit from this.
FIRST_ROWS - It instructs the optimizer to fetch the first row to the client as fast as possible. An interactive, typical end-user application would benefit from it.
