Re: Informix to Oracle Migration

rkusenet wrote: > "Data Goob" <datagoob@netscape.net> wrote > >> The downside is that the development team must know how to provide >> transactions >> that do not block readers without help from the database engine. I've >> worked >> in one system using SQL-Server where this is probably one of the chief >> complaints, >> during peak usage, lots and lots of blocking writes preventing readers >> from >> getting to the data. > > > I had the same problem in my new job wherein SQL-Server user complained of > blocking. Eventually turned out to be poor design. Well it seems only > Informix > is the engine which automatically creates an index on foreign key. On all > other RDBMSs (DB2/LUW,Oracle,SS,Sybase) one has to explicitly create > an index on FKY. That's what happened in the product I am working. The > developer created FKY and just didn't care about index on FKY columns. > Result was that on non-indexed FKY tables, the table was locked whenever > master-child table join is used. > Since Oracle has versioning feature, lack of index on child table will > not result in > blocking despite table scan involved. > > It is the nature of Microsoft environments to create application abstraction from the database, either physically or logically, because of people wanting to program in OO. This means that many developers may not even know how to understand the complexity created by other programmers, to the point where they can't even figure out what the problem is that creates the blocker. The DBA gets involved using the few tools available such as SQL-Profiler or the sysprocesses table, but even here it is difficult to diagnose, and as such, SQL code review is paramount. But the DBA will get blamed for the poor performance or other errors and omissions created by the developers--it's a thankless job. SQL-Server has limited tuning, limited-to-none log management, so you really need to enforce index usage with table hints, and other enforcement to make sure developers do the right things. Maybe Microsoft will buy an old version of Oracle and come up with a new database engine. LOL.