ROracle support for TimesTen In-Memory Database

September 27, 2012

(This article was first published on Oracle R Enterprise, and kindly contributed to R-bloggers)

Today’s guest post comes from Jason Feldhaus, a Consulting Member of Technical Staff in the TimesTen Database organization at Oracle.  He shares with us a sample session using ROracle with the TimesTen In-Memory database. 

Beginning in version 1.1-4, ROracle includes support for the Oracle Times Ten In-Memory Database, version 11.2.2. TimesTen is a relational database providing very fast and high throughput through its memory-centric architecture.  TimesTen is designed for low latency, high-volume data, and event and transaction management. A TimesTen database resides entirely in memory, so no disk I/O is required for transactions and query operations. TimesTen is used in applications requiring very fast and predictable response time, such as real-time financial services trading applications and large web applications. TimesTen can be used as the database of record or as a relational cache database to Oracle Database.

ROracle provides an interface between R and the database, providing the rich functionality of the R statistical programming environment using the SQL query language. ROracle uses the OCI libraries to handle database connections, providing much better performance than standard ODBC.

The latest ROracle enhancements include:

  • Support for Oracle TimesTen In-Memory Database
  • Support for Date-Time using R’s POSIXct/POSIXlt data types
  • RAW, BLOB and BFILE data type support
  • Option to specify number of rows per fetch operation
  • Option to prefetch LOB data
  • Break support using Ctrl-C
  • Statement caching support

Times Ten 11.2.2 contains enhanced support for analytics workloads and complex queries:

  • Analytic clauses: OVER PARTITION BY and OVER ORDER BY
  • Multidimensional grouping operators:
  • Grouping functions: GROUP, GROUPING_ID, GROUP_ID
  • WITH clause, which allows repeated references to a named subquery block
  • Aggregate expressions over DISTINCT expressions
  • General expressions that return a character string in the source or a pattern within the LIKE predicate
  • Ability to order nulls first or last in a sort result (NULLS FIRST or NULLS LAST in the ORDER BY clause)

Note: Some functionality is only available with Oracle Exalytics, refer to the TimesTen product licensing document for details.

Connecting to TimesTen is easy with ROracle.  Simply install and load the ROracle package and load 
the driver.
   > install.packages("ROracle")
   > library(ROracle)
    Loading required package: DBI
   > drv <- dbDriver("Oracle")

Once the ROracle package is installed, create a database connection object and connect to a 
TimesTen direct driver DSN as the OS user.

   > conn <- dbConnect(drv, username ="", password="",
                   dbname = "localhost/SampleDb_1122:timesten_direct")

You have the option to report the server type - Oracle or TimesTen?

   > print (paste ("Server type =", dbGetInfo (conn)$serverType))
   [1] "Server type = TimesTen IMDB"

To create tables in the database using R data frame objects, use the function dbWriteTable.  
In the following example we write the built-in iris data frame to TimesTen. The iris data set is 
a small example data set containing 150 rows and 5 columns. We include it here not to highlight 
performance, but so users can easily run this example in their R session.

   > dbWriteTable (conn, "IRIS", iris, overwrite=TRUE, ora.number=FALSE)
   [1] TRUE

Verify that the newly created IRIS table is available in the database. To list the available tables and 
table columns in the database, use dbListTables and dbListFields, respectively.

   > dbListTables (conn)
   [1] "IRIS"
   > dbListFields (conn, "IRIS")

To retrieve a summary of the data from the database we need to save the results to a local object. The 
following call saves the results of the query as a local R object, iris.summary. The ROracle function 
dbGetQuery is used to execute an arbitrary SQL statement against the database. When connected to 
TimesTen, the SQL statement is processed completely within main memory for the fastest response 
   > iris.summary <- dbGetQuery(conn, 'SELECT
                                        AVG ("SEPAL.LENGTH") AS AVG_SLENGTH,
                                        AVG ("SEPAL.WIDTH") AS AVG_SWIDTH,
                                        AVG ("PETAL.LENGTH") AS AVG_PLENGTH,
                                        AVG ("PETAL.WIDTH") AS AVG_PWIDTH
                                       FROM IRIS
                                       GROUP BY ROLLUP (SPECIES)')

   > iris.summary
   1     setosa    5.006000   3.428000       1.462   0.246000
   2 versicolor    5.936000   2.770000       4.260   1.326000
   3  virginica    6.588000   2.974000       5.552   2.026000
   4       <NA>    5.843333   3.057333       3.758   1.199333

Finally, disconnect from the TimesTen Database.

   > dbCommit (conn)
   [1] TRUE
   > dbDisconnect (conn)
   [1] TRUE

We encourage you
download Oracle software for evaluation from the Oracle Technology
Network. See these links for our software: Times Ten In-Memory DatabaseROracle.  As always, we welcome comments and questions on the TimesTen and  Oracle R technical forums.

To leave a comment for the author, please follow the link and comment on their blog: Oracle R Enterprise. offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...

If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.


Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)