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

5
votes

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.

3
sqloracle
Are you running both selects in the same session? How are you inserting the rows? Is this reproducible (that is, it happens every time you insert into the table?How do you know how many rows are returned by select * from TABLE ? Do you scroll through them in a client or IDE? What is the behaviour of select count(*) from (select * from TABLE)? - APC
No the both select statements uses different sessions. What do you mean with how do I insert? We use an normal INSERT Statement LIKE INSERT INTO TABLE (COLUM1, COLUMN2...COLUMNX) VALUES (1, 2, ...X) . Yes it can be reproduced and happens also during other inserts. We have an count option in our software that can simply count all rows that are returned. I will test your statement. - Andreas Rehmer
The result from the statement SELECT COUNT(*) FROM (SELECT * FROM TABLE) give me the correct row count. - Andreas Rehmer
Yes, but in the same time window when select * from TABLE gives you the wrong row count? - APC
@APC Yes this is correct. I executed SELECT COUNT(*) FROM (SELECT * FROM TABLE) and this gave me the correct row count. After this i directly executet only the statement SELECT * FROM TABLE and i got the wrong row count. - Andreas Rehmer

3 Answers

1
votes

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.

0
votes

Have you already verified with these things:

1- Count single column instead of * ALL to verify both result

2- You can verify both queries result by adding where clause and gradually select more rows by removing conditions so that you can get the issue where it is returning different value from both.

-1
votes

I think you should check Execution plan to identify missing indexes to improve performance. Add missing Indexes and check the result.

Why missing Indexes are impotent:

To count row, Oracle engine no need to go throw paging operation. But while fetching all the details from a table, it requires to go through paging.

And paging process depends on indexes created on a table to fetch the data effectively and fast.

So to decrease time for your second statement, you should find missing indexes and create those indexes.

How to Find Missing Indexes:

You can start with DBA_HIST_ACTIVE_SESS_HISTORY, and look at all statements that contain that type of hint.

From there, you can pull the index name coming from that hint, and then do a lookup on dba_indexes to see if the index exists, is valid, etc.