Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Unanswered: all_rows vs first_rows vs no hint

    Being more into DB2 I am experimenting a bit with the optimizer hints for ORACLE.

    I have one query performing on two large ORACLE 7.3.4 warehouse tables that runs for more than 3 minutes. This is obviously too long, cause the query eats a lot of resources and is run during the day.

    When I add /*+FIRST_ROWS*/ the query time drops to roughly a minute, but with /*+ALL_ROWS*/ it is very fast, about 5 seconds.

    I can't find the direct difference between the two hints, but they seem to do the same for resp. OLTP and BATCH processing.

    What is the story behind this, any links to good info on the optimizer-use?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: all_rows vs first_rows vs no hint

    From here you can access all Oracle documentation for your version. Look for the Performance Tuning Guide, although the precise name may differ in older versions (7.3.4 is positively ancient!):

    http://otn.oracle.com/documentation/index.html#previous

    FIRST_ROWS means optimise to get back the first rows from the query as fast as possible, although getting all the rows back may take longer. This is suitable for OLTP processes where the user may run a query that will return 1000 rows, but will scroll through them slowly from the top. Rather than wait a minute and get all rows very quickly, it is nicer to wait only a second and then let the other rows arrive more slowly - but still probably faster than you will scroll through them.

    ALL_ROWS means optimise to get back all the rows from the query as fast as possible, although you may wait longer to get the first row. This is suitable for batch processes or reports where all the user wants is the end result, not to see the partial result as it is being built.

    Typically, FIRST_ROWS queries will use indexes and nested loops even for large data sets, whereas ALL_ROWS queries will use full table scans and merge or hash joins.

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Thanks for your comprehensive reply.....
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

Posting Permissions

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