Hello, I am using Firebird more than 10 years. Before I start with firebird I had an Oracle DBA course. It was very good expirience for me and much of that knowledge helps me to better understand Firebird. There is one realy important thing that Oracle has it and it is important for Firebird too.
When I am using subqueries in my query than when I gave name to result with an alias I could not use that alias value in my where clause. Maybe I was not clear as I should be but here is an example in Firebird: select something,anotherthing,(select sum(thirdthing) from second b where b.something = a.something) total from a where (select sum(thirdthing) from second b where b.something = a.something) > 10 in Oracle it looks like select something,anotherthing,(select sum(thirdthing) from second b where b.something = a.something) total from a where total> 10 and subquery executed only once but in Firebird subquery is executed twice. When you have very complex subquery and possible two or three times using total value in where clause it makes great impact on perfomance and of course it is very bad query but in Oracle it works like charm. If it is possible to incorporate in Firebird optimization like this it will be small amount of work but big improvement. If one has better solution (idea) with this state of Firebird optimizer I would like to know. Best regards, Djordje
