Hi,
I have a query which returns a record on a wider time range in the query
but not on the specific time range the record falls under.
SQL> select LOG_TIMESTAMP, PMTID_ENDTOENDID, LAST_MODIFIED
from trx_log where last_modified >= to_date('*25/07/2019 11:00:00*',
'dd/mm/yyyy hh24:mi:ss') and last_modified <= to_date('*27/07/2019 13:00:00*',
'dd/mm/yyyy hh24:mi:ss')
and PMTID_ENDTOENDID = '20190726XXXXXXXX010ORB01273251';
LOG_TIMESTAMP PMTID_ENDTOENDID LAST_MODIFIED
----------------------------- -------------------------------
-----------------------------
*26-JUL-19 12.52.46.998000 PM 20190726XXXXXXXX010ORB01273251 26-JUL-19
12.52.47.035000 PM*
DB02
SQL> select LOG_TIMESTAMP, PMTID_ENDTOENDID, LAST_MODIFIED
from trx_log where last_modified >= to_date('*26/07/2019 11:00:00*',
'dd/mm/yyyy hh24:mi:ss') and last_modified <= to_date('*26/07/2019 13:00:00*',
'dd/mm/yyyy hh24:mi:ss')
and PMTID_ENDTOENDID = '20190726XXXXXXXX010ORB01273251';
*no rows selected*
I am querying on the last_modified value and in the both the queries
last_modified timestamp for the record falls in the time range in the query
but one query shows the record while the other do not. The Oracle version
is 12.1.0.2 and trx_log is a huge partitioned table with 250 million+ rows.
Any clues what possibly could be the reason for this behaviour?
Thanks
Regards,
Ashish
