2 Replies Latest reply on May 10, 2007 3:53 PM by 576996

    query on Views is slow


      in OC, i understand each question group correspond to a view and we can query it using SQL. I have tried to query on 1 record (by pt, patient ID) and it takes quite a while to respond back. From Explain plan, it can be seen that it takes a long path to return back a row.

      When i create a temp table with all records from this view, the speed to return this same record takes less than a split second. As such, may i know is there anyway to improve the query speed on such views ?

      for some advice please
      Thank you
      Boon Yiang
        • 1. Re: query on Views is slow
          Naveen B
          Hi, The speed depends on which view you are querying and the the number of tables the view refers.

          You could try querying the underlying tables directly by checking the view definition.
          • 2. Re: query on Views is slow
            One thing that you need to do to ensure good query performance in OC is to make sure the database has statistics created for the OC tables via the "analyze table" command. The cost-based query optimizer in Oracle relies on up-to-date table statistics to optimize queries. Table analysis should be performed regularly by the DBA. OC provides scripts to run the table analysis. They are in the $RXC_INSTALL directory and are named anarxctab.sql, anadestab.sql, and analrtab.sql.

            Bob Pierce
            Constella Group