-
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...
-
What is the release of oracle?
Try FIRST_ROWS(10).
Tamil
-
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...
-
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
-
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 02:25 PM.
-
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...
-
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
-
========================
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|