First_rows/all_rows
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: First_rows/all_rows

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    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.

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    what do you mean by "regardless of statistics"?
    stats will always influence the CBO...

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  4. #4
    Join Date
    Dec 2005
    Posts
    195
    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

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    yes, the cbo will be used

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    "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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width