Why is the OCR&Vote disk group servicing SCAN name

AVG and other aggregate functions work on sets of data. The WHERE cause does not have access to the entire set, only to data for the row it is operating on. If you created your own AVG function (as a normal function and not a custom aggregate function) it would only be passed one ID value when called from the WHERE clause not the entire set of ID values.

Mezmo's solution will give you your expected results, but if you want to avoid two full table scans (assuming no indexes) you can use a windowing function like this:

SELECT * FROM (SELECT AVG(id) OVER () avgid, t.* FROM tab1 t) WHERE id > avgid;

[...] it goes against the general concept that full table scans are bad.

This has exceptions, like all general concepts. A full table scan can be less expensive than an index scan followed by table access by rowid - sometimes much less expensive.

Remember that:

• Index blocks need to be read too, in addition to the table's blocks. If the index is large (a sizable percentage of the table's size), the pressure on the SGA (and associates latches) might be noticeable
• If the sort order of the index doesn't match the way the data is stored in the actual table, the number of logical I/Os necessary to fulfill the query could be (potentially much) more than the number of LIOs to do the full table scan. (The index's clustering factor is one of the indicators you can look at to estimate this.)

I find the figures in this article Oracle clustering_factor tips explain the issue pretty well.

Essentially, if scanning via your index makes the data block I/O requests jump all over your table, the overall cost is going to be higher than if you could do large sequential reads. An FTS is more likely to use multi-block direct-path reads, bypassing the SGA entirely, which is also potentially good - no "cache thrashing", less latching.

If you have a covering index, chances are that's going to beat a full table scan all the time. If not, it's going to depend on what percentage of actual blocks (data + index) will need to be processed (the index's selectivity for that query), and how well they're "physically sorted" with respect to each-other.

As to why the optimizer is picking the "wrong" path for you here: hard to tell. Stale statistics on the index or table could be an issue like always, the estimate calculated base on the LIKE might be off for certain patterns, instance parameters could favor indexes a bit too much, ... If this is the only query that's misbehaving, and your stats are up to date, using a /*+ full */ hint doesn't sound too bad.