First_rows/all_rows

All, I read the below content in Oracle help. I am trying to understand some thing here. Does ALL_ROWS/FIRST_ROWS work regardless of statistics????

ALL_ROWS
The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

FIRST_ROWS_n
The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n number of rows; n can equal 1, 10, 100, or 1000.

FIRST_ROWS
The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.

what do you mean by "regardless of statistics"?
stats will always influence the CBO...

i think he means that even if there are no stats the cbo will still be used if those hints are used.

not sure of the point of the post though

All, What i meant is even if there is no statsitics, CBO will be used if we speicy Hints(ALL_ROWS, FIRST_ROWS_n). This is what i understood.

ALL_ROWS
The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

FIRST_ROWS_n
The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n number of rows; n can equal 1, 10, 100, or 1000.

Quote:

---

Originally Posted by davey23uk

i think he means that even if there are no stats the cbo will still be used if those hints are used.

not sure of the point of the post though

---

yes, the cbo will be used

"regardless of the presence of statistics "

what is missing here is:
Before arriving a optimum execution plan, based on the value of optimizer_dynamic_sampling parameter, oracle computes the statistics on the tables and uses them to compute the plan.

Tamil