From: Nigel McFarlane Subject: Re: ORACLE, MINI-SQL, INGRES Comparison Date: 1995/11/13 Message-ID: <4865ot$718@jacana.tusc.com.au> X-Original-Message-ID: <4865ot$718@jacana.tusc.com.au>#1/1 X-Deja-AN: 119194063 references: <482700$t5i@carbon.cudenver.edu> content-type: text/plain; charset=us-ascii organization: TUSC computer Systems Pty. Ltd. x-url: news:482700$t5i@carbon.cudenver.edu mime-version: 1.0 newsgroups: comp.databases,comp.databases.oracle,comp.databases.ingres,comp.databases.theory x-mailer: Mozilla 1.1N (X11; I; HP-UX A.09.04 9000/867) 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