Data access issue with HZ_LOCATIONS table
Summary:
I am trying to troubleshoot an issue with a custom BI Publisher query. The report works just fine for some users, but for other users it never finishes and times out.
It seems the issue is related to the HZ_LOCATIONS table. The part of the query that seems to be having trouble is below. I have noted when running this query as both users and generating an Explain Plan, I get different results for "User 1" vs. "User 2".
For "User 2" there is some filter being applied, which is not involved when run as "User 1":
filter(CASE WHEN NVL("PERSONAL_ADDRESS_FLAG",'N')<>'Y' THEN "STATE" ELSE NULL END ='SC')