This content has been marked as final. Show 2 replies
1 person found this helpful
user8941550 wrote:Ok, I was following you to that point, but what do you mean the query never gets executed?
There are two schemas Schema A and Schema B
Table-> XML_TABLE is in Schema A.
If I create a view V1 with the following query:
FROM XML_TABLE t
WHERE XMLEXISTS (
'declare namespace nmsp ="name:namespace"; fn:collection("oradb:/PUBLIC/Table2")/ROW[ColumnName=$d/nmsp:Colmn2/nmsp:id]'
PASSING t.textxml AS "d")
My XML Indexes are used and output is fast.
BUT in Schema B also I have a synonym for table XML_TABLE pointing to the table in Schema A.
And I create a similar view v1 in schema B also using the same query as above.
BUT this doesn't use indexes. I can see that in the plan and also query never gets executed.
So IS IT correct to have table in schema A, Synonym in Schema B for this table and View in Schema B for this.Well, it's not incorrect. Perhaps more tidy to just have the view defined in schema A along with the table, if it's going to be used in schema A as well, and then have a synonym for schema B to use that view.
How should I make it workPerhaps you could post an example of your table with some example data for people to use, along with the views and statement to set up the synonyms, so it can be reproduced by others to see what's going on. Also, post the explain plans you're referring to.
Should I remove the view to Schema A only?
Thanks for the reply.
By Query never gets executed, I mean that they query gets executed but does not finish. I cancel it after an hour or so.
Explain Plan also shows it's not using the XMLIndex.
Since view is working fine in SchemaA, I decided to go by tidier approach suggested by you.
Create a Synonym for View V1 in SchemaB.
View Query has two tables:
XML_TABLE in Schema A
Table2 in Schema B.
Now I create View V1 in Schema A -> SUCCESSFUL.
Create Synonym for View V1 in Schema B -> SUCCESSFUL
Grant Select on View V1 to Schema B -> UNSUCCESSFUL
ORA-01720: grant option does not exist for 'SCHEMAB.Table2'