Not getting count of rows
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Not getting count of rows

  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Not getting count of rows

    Hi, My table 'A' has 11 million rows . I created temp table 'B' same as table A and inserted rows upto 4 million from table 'A' using where caluse.
    The data was inserted fine without any errors, But when Iam trying to do a row count on table 'b'.
    selct count(*) from b.
    But it is not giving me any output. It has been more than 30 min but still there is no output. Table A rowcount gives me in few seconds. Can any one let me know what the problem could be??

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    if you count from other session and you have not issue commit then for your counting session there is no rows

    if your table b has NO primary key it will use Full Table Scan which is slower if you are counting the whole table

  3. #3
    Join Date
    Oct 2003
    Posts
    5
    I did give the commit after inserting. I am logged into the same user in which I created the table, but still I am not able to get the row count. THe primary keys are disabled for table A and table B.
    Table B do not have any indexes or any constraints.

    I created table B as ..
    Create table B (col1 number, col2 varchar(10)........ 15 columns)
    TABLESPACE TS012 PCTUSED 75
    PCTFREE 20
    INITRANS 1
    MAXTRANS 255
    STORAGE (
    INITIAL 2000048k
    NEXT 40000K
    MINEXTENTS 3
    MAXEXTENTS 1024
    PCTINCREASE 0
    FREELISTS 8
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
    )
    LOGGING
    NOCACHE
    NOPARALLEL;

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    if you dont have PK then obviously it is going to take much longer!

  5. #5
    Join Date
    Oct 2003
    Posts
    5
    THe table A does not have primary key which has 11 million rows but can get the row count in seconds, but the table B which has 4 million rows do not give the row count.
    Thanks

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    reading index is faster than table, why you dont try to use autotrace and see why

  7. #7
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    If u don't have primary key then I would suggest you to analyze the table then do count(*), it would start using FFS instead of FTS.

    HTH

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by bhallar
    If u don't have primary key then I would suggest you to analyze the table then do count(*), it would start using FFS instead of FTS.

    HTH
    Do u know wat is FFS? and when will it be used ?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    he probably means IFFS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width