informix vs oracle

Hi Gurus , After 7 years with informix and 2 years with oracle , these are my own comments about them. All the comments / corrections are wellcomed: 1. backup / restore informix : the backup set is a complete object . It does not depend anything .It can be used as the only object in order to do restore. oracle : lots of things must be considered. You must know what you do. 'I just want to backup and restore my db ' is not enough. To do a complete restore of the db , first it is necessary to copy all the files to a backup location (including datafiles,control files, undo and redo log?no be careful if you want to recover the db after copying files back , you need current logs so backing up redo logs are not necessary . On the other hand copying entire datafiles before any type of incomplete recovery is very important . (in order to restore a db , copying the files once again huh...) And dont expect the ontape -r command's lovely message: do you want to backup the current logs? No such a thing exist....) If someone who only knows oracle ,doesnt know aynthing about the other databases , this backup method can be the best one. But if you know the other databases restore backup methods, oracle method is much more difficult and complex. 2. Parallel query oracle : parallel query bypasses the buffer cache. it directly reads data from the disk into the user process's pga memory . It also does not check whether a block is cached in memory or not , it reads the disk every time. So same parallel query executed several times makes the same amount of disk io . On the other hand , parallel query architecture is more flexible than informix. It does not depend on tablespace number / partition . And parallel dml , parallel optimizer hints are very rich. (last veison of informix i used is 9.2 so may be informix have some improvments which i dont know) 3. Nologging operations: In oracle , it is possible to do certain operations without generating redo log . These are called unrevorable operations : create table test nologging as (select * from dual) insert /*+append */ into a select * from dual these operations is not logged in redo logs so cant be reovered. But the drawback of such an operation is , if you have to recover the db server or datafile by using redo logs , the entire table becomes useless. Not only these insert statements are invalid but also the whole table. 4. multi-versioning oracle has no dirty read concept but instead multi-versioning . So if session a updates data , session b see the previous version of the recods .But this can also happen for a long running queries!Although no updates take place , lots of different versions can be put in memory especially if your db is a datawarehouse type. Kind Regards, hope

"utkanbir" <hopehope_123@yahoo.com> wrote > 1. backup / restore > > informix : the backup set is a complete object . It does not depend > anything .It can be used as the only object in order to do restore. > If someone who only knows oracle ,doesnt know aynthing about the other > databases , this backup method can be the best one. But if you know > the other databases restore backup methods, oracle method is much more > difficult and complex. Only now I started working with Oracle, but mainly as a PL/SQL developer, besides being SQL Server DBA. Whomsoever I spoken to about Oracle backups seems to give a very complex picture. > 2. Parallel query > > oracle : parallel query bypasses the buffer cache. it directly reads > data from the disk into the user process's pga memory . It also does > not check whether a block is cached in memory or not , it reads the > disk every time. So same parallel query executed several times makes > the same amount of disk io . IS THIS REALLY TRUE? Sounds too outlandish to be true. When are our resident Oracle gurus MarkT and DMorgan when we need them most. well if despite all this Oracle is the leader in the RDBMS market, what does it tell about IT industry :-). I read some where that the same question was asked by some senior Microsoft employee "if MS is so bad, what does it tell about IT industry if it allowed MS to be the 800 pound guerrilla". I have used MS argument against Oracle folks who arrogantly assume their product to be the best purely on market share. My counter argument always been , "then we should also agree that MS products are best for the same market share reason". I don't have to tell how objective they are in their replies.

Article to add to this: http://www.devx.com/IBMDB2/Article/26671 might add some value to your knowledge base... utkanbir wrote: > Hi Gurus , > > After 7 years with informix and 2 years with oracle , these are my own > comments about them. All the comments / corrections are wellcomed: > > 1. backup / restore > > informix : the backup set is a complete object . It does not depend > anything .It can be used as the only object in order to do restore. > > oracle : lots of things must be considered. You must know what you > do. 'I just want to backup and restore my db ' is not enough. To do a > complete restore of the db , first it is necessary to copy all the > files to a backup location (including datafiles,control files, undo > and redo log?no be careful if you want to recover the db after copying > files back , you need current logs so backing up redo logs are not > necessary . On the other hand copying entire datafiles before any type > of incomplete recovery is very important . (in order to restore a db , > copying the files once again huh...) And dont expect the ontape -r > command's lovely message: do you want to backup the current logs? No > such a thing exist....) > > If someone who only knows oracle ,doesnt know aynthing about the other > databases , this backup method can be the best one. But if you know > the other databases restore backup methods, oracle method is much more > difficult and complex. > > > 2. Parallel query > > oracle : parallel query bypasses the buffer cache. it directly reads > data from the disk into the user process's pga memory . It also does > not check whether a block is cached in memory or not , it reads the > disk every time. So same parallel query executed several times makes > the same amount of disk io . > > > On the other hand , parallel query architecture is more flexible than > informix. It does not depend on tablespace number / partition . And > parallel dml , parallel optimizer hints are very rich. (last veison of > informix i used is 9.2 so may be informix have some improvments which > i dont know) > > > > 3. Nologging operations: > > > In oracle , it is possible to do certain operations without generating > redo log . These are called unrevorable operations : > > create table test nologging as (select * from dual)> > insert /*+append */ into a select * from dual > > these operations is not logged in redo logs so cant be reovered. > > But the drawback of such an operation is , if you have to recover the > db server or datafile by using redo logs , the entire table becomes > useless. Not > only these insert statements are invalid but also the whole table. > > 4. multi-versioning > > oracle has no dirty read concept but instead multi-versioning . So if > session a updates data , session b see the previous version of the > recods .But this can also happen for a long running queries!Although > no updates take place , lots of different versions can be put in > memory especially if your db is a datawarehouse type. > > > Kind Regards, > hope

utkanbir compared IDS and that other thing: > 3. Nologging operations: IDS also allows non-logged tables, called RAW tables: The following is from the IDS 10 doc, because I can link directly to the appropriate passage, but is true for IDS 7.31, 8.x, and 9.3 and higher (not certain about 9.2): RAW tables are nonlogging permanent tables and are similar to tables in a nonlogging database. RAW tables use light appends, which add rows quickly to the end of each table fragments. Updates, inserts, and deletes in a RAW table are supported but not logged. RAW tables do not support indexes, referential constraints, or rollback. You can restore a RAW table from the last physical backup if it has not been updated since that backup. Fast recovery rolls back incomplete transactions on STANDARD tables but not on RAW tables. A RAW table has the same attributes whether stored in a logging or nonlogging database. RAW tables are intended for the initial loading and validation of data. To load RAW tables, you can use any loading utility, including dbexport or the High-Performance Loader (HPL) in express mode. If an error or failure occurs while loading a RAW table, the resulting data is whatever was on the disk at the time of the failure. It is recommended that you do not use RAW tables within a transaction. Once you have loaded the data, use the ALTER TABLE statement to change the table to type STANDARD and perform a level-0 backup before you use the table in a transaction. This is from the Administrator's Guide: http://publib.boulder.ibm.com/epubs/html/25122670/admin357.htm Raw tables are quite useful when loading but the lack of indexes, referential constraints, and rollback can make other processing rather tedious. Sincerely, Christopher Coleman President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Medication Management Mediware Information Systems, Inc.