Useless joins…

Sometimes when you’re building queries you end up joining tables which you don’t 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’t need to look at the data in the table it is normally much faster (if you don’t have to do the work, you’re always finished).

Look at the following test script and see how it can be done without the extra join:

select blog1.*
     , blog2.*
  from blog1
  join blog3 on blog1.blog3_id = blog3.id
  join blog2 on blog2.blog3_id = blog3.id
/

and:

select blog1.*
     , blog2.*
  from blog1
  join blog2 on blog2.blog3_id = blog1.blog3_id
/

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

PLAN_TABLE_OUTPUT

——————————————————————————–

Plan hash value: 3385940288

——————————————————————————–

| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time

——————————————————————————–

|   0 | SELECT STATEMENT    |             |     3 |   279 |     7  (15)| 00:00:0

|*  1 |  HASH JOIN          |             |     3 |   279 |     7  (15)| 00:00:0

|   2 |   NESTED LOOPS      |             |     3 |   159 |     3   (0)| 00:00:0

|   3 |    TABLE ACCESS FULL| BLOG1       |     3 |   120 |     3   (0)| 00:00:0

|*  4 |    INDEX UNIQUE SCAN| PK_BLOG3_ID |     1 |    13 |     0   (0)| 00:00:0

|   5 |   TABLE ACCESS FULL | BLOG2       |     3 |   120 |     3   (0)| 00:00:0

——————————————————————————–

Predicate Information (identified by operation id):

—————————————————

   1 – access("BLOG2"."BLOG3_ID"="BLOG3"."ID")

   4 – access("BLOG1"."BLOG3_ID"="BLOG3"."ID")

Note

   – dynamic sampling used for this statement

22 rows selected

PLAN_TABLE_OUTPUT

——————————————————————————–

Plan hash value: 2974663523

—————————————————————————-

| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————-

|   0 | SELECT STATEMENT   |       |     3 |   240 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN         |       |     3 |   240 |     7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| BLOG1 |     3 |   120 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| BLOG2 |     3 |   120 |     3   (0)| 00:00:01 |

—————————————————————————-

Predicate Information (identified by operation id):

—————————————————

   1 – access("BLOG2"."BLOG3_ID"="BLOG1"."BLOG3_ID")

Note

—–

   – dynamic sampling used for this statement

19 rows selected

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.

show entire test script

links used in this post:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/ex_plan.htm

Leave a Reply

Your email address will not be published.