Hi All,

I am running into a challenge with some tables which I have linked from an Oracle database into Access. I have been told that these tables probably have millions of lines in them. My question is: Does anyone have any advice in how to efficiently structure queries in order to the information from these large tables or any guidance you may have from past experience?

Thanks for all the help.
Brian

You can create views in Oracle and point to those instead of tables. Note: each view will process on the DB up on the server, so you'll only send information across the network that you actually need.
Otherwise, it's all about the index.

See your Oracle DBA about both.

Thanks for the post. My problem is the DBA with our company, he is nonexistent.

You don't need the DBA if someone will give you authority to do what I mentioned. Unfortunately this someone is usually the DBA. If you have the authority, just do some reading up on-line about views and indexes. If they won't give you authoirty, because it's produciton, see if they'll create a separate oracle test instance with only the tables you need on a separate server. It's easy enough to update the test instance and if you blow it up who cares. Since, it's a test instance on a different server - production won't be affected. Note: do not put the test instance on the same server as production, since a runaway query in test would then grab all the resources, which would impact production.

If you can't do views and/or get a test instance then it's all about the index. You'll have to write any sql to conform with the index or the wait time will kill you. Indexes can be displayed with sql, so you may be able to view them yourself. Otherwise you'll need to contact your DBA again.

If the powers that be won't do either, then you're going to have to find a way to get over it. We called this GOI at my last job, since this happened frequently. Note: the best GOI solution seemed to be beer.

Edited by: Ender on Wed Aug 23 17:20:51 EDT 2006.

Edited by: Ender on Wed Aug 23 17:21:55 EDT 2006.