There are two types of query optimizer Rules-based optimizer and Cost-based-optimizer

Rules-based optimizer: Uses a set of predefined rules to determine query optimization. Not useful for complex quires where many execution plans involves.

Cost-based-optimizer: It selects the execution path that requires the least number of logical I/O operations and the lowest cost for the completion of the query.