Access Path
Before applying your join operators, you first need to get your data. Here is how you can get your data.
Note: Since the real problem with all the access paths is the disk I/O, I won’t talk a lot about time complexity.
Full scan
If you’ve ever read an execution plan you must have seen the word full scan (or just scan). A full scan is simply the database reading a table or an index entirely. In terms of disk I/O, a table full scan is obviously more expensive than an index full scan.
Range Scan
There are other types of scan like index range scan. It is used for example when you use a predicate like “WHERE AGE > 20 AND AGE <40”.
Of course you need have an index on the field AGE to use this index range scan.
We already saw in the first part that the time cost of a range query is something like log(N) +M, where N is the number of data in this index and M an estimation of the number of rows inside this range. Both N and M values are known thanks to the statistics (Note: M is the selectivity for the predicate AGE >20 AND AGE<40). Moreover, for a range scan you don’t need to read the full index so it’s less expensive in terms of disk I/O than a full scan.
Unique scan
If you only need one value from an index you can use the unique scan.
Access by row id
Most of the time, if the database uses an index, it will have to look for the rows associated to the index. To do so it will use an access by row id.
For example, if you do something like
SELECTLASTNAME, FIRSTNAMEfromPERSONWHEREAGE = 28 |
|---|
If you have an index for person on column age, the optimizer will use the index to find all the persons who are 28 then it will ask for the associate rows in the table because the index only has information about the age and you want to know the lastname and the firstname.
But, if now you do something like
SELECTTYPE_PERSON.CATEGORYfromPERSON ,TYPE_PERSONWHEREPERSON.AGE = TYPE_PERSON.AGE |
|---|
The index on PERSON will be used to join with TYPE_PERSON but the table PERSON will not be accessed by row id since you’re not asking information on this table.
Though it works great for a few accesses, the real issue with this operation is the disk I/O. If you need too many accesses by row id the database might choose a full scan.
Others paths
I didn’t present all the access paths. If you want to know more, you can read the Oracle documentation. The names might not be the same for the other databases but the concepts behind are the same.