-
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??
-
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
-
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;
-
if you dont have PK then obviously it is going to take much longer!
-
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
-
reading index is faster than table, why you dont try to use autotrace and see why
-
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
-
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"
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|