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:
Times Ten 11.2.2 contains enhanced support for analytics workloads and complex queries:
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
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))
 "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)
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)
> dbListFields (conn, "IRIS")
 "SEPAL.LENGTH" "SEPAL.WIDTH" "PETAL.LENGTH" "PETAL.WIDTH" "SPECIES"
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
GROUP BY ROLLUP (SPECIES)')
SPECIES AVG_SLENGTH AVG_SWIDTH AVG_PLENGTH AVG_PWIDTH
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)
> dbDisconnect (conn)
We encourage you
download Oracle software for evaluation from the Oracle Technology
Network. See these links for our software: Times Ten In-Memory Database, ROracle. As always, we welcome comments and questions on the TimesTen and Oracle R technical forums.