Query caching problem

Khalil

I'm using Oracle database 19c. A query in a package returns 4 records. I added a 5th record to the table but the query still returns 4 records. I had to add a condition (and 1=1) to get the correct results so I guess it is a caching problem. I'm not using result_cache and the result_cache_mode is set to manual. Any idea how to solve this serious problem?

Khalil

The two queries have different execution plans.:

• The correct one (with 1=1) uses a full table scan.
• The other one uses Or-Expansion

So maybe this is an optimizer issue.