SQL performance issue

[ocp2b]

I am having a performance problem with some sql scripts.

I have two Windows 2000 machines, same release + service pack. Both have 2 gig of Ram, both have the same CPU type.

One machine is running Oracle 8.1.6 and the other SQL Server 2000.

We created a database on each machine. We created the same tables on each machine (Table A, B and C). We then loaded each table with 2.1 million rows of data. We then created a view of all three tables -> Select all columns from A, union all Select all columns from B union all Select all columns from C. All the tables have an index (columns 1 & 2).

The issue that has come up is why does it take Oracle a combined total of 36 seconds to extract one record from the three tables when it only takes SQL Server 5 seconds? Here is the select statement:

Select col3 from table A where column 5 = xyz;
Select col3 from table B where column 5 = xyz;
Select col3 from table C where column 5 = xyz;

Please note that column 5 is not part of the index. This was done purposely.

Is there anything that I can do (change, reset, add, delete, etc) so that Oracle will perform close to what SQL Server does?

This really annoys me that SQL Server can beat Oracle.

Thanks.

[sambavan]

Set your optimizer to COST. Indexed those colums. Analyze your table and indexes and then have a the

SQL> set autotrace traceonly
SQL> run your query

and see what exaclty oracle does behind the query.

Sam

[roobaron]

Could be doing lots of sorting. Increasing the SORT_AREA_SIZE may help it out.
As above need to see the execution plan for both databases.

Cheers

Paul