Select * vs select count(*)
I have 2 schemas within a database, one is an exact copy of the other. One of the views that has been created is behaving strangly.
On schema 1:
doing a "select * from view" lists all the records.
doing a "select count(*) from view" gives the correct count of the records
On schema 2:
doing a "select * from view" lists all the records.
doing a "select count(*) from view" gives a count of zero!
Stats were gathered yesterday for both schemas so are not out of date (not much data has been inserted since).
The above applies for Toad, sqlplus, isql, etc.
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production.
Any ideas what could cause this?
Thanks.