Forum Stats

  • 3,838,237 Users
  • 2,262,343 Discussions


Best way to execute an huge query

Hi team,

Let me share a very simple question for you. Imagine this situation.

Table 1: pk1, field11, field12,field13,field14

Table 2: pk2,field21, field2,field23,field24

Table 3: pk3, field31,field32,field33,field34

and this relationship:

field11 = pk2


And we need: field11, field12, field, 23,field24, field 31,field34

In this 3 tables can be 1 million of nodes in each one.

I was thinking about to solve it with 2 inner join but maybe any other solution for this kind of huge query?




  • Try this:

    SELECT T1.field1, T1.field2,

    T23.field23, T23.field24,

    T23.field31, T23.field34

    FROM table1 T1


    ( SELECT T2.pk2, T2.field23, T2.field24.

    T3.field31, T3.field34

    FROM table2 t2

    INNER JOIN T3 ON T2.field24 = T3.pk3 ) T23

    ON T1.field11 = T23.pk2

    You could use outer joins to preserve data. Also you may want to consider PARALLEL hints on the SELECT statements. Also, you could create temp tables from your main tables of just the columns you need. I've found the narrower tables are quicker to scan, I presume because more rows are brought into the available memory area.