CODEWITHSUNDEEP
sqloracle-database
user1881712
user1881712

Reputation: 99

How to write queries with LEFT JOIN for more performance

I try to understand how to correct write big queries with a lot of JOIN clause. Are this queries has the same performance?

/*Sql 1*/
SELECT G.ID, T1.QUANTITY, T2.QUANTITY
 FROM GOODS G
LEFT JOIN
/*First subquery*/
(SELECT SUM(QUANTITY) QUANTITY, GOOD_ID
FROM MY_TABLE_1
GROUP BY GOOD_ID
) T1 ON G.ID = T1.GOOD_ID
LEFT JOIN
/*Second subquery*/
(SELECT SUM(QUANTITY) QUANTITY, GOOD_ID
GROUP BY GOOD_ID
FROM MY_TABLE_2
) T2 ON G.ID = T2.GOOD_ID
/*and so on....Next same subqueries*/
WHERE G.ID IN (1, 2, 3, 4);

In the next query same WHERE clause set in all joining subqueries and outside LEFT JOIN clause replace on INNER CLAUSE. Is it a good solution? Or oracle optimizer automatic make such things? 

/*Sql 2*/
SELECT G.ID, T1.QUANTITY, T2.QUANTITY
 FROM GOODS G
INNER JOIN
/*First subquery*/
(SELECT SUM(QUANTITY) QUANTITY, GOOD_ID
FROM GOODS G
LEFT JOIN MY_TABLE_1 M ON M.GOOD_ID = G.ID
WHERE G.ID IN (1, 2, 3, 4);
GROUP BY GOOD_ID
) T1 ON G.ID = T1.GOOD_ID
INNER JOIN
/*Second subquery*/
(SELECT SUM(QUANTITY) QUANTITY, GOOD_ID
FROM GOODS G
LEFT JOIN MY_TABLE_2 M ON M.GOOD_ID = G.ID
WHERE G.ID IN (1, 2, 3, 4);
GROUP BY GOOD_ID
) T2 ON G.ID = T2.GOOD_ID
/*and so on....Next same subqueries*/
WHERE G.ID IN (1, 2, 3, 4);

Upvotes: 2

Views: 21672

Answers (3)

AngelWarrior
AngelWarrior

Reputation: 1380

For best results, cut down your first table as fast as possible so that oracle doesn't create lots of extra rows to throw away. Then add that filter inside the subqueries, not after they complete. I'd try:

SELECT G.ID, T1.QUANTITY, T2.QUANTITY
  FROM GOODS G
  JOIN DUAL ON G.ID IN (1, 2, 3, 4)
  LEFT JOIN (SELECT SUM(QUANTITY) AS QUANTITY, GOOD_ID
          FROM MY_TABLE_1 T
         GROUP BY GOOD_ID
        ) T1 ON T1.GOOD_ID = G.ID
  LEFT JOIN (SELECT SUM(QUANTITY) AS QUANTITY, GOOD_ID
          FROM MY_TABLE_2 T
         GROUP BY GOOD_ID
        ) T2 ON T2.GOOD_ID = G.ID

Upvotes: 1

Mangoose
Mangoose

Reputation: 922

Optimizer takes care of most of the things. You should do following Try to join on primary key If not possible then provide indexes for columns involved in join If possible Filter results using where clause to reduce rows in source table before join

Upvotes: 0

Curt
Curt

Reputation: 5722

Query performance will depend on a lot of things including:

  • The relative size of the tables involved
  • The presence or absence of indices covering join columns and filter criteria
  • The currency of the table statistics in the database
  • The way that the query is written.

It is absolutely untrue that OUTER joins are faster than INNER joins (although there might be some queries for which that is true). So it is not a great use of your time to try to convert queries to use more OUTER joins based on that theory.

There are entire books written on performance optimization, but some general rules include

  • In your query, do table joins and WHERE criteria in most-selective to least-selective order (i.e. if you have a join that will reduce the size of the return set by 80%, put it up near the top of your query). The optimizer should theoretically be able to re-order the join these criteria based on table statistics, but I have found that giving it that extra help sometimes makes the difference.

  • Learn to read SQL execution plans. They will tell you exactly how the database went about fulfilling your request, and you can learn whether it missed and index (or if the database is missing an index that might be useful for performance), when it was able to use ranges, direct index hits, or full table scans, and the like.

  • It is sometimes useful, with very long-running queries, to divert some preliminary results into a temporary table, index that table appropriately, and then do the rest of your querying against that table. That is particularly true when you need to join or filter by some computed criteria that would force a table scan of a large table when you can find some strategy to pre-filter part of your result by more direct criteria.

Upvotes: 3

Related Questions