This content has been marked as final. Show 6 replies
RajeshKanna wrote:The table High Water Mark has not been reset, so Oracle must spin past all the "deleted" rows to ensure it responds with an accurate COUNT=0.
My emp table contains one milion of records.
I had perform the above three queries parallely. After applying the commit operation, To retrive the no.of records
delete from emp; commit; select count(*) from emp;
in that table it takes some more time after that it display the result zero.
I have faced this question recently in one of the interview. why it is taking some more time..
can any one help me?
I have tried this one with one milion records , but it does not takes any time . how will u say watermark is to be present
What have you tried?
Could you please show us?
I am executing the below script..
create table emp(eno number(10)); BEGIN for i in 1 .. 1000000 loop insert into emp values(i); commit; end loop; END; / delete from emp; commit; select count(*) from emp;
Did you read the link provided?
A high water mark is the set of blocks that have at one point contained data. You
might have 1000 blocks allocated to a table but only 500 are under the HWM.
The blocks under the HWM are the blocks that will be read when the table is full scanned.
And after delete, when you count, a FULL TABLE SCAN will happen. Which will scan all the blocks under HWM. So, here 1000 blocks will get read, even if there is no "data". That way your query will take time..
Whats the solution? - Do a TRUNCATE or do a SHRINK.
NOW - Tell us - What is your concern?