Conjunctions
This is the reference to the conditions in the WHERE clause combined with AND operator. For example, SELECT * FROM STUDENT WHERE CLASS_ID = ‘DESIGN_01’ AND AGE>=18;
There are different combinations of columns can be used in conjunctions – single index, multiple-key index or composite index, multiple indexes (multiple index on different columns) etc. In each of the case, we have to check
- If we can access the record by using any one of the condition which provides better access path i.e.; if AGE>=18 alone can give all the records that matches both AGE>=18 and CLASS_ID = ‘DESIGN_01’, and if it has shortest cost time or vice versa.
- If any one of the index and selection methods described above provides the better cost.
In all the combinations and permutations of indexes and selection methods on conjunctions, we have to select the one with better path and less query cost for the efficiency of the query.
Disjunction
This is the reference to the conditions in the WHERE clause combined with OR operator. This is even considered as queries with UNION operator.
For example,
SELECT * FROM STUDENT WHERE CLASS_ID = ‘DESIGN_01’ OR AGE>=18;
or
SELECT * FROM STUDENT WHERE CLASS_ID = ‘DESIGN_01’ UNION SELECT * FROM STUDENT WHERE AGE>=18;
In this case, indexes will be used when all the search key columns have indexes. It will convert the queries with OR operators to queries with UNION and will use the indexes based on the search key column. Otherwise, it uses linear search.
Negation
It has not equal condition used in the WHERE clause. In most of the cases it uses the linear search method to fetch the records. If the index is present on the search key column then index is used search the records.
For example, SELECT * FROM STUDENT WHERE CLASS_ID <> ‘DESIGN_01’
If we have index created on CLASS_ID then it will use any of the above index methods to fetch the records. Otherwise it will search each record from the beginning of the file.