7 Replies Latest reply: Aug 4, 2011 12:37 AM by Park RSS

    Materialized View Error  - Help Needed

    Park
      Hi friends

      We have a table named weight_slip at two different independently running database locations and we need to combine the records at both these locations into a single table at central server.For this purpose we are trying to use materialized view with primary key as location code.Below mentioned is Materialized View Query (park and ang are 2 different oracle db).     
      select * from weight_slip@park
      union
      select * from weight_slip@ang;
      We are getting the following error

      ORA-12015: cannot create a fast refresh materialized view from a complex query

      Kindly suggest a way to overcome this error or suggest any other way of achieving this task

      Regards
      Park
        • 1. Re: Materialized View Error  - Help Needed
          JustinCave
          The restrictions on fast refreshable materialized views are available in the Data Warehousing Guide for your particular version of Oracle. You don't specify what version of Oracle you are using and the restrictions are version dependent so your version of Oracle may have different restrictions.

          Do you need to UNION the results together? Or could you do a UNION ALL instead? If you modified your query to add a UNION ALL marker, it should be possible to create a fast-refreshable materialized view.

          Could you replicate the data from each site separately with a WEIGHT_SLIP_PARK and a WEIGHT_SHIP_ANG materialized view and then combine the data locally (via a UNION or a UNION ALL)?

          Justin
          • 2. Re: Materialized View Error  - Help Needed
            Park
            Thank You Justin for the valuable suggestions

            >
            The restrictions on fast refreshable materialized views are available in the Data Warehousing Guide for your particular version of Oracle. You don't specify what version of Oracle you are using and the restrictions are version dependent so your version of Oracle may have different restrictions.
            >

            We are using Oracle version  10.2.0.1.0

            >
            Do you need to UNION the results together? Or could you do a UNION ALL instead? If you modified your query to add a UNION ALL marker, it should be possible to create a fast-refreshable materialized view.
            >

            We have Tried using UNION ALL But the error persists .We thought it might me due to connection to different db links.


            >
            Could you replicate the data from each site separately with a WEIGHT_SLIP_PARK and a WEIGHT_SHIP_ANG materialized view and then combine the data locally (via a UNION or a UNION ALL)?
            >

            This solution looks good.Does it hold and an advantage in terms of reliability and are you suggesting we create a   materialized view of  materialized view (WEIGHT_SLIP_PARK and a WEIGHT_SHIP_ANG  tables) with UNION at the central server for  automatic synchronization.

            Reagrds
            Park
            • 3. Re: Materialized View Error  - Help Needed
              NikolayIvankin
              Did you create a matview logs for those tables?
              • 4. Re: Materialized View Error  - Help Needed
                Park
                >
                Did you create a matview logs for those tables?
                >

                Yes we have created materialized view log at both the locations.

                Regards
                Park
                • 5. Re: Materialized View Error  - Help Needed
                  Park
                  I forgot to mention one more detail i'm trying this on a prebuilt table @ central server.


                  Regards
                  Park
                  • 6. Re: Materialized View Error  - Help Needed
                    JustinCave
                    Park wrote:
                    >
                    Do you need to UNION the results together? Or could you do a UNION ALL instead? If you modified your query to add a UNION ALL marker, it should be possible to create a fast-refreshable materialized view.
                    >

                    We have Tried using UNION ALL But the error persists .We thought it might me due to connection to different db links.
                    Did you modify the query to add the UNION ALL marker that the documentation I linked to discussed?

                    >
                    >
                    Could you replicate the data from each site separately with a WEIGHT_SLIP_PARK and a WEIGHT_SHIP_ANG materialized view and then combine the data locally (via a UNION or a UNION ALL)?
                    >

                    This solution looks good.Does it hold and an advantage in terms of reliability and are you suggesting we create a materialized view of materialized view (WEIGHT_SLIP_PARK and a WEIGHT_SHIP_ANG tables) with UNION at the central server for automatic synchronization.
                    There are advantages and disadvantages to having separate materialized views. If one of the remote databases is unavailable, for example, you'd still be able to refresh the other materialized view. That may be advantageous or it may be disadvantageous to you depending on what you would want to happen if only one of the two sets of data were refreshed.

                    You could create a materialized view on top of your materialized views to do the UNION ALL. Or you could simply do the UNION ALL in your query-- it depends whether the performance gains of combining the data into one object locally outweigh the additional disk space required and the additional time to refresh the second layer of materialized views.

                    Justin
                    • 7. Re: Materialized View Error  - Help Needed
                      Park
                      Thanks a bunch Justin really appreciate u r help :-)

                      I'm still getting the error this is my query with the union all marker i don't know if I've got it right
                      SELECT 1 AS marker,SNO,WEIGHT
                      from weight_slip@park
                      UNION ALL
                      SELECT 2 AS marker,SNO,WEIGHT
                      from weight_slip@ang;
                      Result
                       ORA-12052: cannot fast refresh materialized view weight_slip
                      If i try it with query rewrite enabled it says
                       Failed to commit: ORA-30353: expression not supported for query rewrite 
                      Regards
                      Park