FIRST_ROW hint returns no row

[james_cc]

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.

[tamilselvan]

What is the release of oracle?

Try FIRST_ROWS(10).

Tamil

[james_cc]

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?

[tamilselvan]

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

[pando]

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

[james_cc]

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?

[marist89]

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.

[tamilselvan]

========================
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