TOI

Oops, we did it again

• Exasol
• TOI

As a Database Developer or Database Administrator, it becomes increasingly unlikely that you will work with only one platform.

It’s quite useful to have one single tool to handle multiple different database platforms. And that’s exactly the ambition of DbVisualizer.

As a hypothetical scenario, let’s assume you are a database admin who works on a project to migrate from Oracle to EDB Postgres and Exasol.

The goal might be to replace the corporate Oracle database landscape, moving the OLTP part to EDB Postgres and the DWH / Analytics part to Exasol.

Instead of having to switch constantly between say SQL Developer, psql and EXAplus, a more efficient approach would be using DbVisualizer for all three.

Many of our customers are using Oracle together with SQL Developer, so this question comes up regularly: Can we use SQL Developer also for Exasol?

Short answer is: Unfortunately not.

I tried myself to make that work with no success. Then I found this on Stackoverflow:

Jeff Smith: “No, that’s not supported. SQL Developer’s 3rd party JDBC connectivity is provided for one use case – migrations to Oracle Database.
There’s no support on that for Exasol DB, so there’s no connectivity support provided.
If you want a generic jdbc db client, that’s not Oracle SQL Developer.” [Highlighted by me]

Why should you bother? Because Exasol is the fastest analytical database in the world, outperforming any competitor. Therefore, expertise about Exasol might soon be very valuable also in your company.
Free training helps us to spread the knowledge in a scalable way, empowering customers across the globe to get the best out of Exasol and supporting our rapid growth.

You can register here. The free online courses are branded as “Exacademy”:

An Exasol database will automatically create, maintain and drop indexes, following the core idea to deliver great performance without requiring much administrative efforts. Like our tables, our indexes are always compressed and you don’t need to configure anything for that.

Joins between two or more tables are processed like this in Exasol: One table is full scanned (this is called the root table) and the other tables are joined using an index on their join columns.

If these indexes on the join columns are not already existing, they are automatically created during the join operation. Taking two tables t1 and t2 as an example, and a statement like

After having worked with both databases for quite some time, this is what I consider to be the key differences between Oracle and Exasol. Of course the two have much in common: Both are relational databases with a transaction management system that supports the ACID model and both follow the ANSI SQL standard – both with some enhancements. Coming from Oracle as I do, much in Exasol looks quite familiar. But let’s focus on the differences:

Strengths

Oracle is leading technology for Online Transaction Processing (OLTP). If you have a high data volume with many users doing concurrent changes, this is where Oracle shines particularly.

Exasol is leading technology for analytical workloads. If you want to do real-time ad hoc reporting on high data volume, this is where Exasol shines particularly.

The technique to recover only certain objects from an ordinary backup is called Virtual Access Restore. Means you create a database from backup that contains only the minimum elements needed to access the objects you request. This database is then removed afterwards.

Let’s see an example. This is my initial setup:

• Exasol
• TOI

Exasol introduced Partitioning in version 6.1. This feature helps to improve the performance of statements accessing large tables. As an example, let’s take these two tables:

Your BI users complain about slow performance of their analytical queries? Is this your Status Quo?

• Exasol
• TOI

During the Data Guard round table of the excellent UKOUG Tech18 conference I got aware of this topic that I’d like to share with the Oracle community:

What is the locking behavior for user accounts in an environment where users may connect to the primary as well as to the standby database?