Results 1 to 4 of 4

Thread: Indexes

  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Indexes

    How does Access manage single field indexes verses multiple field indexes? To clarify this question. If you have several fields in a table that you want to index in an Oracle database, you would create a single index out of all of the fields. Oracle will treat any operation on a single field in the compound index as if there was an individual index on the field. Does Access view indexes in this fashion or do you need to put an index on each field? It also follows that if you have to have an index on each field you want indexed, if you also want a compound index, do you have to create this independent of the single field indexes?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Indexes

    In Access, you can have indexes on the individual fields in a combined index, and you can have a different index on the combined fields. For example, you might include a foreign key (indexed Yes, Duplicates OK) in a unique multi-field key.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Indexes

    I wasn't too clear in my question so please let me rephrase this. If you have a combined index does Access use the combined index when working with a single field that is already indexed in the combined index? For example, say you have combined index of: id1 id2 and id3. You carry out some operation that would make use of an index on the field id2. Will Access use the index info from the combined index or do you need to create a single index on field id2?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Indexes

    If you use a single field from a multiple field index in a relationship, whatever index may have been placed on that field is invoked unless whatever you're doing would violate the conditions of the combined index. That was the point of my example. In a unique index, none of the individual fields are indexed as no duplicates (otherwise, there wouldn't be any point to including that field in a multiple-field index) but the combined fields have to form a unique key.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •