Oracle Compared with other Mid-Size Solutions

[Quehay]

I'm not yet acqainted with Oracle although planning to study it in earnest soon.&nbsp;&nbsp;For small to midsize applications<br>(&lt;20,000 records) how does Oracle compare with MS Access<br>vis a vis ease of form development, relational lookup, report generation, and functional issues such as speed, reliability, and security?&nbsp;&nbsp;When referring to Oracle is it necessary to distinguish between PC and mainframe versions, at least with regard to the development/programming environment?&nbsp;&nbsp;Thanks for your insight. . .

 

[MikeLacey]

You don't, in general, need to worry about which version of Oracle your application uses. Accessing an Oracle database from VB or PL/Sql is the same whether the DB in hosted on an NT server or Unix or VMS or Netware.<br><br>In my experience it's not the number of rows in the DB that is the limiting factor when choosing between MS Access and Oracle. I've found that it's much more significant to look at the number of (concurrent) users that will be logged in and using the DB.<br><br>With Access:<br><br>(a fine product - don't get me wrong) you can have problems if more than about 25 users are reading and writing. If one user per day turns off his/her PC without shutting down correctly you will have database corruption problems.<br><br>Against that you have to consider ease of use - Access is *very* easy to use.<br><br>Oracle:<br><br>Muliple users updating your data just isn't a problem for Oracle (or SQL Server, Informix for that matter) as it's designed to cope with thousands of concurrent users. If you need a DB that is available 24 hours a day then Oracle does tha nicely.<br><br>Against that you have to consider ease of use - Oracle demands that you know *something* about it right from the start. Start getting into hundreds of users plus online backups and you have to know quite a bit more.<br><br>Reporting: isn't such a problem - there are quite a few reporting tools available. You can, if you wish, use Access as a reporting front end onto an Oracle DB (we do) by defining the Oracle tables as External and accessed via ODBC.<br><br>I hope this is of use to you, please get back to us if you need more information etc.<br><br> <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[Quehay]

Mike thanks for your input!&nbsp;&nbsp;What is the preferred choice of front end?&nbsp;&nbsp;I know that Oracle has the means for developing forms in place (and know nothing about it) but you mentioned VB--do you prefer developing the forms in VB?<br>If I develop a db that works well in Access would the forms be a good front end when migrating to Oracle (this is the intermediate to long range plan) or would it be better to start from scratch for the next phase?

 

[MikeLacey]

I only know VB and (some) Oracle Forms so my experience isn't exactly vast.<br><br>Having said that:<br><br>We use VB for all user-interface stuff and Perl (with the DBI) for batch processing.<br><br>I have had some work done by contractors that started off life as an Access application. I didn't do it myself but the forms exported from Access OK - it's all live now actually.<br><br>It's probably best to re-do the code behind the Access forms just on the basis that code from a prototype should be re-visited, at least, before it goes into production; error checking and things tend to get left out of prototypes somewhat.<br><br>I understand, and I'm not an Access person myself, that Access does quite a bit for you - populating screen fields, updating the current record and such. You should bear in mind that that all needs to be done &quot;by hand&quot; in VB. <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[Quehay]

Thanks again Mike!&nbsp;&nbsp;Yes, Access forms make the creation of the recordset and the movement, count commands, etc., automatic and invisible, which makes for rapid form development but does remove the explicit awareness and control of how a recordset is being handled.&nbsp;&nbsp;As far as I know the code behind wizard-created forms is &quot;in-Accessible.&quot;&nbsp;&nbsp;We have an Oracle dba in house and I'll eventually meet with him to discuss the migration; I first wanted to check with sources in what has proved to be a very helpful and reliable forum (TekTips).

 

[CRoberts]

Here is something else to consider, how many will be accessing the same, or adjacent records. The overall uber bosses decided that IT is too slow (and we are) to service all their needs.&nbsp;&nbsp;So, various offices were given the okay to use Access 97.&nbsp;&nbsp;One Office used Access 97 in a work flow situation.&nbsp;&nbsp;Less than six users were always trying to access the same (or adjacent) record.&nbsp;&nbsp;Very bad, we had weekly corruption on those data files.&nbsp;&nbsp;Access 97 has page locking, not row locking, and does not handle locking very well. This should be factored in your decision if this could also be a problem.

 

[Quehay]

Hey thanks CRoberts!&nbsp;&nbsp;Fortunately I'm developing a sophisticated system but will be able to have just one or two users (the basic system serving as template for separate, multiple instantiations)and use exclusive mode.&nbsp;&nbsp;Our Oracle DBA informed me that the Windows environment is best served by VB as the forms source--apparently Oracle's form creation is kind of clunky.&nbsp;&nbsp;<br><br>Thanks for the general orientation and look at key issues.

 

[MikeLacey]

Agree with your DBA - although I wouldn't call Forms clunky <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[DBAwhosaysNIE]

I would!<br><br>Oracle makes by far the best RDBMS on the market (also carries a healthly price tag) but most of their tools are, IMHO, overpriced POS (ex:&nbsp;&nbsp;Designer vs. ERWin, Reports vs. Crystal/Impromptu, Forms vs. VB/InterDev, Discoverer vs. Hyperion, etc.).&nbsp;&nbsp;If your co wants a single vendor solution (i.e. you HAVE to use Oracle tools) then I'd look at WebDB or JDeveloper.<br><br>Not trying to be confrontational but my experience has been that I can get more functionality for less money from other vendors tools.

 

[MikeLacey]

Hi DBAwhosaysNIE, nice to see a new nickname in the Oracle forums &lt;smile&gt; <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[DBAwhosaysNIE]

Hi, Mike,<br><br>Thanks for the kind greeting in spite of my reply! <br><br>I actually found this board looking for mySQL help (I just got handed responsibility for it; never touched it before last Friday) and figured I'd check out the Oracle action while I was here.

 

[MikeLacey]

Non-Oracle = &quot;more functionality for less money&quot; -- DBAwhosaysNIE<br><br>&nbsp;&nbsp;&nbsp;&nbsp;probably<br><br>Oracle = &quot;lots of users, lots of programmers, support for very large DB's&quot;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;again, probably &lt;grin&gt;<br> <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[avivit]

Oracle = &quot;lots of users, lots of programmers, support for very large DB's&quot;.
Can anyone details as to the db size which oracle can handle but access can't???
Thanks in advance

 

[grayhairDBA]

MS Access db isn't in the same league as Oracle! It's been my experience the Access chokes at around 500k rows. By comparision, I've worked on numerous Oracle databases in the 100 million row range with a size of 1 terabyte and up.

I'm currently getting ready to deploy a dot.com Oracle (8.1.6) database on Sun hardware with an initial size of 4 terabytes and a projected size of 150 terabytes with 6 months. In this example Access wouldn't even hold my user file.

Access is a nice product for small (think desktop) apps but not much else.

Maybe this helps??

 

[avivit]

grayhairDBA, Thanks for your reply.
&quot;Access chokes at around 500k rows&quot;. How can you measure the size of &quot;rows&quot;? Do you mean the size of the whole db?
I have a db that holds 1700 rows in the main table.
At the moment the db weights 450 kb on my hardisk (when closed if it matters).
What do you say about that?
And what do you mean by &quot;choke&quot;?
If I have to replace the access db with sql server or oracle db, will I have to learn new stuff/
Right now I use asp, vbscript, sql.
Thanks in advance

 

[grayhairDBA]

Hi avivit,

Good points, I guess I wasn't too clear on my definitions.
Perhaps this redefinition will help
1. The reference to 500k rows is the number of rows in the
in the database, not the length of each row.
2. By 'choke' I mean Access may abort or hang. In either
case it would require a re-boot with a possibility of
a corrupted database :-(
3. You are correct in stating you will have something to
learn with either SQLServer or Oracle.
Personal Opinion:
If you use VB...etc and want to use Access in
conjunction with another RDBMS SQLServer would be
my first choice...

I hope this helps.

 

[avivit]

Thanks grayhairDBA.
The truth is I still do not understand hoe measuring the whole rows size. I only understand about measuring the size of the whole db, or COUNTING the no. of rows in a table.
How can you do otherwise?
Will you help again?

 

[grayhairDBA]

avivit,

Rowsize or average rowsize for all rows in a table can have huge impact on overall database performance. I can't give you definitive answer as related to Access but I can give you some observations and guidelines that apply to RDBMS's in general.

1. You need to know the average rowsize for all rows in
the table in question. Some RDBMS's, such as, Oracle
will do this for you, if not you will have to do it
via a script. The general requirements are:
a. determine the internal storage representation
for each data type supported by your RDBMS.

b. for each column in a table apply this internal
size based upon it data type. This is rather
easy for fixed values, such as, DATE, SMALLINT,
INTEGER, NUMERIC, CHARACTER, etc..., however,
when variable length columns are involved it
gets a little harder since a column may contain
1.....n characters which in turn can make each
row a different length. When the average
length is determined for each row in the table
calculate the average length for all rows in
the table.

2. Compare the average row length to the data block size
that the database was created with. For example, a
block size of 2048 and an avg row size of 250 will
allow each block to contain approximately 8 rows of
data. This means that a single I/O will return can
return 8 rows.
NOTE:
For gross estimation purposes I ignore any overhead
within a block, such as, header information or
storage maps.

If you find that the avg row size is larger than the
block size, multiple I/O's will be required for each
row retrieved (not a good thing!!!). There are times
that you may have to just live with it due to 3rd party
software or design requirements:-(

3. As a general rule I create my databases with a block
size based upon their intended purpose.
OLTP/Ad Hoc 4k
Batch/Ad Hoc 8k
OLAP/Data WS 16k +

4. Be aware that any of the above planning is and must
be based upon available RAM and RDBMS operating
constraints. It's been my experience that insufficent
RAM will beat a system to death due to paging or
swapping so I always start my performance evaluations
based upon the machine configuration and then work
toward the database.

I realize that this is kinda long-winded and may confuse
more than help but I'll be glad too help you with any
questions within my humble ability.

 

[avivit]

&quot;humble&quot;??
I wouldn't say that.
Thanks, I'll try to implement it.