Oracle

Setup and Best Practices

Connection Information

In addition to a hostname or IP and port, you will also need to know the site identifier (SID) of the Oracle server.

Resource Allocation Considerations

For RDBMS sources like Oracle, Dremio's query execution is largely single threaded. This means that for each Oracle directed query, only one Dremio node will experience a computational load. So unlike most other data sources, larger Dremio clusters won't lead to faster individual query execution times. However, if you expect a large number of concurrent queries (as in the case of many simultaneous Dremio users) these will be distributed evenly across the nodes.

NOTE: Ensure that your Dremio cluster has access to the appropriate port for your Oracle source. By default this is port 1521.

Dremio and Oracle

Query Pushdowns

For some operations Dremio can tell the data source to execute that part of the query natively, often dramatically improving performance. These operations are called 'pushdowns.'

Since they share a common language (SQL), Dremio supports most operations as pushdowns in Oracle. These include:

• Filter (SQL: WHERE)
• Limit (SQL: LIMIT)
• Sorting (SQL: ORDER BY)
• Aggregation (SQL: GROUP BY)
• Project (with expressions) (e.g. SQL: SELECT columnA + columnB, columnC, columnD)

NOTE: Since Oracle has no boolean type, project operations that contain SQL expressions which evaluate to true or false (e.g. SELECT username, friends > 0), and filter operations that include boolean literals in a filter (e.g. WHERE currentAccount = true) cannot be executed as pushdowns.

Initial Connection

Depending on the number of tables in your Oracle source, the final step of adding it to Dremio can take anywhere from a few seconds to a few minutes as the source's metadata is processed. However, this is a one-time cost and further queries to the source will not incur additional metadata reads.

Dremio Configuration

Here are all available source specific options:

Oracle TLS Configuration

To use TLS to connect to an Oracle source follow the instructions below.

Select the option Connect using SSL/TLS when initially configuring it

Optionally add the Server Certificate Distinguished Name
If you want to ensure the server you’re connecting to be the exact one matching a particular certificate string then add the Server Certificate Distinguished Name under the SSL/TLS option in Advanced Options

Add the Certificate Authority certificate to Dremio's trust store
To add the CA certificate that is used to sign the Oracle certificate into Dremio's trust store follow the instructions below.

1. Import the CA certificate Convert the certificate into DER format (required by Java keytool).

   For example, using OpenSSL tool:

   $ openssl x509 -outform der -in oracle-ca.pem -out oracle-ca.der

2. Add the certificate to a new or existing truststore

   $ keytool -import -alias oracle-ca -keystore dremio-truststore.jks -file oracle-ca.der

3. Modify the DREMIO_JAVA_SERVER_EXTRA_OPTS section of the dremio.env configuration file to use the trust store by adding the following:

   Djavax.net.ssl.trustStore=<path/to>/dremio-truststore.jks

   Djavax.net.ssl.trustStoreType=JKS

   Djavax.net.ssl.trustStorePassword=<password>