APPENDIX O A Review of Informix vs. Oracle

Anonymous, but the author used to be a regular of c.d.i.

Hello, again.

I'm back with an answer to your request for an impartial opinion of 
Oracle.  First, some caveats:

1. I have not used Informix 4GL since version 4.1 or the Informix 
engine since v. 5.1, so some of my comments may be invalidated by 
later versions.

2. I found a copy of several messages from the Informix newsgroup 
discussing comparison of Informix and Oracle.  My file is dated 
Apr 19, 1995, so all of the messages are at least that old.  It 
starts off well, with a comparison by yours truly, and then goes 
downhill somewhat as other folks quote still other folks, including 
Gartner Group articles, out of context.  I can send you this 26436 
byte file, if you wish.

Now for my opinions:

1. If you are doing full scale embedded systems, i.e., the users 
only interface to the DB is thru your program, then Inf. and Ora. 
are pretty equivalent.  Taken as *complete* tool kits, the two DBMSs
*have* to cover the same ground, or else one would blow the other out 
of the water.

2. However, if you are doing a lot of ad hoc stuff, then I think that 
Oracle has a better tool set for such things.  Some examples:

a. SQL*Plus provides substitutable parameters. with either automatic 
(Oracle-generated) or customized prompting.  Thus if you want to run 
a test SQL query using several values for some WHERE conditions, you 
can say

FROM   ...
WHERE  db.province  = '&&province'
AND = '&city'

If you use Oracle prompting, the &&variable will be prompted for once 
and then remembered by subsequent runs of the query within a session, 
(and even by other queries that use the same variables), while the &variable 
will be prompted for each time you run.  One SQL query is retained in the 
SQL*Plus buffer, and may be re-executed by simply typing / at the prompt.
If you must use multiple queries, they can be included in a single 
script and then rerun, and the variables will be remembered or not as 
described above.  If your script manually prompts for variables, or 
if it "undefine"s a remembered variable, then that variable will be 
prompted for each time the script is run, but may be used in more than 
one place in the query(ies).

This is very handy for exploring your data, better than having to edit 
your SQL and resubmit it to Informix SQL.  (I presume that DBaccess works 
the same, but I don't know.)

b. SQL*Plus provides powerful output formatting features, so you can 
provide very good looking reports without going all the way to a report 
writer tool or to embedded SQL/C.

c. Oracle provides more functions than Informix and more of them are in 
the engine, rather than in the tools.  For example, last I knew UPSHIFT 
and DOWNSHIFT were I4GL functions, not directly available in ISQL.  But 
the Oracle functions UPPER and LOWER are built into the engine, and are 
thus available to *all* Oracle products.  Thus, I make almost all of 
my parameterized queries work like this:

WHERE UPPER(db.column) LIKE UPPER('&variable')

This eliminates case sensitivity on user input.  The LIKE also allows 
use of wild-card characters, which Informix does just as well as Oracle.

d. So combining a, b, & c, Oracle's ad-hoc tool can provide case insensi-
tive, parameterized, formatted reports, something that Informix required 
ACE to accomplish.  I never used ACE, maybe it is really fast to develop 
ACE reports, but I like the flexibility of SQL*Plus.

e. Oracle provides more functions than Informix.  This is a repeat of c, 
but at a higher level.  Oracle provides tree walking constructs which 
were not available at all in Informix, last time I used it.  The tree 
walking features are sort of a self-join, sort of not.  An example of 
the syntax used on table "emp_data" with columns "emp_name", "job", 
and "mgr_name" is: 

SELECT	LPAD(emp_name,2*LEVEL,' ') employee, job
FROM	emp_data
START WITH job = 'President'
CONNECT BY mgr_name = PRIOR emp_name

The START WITH tells the query to begin with the (presumably one)
employee whose job is 'President', then to list the employees whose 
manager is the president, and then to list those employees' subordinates, 
etc.  Although I have described it as a breadth-first search, it is 
performed as a depth-first search, so subordinates immediately follow
their superiors.  LEVEL is a pseudo-column telling how many levels down 
the tree you are.  LPAD() left pads the first argument with the number 
of repetitions specified by the second argument of the third argument.
Thus the employee column contains an indentured list of employee names,
with each level indented two spaces from the next higher level.

You can also construct queries that walk *up* the tree, to build a chain 
of command from a private all the way to the highest general, tho' the 
LEVEL pseudo-column is not as useful in this context.

f. Starting with version 7 of Oracle, the system privileges have been 
cut much finer.  Instead of CONNECT, RESOURCE, and DBA, there are 90 
or so system permissions.  This is almost too many to manage, so they 
can be grouped into ROLES, and then the ROLES can be assigned, either 
to other ROLES or to users.  Oracle immediately provided CONNECT, 
RESOURCE, and DBA ROLES which are collections of system permissions 
that approximate the old system permissions of the similar names. 
Oject (table, synonym, etc.) access may also be granted to ROLES,
so a ROLE can combine system and object privileges to tightly specify
who a user granted that ROLE can and cannot do.

This level of flexibility is a two-edged sword:  It takes a while to 
set up a really customized set of ROLES, but once you do so, you can 
really control the system well.  For example, you could grant literally 
hundreds of specific levels of access to a new employee, by granting a 
single ROLE to the new user. 

With the exception of item f, all of the features I have discussed were 
available in version 6.0 of Oracle, which was contemporary with Informix 
OnLine 5.1 / Informix 4GL 4.1.  

I am sure that the two DBMS engines have taken turns leap-frogging each 
other with each new release.  I know that Informix had very good cost 
based optimization well before Oracle, but Oracle has come a long way 
in that area.  Etc., etc., etc.