correct query design? cross joins driving ad-hoc reporting interface

Question

I'm hoping some of the more experienced database/dwh developers or DBAs can weigh in on this one:

My team is using OBIEE as a front-end tool to drive ad-hoc reporting being done by our business units.

There is a lot of latency when generating sets that are relatively small. We are facing ~1 hour to produce ~50k records.

I looked into one of the queries that is behaving this way, and I was surprised to find that all of the tables being referenced are being cross-joined, and then filters are being applied in the WHERE clause.

So, to illustrate, the queries tend to look like this:

SELECT ...
FROM tbl1
    ,tbl2
    ,tbl3
    ,tbl4
WHERE tbl1.col1 = tbl2.col1
and tbl3.col2 = tbl2.col2
and tbl4.col3 = tbl3.col3

instead of like this:

SELECT ...
FROM tbl1
INNER JOIN tbl2
    ON tbl1.col1 = tbl2.col1
INNER JOIN tbl3
    ON tbl3.col2 = tbl2.col2
INNER JOIN tbl4
    ON tbl4.col3 = tbl3.col3

Now, from what I know about the order of query operations, the FROM clause gets performed before the WHERE clause, so the first example would perform much more slowly than the latter example. Am I correct (please answer only if you know the answer in the context of Oracle DB)? Unfortunately, I don't have the admin rights to run a trace against the 2 different versions of the query.

Is there a reason to set up the query the first way, related to how the OBIEE interface works? Remember, the query is the result of a user drag-and-dropping attributes into a sandbox, from a 'bank' of attributes. Selecting any combination of the attributes is supposed to generate output (if the data exists). The attributes come from many different tables. I don't have any experience in designing the mecahnism that generates the SQL based on this kind of ad-hoc attribute selection, so I don't know whether the query design in the first example is required to service this kind of reporting tool.

Answer 1

Don't worry, historically Oracle used the first notation for inner joins but later on adopted ANSI SQL standards.

The results in terms of performance and returned recordsets are exactly the same, the implicit 'comma' joins are not crossing resultset but effectively integrating the WHERE filters. If you doubt it, run an EXPLAIN SELECT command for both queries and you will see the forcasted algorithms will be identical.

---

Expanding this answer you may notice in the future the analogous notation (+) in place of outer joins. This answer will also stand correct in that context.

The real issue comes when both notations (implicit and explicit joins) are mixed in the same query. This would be asking for trouble big time, but I doubt you find such a case in OBIEE.

Answer 2

Those are inner joins, not cross joins, they just use the old syntax for doing it rather than ANSI as you were expecting.

Most join queries contain at least one join condition, either in the FROM clause or in the WHERE clause. (Oracle Documentation)

For a simple query such as in your example the execution should be exactly the same.

Where you have set outer joins (in the business model join) you will see OBI produce a query where the inner joins are made in the WHERE clause and the outer joins are done ANSI in the FROM statement – just to make things really hard to debug!

SELECT ...
FROM tbl1
    ,tbl2
    ,tbl3 left outer join
         tbl4 on tbl3.col1 = tbl4.col2
WHERE tbl1.col1 = tbl2.col1
and tbl3.col2 = tbl2.col2
and tbl4.col3 = tbl3.col3