Database Query Optimization via IN-List to Join Transformation

The patent segments the IN-List query into multiple individual equality conditions (e.g., column = value1 OR column = value2 OR column = value3) and processes each segment separately through index access paths, rather than scanning the entire table. This segmentation allows the optimizer to use efficient index seeks for each value and combine results, dramatically reducing I/O operations while maintaining complete data retrieval.

The patent introduces an intermediary transformation process that converts the IN-List syntax (column IN (value1, value2, value3)) into an equivalent form that can utilize indexes. The optimizer acts as an intermediary by automatically rewriting the query to use individual equality comparisons with the indexed column, allowing index access without requiring the user to manually specify complex index keys. This intermediary transformation maintains ease of operation while achieving efficient index-based access.

The patent applies preliminary action by having the optimizer evaluate and select the most efficient access path before executing the query. The system pre-assesses available indexes, estimates the cost of different access methods (scan vs. index seek), and chooses the optimal path in advance. This preliminary optimization prevents unnecessary full table scans and reduces computing resource consumption by using index seeks that access only the required rows based on the IN-List values.