Last modified: March 09, 2015
Applies to: Windows | Windows Server
The JetIntersectIndexes function computes the intersection between multiple sets of index entries from different secondary indices over the same table. This operation is useful for finding the set of records in a table that match two or more criteria that can be expressed using index ranges.
JET_ERR JET_API JetIntersectIndexes( __in JET_SESID sesid, __in JET_INDEXRANGE* rgindexrange, __in unsigned long cindexrange, __in_out JET_RECORDLIST* precordlist, __in JET_GRBIT grbit );
The session to use for this call.
A pointer to an array of JET_IndexRange structures. Each structure includes a JET_TABLEID that has been set up to hold one of the index ranges to be intersected. For more information, see JET_IndexRange.
The number of JET_IndexRange structures in the array that is contained in the rgindexrange parameter.
Pointer to a JET_RECORDLIST structure. This structure will be populated with enough information to traverse the temporary table with the results from JetIntersectIndexes.
The output buffer that receives a JET_RECORDLIST structure. The structure contains a description of the result set of the intersection.
Reserved for future use.
The operation completed successfully.
It is not possible to complete the operation because all activity on the instance associated with the session has ceased as a result of a call to JetStopService.
It is not possible to complete the operation because the instance associated with the session has encountered a fatal error that requires that access to all data be revoked to protect the integrity of that data.
Windows XP: This return value is introduced in Windows XP.
One of the options requested was invalid, used incorrectly, or not implemented.
This error is returned by JetIntersectIndexes when:
The grbit contained in the JET_IndexRange structure pointed to by any element in the rgindexrange array is not equal to JET_bitRecordInIndex.
One of the parameters provided contains an unexpected value or a value that is inconsistent when combined with the value of another parameter.
This error is returned by JetIntersectIndexes for of the following reasons:
The session handle is invalid or refers to a closed session.
This error is not returned under all circumstances. Handles are validated on a best effort basis only.
It is not possible to complete the operation because the instance associated with the session has not been initialized.
The operation failed because the engine could not allocate the resources required to open a new cursor. Cursor resources are configured by calling JetSetSystemParameter with JET_paramMaxCursors specified in the paramid parameter.
The operation failed because not enough memory could be allocated to complete it.
JetIntersectIndexes can return JET_errOutOfMemory if the address space of the host process becomes too fragmented. The temporary table manager will always allocate a 1MB chunk of address space for every temporary table created regardless of the amount of data to be stored. JetIntersectIndexes will create one temporary table for each JET_IndexRange specified in the rgindexrange parameter, and one temporary table for the output in JET_RECORDLIST.
It is not possible to complete the operation because a restore operation is in progress on the instance associated with the session.
It is illegal to use the same session from more than one thread at the same time.
Windows XP: This return value is introduced in Windows XP.
It is not possible to complete the operation because the instance associated with the session is being shut down.
The operation failed because the engine could not allocate the resources required to cache the indices of the table. The number of indices whose schema can be cached is configured using JetSetSystemParameter with JET_paramMaxOpenTables specified in the paramid parameter.
The operation failed because the engine could not allocate the resources required to cache the schema of the table. The number of tables whose schema can be cached is configured using JetSetSystemParameter with JET_paramMaxOpenTables specified in the paramid parameter.
The operation failed because the engine could not allocate the resources required to create a temporary table. Temporary table resources are configured using JetSetSystemParameter with JET_paramMaxTemporaryTables specified in the paramid parameter.
On success, a new temporary table is returned that contains the bookmarks of the records that match the criteria represented by each of the input index range descriptions.
On failure, the temporary table containing the results will not be created. The state of the temporary database may be changed. The state of any ordinary databases in use by the database engine will remain unchanged. The current position of the JET_TABLEIDs provided to this function may be changed.
JetIntersectIndexes can be used to efficiently filter the records in a table by multiple criteria if those criteria can be expressed in terms of the secondary indices over that table. For example, consider that you have a very large table containing people. The table can have columns for their user id, first name, last name, and so on. Suppose that each of these columns is indexed separately and that the primary index of the table is over user id. If you wanted to find everyone whose first name starts with an A and whose last name starts with G, you would perform the following steps:
Open a new cursor on the table, and set that cursor to use the index over the "first name" column. Then setup an index range for all people whose "first name" started with 'A', and build a JET_IndexRange struct that contains this cursor.
Repeat step 1 with a new cursor on the "last name" index for all people whose "last name" started with 'G'.
Pass these criteria to JetIntersectIndexes to compute the result into a temporary table.
Traverse the temporary table and retrieve each of the records that pass the criteria by bookmark.
The temporary table containing the result set is a simple table with one column that contains the bookmark of each record that passed all the criteria used to compute the intersection. The result set is sorted in the same order as the primary index and contains no duplicate entries. The application can enumerate the results of the intersection by enumerating the rows in the temporary table, retrieving the bookmark for each result using JetRetrieveColumn, and then visiting the record in the database by calling JetGotoBookmark with that bookmark on a cursor positioned on the primary index.
The temporary table returned by JetIntersectIndexes can only be scanned in a forward direction. It should also be closed via JetCloseTable when the scan has been completed. For more information about temporary tables and how they work, see JetOpenTemporaryTable.
JetIntersectIndexes is generally an efficient and convenient way to filter records based on multiple indexed criteria. However, there are some important tips that should be followed to maximize the usefulness of this feature. If you know that one of the criteria is so restrictive that the resulting index range has very few records then it is probably better to simply walk that index range and filter the records at the application level. Further, if you know that you have criteria that are much less restrictive than other criteria in your intersection then you might consider dropping those much less restrictive criteria from the intersection. Finally, if you know that one of the criteria is not at all restrictive such that the resulting index range is almost as large as the primary index then it is unlikely that intersecting with that index range will benefit (reduce the size of) the result set. In all cases, you should be selecting criteria in a manner that will take the fewest possible index entries on input and generate the most specific set of bookmarks on output for maximum performance.
Requires Windows Vista, Windows XP, or Windows 2000 Professional.
Requires Windows Server 2008, Windows Server 2003, or Windows 2000 Server.
Declared in Esent.h.