Access Index

[alex]

Hello all,

using Access '03...

I sometimes use Access in conjunction with ORACLE (through an ODBC).

If I create a table in ORACLE, set indexes on that table, then link
the table to Access, will Access recognize the indexes?

I know the answer is No if I import the table...I didn't know if the
answer was the same with a linked table.

....not that it matters, but the table changes daily, contains between
20 and 40 thousand records, and is quite fat!

Thanks,
alex

 

[John Spencer]

I believe that the indexes will be used. I'm not sure about ORACLE.

You could obviously test, write a query that will bring back just one record.
If it is really fast then the index is probably being used.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

If I create a table in ORACLE, set indexes on that table, then link
the table to Access, will Access recognize the indexes?

It may depend on what you mean by "recognize". Access takes advantage of
indexes (in native or in linked tables) to speed sorting and searching; what
it won't necessarily do is change the order in which records are presented.
You'll still need to use a Query with a sort on the desired field - it's just
that the sorting should be faster.

 

[alex]

It may depend on what you mean by "recognize". Access takes advantage of
indexes (in native or in linked tables) to speed sorting and searching; what
it won't necessarily do is change the order in which records are presented.
You'll still need to use a Query with a sort on the desired field - it's just
that the sorting should be faster.

Thanks John (both) for the help.

It may depend on what you mean by "recognize". Access takes advantage of
indexes (in native or in linked tables) to speed sorting and searching;

That's exactly what I meant by recognize.

Historically, I import the ORACLE table every day (without any
indexes). I could save a few steps by writing index code in my ORACLE
script(s) and then link the table to Access instead of importing it.
I was advised in this group that even if I create indexes in ORACLE,
once the table is imported, Access will not utilize them. Sound
right?

I run about a dozen queries off this table, utilizing about 30 fields.

alex

 

[John W. Vinson]

Historically, I import the ORACLE table every day (without any
indexes). I could save a few steps by writing index code in my ORACLE
script(s) and then link the table to Access instead of importing it.
I was advised in this group that even if I create indexes in ORACLE,
once the table is imported, Access will not utilize them. Sound
right?

If the table resides in Oracle (linked) then the Oracle indexes will be used.
If the table resides in Access (imported) then any Access instances will be
used.

Importing a table from outside Access will NOT import indexes, you'll need to
recreate them in Access.

I'd go with linking.