FIRST_ROW hint returns no row
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: FIRST_ROW hint returns no row

  1. #1
    Join Date
    Oct 2002
    Location
    CA
    Posts
    67

    FIRST_ROW hint returns no row

    Hi There,

    Environment - Oracle 9i, Solaris

    Here is the sql that works in CNV but not in TST.

    SELECT /*+ FIRST_ROWS */ BUSINESS_UNIT_PO, PO_ID, TO_CHAR(PO_DT,'YYYY-MM-DD'), VENDOR_ID, NAME1 FROM PS_VCHR_PO_VW A WHERE BUSINESS_UNIT_PO=:1 ORDER BY BUSINESS_UNIT_PO, PO_ID

    0 row in TST

    If I replace the hit with ALL_ROWS, I get 772 rows.

    Please advise.
    --------------------------------------
    It's not what the world does to you that matters. It's how you respond...

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    What is the release of oracle?

    Try FIRST_ROWS(10).

    Tamil

  3. #3
    Join Date
    Oct 2002
    Location
    CA
    Posts
    67
    Version 9.2.0.2


    first_rows(10) or (1) did return 772 rows by first_rows by itself stil returns 0 row.

    Any idea?
    --------------------------------------
    It's not what the world does to you that matters. It's how you respond...

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    There are many bugs reported in 9.2.0.2 optimizer.

    Better upgrade to 9.2.0.4 (This is the standard reply from Oracle, to earn $$$$$$$$$).

    Even in 9.2.0.4, there is a bug in a select statement that has a subquery.

    Basically, Oracle Corpn does not have enough intelligent people to test their product. No matter what tools are employed to test a product, you need still intelligent people.

    Tamil

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    renaming a redo log member in 10g can corrupt the database (depend on dba's experience)

    so, oracle needs to get some better beta testers, such a basic feature such a failure

    btw try 9.2.0.5 but seems like 9.2.0.6 is not far away hehe
    Last edited by pando; 05-25-2004 at 03:25 PM.

  6. #6
    Join Date
    Oct 2002
    Location
    CA
    Posts
    67
    I am running schema analyze now and for some strange reason the sql now works.
    The schema was last analyzed on May 14 so it wasn't too long anyway.

    The other test schema that works was analyzed way back in Jan and the same sql works fine.


    FIRST_ROWS is supposed to return row regardless of old the analyze was, in this case 10 day old. The speed is the only thing that will be affected by analyze frequency, correct?
    --------------------------------------
    It's not what the world does to you that matters. It's how you respond...

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by tamilselvan
    Better upgrade to 9.2.0.4 (This is the standard reply from Oracle, to earn $$$$$$$$$).
    I'd hate to see your support contract. My support includes upgrades and patches.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ========================
    I'd hate to see your support contract. My support includes upgrades and patches.
    ==========================

    ????? Support Contract ?????????

    Which one? Is it Platinum or Gold or Silver or Bronze or Aluminum?

    Why does Oracle need all these “metals” ?

    James,

    May be the statistics on the table is wrong ( not upto date). The optimizer is a pure mathematical animal. There is a high posiibility that the animal goes wrong way many times in a forest. And it happened in your case.

    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