Oracle Optimizer modes

There are four Oracle optimizer modes

First Rows

• The first_rows optimizer mode uses Index access which adds additional I/O overhead, but they return rows faster
• Because the first_rows mode favors index scans over full-table scans, the first_rows mode is more appropriate for OLTP systems where the end user needs to see small result sets as quickly as possible

ALL Rows

• The all_rows optimizer mode is designed to minimize computing resources and it favors full-table scans
• Because the all_rows mode favors full-table scans, it is best suited for data warehouses (OLAP), decision-support systems, and batch-oriented databases where intermediate rows are not required for real-time viewing.
• Note: First Rows and All Rows are both CBO modes

Choose and Rule: 

• They both are RBO modes