May 26 2012
Firebird vs Oracle – First impressions
I and my team recently managed the development of a huge database with Oracle. The same database already exists and successfully lives thanks to Firebird SQL.
The main goal of this development is the creation of a cross-platform application, able to work both with Firebird SQL and with Oracle at the same time.
We reached the goal after a lot of headache and frustration due to the subtle, and often incredible, differences between the two platforms. In this post I list the main differences we faced to. The next table summarize the major topics, this topics will be better discussed in a next post.
This analysis is not intended to be exhaustive and general for all types development. This comparison is based on the experience of a team that reaches great results following the "best practices" in designing a Firebird SQL and expects similar results, and possibly similar winning strategies, in developing with Oracle .
Topic |
Oracle 11g |
Firebird 2.5 |
| Installation | Cumbersome with a lot of unexpected incidents | Simple and straightforward |
| Resource eating | As much RAM as possible
A lot of services running |
Minimal, proportional to DB size
Two services running |
| Logs | Widespread on the machine
Mainly non human readable |
One log for all |
| Documentation | Huge | Not well structured |
| Support form the community | A lot of discussion forums and blogs
Low quality Difficulties to find the right solution due different approaches and philosophies |
Sparse specific forums
Great support from Firebird enthusiasts |
| Empty strings | Automatically transformed to NULL | Differentiation of empty strings and NULL strings |
| INTEGER | Automatically converted to NUMBER(38) | INTEGER |
| NULLs in expression | NULL operators gives NULL results
String CONCATENATION has different behaviour: NULL treated as empty string |
SQL-92 Compliant: NULL operators gives NULL results |
| Stored procedure as Tables | Possible with "FROM Table()" construct | Possible with "FROM" construct |
| SELECT INTO with empty result set | Fails | Processed with NULL |
| IF EXISTS() | Non present. Workaround with tricks (for loop) | Present |
| SELECT FIRST SKIP or SELECT ROWS | WHERE ROWNUM
Due to the prefiltering effect of the where clause, and odd solution should be used: select * from (select .... order by) where rownum |
SQL-92 compliant |
| SELECT COALESCE() with no rows | Returns empty result set | Executes the COALESCE |
| Index on Foreing Keys | Explicit creation required | Automatically created |
| CONTAINING | Workaround: upper(filed) LIKE upper('%pattern%') | Native |
Comments Off
