I got a couple of index questions when I was tuning some sql statements.
1. Composite index
Suppose I have a composite index idx_comp(col1, col2, col3, col4), and my query's WHERE clause is like
WHERE col1=xx and col3=xx;
In this case, is col3 still being checked during the index scan OR col1 is the only one being checked since col2 is not in the where clause?
2. composite index v.s. individual column index
How do I tell which one is performed better than the other? By execution time or what? I looked at explain plan, but the cost over there seems irrelevant to the execution time for the query to execute.
3. logical read?
Since the physical read etc could be changed when we repeatly execute the same query, does Oracle have something like 'logical read' which provide objective measurable result which won't change for the same query, but will differ between different queries?