Linked Table Issue to Oracle

[PSULionRP]

We have a strange situation in which a batch job blew up which drops and then
re-builds Indexes on an Oracle Table that is linked via an Access Database.
The job was supposedly fixed and all the Indexes re-created and re-built.
However, now when we go and try and open up this Linked Table in the Access
Database it is taking FOREVER to open up???

We deleted the old linked table in the Access Database and created a "New"
linked Table to the same Oracle Table. And the same thing is happening. Just
taking FOREVER to Open up the Linked Table in Access and the dependent
reports are taking FOREVER as well.

Are we missing something here???

Thanks in advance for your review and hopeful for a reply.

PSULionRP

 

[Jerry Whittle]

I'm an Oracle DBA. How often is this batch job run and what version of
Oracle? Rebuilding indexes is somewhat unnecessary unless the tables involved
have a lot of changes such as deletions, additions, or updates. Often such
rebuilds are just something people "do" that have little real performance
impact such as Compacting and Repairing Access databases daily.

It's possible that the bad batch job messed up the indexes. Here's one way
that the Oracle DBA can find out:

select chr(9) || 'alter index "'||owner||'"."'||index_name||
'" rebuild nologging compute statistics;' cmd
from dba_indexes
where status = 'UNUSABLE'
order by 1;

Are you connecting to the tables or through a view? If a view, have your DBA
run Explain Plan to see if the indexes are being used like you expect.

You should be using Pass Through queries which do the processing in the
Oracle database instead of bringing over the raw data into Access then
running a query. If using Pass Through queries, have the DBA run Explain Plan
on them.

You might find that Oracle is now choosing to do a Full Table Scan instead
of using an index when running your query. Depending on the Oracle version,
it's possible to use a Hint to use the index. Hints are deprecated, but
usually still work, up to Oracle 10g.

Another trick is to have Access open up a table in Oracle on startup using a
form that can be hidden. It can have the connection ready when needed plus
keep the connection open at all times.