{"id":304,"date":"2008-12-28T13:51:03","date_gmt":"2008-12-28T12:51:03","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=304"},"modified":"2008-12-28T13:51:03","modified_gmt":"2008-12-28T12:51:03","slug":"useless-joins","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=304","title":{"rendered":"Useless joins…"},"content":{"rendered":"

Sometimes when you\u2019re building queries you end up joining tables which you don\u2019t really need (anymore). They are just there to join some other table from which you do need some data. The join is just there to satisfy some foreign key references. It can however greatly impact the speed of your query. If the database doesn\u2019t need to look at the data in the table it is normally much faster (if you don\u2019t have to do the work, you\u2019re always finished).<\/p>\n

<\/p>\n

Look at the following test script and see how it can be done without the extra join:<\/p>\n

\n
\n
select<\/span> blog1.*<\/pre>\n
     , blog2.*<\/pre>\n
  from<\/span> blog1<\/pre>\n
  join<\/span> blog3 on<\/span> blog1.blog3_id = blog3.id<\/pre>\n
  join<\/span> blog2 on<\/span> blog2.blog3_id = blog3.id<\/pre>\n
\/<\/pre>\n<\/p><\/div>\n<\/div>\n
and:<\/p>\n
\n
\n
select<\/span> blog1.*<\/pre>\n
     , blog2.*<\/pre>\n
  from<\/span> blog1<\/pre>\n
  join<\/span> blog2 on<\/span> blog2.blog3_id = blog1.blog3_id<\/pre>\n
\/<\/pre>\n<\/p><\/div>\n<\/div>\n
The results for both queries are the same, but the second query has to do less work. When you look at the explain plans you see that the second query is  doing less work<\/p>\n
PLAN_TABLE_OUTPUT
\n    
——————————————————————————– <\/p>\n
Plan hash value: 3385940288 <\/p>\n
——————————————————————————– <\/p>\n
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time <\/p>\n
——————————————————————————– <\/p>\n
|   0 | SELECT STATEMENT    |             |     3 |   279 |     7  (15)| 00:00:0 <\/p>\n
|*  1 |  HASH JOIN          |             |     3 |   279 |     7  (15)| 00:00:0 <\/p>\n
|   2 |   NESTED LOOPS      |             |     3 |   159 |     3   (0)| 00:00:0 <\/p>\n
|   3 |    TABLE ACCESS FULL| BLOG1       |     3 |   120 |     3   (0)| 00:00:0 <\/p>\n
|*  4 |    INDEX UNIQUE SCAN| PK_BLOG3_ID |     1 |    13 |     0   (0)| 00:00:0 <\/p>\n
|   5 |   TABLE ACCESS FULL | BLOG2       |     3 |   120 |     3   (0)| 00:00:0 <\/p>\n
——————————————————————————– <\/p>\n
Predicate Information (identified by operation id): <\/p>\n
————————————————— <\/p>\n
   1 – access("BLOG2"."BLOG3_ID"="BLOG3"."ID") <\/p>\n
   4 – access("BLOG1"."BLOG3_ID"="BLOG3"."ID") <\/p>\n
Note <\/p>\n
   – dynamic sampling used for this statement <\/p>\n
22 rows selected<\/font><\/p>\n
PLAN_TABLE_OUTPUT
\n    
——————————————————————————– <\/p>\n
Plan hash value: 2974663523 <\/p>\n
—————————————————————————- <\/p>\n
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     | <\/p>\n
—————————————————————————- <\/p>\n
|   0 | SELECT STATEMENT   |       |     3 |   240 |     7  (15)| 00:00:01 | <\/p>\n
|*  1 |  HASH JOIN         |       |     3 |   240 |     7  (15)| 00:00:01 | <\/p>\n
|   2 |   TABLE ACCESS FULL| BLOG1 |     3 |   120 |     3   (0)| 00:00:01 | <\/p>\n
|   3 |   TABLE ACCESS FULL| BLOG2 |     3 |   120 |     3   (0)| 00:00:01 | <\/p>\n
—————————————————————————- <\/p>\n
Predicate Information (identified by operation id): <\/p>\n
————————————————— <\/p>\n
   1 – access("BLOG2"."BLOG3_ID"="BLOG1"."BLOG3_ID") <\/p>\n
Note <\/p>\n
—– <\/p>\n
   – dynamic sampling used for this statement <\/p>\n
19 rows selected<\/font><\/p>\n
Note that is this demo there is little work done and you may not even note the difference in performance, but using real tables with real data in them can make your queries speed up an order of magnitude when you exterminate the useless joins.<\/p>\n