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

No comments:

Post a Comment