Share this:

Like this:

Related

The CBO Could use the ‘INDEX_ASC’ hint in order to avoid the sort (possible since you said the sort column is never null). The cost would be very high as you will have a hit on the index then on the table.

However if the table has 1000 columns, maybe it is cheaper to follow the index than load every blocks before sorting in multipass.

It’s all about cost: On the con side regarding using the index there are the additonal costs of doing the full index scan. On the pro side, we don’t have to access empty table blocks as we would with a FTS and of course we don’t need a sort step when using the index.

Another con would be a large clustering factor of the index, since we can’t expect a revisited block to be still in cache, so that would mean multiple “physical” reads for the same block when using the index.

All of this and more is considered by the CBO and whatever plan sums up to the lowest predicated cost is seleced for execution.

My small input into this topic notes that the original posting did not set the criteria that the index must be unique.
So, if the table consisted of millions of rows, but just a handful of unique IDs and a bit mapped index was used. Maybe the CBO would be inclined to favour a bit mapped index to access the table in preference to a full table scan?

So, based on your comments. What we are looking for is a scenario where the CBO would choose a B-Tree index to access every row in a large table in preference to a FTS.

The FTS would read table data based on the high water mark. So, if the big table had in the past been even bigger so big that the I/O cost in performing a FTS was greater than the index scan, then in theory there should be no reason that the index could not be selected.

My first post was a little short: I wanted to get the first lines out on the Web as quickly as possible😉

Interesting that the comments use the word “consider” in a different sense than Richard. I think what Richard means is “does the CBO think the index might be used to provide the correct answer?”. The index can be used if it contains an entry for every row in the table, and if the index order helps answer the question.

If those conditions are met, the CBO may consider an INDEX FULL SCAN or an INDEX FULL SCAN DESCENDING followed by a table access BY INDEX ROWID.

Of course, the CBO may consider this and then reject it, but that’s another story. With the FIRST_ROWS hint, I get plans that use both UNIQUE and NONUNIQUE indexes, but not BITMAP indexes (no doubt because bitmap indexes can’t be FULL SCANned).

If the table is perfectly compacted and has a perfect clustering factor (clustering factor equals cca to number of blocks of the table)
then avoiding sorting (I think it would be a disk sort for such big table) by using index may be more effective..

Besides other factors (and assumptions) already mentioned by others (and you), is it possible that CBO’s decision to (not) use index would be influenced by PGA_AGGREGATE_TARGET setting? Or to be more specific PGA_MAX_SIZE setting?
My reasoning goes as follows:
The table is really large and all the table data is being selected. This generally tends to say don’t use index but…
The index is on a NOT NULL column which means index can be used to execute the query and produce correct results.
As it is assumed that correct statistics are in place and no hints allowed, the only (??) factors that would affect optimizer’s decision whether to use index or not would be
i) The cost of sorting the data in order to take advantage of multi-block reads as opposed to cost of reading table and index with single block reads in order to avoid the final sort
ii) If it is not a unique index, I think the clustering factor may influence the optimizer decision as others mentioned above
iii) Not sure but would the fact that 11g prefers direct path reads over cache reads affect optimizer’s decision
iv) I guess the higher degree of parallelism (on table) might influence the optimizer to use full table scan instead of index scan.

Yes, it is possible. It is a cost thing – if the cost of one full table scan + sort is bigger than the cost of one index full scan + table accesses, the CBO will use the index. Otherwise, it will not, unless hinted or otherwise persuaded to do so.
Tested on 10.2 and 11.2.

As a few others have kind of said already, it depends on how the Optimizer costs out the various alternatives and which is cheapest.

FTS = FTS Cost (all I/O) + Sort Cost
Sort Cost is either just CPU if the data fits in available memory, or is CPU + I/O to do a multi-pass sort.
Available memory depends on memory settings (PGA I assume).

Index = Index Full Scan Cost (all I/O) + Data Row (all I/O)
Data Row cost depends on Clustering Factor. In the worst case with poor clustering (high Clustering Factor) it would be as many I/Os as rows in the table (10 million). In the best case with the data in order and clustered on the indexed column, the number of I/Os would be the number of blocks in the table i.e. each block would only be read once, because the rows were well clustered.

So it depends on how well clustered the data is on this column, and how much memory is available. If enough memory is available then the Full Table Scan should be cheaper than the Index, as there are less disk I/Os in total (it will use multi-block reads).

This assumes that the CPU cost calculated is lower than the extra I/Os in the alternative execution plans. With the rate of CPU increase over the years, I think this would be true.

An alternative execution strategy would be available if the table was an Index Organised Table, on this column. Then the data is stored in order in the leaf blocks of the index. So an Index Full Scan could be done, and there would be no Sort because the data would be retrieved in the correct order.

Looks like oracle optimizer did not consider unique or bitmap (obvious) for order by but when I added primary key constraint it did consider primary key index but overall cost of index scan was high compared to table scan so it decided to do full table scan.

[…] access path becomes viable. A poor (or average) CF, and using the index is just too expensive. Radoslav Golian has an excellent example in the comments on when an index with an excellent CF is chosen by the […]