2 Replies Latest reply: May 10, 2007 10:53 AM by 576996 RSS

    query on Views is slow

    337600
      Hi

      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
          NaveenB
          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
            576996
            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
            bpierce@constellagroup.com