High performance data unloading from Oracle to ODBC database

I got a task some time ago, to copy a-lot-of-millions of rows from Oracle to MySQL. As fast as possible.
In the last year I have used Oracle Heterogeneous Services (HSODBC) a lot to select data from different non-Oracle databases, but I had yet to try to unload data from Oracle with best performance.

My first thought was, of course, that the process needs to run in parallel. Here I'll first take a look at the overall HSODBC performance and then look how can I make this process run in parallel.

In this test I use Oracle Database 11.1. (more...)

Using linguistic indexes for sorting in open source databases

Here I'm following up my previous post Using linguistic indexes for sorting in Oracle. I don't much like the Oracle solution, that requires creating a special index to speed up sorting, but... at the same time its very powerful, allows to index in many languages and no database changes are needed.

In this post I’ll take a look at the two popular open source databases MySQL and PostgreSQL. I'll take a look only at features, that the database has included and that can be used without any special application changes.

PostgreSQL 8.4

Starting from 8.4, collation (sorting) rules (more...)

Refreshing test database from production using incremental backups

It's common to use a production database copy for testing. But how to keep it fresh? If production database is large and located on a remote site (even on another country/continent), then copying the full database backup over network may be too unreasonable and if the test database needs to be open for a longer time, then transporting and storing production archived logs to test site may be too unreasonable also.
In this situation Oracle has three great technologies that can help: physical standby database, flashback database and RMAN.

Here I'm using Oracle Database EE.

Initial (more...)

Using linguistic indexes for sorting in Oracle

In Estonia we have our own language and Estonian language has its own alphabet that is a little bit different from English :) And it would be good if a database application sorts text fields according to the correct language rules (I have seen many bug reports from clients, that "the sort order is wrong").

In Oracle database its quite easy to sort according to the correct language rules, you just need to set the correct database session language from the client. This can be done using ALTER SESSION SET NLS_LANGUAGE or quite often the database client picks up the (more...)

APEX application running on “another” database

I have attended some APEX presentations/seminars and one question seems to be repeating and got my attention. Can APEX run on another database?

Well, APEX itself or APEX application itself cannot run in any way on another database besides Oracle. APEX is PL/SQL application and no other database supports Oracle PL/SQL language.

But it's possible to select and modify data from another database from your APEX application.

First of all you need to set up a database link to the other database using Oracle Heterogeneous Services/Transparent Gateways. I'm not going to get into that right now, please use documentation/google for (more...)