This discussion is archived
6 Replies Latest reply: Feb 11, 2013 9:03 PM by jeneesh RSS

query takes more time

RajeshKanna Newbie
Currently Being Moderated
Hi,

My emp table contains one milion of records.
 

  delete from emp;

  commit;

  select count(*)
  from emp;

  
I had perform the above three queries parallely. After applying the commit operation, To retrive the no.of records
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?
  • 1. Re: query takes more time
    sb92075 Guru
    Currently Being Moderated
    RajeshKanna wrote:
    Hi,

    My emp table contains one milion of records.
     
    
    delete from emp;
    
    commit;
    
    select count(*)
    from emp;
    I had perform the above three queries parallely. After applying the commit operation, To retrive the no.of records
    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?
    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.
  • 2. Re: query takes more time
    jeneesh Guru
    Currently Being Moderated
  • 3. Re: query takes more time
    RajeshKanna Newbie
    Currently Being Moderated
    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
  • 4. Re: query takes more time
    jeneesh Guru
    Currently Being Moderated
    What have you tried?

    Could you please show us?
  • 5. Re: query takes more time
    RajeshKanna Newbie
    Currently Being Moderated
    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;
  • 6. Re: query takes more time
    jeneesh Guru
    Currently Being Moderated
    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.
    In your sample code, when you insert 1Milion records, the data will reside in some number of blocks - Say 1000. Now the HWM is 1000. When you delete the data, HWM will not get reset..

    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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points