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.
links used in this post:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/ex_plan.htm