OraFAQ Forum: Performance Tuning » Optimize mode first

Home » RDBMS Server » Performance Tuning » Optimize mode first_rows returns full table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Optimize mode first_rows returns full table [message #221088]

Fri, 23 February 2007 14:16

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?

Report message to a moderator