Weird query behaviour;

From: Ashish Lunawat <ashish.lunawat_at_gmail.com>
Date: Thu, 1 Aug 2019 00:32:09 +0800
Message-ID: <CAEzAyeB4_rDLrUPcm_02RYYormW-OLR3jSG+Ma3kVyWjjxuqdQ_at_mail.gmail.com>



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

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jul 31 2019 - 18:32:09 CEST

Original text of this message