Different result size between SELECT * and SELECT COUNT(*) on Oracle

Question

I have an strange behavior on an oracle database. We make a huge insert of around 3.1 million records. Everything fine so far.

Shortly after the insert finished (around 1 too 10 minutes) I execute two statements.

1. SELECT COUNT(*) FROM TABLE
2. SELECT * FROM TABLE

The result from the first statement is fine it gives me the exact number of rows that was inserted.

The result from the second statement is now the problem. Depending on the time, the number of rows that are returned is for example around 500K lower than the result from the first statement. The difference of the two results is decreasing with time.

So I have to wait 15 to 30 minutes before both statements return the same number of rows.

I already talked with the oracle dba about this issue but he has no idea how this could happen.

Any ideas, questions or suggestions?

Update

When I select only an index column I get the correct row count. When I instead select an non index column I get again the wrong row count.

Answer 1

That doesn't sounds like a bug to me, if I understood you correctly, it just takes time for Oracle to fetch the entire table . After all, 3 Mil is not a small amount.

As opposed to count, which brings 1 record with the total number of rows.

If after some waiting, the number of records being output equals to the number that the count query returns, then everything is fine.