/Databases
Sort-Merge Joins
Friday, April 29th, 2005Sort-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 [...]
Hash Joins
Friday, April 29th, 2005Lets 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 [...]
Nested-Loops Join
Thursday, April 28th, 2005This 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.
emp is chosen as the driving table for this query
Since there is no where clause on emp a [...]
Different Join Types
Thursday, April 28th, 2005There 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.
General considerations for selecting Database Indexes
Tuesday, April 19th, 2005Clustered Index
Most tables should have a clustered index* since there is little point storing the data in a random order. Some DBMSs have problems with hotspots if you use a clustered index on a column containing a monotonically increasing id or a date column in OLTP databases. SQL Server 7+ handle this quite nicely though. [...]
Firebird - Perhaps in the next incarnation
Tuesday, April 19th, 2005I’ve been tinkering with Firebird in conjunction with .NET. I’ve been using IB Expert which seems to be a reasonably competent UI.
My main problems so far are:
Lack of documentation for use with .NET
Could not get my simple Insert SP to work when called from .NET
None of the VS.NET tools work with it
On a commercial project [...]
Deleting lots of data…
Sunday, March 13th, 2005This question came up recently and there was some discussion about it so I thought I’d summarise here…
Problem
How best to delete a large amount of data from a table. The database in question was Sybase so we’ll talk about that. The concepts are the same for all major databases though the syntax may differ.
Solution
As came [...]
A new Category…
Wednesday, March 2nd, 2005Databases are a bit of a pet subject to me so I thought I’d create a whole new category about them. I’m in the process of creating a course about SQL Tuning so some of the content of that will find it’s way here. I’ll probably other random things about databases as they come to [...]