How did you load the table with these rows? Any chance you did exchange
partition without validation so they don’t live in the partition they
should? Assuming this column is the partition key.
You can check what partition they’re in by getting the partition object Id
by selecting dbms_mview.pmarker(rowid) in the query that can find them.
Also worth seeing how these timestamps cast to dates, perhaps there’s some
timezone difference in effect? Include Cast(last_modified as date) in your
query and that should point out any issues there.
Hope this helps,
Andy
On Wed, 31 Jul 2019 at 18:14, Rich J <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>
wrote:
On 2019/07/31 11:32, Ashish Lunawat wrote:
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*
The LAST_MODIFIED column is in a format that suggests one of the TIMESTAMP
datatypes. Do you get the same results using TO_TIMESTAMP instead of
TO_CHAR? Or, could timezone be a factor that's not being displayed in your
results but is in the datatype and is different than the client where the
query is running?
Rich
