Re: Oracle8 vs. MS Access

Ron Denham wrote:
>
> MS Access requires access to the file, since it is file based. That also means all user must be able to get at the same
> file server (which is an admin nightmare in a large organization) and, the biggie, all users have the ability to overwrite
> the file on the file server. Locking is done at the, you guessed it, the file level. Big downer since you can't have "row
> level locking". Since file access is 100's of time slower than memory access, you'll crap out on a server with more than
> 10 heavy users.

This is partially true, esp. regarding memory vs file access time. However, most MS Access locks are done at a page-level, not file-level. Moreover, you have the choice of different locking strategies in Access:

1. "No locks", which will allow other users to write to a record while another user has it open, and will let you know if a record has changed while you've had it open (choices are to overwrite the record with your changes, copy your changes to the Clipboard and review the other user's change, or discarding changes).
2. "Edited Records" or "optimistic" locking, where you only lock a record while you're editing it. This does *not* lock at a file level (Access databases are often contained in one file - which would mean that the whole DB was locked!), but does lock all records on the same disk page. Therefore, you may end up locking quite a few records, unlike Oracle and most other large RDBMSs which use true row-level locking.
3. "All records" or "pessimistic" locking - all records in an opened datasheet (which can include other tables if it is a multi-table query) are locked. Very restrictive, but safer for data integrity.

> memory by the server at the row level. Since the server can do so many things in memory, it is blistering fast compared to
> a file based database like MS Access. Also, the Oracle server is nearly impossible to hack or crash so it is very safe and
> stable.

True: Oracle scales *much* better to high use and/or multiple users. Access can be hacked, so it's not a solution for sensitive data. However, you can implement security in Access that will prevent trivial hacks from average users, and this can be beefed up somewhat using security on a file server. On the other hand, I've seen relatively critical, multi-user (<10 concurrent users) systems performing very well when designed properly.

I'm primarily an Oracle developer, but have used both for professional systems development. The bottom line? In my opinion, Oracle is a better solution in most cases for medium to large businesses, but Access is still a very good solution for small-scale, desktop databases, especially when the system will not normally be maintained by highly-skilled IT personnel.

It is very easy to get things up & running in Access, Access is extremely cheap compared to the cost of having specialized Oracle development and DBA skills on hand, and it's easy to have Power Users do ongoing development - especially custom reporting - in Access (which can be controlled to protect critical elements of the system if a decent security scheme is implemented).

On the flipside, Oracle can scale *very* high, has excellent, granular control of security, especially with the Secure Server option. Oracle development is easy to do from a large variety of tools that use native drivers - thus increasing speed and reliability. Oracle is pretty much "the" database for most serious development - it has the largest installed base in the industry. Moreover, it runs on a huge variety of platforms and conforms to a large variety of relatively open standards (CORBA, Java, native APIs for a major languages (C, C++, COBOL), strong support with Perl, excellent networking capabilities, integration with Application Server for seamless Web development & deployment, etc., etc., etc.... Oops, my bias is showing here... ;-)

I hope this helps a bit.

-- 
------------------------------------------
Rob Cowitz
Oracle Solutions Consultant, 
MONTAGE IT Services, Inc.
Calgary, AB., Canada
e-mail: cowitz_at_cal.montage.ca
(public key available upon request)
Web: http://www.montage.ca
------------------------------------------




> > Greg Williams wrote:

> >

> > > I have been asked to research the difference between using a Oracle8

> > > database vs. MS Access 97 database.  If you could give me specifics on the

> > > differences and advantages/disadvantages of the two databases I would be

> > > most appreciative.  Security opinions/detials would be a plus!

> > >

> > > Greg Williams

> > > Lambda Consulting

> > > greg.williams_at_lambaconsulting.com