First_rows/all_rows

[pranavgovind]

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.

[mike9]

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

[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

[pranavgovind]

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.

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

[davey23uk]

yes, the cbo will be used

[tamilselvan]

"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