Home » RDBMS Server » Performance Tuning » Optimize mode first_rows returns full table
Optimize mode first_rows returns full table [message #221088] Fri, 23 February 2007 14:16 Go to next message
coleex
Messages: 2
Registered: February 2007
Location: US
Junior Member

I reinstalled a crashed production database two days ago. We were experiencing slow application response time so I took the liberty of changing the optimize mode to 'FIRST_ROWS_100'.

All seemed well but the tester said some junk data was being fed to the app.

He ran this query

Select * from CR_VIEW where loan_id = 51332;

Result: 1400 rows

As you can see every row in the underlying table showed up despite the use of the where clause.

However if you run this query,

Select count(*) from CR_VIEW where loan_id = 51332;

Result: count 2

When I changed the optimizer back to CHOOSE this problem does not occur. I am using Oracle 9.2.0.1

Any ideas?

Re: Optimize mode first_rows returns full table [message #221089 is a reply to message #221088] Fri, 23 February 2007 14:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://www.oracledba.co.uk/tips/9i_first_rows.htm
Re: Optimize mode first_rows returns full table [message #221091 is a reply to message #221088] Fri, 23 February 2007 15:38 Go to previous message
coleex
Messages: 2
Registered: February 2007
Location: US
Junior Member

Looking thru the doc now.

Thanks,
Eric
Previous Topic: Queries not running - pls help
Next Topic: DBMS_STATS.GATHER_SCHEMA_STATS - Suggestion
Goto Forum:
  


Current Time: Sun Dec 11 02:33:55 CST 2016

Total time taken to generate the page: 0.07006 seconds