Archive for the 'Oracle' Category

May 26 2012

Firebird vs Oracle – First impressions

Published under Database,English,Firebird,Oracle

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