2 Replies Latest reply: Apr 9, 2013 3:39 PM by 1001828 RSS

    Performance and optimization from R <-> SQL in Oracle

    1001828
      Hello,

      I'm assessing the adoption of Oracle R. I've glanced some of the Learning R Series files and forum threads but didn't find the answers I need.

      My questions are:
      1: Given any R source code, how to view the resulting SQL (or maybe PL/SQL) script?
      2. Would "my" optimization of db objects (e.g. creating an index, ALTERing a procedure) disrupt functionality?
      3. Are Vectorize, tapply, lapply, etc fully implemented in Oracle R? I'm guessing the answer will be yes.
      4. Is every line in R submitted to the db, or do any portions run purely in R? This relates to scenarios (if any) where an operation might be more efficient on SQL than on R or viceversa.

      Thank you.
        • 1. Re: Performance and optimization from R <-> SQL in Oracle
          Denis Mukhin
          1. Given any R source code, how to view the resulting SQL (or maybe PL/SQL) script?
          We do not expose this.
          2. Would "my" optimization of db objects (e.g. creating an index, ALTERing a procedure) disrupt functionality?
          It will be considered by the query optimizer when building a plan.
          3. Are Vectorize, tapply, lapply, etc fully implemented in Oracle R? I'm guessing the answer will be yes.
          There is support for some predefined functions. To work with custom functions we have a family of <tt>ore.*Apply</tt> functions.
          4. Is every line in R submitted to the db, or do any portions run purely in R? This relates to scenarios (if any) where an operation might be more efficient on SQL than on R or viceversa.
          It is a mixed mode. ORE makes the decision. But in general not every line is executed right away. The execution is deferred to the point when the results are requested on the client side.

          Denis
          • 2. Re: Performance and optimization from R <-> SQL in Oracle
            1001828
            Thank you Denis.

            The principle of combining the database and R can be so powerful, which is why I was getting excited about seeing the SQL code in action. It would also aid in program testing.

            Thanks again.