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
SELECT ...
FROM ...
WHERE db.province = '&&province'
AND db.city = '&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:
SELECT ... FROM ...
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.