3 Replies Latest reply: Jun 25, 2014 10:42 AM by authoritydba RSS

    No indexes on the primary database. Need help with 'RELY DISABLE;' option

    authoritydba

      11gR2 11.2.0.3 on linux

       

      Hi all the contributors,

      It's an honor to be with you guys.

       

      I have a big question here and am unable to decide a permanent fix.

       

      We have a primary database configured to have a logical standby.

       

      The logical standby was not built by me. But I am supporting it now...

       

      So as the title says, there are NO primary / unique + not null indexes on many of the tables in the primary/logical standby database. But the application logic ensures uniqueness and not-null values.

       

      Here is where the problem starts.

      The application guys DO NOT want us to create any index on the primary database.

       

      According to my understanding, as the database is lacking indexes, the SQL Apply has to do a full table scan each time it modifies / delete data. This is a very expensive process and consumes a lot of time. This takes hours together to apply a minute worth of logs on the logical standby... A huge lag is created over hours and is not acceptable.

       

      So as I told you earlier, the application guys do not want an index on the unsupported tables.

      They use the logical standby database exclusively for reporting purposes and it's like production for them...


      Let me give an overview...

       

      According to the document on creating the logical standby for 11.2 as below

      http://docs.oracle.com/cd/E11882_01/server.112/e41134/create_ls.htm#SBYDB4730

       

      Step 1   Find tables without unique logical identifier in the primary database.

      Query the DBA_LOGSTDBY_NOT_UNIQUE view to display a list of tables that SQL Apply may not be able to uniquely identify. For example:

      SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
       2> WHERE (OWNER, TABLE_NAME) NOT IN 
      3> (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) 
      4> AND BAD_COLUMN = 'Y'; 

       

       

      The above query does report 1040 tables under a particular schema. All of these have no primary / unique not-null indexes.


      Now according to step-2 as below, we have an option to add a disabled primary-key RELY constraint.

       

      Step 2   Add a disabled primary-key RELY constraint.

      If your application ensures the rows in a table are unique, you can create a disabled primary key RELY constraint on the table. This avoids the overhead of maintaining a primary key on the primary database.

       

      Can anyone explain what this disabled RELY constraint is and how it's going to function and help please?


      I have few questions regarding the RELY constraint.

      1) Will it change the current optimizer plans on primary for the being executed?

      2) Will it help boost the SQL Apply performance without creating indexes on the logical standby database?

      3) Will it change the current optimizer plans on logical standby for the SQLs being executed?

       

       

      What options do I have to increase the performance and AVOID full table scans by SQL Apply engine on the logical standby?

       

       

      Thanks a ton for reading my post.


      Regards,

      Abhishek