CODEWITHSUNDEEP
sqloracle-databaseperformanceindexing
diablo1234
diablo1234

Reputation: 409

Confused about Composite Indexes and FFS

I've been reading a lot about Oracle indexes and various types of index scans, notably fast full scan (ffs). My question will be about the performance of a ffs regarding different index definitions. I'll describe what I think I know about index construction and fast full scan because chances are, I have holes or mistakes in my understanding.

Oracle's indexes are typically backed by a B-tree, where the branches are pointers to a sorted range of keys. The keys generated are dependent on the columns specified by the index.

If I have an index on columns (A, B), then in sorted order, some leaf nodes might look like this. The key is column A sorted first, then column B sorted. Each of the keys point to some row id 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, the branches might 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 just be a simple binary search.

About fast full scan: I understand that if a query only selects columns contained by the index, then we do not need to look at the table. The index acts as a miniature/skinny table, i.e. we can query the index directly without ever having to look up the table. As long as the query does not have an ORDER BY clause, we can scan the index using fast full scan. I believe fast full scan simply looks picks up multiple blocks and filters by whatever selector clause we have.

Finally here are my questions:

Consider two indexes X and Y:

X is an index on columns (A, B)
Y is an index on columns (A, B, C)

I assume the following SQL should be executed with a ffs for BOTH X and Y index.

select count(1) from table where A in (a1, a2) and B <= b';

Is there a performance difference during ffs for both of the indexes? Does index Y perform "slower" because the SQL has predicates only on A and B?

I'm also wondering what "multi block reads" mean. What is a block? Is it a section of leaf nodes? Using the example above, are all of branch 1's leaf nodes a block and all of branch 2's leaf nodes another block? Is this a multi threaded, divide and conquer read? i.e. each thread reads their own block and aggregrate the results?

Thanks!

Upvotes: 4

Views: 237

Answers (1)

Husqvik
Husqvik

Reputation: 5809

The index tree looks like you wrote only when the index is unique.

In most cases the optimizer picks the index X, simply because its size will be smaller - contains only two columns. If the indexes are big and the difference in amount of data scanned is big as well you will notice the difference.

Block is the smallest data unit in Oracle database. The default size is 8 kb (range 2 kB - 32 kB), every data object (like index) in Oracle consists of segment -> extents -> blocks. So all root, branches and leafs of the index are stored in blocks. If the number of entries is small the root block is the leaf block and there are no branches. So in bigger scale is your description correct.

If the scan is parallel, the extents are consecutive ranges of blocks so it's not a problem to define sub-ranges so the processes don't share the same data.

Multi-block read is typical when data isn't needed to be read sorted so you can read many blocks (close to the wanted) with the same effort as one, like your example query where you just count.

Upvotes: 2

Related Questions