Forum Stats

  • 3,838,237 Users
  • 2,262,343 Discussions
  • 7,900,552 Comments

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

field24=pk3

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?

Regards,

Jose

Answers

  • Try this:

    SELECT T1.field1, T1.field2,

    T23.field23, T23.field24,

    T23.field31, T23.field34

    FROM table1 T1

    INNER JOIN

    ( 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.