0

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?

3

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!

| improve this answer | |
  • I thought Oracle could use this index for this query even if it was asc. Does it have to do with Nulls being placed last in the index, which makes the desc version more efficient? – ypercubeᵀᴹ Jul 17 '15 at 17:54
  • I know PostgreSQL can. I've never tried it on Oracle though... – Colin 't Hart Jul 17 '15 at 18:06
1

As only fldC & fldB are in the filter criteria, an index with these two columns on the left side will be right.

Try to index on (fldC, fldB, fldA), this should do the trick mostly.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.