Separate indices for the field I want to get the MAX() and the ones in the WHERE clause
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!