I read a lot about Oracle indexes and various types of index scans, in particular about fast full scan (ffs). My question will concern ff performance regarding various index definitions. I will describe that, as it seems to me, I know about the design of the index and the quick full scan, because there is a possibility that I have holes or errors in my understanding.
Oracle indexes are usually supported by the B-tree, where branches are pointers to a sorted range of keys. The generated keys depend on the columns specified by the index.
If I have an index in the columns (A, B), then in sorted order some leaf nodes might look like this. The key is first sorted in column A, then column B is sorted. Each of the keys indicates a row identifier in the table.
A1-B1 -> rowid 5
A1-B1 -> rowid 7
A1-B2 -> rowid 12
A1-B3 -> rowid 24
A2-B3 -> rowid 123
A2-B3 -> rowid 2412
A2-B3 -> rowid 241
A3-B1 -> rowid 234
A3-B2 -> rowid 213
Assuming we have 2 branches, branches may contain the following data:
Branch 1:
Range: A1-B1 to A2-B3
Children Leaf nodes with row ids: [5, 7, 12, 24, 123]
Branch 2:
Range: A2-B3 to A3-B2
Children Leaf nodes with row ids: [2412, 241, 234, 213]
Look ups should be just a binary search.
. , , , . / , .. , . ORDER BY, , . , , .
, :
X Y:
X is an index on columns (A, B)
Y is an index on columns (A, B, C)
, SQL ffs BOTH X Y.
select count(1) from table where A in (a1, a2) and B <= b';
ffs ?
Y "", SQL A B?
, " ". ? ? , 1 , 2 - ? , ? ?
!