How the query optimizer chooses the a specific plan to execute SQL?

A SQL Statement executes in multiple ways,such as full table scans,index scans,nested loops,and hash joins.

Optimizer considers many factors.

When the user submits a SQL statement for execution, the optimizer performs the following steps:

1. The optimizer generates a set of potential plans for the SQL statement based on
   available access paths and hints.
2. The optimizer estimates the cost of each plan based on statistics in the data
   dictionary. Statistics include information on the data distribution and storage
   characteristics of the tables, indexes, and partitions accessed by the statement.
   The cost is an estimated value proportional to the expected resource use needed to execute the statement with a particular plan. The optimizer calculates the cost of access paths and join orders based on the estimated computer resources, which includes I/O, CPU, and memory.
   Serial plans with higher costs take longer to execute than those with smaller costs.
   When using a parallel plan, resource use is not directly related to elapsed time.

3.The optimizer compares the plans and chooses the plan with the lowest cost.