This discussion is archived
2 Replies Latest reply: Dec 13, 2012 12:57 AM by user8941550 RSS

Table T1 in Schema A, Synonym T1 in Schema B, View V1 in Schema B.

user8941550 Newbie
Currently Being Moderated
Hi,

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:

SELECT *
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.

How should I make it work
OR
Should I remove the view to Schema A only?

Thanks..

Edited by: user8941550 on Dec 12, 2012 11:56 PM
  • 1. Re: Table T1 in Schema A, Synonym T1 in Schema B, View V1 in Schema B.
    BluShadow Guru Moderator
    Currently Being Moderated
    user8941550 wrote:
    Hi,

    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:

    SELECT *
    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.
    Ok, I was following you to that point, but what do you mean the 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 work
    OR
    Should I remove the view to Schema A only?
    Perhaps 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.
  • 2. Re: Table T1 in Schema A, Synonym T1 in Schema B, View V1 in Schema B.
    user8941550 Newbie
    Currently Being Moderated
    Hi BluShadow,

    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
    Error:
    ORA-01720: grant option does not exist for 'SCHEMAB.Table2'

    Please suggest..Thanks..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points