Optimizing Oracle SQL performance using an inline view!

In our production environment, we have a process that runs for about 30 minutes, and may even take hours on busy days. Way too long of course!
I’ve been rewriting this function this week, and finally brought it back to a matter of minutes by using a materialized view and a more efficient query.
Still, I think nowadays most data should be available instantanious, if possible. So, a few minutes is way too long.

I have 2 tables, tabel A with 2.4 million records, materialized view B with 82000 records. The materialized view is a joined subset of 3 tables containing about 240.000 records each.

Here’s the culprit:

SELECT last_name,
   date_of_birth ,
    bsn 
  FROM mat_view
    JOIN table_a 
  ON mat_view.fk_id = table_a.fk_id
WHERE mat_view.fk2_id = :BIND

optimizing Oracle SQL performance using an inline view

Not too bad I thought. Relatively small view, Oracle knows how to handle this! The result was lots of full table scans and hash joins, and of course a high cost. After giving it some thought, I remembered the thing called inline views. Maybe they would bring Oracle to some other thoughts about the execution path.

As you see in the query below, I’ve rewritten the join with the materialized view + bind variable to a join with an inline view. I now force Oracle to first fetch a tiny subset of the materialized view. This subset is joined on foreign keys to the large table, which performs like a breeze.

SELECT last_name,
 date_of_birth ,
 bsn
 FROM
 ( SELECT * FROM mat_view WHERE fk2_id =:BIND
 ) gpl
 JOIN table_a
 ON gpl.fk_id = table_a.fk_id

Fortunately, this did the trick. Believe it or not, this query gives instantanious result, instead of in minutes. The drawback is that I needed to add some extra, quite complex clauses, so the responsetime is now about 2-3 seconds. In the end, not bad compared to the 30 minutes we started with!

You can read more about Oracle inline views here. That page also describes the subtle difference between subqueries and inline views. Also check Tom Kyte’s thoughts on the With clause, which is similar but even more powerful.

Laat een reactie achter

Je e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *

Deze site gebruikt Akismet om spam te verminderen. Bekijk hoe je reactie-gegevens worden verwerkt.

By using this site you acknowledge the use of cookies (which are mostly harmless, btw) More information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below you are agreeing to these settings.

Close