Oldest Record discrepancy?

Question

I was trying to determine the oldest record in a table and am having conflicting results.

Scenario 1

SELECT TOP 10 * FROM Ens_Util.Log ORDER BY TimeLogged ASC

SELECT TOP 10 text, TimeLogged, TraceCat, Type FROM Ens_Util.Log ORDER BY TimeLogged ASC

Text                            TimeLogged          TraceCat    Type
Email sent via mailserv:25      2016-05-08 01:00:01 (null)      4
[Requested record not found]    2016-05-08 01:01:13 (null)      2
[Requested record not found]    2016-05-08 01:04:39 (null)      2
[Requested record not found]    2016-05-08 01:04:53 (null)      2
[Requested record not found]    2016-05-08 01:05:22 (null)      2
[Requested record not found]    2016-05-08 01:05:45 (null)      2
[Requested record not found]    2016-05-08 01:05:58 (null)      2
[Requested record not found]    2016-05-08 01:05:58 (null)      2
[Requested record not found]    2016-05-08 01:06:08 (null)      2
[Requested record not found]    2016-05-08 01:06:15 (null)      2

Scenario 2a

SELECT TOP 10 TimeLogged FROM Ens_Util.Log ORDER BY TimeLogged ASC

TimeLogged
2015-10-10 16:30:46
2015-10-10 21:15:07
2015-10-11 22:08:18
2015-10-12 21:57:00
2015-10-13 21:39:27
2015-10-14 23:40:15
2015-10-15 23:28:10
2015-10-16 23:36:52
2015-10-17 23:10:04
2015-10-18 22:32:49

Scenario 2b

SELECT MIN(TimeLogged) FROM Ens_Util.Log

2015-10-10 16:30:46

Question

What is happening here? Why the discrepancy? What is the date of the oldest record? 2016-05-08 or 2015-10-10?

Answer 1

More looks like your indices are incorrect, while in the different queries uses different indices. Just call this two commands, to purge all index, and rebuild it again, it may take a long time depends on how many logs you have.

do ##class(Ens.Util.Log).%PurgeIndices()
do ##class(Ens.Util.Log).%BuildIndices()