major difference between oracle and ms-access with java code

[mohan shetty]

Hi All,
I am facing a problem regarding performance.This is all about java code which involves a sql query to fetch huge data(in 1000's)from tables.this code with sql select statement works fine in MS-ACCESS(i.e execution of this query takes in seconds)but the same java code with same sql select statement takes 40 minutes or even more for its execution on ORACLE.The query goes like this
select ="e.dataDate, e.reference,e.status,";

from = DBNames.A_TABLE " p, "
+ DBNames.B_TABLE
+ " t,
+ DBNames.WECTM_ENGINE_ALERT_TABLE
+ " e ";

where1="p.id=t.nl"+ " and t.alertId=e.id and e.engineId=";

where2 ="p.id=t.nh"+ " and t.alertId=e.id and e.engineId=";

This where1 or where2 am passing through a FOR loop to make a sql statement.

[Jeanne Boyarsky]

Mohan,
Do you have an index on those tables? The first thing I think of when seeing an order or magnitude difference is that maybe MS-Access has an index while Oracle is doing a full table scan - or worse - 3 full table scans.

You can tune this by running the query through Oracle explain. Explain will tell you what execution plan the database is using. You want to make sure it is using indexes rather than table scans. If it is not, you may be missing an index.