0

I'm currently working with an Oracle DB and I noticed a curious fact. When left-joining two big tables the syntax:

SELECT *
FROM table1 t1 LEFT JOIN table2 t2
ON t1.join_field = t2.join_field

is much slower than:

SELECT *
FROM table1 t1, table2 t2
WHERE t1.join_field = t2.join_field(+)

I'm talking about a speedup factor of 1/2 sometimes.

I've looked around and I saw that the second syntax should be deprecated, so is there a particular reason why, in some cases, it is faster than the first one?

jackscorrow
  • 682
  • 1
  • 9
  • 27
  • 6
    Accurately benchmarking this sort of thing can be tricky to get right - e.g. if you always test the second query second, the first query may have primed caches, etc. What does `EXPLAIN PLAN` give you for both queries? – Damien_The_Unbeliever Oct 23 '18 at 07:08
  • 2
    Such propositions are not meaningfull, except you post at least you Oracle version, basic information about your tables and execution plan. See [here](https://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database?answertab=votes#tab-top) some hints how to post – Marmite Bomber Oct 23 '18 at 07:09
  • https://community.oracle.com/thread/503834 – Rob van Wijk Oct 23 '18 at 07:10
  • @Damien_The_Unbeliever as far as i know the query are cached (as for oracle) in the buffer cache, when it want to parse it check if the query(with no text changes) if it was parsed before or not. so as per above example the query is written is different , so how it can be cached ? is there another way of caching ? – Moudiz Oct 23 '18 at 07:13
  • 1
    Anecdotes are not evidence. – APC Oct 23 '18 at 07:14
  • 2
    @Moudiz - there's more than one type of cache. I was specifically thinking of the buffer cache that might still be full of *data rows* from the tables. Saving lots of I/O ops – Damien_The_Unbeliever Oct 23 '18 at 07:14
  • 2
    @Moudiz - the **queried data** might be cached database buffers. The table blocks might be cached in OS buffers. Modern systems have many places where data is cached, as part of architectural optimisation. – APC Oct 23 '18 at 07:17
  • 1
    Please **[edit]** your question (by clicking on the [edit] link below it) and add the execution plans for both queries. –  Oct 23 '18 at 07:54
  • 2
    See also here: https://ericlippert.com/2012/12/17/performance-rant/ –  Oct 23 '18 at 07:56
  • @a_horse_with_no_name I can't post an execution plan. **table1** and **table2** are the result of two views obtained from a lot of lines of code. I just noticed this strange behavior a couple of times and I was wondering if there could be a known reason – jackscorrow Oct 23 '18 at 08:09
  • Why would the fact that those two tables are views prevent you from creating the execution plan? Just run `explain plan for ...` for both queries. –  Oct 23 '18 at 08:10
  • I can't because the execution pan includes also the steps to create the two big tables, not just this last join and field and table names of the tables that I'm using are sensitive information – jackscorrow Oct 23 '18 at 08:51
  • 1
    Are the two execution plans the same? That's what you've got to look for. – Boneist Oct 23 '18 at 11:03

0 Answers0