David Hayes

Sort-merge joins work in a similar way to hash joins in that each table is accessed independently. This time instead of using a hash the two rowsets are sorted on the join key. Once both rowsets are sorted they are merged on the join key using a merge join algorithm.

Sort-merge joins have the same advantages as hash joins but are far less robust since both rowsets need to be loaded into memory and sorted before the join can occur. You should only use this join method if a hash join is not available (Oracle databases using the Rules Based Optiomiser)

Uncategorized /Databases

Lets use the same query we looked at when talking about nested-loop joins and see how that would be processed using a hash join.

SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno

A hash join is different to a nested-loop join in that the tables are accessed independently and then rows from each table are matched using the join condition.

The steps taken to execute this query are as follows:-

1. Firstly a full table scan is performed on dept (being the smaller table)
2. The results of this table scan are placed in an in memory hash
3. Then a full table scan is performed on dept
4. As each row is processed the corresponding row is looked up in the hash of dept

So in pseudo-code this looks like:-

For Each deptrow in dept
  Add deptrow to hash (keyed on dept.deptno)
End For

For Each emprow in emp
  Look up deptrow from hash using emp.deptrow as key
  Combine columns from emprow and deptrow
  Return combined row
End For

Clearly this approach is not as robust as the the nested-loop join since it requires scratch memory (or disk, if the rowset is large enough) space. This could cause poor performance or possibly even error due to lack of space. Hash joins should only be considered if you are sure the smaller rowset will fit in memory

Ok, so if hash joins are less robust than nested-loop joins when should you consider using one?

• Hash joins are useful in cases where you have one large rowset joining to a small rowset. You then end up with the small rowset cached in RAM being joined to the large rowset.
• Hash joins can be better in situations where you are joining several unfiltered tables since the two smallest tables can be cached in RAM for fast keyed access.

Obviously you should test performance using a hash join and a nested-loop join before deciding which is best for your query.

Uncategorized /Databases

Looks like Nokia are firmly pitching for the top of the stack when it comes to high tech phones with the release of their N-series phones (N70, N90 and N91). I think I’m still going to pick up a Nokia 6230i when they are (hopefully) released next month

Comparison of all 3 new phones

/Technology /Technology

This is probably the simplest and most robust approach for joining two tables.
Lets take the query

SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno

as an example that should be familiar to those with an Oracle background.

1. emp is chosen as the driving table for this query
2. Since there is no where clause on emp a full table scan is required
3. “For each row in emp look-up the corresponding department”
4. This look-up will be carried out with a unique index scanon the primary key of dept
5. The rowid from the previous step can then be used to retrieve the actual row from dept
6. The database then combines the rows from emp with the joined rows from dept and returns the result

In pseudo-code this would look like:-

For Each emprow in emp
  For Each key in dept.primary_key_index
    If emprow.deptno equals key.deptno
       Fetch deptrow from dept
       Combine columns from emprow and deptrow
       Return combined row to client
       Exit For
    End If
  End For
End For

Hopefully that should make it fairly obvious how this join method got it’s name.

As can be seen from the pseudo-code a nested-loop join only needs to know about the current row it’s processing so it doesn’t need to make use of any temporary space on disk and it requires very little memory. This makes nested-loop joins very robust and scalable to very large resultsets.

The choice of join order will obviously make a huge difference to how quickly the query will execute. Given a choice it is always best to drive from a small set of rows (whether from a small table or from the results of a very selective filter) since that will limit the number of iterations in the outer loop.

You can also see in this simple example how a unique index scan can improve efficiency since the inner loop can be terminated when a matching row is found

/Technology /Databases, /Technology

There are 3 main ways a database can choose to execute a join between two tables. In no particular order they are:

• Nested-loops Join
• Sort-Merge Join
• Hash Join

I’ll take each of these in turn, explain the differences and in what circumstances you might pick one over another.

/Technology /Databases, /Technology

You can finally order your copy of Visual Studio 2005 Beta 2 if you’re based in Europe (it wouldn’t let me earlier in the week). I’ve placed my order but it will take up to 28 days!

/Technology /Technology

I hope never to have to work with any of the Office apps again (in an automation sense) but this Introduction to the Office 2003 Primary Interop Assemblies Sample Launcher looks very useful for anybody out there who is using them

/Technology /Technology

Apparently it’s possible to make fire using only a coke can and a bar of chocolate (and a nice sunny day of course)
Coke and Chocolate Fire

/Technology /Stuff, /Technology

I went up to Milton Keynes at the weekend to try out some boards. Since going on the McNab Advanced Technical Clinic I’ve significantly changed the way I board. I’ve moved from wanting a very stiff and stable board (I ride a Burton BMC at the moment) to wanting a far more flexible board. I have a part share in Ruth’s Burton Michi which I really enjoy riding so when I saw that The Snowboard Asylum have Albin Chaos boards on their Demo List I knew I had to go and try it.

So I turned up at TSA and handed over my credit card as deposit and they handed me a slightly scuffed (but in otherwise excellent condition) Albin Chaos board. They also looked after my board for me while I was on the slope which was handy.

I had’t been to MK for quite some time so I’d forgotten how short it is, pretty much 4 turns and you’re at the bottom! Oh well, they did have a learner rail and a small kicker open (this was too near the top of the slope, not really enough time to get your speed and balance right). I had a few warm up runs before going over the rail and jumps. The board handles really well, it’s incredibly flexible and really light (I kept on over rotating it). I would say it’s similar in weight to my BMC and much, much lighter than the Burton Michi.

When my hour was up I decided I’d buy it if I could get the right price, after a little haggling I managed to get TSA down to £260 for the demo board! Looking forward to getting it out on a proper slope. Maybe a little glacier boarding this summer…

This does mean I now need to sell some boards, expect to see the following on ebay this winter:

• A battered but rideable Burton BMC 162
• A Burton Motion 151 (in good condition)
• A Burton Floater 159 (in good condition)

/Snowboarding /Snowboarding

Microsoft have released Windows XP Pro 64. You can upgrade from a kosher 32 bit version!

/Technology /Technology