Optimization Goals and Approaches

Oracle only lists two hints in this category: ALL_ROWS and FIRST_ROWS( number_of_rows ). These are mutually exclusive. If you happen to be drunk while programming and inadvertently write both hints in the same statement, Oracle will go with ALL_ROWS.

In mathematical optimization nomenclature, these two hints affect the objective function. ALL_ROWS causes Oracle to optimize a statement for throughput, which is the minimum total resource consumption. The FIRST_ROWS hint does not care about the throughput and instead chooses the execution plan that yields the first number_of_rows specified as quickly as possible.

Note

Oracle ignores FIRST_ROWS in all DELETE and UPDATE statements and in SELECT statement blocks that include sorts and/or groupings, as it needs to fetch all relevant data anyway.