Why selecting max (field) from a table is as fast as select min (field) from a table, Oracle 9i

Question

I came across the ability to run select max expressions in Oracle 9i and it worked really fast.

select max(id) from audit_log; select min(id) from audit_log; 

However, when you run select min, the request seems to hang and never returns. This table has audit logs and several hundred million entries.

Explain the plan for choosing min

Explain the plan for choosing max

Answer 1

Perhaps your maximum value is in memory. If it increases rapidly, it is most likely in memory all the time. If no one addresses the minimum value, it will need to be found.

You may have encountered blocking issues. Try changing the isolation level to make sure it matters.

Your index may be corrupted. Try to restore it.

Answer 2

One possible reason is that the column index has an index, but may be null . In this case, rows with the identifier null will not be in the index . Therefore, min () cannot use an index in a column with a null value.

... so you just need alter table audit_log modify id not null;

Or you can create a new composite index with a nonzero column after the identifier. This should work, because then each line will have a record.