I think your synonym example does not contradict the documentation:
"When a synonym is specified in the CONTAINERS clause, the synonym must resolve to a table or a view owned by the common user issuing the statement."
In the containers clause you actually specify a TABLE owned by the common user, not a synonym.
If you do use a synonym, it should point to a table/view in the COMMON user.
Yes. My wording was crap. What I was trying to say is it doesn't stop you using synonyms to local objects in the PDBs. I've altered to wording to make my point a little clearer. :)
Thanks for the heads-up.
Mor said..."provided the object in the root container is not a synonym"
Are you sure? While I did not test - If I understand the documentation correctly I assume if the SYNONYM is on the common user, and points to a table/view on the same common user - it WILL work.
It will not work if the synonym is pointing to a local user's object or any other common user which is not the owner of the synonym.
1) If common user root synonym point to common user object and PDB common user synonyms point to common user object it works.
2) If common user root synonym point to common user object and PDB common user synonyms point to local user object it doesn't work.
So if you care about local objects, you can't use a synonym. Try it. Let me know if you get different result.
Mor said...Perhaps that's what you should write in the article :)
Your blog is my Oracle bible... keep up the awesome work.
I'm working through the examples now. They will be in the article soon. :)
Thanks for the compliments, and the suggestions. All advice welcome!
Just ran through the tests again in a consistent manner and actually option (1) I mentioned above doesn't work either. So unless I've done something wrong, only my original method works. All examples in the article now. :)
Yasin Baskan said...Hi Tim,
The two queries you used to demonstrate the CONTAINERS hint are different, one has a GROUP BY, the other does not. That is why the plans are different. The DEFAULT_PDB_HINT you used should not affect the query plan in the root, it should affect the plan for the recursive query that is run inside each PDB.
For the same query, I see the same plan with or without the hint.
Yes you are correct. The example was bad. I've changed the example to an elapsed time one, which clearly shows a difference on this small VM. Thanks for spotting my mistake! :)
DO NOT ask technical questions here! They will be deleted!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!
If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.