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?