title: Performance Impact of Using RLS
body: <p>Generic_Schema has one common table, GENERIC_TABLE. The primary key of GENERIC_TABLE is COL1 and COMPANY_ID.</p>
  
  <pre><code>Company_id     Col1 Col2 Col3
  -------------- ---- ---- ----
  ALL_COMPANIES  A    B    C
  Comp1          A    B1   C1
  Comp3          A    B3   C
  </code></pre>
  
  <p>Specific Company Schema </p>
  
  <ol>
  <li>Schema1 =>Comp1   </li>
  <li>Schema2 =>Comp2</li>
  <li>SchemaN =>CompN</li>
  </ol>
  
  <p>We have implemented RLS on GENERIC_TABLE. The RLS function returns a clob value, but the logic to retrieve the filter values inside the function is:</p>
  
  <ol>
  <li>Compare the key with other values, and if the Comp1 Schema request data, then A|B1|C1 data is retrieved and the ALL_COMPANIES data is not retrieved because a company-specific value exists in the table.</li>
  <li>If the Comp4 retrieve data, it will get only the A|B|C record.</li>
  <li>The filter condition need to be retrieved from another function which returns the rowid.</li>
  </ol>
  
  <p>Now, here is the issue. After implementing this, the elapsed time of the RLS is much higher, though we didn't see much I/O. Can anyone think that this design is an issue, because this is not a static predicate and it is context sensitive.
  Please let me know if you need more inputs.</p>
  
  <p>Functions Definition is </p>
  
  <pre><code>create or replace function
    book_access_policy
    (obj_schema varchar2, obj_name varchar2)
    return clob
  
   is
  
  CURSOR get_d_predicate
  IS
   SELECT /*+ RESULT_CACHE */ rd
    FROM
    (
    SELECT rowid rd
          ,DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY CASE WHEN COMPANY_ID = sys_context('publishing_application','company_id') THEN 1 ELSE 2 END) rnk
  FROM GENERIC_TABLE
    )
     WHERE rnk=1;
  
  get_d_predicate_rec get_d_predicate%ROWTYPE;
  d_predicate clob;
  
  begin
  
   if sys_context('publishing_application','company_id') IS NULL THEN
    d_predicate:= ' 1=1';
  
   else
     d_predicate =' rowid in ('
      OPEN get_d_predicate;
      LOOP
       FETCH get_d_predicate INTO get_d_predicate_rec;
       EXIT WHEN get_d_predicate%NOTFOUND;
         d_predicate = d_predicate|| get_d_predicate_rec.rd;
      END LOOP;
     CLOSE get_d_predicate;
      d_predicate:=d_predicate||')';
  
  
   end if;
  
  return d_predicate;
  
   end;
   /
  </code></pre>