Re: Informix vs Oracle. SOme questions.

Hi Jorge,

I am in the process of moving some applications from Informix to Oracle so I think I can satisfy your need for information.

Jorge Torralba wrote:
>
> Our courrent environment consist of Informix ODS. We have been asked to
> learn about Oracle. As an Informix user, I know very little about
> Oracle and have created a list of questions relative to how we work
> around here. Could some of you Oracle gurus please send me some feedback
> on these question ? I gave them to the Oracle reps a few weeks ago and
> have not heard from them yet.
>
> Thanks,
>
> Jorge :-)
>
> ---------------------------------------------------------------------------------------
>
> A rough draft of things our shop floor control system uses and must
> have. Not only because they currently exists. But, because they are
> Beneficial to our operation.
>
> * On occasions we need to recreate a table on the fly Quickly. These
> are situations where seconds and minutes are critical and can mean large
> dollar losses. Does oracle offer a tool to create a SQL statement of
> the table or entire entire database schema from the command line as
> easily as just typing "dbschema -d databasename" ?

You can use

"CREATE TABLE <table_name> AS SELECT * FROM <old_table_name>;"

This will create a copy of the existing table with a different name.

If you only want to copy the structure and not retain the data (assuming that your tables don't have very many rows of data) do the above to create a table and then:

"TRUNCATE TABLE <table_name>;"

This will instantly drop the data without destroying either the table structure or the indexes.

> * Can database and tables be renamed using simple SQL ? "rename
> database abc to efg;"

Yes "RENAME <old_name> TO <new_name>;" -- Same as Informix :-)

> * Sometimes we need to unload filtered data from a table to a flat file.
> we do this in simple SQL by simply executing commands such as "unload
> to filename select * from customer" Can I do this as easily in Oracle
> as I can now ?

"SPOOL <file_name>"   -- note the ommission of the semi-colon on this
"SELECT * FROM <table_name>;"
"SPOOL OFF"

This sequence will create a flat file but, unlike Informix UNLOAD, it will contain fixed length records rather than variable length records. To reload the data back into the same or another table with the same structure use the SQL*Loader utility from the operating system command line or, if using NT, the appropriate menu.  

> * Also, we can unload databases or tables to ASCII or binary using tools
> such as dbexport, unload, onunload and a high performance loader which.
> We have been able to unload 8 million rows, drop the table, recreate it,
> apply 5 indices and reload the data in less than 30 minutes. Can Oracle
> do the same

Based on my experience with three flavors of Informix and about seven varieties of Oracle I think that Oracle can do it faster! (Just use the DIRECT PATH option of SQL*Loader -- I loaded 7 million rows with 6 indexes in under twenty minutes on an NT).  

> * We currently have several options for achieving data replication. from
> one server to another or from one to many. We currently use high
> availability replication which sends logical logs from one server to the
> other thus maintaining a real-time redundant server without having to
> setup any special database actions such as triggers or procedure to
> replicate for us. ( low maintenance ). Does oracle offer similar
> replication.

Yes.

> * Is oracle 8 currently compatible with sql-92 ?

I am not certain, but I think it is full compliance with low level ANSI 92 and has some of the higher level extensions.
>
> * Does Oracle offer the ability to fragment a table by expression or
> round robin over multiple database spaces ? We have bench marked
> parallel processing in our labs with our current product which has been
> using the feature for several years now and have noticed performance
> gains of queries which took 3 minutes plus to 1 (one) second.

In the Oracle world this is called partitioning. (Also in the Oracle world fragmentation is a dirty word used to describe a table with too many extents.) Oracle 7.3 and later support partitioning.

>
> * I understand that when a database instance in oracle is set up, a
> fixed amount of memory is preallocated to the instance. What would
> happen when the system becomes saturated and more memory is needed ?
> Does it allocate memory dynamically ? Do I have to Bring down
> production, to allocate more memory and then restart ? Can I free up
> memory as easily as adding more ?

Oracle does not die when the System Global Area gets filled -- it just gets slow. The solution is to modify the configuration file and then bounce the database. When it comes to eating up memory Oracle is a pig. The best thing to do if you are concerned is to give all of memory, except that needed by the operating system to Oracle. There are three good books on tuning Oracle, one is Oracle Performance Tuning published by O'Reilly and Associates and the second is Oracle Tuning published by Oracle Press, and the third is Oracle 8 Tuning published by Coriolis Group Books. Get at least one of them and study it.  

>
> * On the subject of bringing down the server, is it true that when a
> change to a parameter of the database is needed one must bring down the
> server and restart it ?

That depends on which parameter you are changing. In my experience there is little difference between Informix and Oracle in this regard.

>
> * We put out software release on a regular basis. These release involve
> changes to tables. Currently if I alter a table and add a new column I
> can insert it before or after a specifics existing column. Can I do the
> same with Oracle using basic SQL. "alter table cust add ( state char (2)
> before zip );"

No. When you add a column to a table in Oracle it gets added at the end of the table. This allows you to make dynamic changes to tables without either disturbing their contents or forcing recreation. To solve the problem you mention I suggest that you use views and hide the tables from your customers. When a change was needed you could then provide your customers with a new view with the columns ordered as you prefer.  

> * We use RAW disk partitioning for our database. This increases the
> performance of the IO. Does Oracle offer and SUPPORT RAW disk
> partioninig with oracle 7 or 8 ?

Yes, but many Oracle personnel and many DBAs feel that it doesn't buy enough performance to make it worthwhile. This is because of difference in the way Oracle writes to disk as compared to Informix. (Note that if you plan to use the Parallel Server option (not parallel query, but paralled server) the control files and redo logs (I think) are kept on raw disk.

> * Does oracle offer automatic seamless char to numeric conversion and
> vice versa without the use of additional functions ?

When working within SQL I have always used the functions and do not know if they are required. I guess I am a control freak :-)

If you are talking about flat file or keyboard/screen input and output the conversion is seamless.

> * On our shop floor system everything is real time. Being so, we must
> capture dates and times down to a fraction of a second. these are
> regular data types which we use now. does oracle offer this data type ?
> or the interval type for differences between two time stamps ?

In order to get below seconds you will have to use PL/SQL stored procedures which can go down to 1/100ths of a second. You will note that one difference between Informix and Oracle is that in Oracle there is only one date type. The format is specified on output.  

> * Cursors allow a user to compile a Data Management Statement thus
> improving performance. Does Oracle support Dynamic cursors, meaning a
> user may fetch the last, previous, or an absolute element within a
> cursor ?
> *

Again this can be done with PL/SQL. Bear in mind however that Oracle does not store data in order. All ordering is achieved through the indexes.

Note that all of the above can be specified using either the interactive SQLPLUS program or from a command line.

Regards

Jerry Received on Tue Feb 03 1998 - 00:00:00 CST