Curious Informix/Oracle DBAs collaboration
Posted in 2008
Sometimes we see here flames about code portability and I'd like to share this one... Not a big story but is has some curiosities in it... A fellow Oracle DBA is trying to prove he can do something within the Database much faster than the current application... So he set up two tables, imported the data and run a query. The overall time spent was much less than their application, but the results were not the same... The application is using C code, and he asked my help to check the result of some calculations in C. The application is using "float" and after come calculations it "looses" precision, which is normal (IEEE 754 format). In the Oracle DB he used NUMBER() which in that case works like Informix Decimal(). He asked me if Informix would work as Oracle, and I said "yes..." and he asked me how he could "solve" this and I answered: "You could use Informix :)" and he asked "How?!" I showed him that with smallfloat I could reproduce the application behavior, but with DECIMAL I would have the same results as Oracle. I *was joking* when I said he could use Informix, but he took me seriously and I've found myself and him, using HPL to load ~10M records and running the query... Surprise, surprise, the results matched the application results... So he could prove that the process using the database is faster, and by the way, some of the people involved got really worried about the lack of precision in the current application... He was also very surprised because we didn't have to change the query... Let me note that it was a very basic query using mainly case/decode... But in his mind, it would be much harder to "port" something as simple as that... In between we had very fun times discussing some differences... Don't get me wrong... They will use Oracle for two reasons: 1- The application is already using and Oracle database 2- They prefer not to loose precision, so even if they don't implement the process in the database, they will probably change the C code but it was very easy to use Informix to prove an Oracle DBA point of view :)