pg.ddx.ioUniversity INGRES on Usenet (1985-1995): threads about Berkeley INGRES, QUEL and EQUEL from comp.databases.ingres, comp.databases and net.database
From: Nigel McFarlane <nrm@tusc.com.au>
Subject: Re: ORACLE, MINI-SQL, INGRES Comparison
Date: 1995/11/13
Message-ID: <4865ot$718@jacana.tusc.com.au> (raw)
References: <482700$t5i@carbon.cudenver.edu>
ghharrac@ouray.cudenver.edu (George/Jorge) wrote:
>Hi,
>
>As a user of ORACLE, I am looking for information about what I consider
>to be the major RDBMS and their usage of STRUCTURES ( ISAM, CISAM, HEAP,
>HASH, ....) foreign concepts to ORACLE. How does the usage of these structures
>affect the performance of the system ? I know what theory says, but I am
>look from the users point of view. I also want information of the
>usage of ORACLE tablespace, to how Ingres and Mini-sql handle tables in
>seperate files and their manipulation. What is better, the tablespace
>concept of the 'others'?
>
>George
>
>ghharrac@ouray.cudenver.edu
>gharrach@carbon.cudenver.edu
>
I've used both Oracle and Ingres.
As you point out "the theory" talks about insert/update/delete etc
performance.
User's point of view? Do you mean end user, DBA, or developer?
There's no difference to a SQL user except possibly performance, so
I suppose you mean the DBA as user.
Usage of tablepspaces vs. growing files. It all comes down to
two things: 1) whether you want 'out of space' errors to appear
because of limits the DBA's set, or because of operating system
limits; 2) whether you want the option of multi-disk interleaving
in the database software, or whether you're content to rely on
operating system support for disk interleaving (e.g. 'logical volumes')
There's intra-file fragmentation in either case.
How does usage of structures affect performance of the system:
If we assume an untuned, but well-indexed set of tables, then
obviously there is a performance improvement to be had, otherwise
Oracle wouldn't have added a hash structure for tables. The least
trouble to maintain is just a standard structure for all tables,
e.g. all HEAP, but thats fairly primitive for performance.
Focussing on these structures alone won't let you identify all
performance effects. For my money, rollback segments in Oracle,
even though a pain, give a performance boost over Ingres'
transaction log file, which is still at Oracle 5.1 level of
sophistication. This is a big issue for large numbers of users or
long transactions. Locking and lock escalation is another example.
The number one performance problem to be had is poor query
optimisation due to poor use of indexes. Get indexes on all
primary and foreign keys before even _thinking_ about tweaking
performance with structures, space allocation, dedicated disk,
etc.
regards, Nigel.
--
------------------------------------------------------------------------------
Nigel McFarlane. TUSC Computer Systems nrm@tusc.com.au
666 Doncaster Rd, Doncaster 3108 BH: +61.3.9840.4476
Melbourne, Victoria, Australia. GMT +10 or +11 hours