The access path is the way in which the Query Processor accesses each table that is to be searched (as defined in your query).
There are three types of access path that can be used:
- Scan—To obtain the values required for your query, the Query Processor has to read every row in the relevant table. Scan is an inefficient type of access path.
- Non-Unique Lookup—To obtain the values required for your query, the Query Processor has to read several, but not all, rows in the relevant table (there are several rows with the same value for a column in the table, for example, there could be many rows with the same value for the ParentGroupId column). Non-Unique Lookup access paths can only be used with Index Columns.
- Unique Lookup—To obtain the values required for your query, the Query Processor has to read one specific row in the relevant table (there is only one row with the required value, for example, a search for an item’s Id would mean that the Query Processor has to read the Id column for 1 row in the object table). Unique Lookup is an extremely efficient type of access path.
To determine which type of access path has to be used for each table that is referenced in your query, the Query Processor examines the access sub-clauses in your query (see Access Sub-Clauses).
At the end of the SQL Join Optimization phase, the Query Processor will have calculated access paths for every possible permutation of the Join Order. So, for a query that searches two tables, there will be two join orders (Table A, Table B; Table B, Table A) and two access paths (one for Table A and one for Table B).
The access paths and join order for a permutation are referred to collectively as an Access Plan.