Is it faster to use 1 reference for table merging or a 'chain' of references?

Question

I am querying data from a process historian that was stored using OPC. The querys run very slow. What table merging method should I expect to be faster and why when I query the data?

This:

and a1.TS >'15-MAR-18 07:00'                                             
and a1.TS = a2.TS                                                   
and a2.TS = a3.TS
and a3.TS = a4.TS
and a4.TS = a5.TS
and a5.TS = a6.TS
and a6.TS = a7.TS 
and a7.TS = a8.TS
and a8.TS = a9.TS
and a9.TS = a10.TS
and a10.TS = a11.TS

or this:

and a1.TS >'15-MAR-18 07:00'                                             
and a1.TS = a2.TS                                                   
and a1.TS = a3.TS
and a1.TS = a4.TS
and a1.TS = a5.TS
and a1.TS = a6.TS
and a1.TS = a7.TS 
and a1.TS = a8.TS
and a1.TS = a9.TS
and a1.TS = a10.TS
and a1.TS = a11.TS

How can I time it? (not using the stopwatch on my phone lol) — Vince Miller, Mar 15 '18 at 13:32
should be the same. anyway you test it using [explain plan](https://stackoverflow.com/questions/11799344/how-can-i-see-the-execution-plan) if both have the same plan then are the same for db optimizer — Juan Carlos Oropeza, Mar 15 '18 at 13:34
@VinceMiller I have never worked with Oracle before, but I found the answer to your "How can I time it" question in under 5 secs on Google. — dfundako, Mar 15 '18 at 13:34
The phone stopwatch shouldn't be that bad. Either you get similar results, or a big difference. — jarlh, Mar 15 '18 at 13:35

score 2 · Answer 1 · answered Mar 15 '18 at 13:53

I think the second is preferable. Why? The first imposes a particular ordering on the joins. It is possible that the optimizer recognizes that the two structures are identical, but that I wouldn't assume that is true.

One of the steps in optimization is choosing the ordering of the join operations. This can have a big impact, particularly with regards to indexes and reducing data size.

In a well-structured database, you might not see much of a difference in performance. You can inspect the query plan to see if they are the same. In particular, you want TS to be the first key in an index on all the tables.

Is it faster to use 1 reference for table merging or a 'chain' of references?

1 Answers1