ORACLE 8.0.3.0.0 is extremely slow vs SQL7 for medium database

I am writing an app which I need to work with both SQL server 7 and Oracle 8x

Here are my problems...

1. with small databases things are very comparible between the two. However when using a medium database (100,000 employees, say) Oracle begins to perform very poorly in comparison with SQLServer 7. Now, I thought that Oracle was supposed to come into its own with larger databases. I am a complete newbie to Oracle so, I imagine that I must start some tweeking to get things really flying.

Here is the SQL being executed

INSERT INTO ACLTable (GroupName, EmployeeCode) SELECT 'X', Employee.EmployeeCode FROM Employee WHERE Employee.EmployeeCode In (SELECT EmployeeCode FROM Employee) AND Employee.EmployeeCode Not In (SELECT ACLTable.EmployeeCode FROM ACLTable WHERE GroupName = 'X')

This executes in about 5 seconds in SQL7 when the Employee table has 100,000 records in it (if the result of the INSERT query is not many rows and about 15-20 seconds if it has to insert 100,000 rows). However with Oracle this query takes ages (over 20 minutes). I had to extend the number of extents to 'unlimited' after Oracle compained that it had not enough extents for rollback (this error happened after about 2 minutes).

Both databases are running on identical machines.

Any ideas?

2) when creating users how can i modify them so that i can issue select statements like 'SELECT * FROM Mytable' rather than 'SELECT * FROM MyTableSpace.MyTable' - i.e. they have a default tablespace to select from (like a default database for SQL Server)? I have tried configuring the default tablespace and granting permission on the relevant objects but this is obviously wrong since I get 'table/view does not exist' unless i specify the tablespace.

Many thanks in advance,

Julian Received on Tue Mar 23 1999 - 12:04:59 CST