Re: Weird query behaviour;

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_at_society.servebeer.com> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l