2 Replies Latest reply: Dec 13, 2012 2:57 AM by user8941550 RSS

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

    user8941550
      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
          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
            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..