Optimizer plays a very crucial role while selecting a search path for the query because on the basis of the search path the data will be returned to the user in a stipulated time period which help in optimizing the query and the database performance too, however while we talk about the optimizer whether giving priority to storage index or local index, there are several factors comes into the role.
(1) The first and foremost condition to choose the correct search path for the query is that the statistics of both table and index must be up to date.
(2) Storage index is most effective when the data is evenly distributed.
(3) Since storage index put extra maintenance overhead on the database so while choosing storage index over local index, the optimizer considers the maintenance overhead and the impact of it on the entire database’s performance.
(4) The index type such as b-tree, bitmap etc. also influences the optimizer decision to decide whether to give the priority to storage index or local index while executing a query.
(5) Index cardinality also plays a very important role because the data will higher cardinality value will always be beneficial while filtering data.
(6) Query pattern is also one of the factors that comes into the role while deciding whether to gives the priority to storage index over local index.
(7) Query selectivity or where clause is also one of the factors which decides how many rows will be returned, so if the storage index significantly mitigate the number of rows returned then the optimizer gives the priority to the storage index over local index.
(8) Since our optimizer is a cost-based optimizer so it analyzes the cost of creating and maintaining a storage index while selecting a search path for the query.
Final Thoughts: There is no strict rule to gives the priority to
storage index over local index, the priority always depends upon several
factors while choosing the search path.
