Separate indices for the field I want to get the MAX() and the ones in the WHERE clause My customer's database has a gigantic table (hundreds of millions of records) and a SELECT that, by cumulative use, is eating a huge chunk of processing time. Each exec takes almost a minute in average. (That's what AWR tells me, anyway.)

SELECT
    MAX(fldA)
FROM
    humongoustable
WHERE
    fldB = :B2 AND fldC = :B1


There's only one index involving the above fields, and it's on (fldC, fldA, fldB).

I suspect that isn't helping any, and there should be two separate nonunique indices; one for fldA and another for (fldB, fldC). Am I correct?
The index that will give you the most benefit is one on

fldB, fldC, fldA desc


or on

fldC, fldB, fldA desc


depending on your data. You'll need to check your data and keys to determine which column should go first.

The last column of the index is the column for which the maximum value is being computed. By including it in the index you will avoid table access. And by including the keyword desc it means that Oracle can use the index to calculate the maximum value -- it will be the first entry in the index for which the first two columns match.

In the ideal case, you will have many repeated values in the leading column of your index. In this case you might get some measurable improvement by using index key compression. In this case your create index statement will look something like

create index humongoustable_idx on humongoustable(fldB, fldC, fldA desc) compress 1;


Don't forget to refresh statistics on humongoustable after creating your new index!